How to migrate your PostgreSQL database from Heroku to Amazon RDS

I don’t have any right for this image, shamelessly taken from this interesting post about migration from Heroku to AWS

First of all, this is an adaptation of the post published by Sébastien Saunier from Le Wagon: https://www.lewagon.com/fr/blog/how-to-migrate-your-heroku-postgres-database-to-amazon-rds

Why a new article, then? Because why not, and because I went through a few problems with the link above that led me to simplify the process.

I my case, it concerns a Ruby on Rails application, but I guess the process would remain identical for any other framework.

Before you start

• Dump and save your database
• Activate the maintenance mode of your app:

heroku maintenance:on -a <app_name>

New database

Create the new database instance on Amazon RDS. The configuration depends on your needs. For example in my case, I needed:

• PostgreSQL
• Use-case: production
• Instance class: db.t2.medium
• With read-only replica

Pay attention to the version of PostgreSQL, which should match your source database (the one you want to migrate).

While doing it, using Amazon RDS’ wizard, create a database with the same name as your source database.

Once the instance is initialized, check that the security group has a rule for incoming traffic. Open the corresponding security group, usually named:

rds-launch-wizard-<number> (<some-id>)

Add a rule for incoming traffic:

• Type: PostgreSQL
• Protocol: TCP
• Port: 5432
• Source: anywhere (0.0.0.0/0 and ::/0)

Check access to the created database

Check that the instance is working, with psql for example or any PostgreSQL client:

psql -U $RDS_ROOT_USER -h $NAME.$ID.$DATACENTER.rds.amazonaws.com --dbname=$DATABASE_NAME

With:

• RDS_ROOT_USER is the root username you created with the wizard
• NAME, ID and DATACENTER are given to you by Amazon (look for the Endpoint in the database instance’s details)
• DATABASE_NAME is the name of your database

Add the RDS certificate to your application

In order to let your application talk to RDS, you have to use a SSL certificate:

cd /path/to/your/app
curl https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem > ./config/rds-combined-ca-bundle.pem
git add config/rds-combined-ca-bundle.pem
git commit -m "Add Amazon SSL certificate"
git push heroku

Dump the Heroku database locally

Dump your database and store the data in a file:

heroku pg:backups capture -a <app_name>
curl -o /tmp/latest.dump `heroku pg:backups public-url -r prod`

Load the dump on RDS

Use pg_restore to load your data in your new database:

pg_restore --verbose --clean --no-acl --no-owner -h $NAME.$ID.$DATACENTER.rds.amazonaws.com -U $RDS_ROOT_USER -d $DATABASE_NAME /tmp/latest.dump

Once the operation is finished, you can use your PostgreSQL client to connect to the database and check your data.

Switch the app to RDS

This is the tricky part. You have to kill the Heroku database because Heroku won’t let you override the DATABASE_URL environment variable of your application.

So, assuming you use the heroku-postgresql addon:

heroku addons:destroy heroku-postgresql -a <app_name>

Then set the DATABASE_URL environment variable:

heroku config:set DATABASE_URL="postgres://$RDS_ROOT_USER:$PASSWORD@$NAME.$ID.$DATACENTER.rds.amazonaws.com/$DATABASE_NAME?sslca=config/rds-combined-ca-bundle.pem"

The PASSWORD is the same as your source database.

Deactivate the maintenance mode:

heroku maintenance:off -a <app_name>

Now your can check that everything is working well :)