15

I have a heroku postgres database and want to migrate it to Amazon RDS to save cost. What's a way to do so with minimum downtime? Usually this involves replicating database in real time and then promoting the replicated DB as the main DB.

I know I can use a follower database to migrate DB within heroku, and I can use read replica database to migrate DB within Amazon RDS. Is there a similar method to create database replication of heroku DB that lives in my own Amazon RDS?

migrated from stackoverflow.com Jan 7 '14 at 1:30

This question came from our site for professional and enthusiast programmers.

  • 2
    you can make a slave in RDS when everything is replicated remove the heroku master promote RDS to Master. – Sam D Jan 7 '14 at 0:20
  • 2
    @SamD I didn't think Amazon supported PostgreSQL slaves that receive from outside databases yet. Have they added that? I don't see it in docs.aws.amazon.com/AmazonRDS/latest/UserGuide/… . If it's been added, a documentation link would be greatly appreciated. How do you feed it the pg_basebackup to start with? And deal with differing major versions? – Craig Ringer Jan 7 '14 at 0:56
3

Pretty sure you are going to have to perform a good old-fashioned dump-and-restore here. Amazon doesn't seem to offer any other import options than loading in a dump:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html

and even if they did, AFAICT the only export options offered by Heroku are pg_dumps (i.e. not basebackups or the ability to set up an external hot standby node):

https://devcenter.heroku.com/articles/heroku-postgres-import-export

If you're lucky, you're using a Postgres version new enough to support both parallelized pg_dump (9.3+) and parallelized pg_restore (8.4+), which will make your job a bit quicker. And hopefully you can afford to take a maintenance window during which you lock out writes to your site while the switchover is underway...

1

You can connect your current app to the RDS database and have it insert in both Heroku and RDS, I don't mean to set up replication, just to insert in both servers.

Once this is set up you will have new records stored in both databases.

It should be easy enough to see which records are missing (for example: in table1 all records with id 100 or lower are missing) by reading the first row in the RDS database. Then writing a simple export script shouldn't be too difficult unless you have a lot of tables.

Hope it helps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy