One of the tasks every developer dreads is migrating a database from one service to another. It just always seems like the process is so finicky that one misstep can lead to unexpected downtime and—the worst nightmare of all—lost data. But sometimes, the need to migrate is unavoidable.

Inspired by our own late nights filled with database-fueled anxiety and hair loss, we are offering this concise guide for migrating a PostgreSQL from Heroku’s managed PostgreSQL Database-as-a-Service (DBaaS) to Amazon Relational Database Service (Amazon RDS). We hope you find this guide easy to use, and that with it, your next database migration will be more like a cup of calming chamomile tea than a stress-inducing quad-shot latte.

While preparing for the migration, you should have an idea of how long it will take to import a database dump into a new Amazon RDS instance. With this number on hand, you can come up with a migration plan—including how much downtime you need.

While downtime makes the migration easier, unfortunately there are plenty of us who work in industries where customer expectations, application requirements, or even government regulations limit or prevent us from planning for downtime. If that is the case, you will need to plan for a migration method that avoids downtime.

You are one of the lucky ones, and your request for downtime was approved. Here are the steps you will take to migrate your PostgreSQL database from Heroku to Amazon RDS.

A database dump is a full, granular export of everything in your database, including users, table structures, foreign relationships, and the data itself. It is literally everything. Every major database has a method of doing this. With PostgreSQL, the usual method is to use 

However, with Heroku, you will want to use their wrapper around the 

. The resulting backup file will be in a binary format with a UUID string as its name—e.g. 

. You will want to rename it so it has a 

Follow these steps to create and populate a new test Amazon RDS instance.

1. Create a brand new Amazon RDS instance that will be used for your new production database. Make sure to choose the instance class that meets or exceeds your requirements.

2. If you do not have a target database already created in your new Amazon RDS instance, run the 

 command line program in your shell like this:

 with the URL given to you by the Amazon RDS tool. Replace 

Note that this will prompt you for a password before it allows you to connect. Use the password you set up for the PostgreSQL installation when you created your new instance.

3. Once you are at the PostgreSQL prompt, run this command to create your database:

Replace {db-name} with the name you are using for your database.

5. Make sure your Heroku PostgreSQL database dump file is available on the computer where you are running the PostgreSQL command line tool.

6. From your shell again, run the pg_restore command like this. Note that there are a lot of options here, so keep reading to see what they all do.

 with the URL for your new Amazon RDS instance

 with the username you are using to connect to the database

 with the name of the database you created in step 3

with the number of parallel processing jobs you want to run—try using the number of cores your system has

with the path to your Heroku PostgreSQL dump file

You will be prompted for a password; enter the same password you used in step two.

7. Run your data validation scripts to make sure everything is imported correctly.

Checking that Your Database is Ready for Migration

Before migrating your application configuration, you need to spend some time making sure your migrated database is in a nice, clean, properly working state.

Here are a few items you might want to watch for:

If you have auto-increment on any columns in your tables, make sure it works as expected.

Check all of the data validation scripts in your test suite, and get them ready to run on your database dump.

Double check indexes and foreign keys. Make sure they are working as expected.

Deploy a test application that connects to your migrated database, and make sure it is working properly.

Finally, as you walk through these steps, don’t make the assumption that you have to jump straight to a production migration.

Only after all of this testing is complete—once all of your concerns are put to bed—will it be time to perform the production migration. Note that when you are ready to execute the migration, you will want to put your application into maintenance mode 

Configure Your Production Application for the New Database

Finally, you have migrated your database, checked that it is ready, and even connected a test application to it. It is time to migrate your production applications to their new configuration.

Configure your applications with new connection settings for your Amazon RDS instance.

Deploy your applications’ new configuration and restore your application from maintenance mode.

Like I mentioned earlier, there are some situations where you need to migrate 

 downtime. Unfortunately, there is no simple and easy solution for avoiding downtime on a migration. In fact, it can be a very complex operation, usually requiring an approach that is customized for each situation.

Typically, this approach requires modifying your application to write to multiple databases at the same time. The idea is to replicate changes between the databases and to make sure that any changes to one database are also being applied to the other. You might be able to try setting up 

 to synchronize data between your servers, but at the moment, 

it doesn’t appear that Heroku supports this functionality

In terms of working with the new Amazon RDS instance, the basic process will look much like executing a migration 

 downtime. However, when it comes to running your data validation scripts, instead you will run a script or application that pulls new data from your existing Heroku production database.

You want to make sure that your databases are completely in sync when you deploy your new applications that access both databases at the same time. Your application will want to 

 data from just one database, while doing all updates, deletes, and inserts on 

Once this is running smoothly, and your data validation process is reporting that the databases are in sync, you can deploy a new application configuration that only reads and writes to your Amazon RDS instance.

You can then remove all mention of the old Heroku database from your codebase.

As you can see, migration without downtime is a very lengthy process and requires lots of preparation, testing, dry runs, and QA. Luckily, Flightcontrol has done this before! If you need help to build your plan and execute your downtime-free migration strategy, Flightcontrol can help.