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?

4 Answers4

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...

Josh Kupershmidt
  • 2,516
  • 16
  • 19
2

I recently did this on a multi terabyte Heroku Postgres database moving it to AWS Aurora Postgres with basically zero downtime (only requiring dyno config variable changes and restart).

The process was to generate a log shipped replica on an EC2 instance, promote it and switch the app to it, then use the EC2 Postgres instance as a source for logical replication to Aurora.

I have this fully detailed, in much more detail than I can on StackExchange, in this post.

https://thecodinginterface.com/blog/heroku-postgres-migration-to-aurora/

SciGuyMcQ
  • 143
  • 6
1

It looks like AWS DMS (Database Migration Service) supports migrating data from external SQL instances. I was able to set up a migration task that replicates data and changes from Heroku to RDS. Whether it actually works for me is yet to be seen ;)

--- EDIT ---

Looks like services like DMS don't work with Heroku, because they require more privileged roles than Heroku allows on their databases: https://help.heroku.com/E10ZZ6IJ/why-can-t-i-use-third-party-tools-to-replicate-my-heroku-postgres-database-to-a-non-heroku-database

Murphy Randle
  • 111
  • 3
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

Purefan
  • 131
  • 7