Brutal-force Migration of Postgres from Azure to AWS (or vice versa)

by Jia Chen

At Softmax Data, we often work with a variety of cloud infrastructures; from AWS, Ali Cloud, Google Cloud to Azure. Occasionally, we have to migrate RDS from one cloud server to another. Our favorite is Postgres, given its superior performance and flexibility in storing data. In this short blog post, I want to show how to move the entire Postgres from one cloud server to another with brutal force. Keep in mind, this migration method, although fast, may come with some risks; for example:

  • Since we will be copying files over the Internet, there is a chance some data may go missing
  • Because we are copying files over the Internet, there are security risks. Although you can mitigate it by encrypting the data and using a checksum, which I do not plan to cover as the instructions are readily abundant on Google.
  • If you need to ensure there is no interruption, this is NOT your best choice.
  • Your new database server (at least temporarily) has to have the same version as the old database server. Otherwise, version conflicts can cause a big headache.

You may also face the challenge to migrate Postgres from one server to another. There are typically several ways you can do this:

  • pg_dumps and copy .csv or .sql files over to another server and reload the data into a brand new database. It could be a quite daunting task as you may have a massive database with lots of tables. Second, you need to re-establish a lot of the roles, extensions, rules, triggers, etc. If you are very clear of these subtle details, you should go with it.
  • Using a Master/Slave/Standby scheme to move data from one server to another and then change the slave/standby server to the master. This requires many configurations. It could also be quite slow when the replication happens; The good news is that this will ensure your database will not experience any interruption. You can also do this incrementally to ensure the integrity of the database.
  • Finally, the brutal copy of the raw database files from one server to another. This is relatively fast and requires a minimal amount of configuration.

You must ensure the following prerequisites are met before you proceed with the brutal-force method:

  1. You must have SSH access to the file system, where Postgres is hosted
  2. You must have access to the data folder where Postgres data is stored
  3. You must have two Postgres servers where the version must match (10 vs. 10, 9.6 vs. 9.6). If you want to move an old Postgres to a new one, you must install the old version on the new server first before you can upgrade it to a newer version.
  4. You must have SSH access to the new server and privileges to copy files to the new server via SSH.

Before we continue, let’s understand the basics.

Postgres essentially is software that manages a large volume of data. In such sense, it needs to write data to local disks for fast access. These data files proprietary to Postgres just like a .docx file to Word. Our strategy is to copy all these data files from one server to another and then make the new server load these data files.

Here are the basic steps:

  1. run show data_directory; in your PSQL or Postgres client and mark where your data files are stored in the file system.
  2. run show config_file; in your PSQL or Postgres client and mark where your configure file is.
  3. Stop your Postgres server (sudo systemctl stop postgresql)
  4. Backup your existing Postgres server. You can do this by snapshotting of the data disks or pg_dump. I found it faster to use snapshots. For a 2TB disk, our time on Azure was about 5 minutes and 2 minutes on AWS.
  5. Find where data directory is. There are two ways you can do that.
  6. run show data_directory; as mentioned in step 1
  7. open up your postgressql.config (in step 2) and check a variable called data_directory
  8. Make sure you now have access to the folder. In most cases, the ownership of this folder is postgres . Because later you will use SSH to copy the files, you may need to change the ownership of this folder temporarily to the user that you intend to use for the SSH copy.
  9. Go to the new server, install Postgres (again, you MUST make sure it is the same version as the old one for now) and then shut it down.
  10. Repeat step 1 to find out where the folder is and back it up. ( I found using rsync is the fastest and most effective way to back up the entire folder.
  11. Now use SCP on the old server to copy the folder from the old server to the new one. The schema is similar to: scp -i private.key /datafolder username@newhost:/datafolder
  12. Depending on the size of the disk and number of files, this could take quite a long time. In our case, it took about 8 hours to copy the 3TB of data. You can also compress the folder, encrypt it and copy it over and unencrypt and unzip. This extra step could greatly ensure the integrity and security of the copying operation.
  13. Once the copying is done, go to your Postgres config file on the new server and edit the data_directory variable and point it to the folder you copied over.
  14. Make sure the ownership of the new folder is postgres:postgres (otherwise your Postgres won’t start).
  15. Start your Postgres service.
  16. Check your data, install extensions.
  17. Have an IPA