How to migrate postgreSQL on EC2 instance to AWS RDS?

Question

I have a large PostgreSQL DB (around 100GB) which is on EC2. I want to migrate it to RDS.

How can I achieve this with minimal downtime?

Answer 1

You can't do it with minimal downtime, or not easily anyway.

RDS doesn't support importing base backups and doing streaming replication to/from outside servers.

See Getting WAL from AWS RDS and Moving a standalone DB to Amazon RDS.

You'll have to dump and reload.

If you're keen, you could instead set up Londiste or a similar tool to stream changes from the standalone server into RDS, with the Londiste pgq daemon and the subscriber daemon both running on AWS EC2. The subscriber would connect remotely to RDS over the PostgreSQL protocol. This lets you use trigger based replication to keep the DBs in sync, giving you a shorter outage window. You still have to disable writes on the old DB and make sure Londiste is fully caught up before switching over, though.

• jwadsack: RDS Read Replicas were announced about two days after your post, including a suggestion for using Londiste for building up the replica. Although I don't know how you switch over without disabling writes, as you point out.
• Craig Ringer: @jwadsack RDS read replicas are not a way to get WAL into or out of RDS, which is what this is about. The article you links to mentions using Londiste for that, as I did above. Read replicas will do absolutely no good for migrating from EC2 to RDS, as this question asks.
• jwadsack: Thanks for the clarification. I had assumed one could set up a read replica to an external server (based on things I had read elsewhere). But I couldn't figure out how to do that in RDS because it you have to set up the master in RDS and create a replica of that. And now that I read that article more closely, I see you are right. There's no mention of Read Replicas for data import.

Answer 2

Amazon has a tool for doing exactly this, called the RDS Migration Tool, and there is also AWS Migration Service. The migration tool has to run in windows, however, last I looked.

• Gonzalo Vasquez: now the tool also runs on other OS, at least I've tried it in OSX