Postgres Pro
Downloads
Home   >   mailing lists

Thread: Recommendations for migrating PG 9.3 RDS across regions

Recommendations for migrating PG 9.3 RDS across regions

From
Joshua Ma
Date:
I'm currently looking into how we might migrate an AWS-hosted database (via RDS) from us-west-2 to us-east-1. This isn't natively supported by RDS right now, so I was wondering if anyone has had success with 3rd-party tools. (I'm also on 9.3, unfortunately, so I lack some of 9.4's replication features.)

I can always do a pg_dump and pg_restore, but ideally we do some sort of streaming replication to minimize downtime. Ideally, we'd set up replication, turn off writes to the primary, promote the replica, flip DNS, and resume with newly promoted replica as primary. The DB we're migrating is ~100GB in size - not too huge, but large enough for pg_dump to take awhile.

For starters, will I run into issues with Londiste, Slony, or Bucardo, since RDS has more restricted permissions?

Is one of Londiste, Slony, or Bucardo obviously better than the others for this task? At first glance Bucardo seems the most straightforward, but that may just be due to the docs being easier to glance through. (I've also never used any postgres replication outside of the native replication, so it's possible that some of these tools aren't applicable...)

Any tips/links would be much appreciated.

Best,
Josh
ᐧ

Re: Recommendations for migrating PG 9.3 RDS across regions

From
Jim Nasby
Date:
On 10/17/15 5:24 PM, Joshua Ma wrote:
> Is one of Londiste, Slony, or Bucardo obviously better than the others
> for this task? At first glance Bucardo seems the most straightforward,
> but that may just be due to the docs being easier to glance through.
> (I've also never used any postgres replication outside of the native
> replication, so it's possible that some of these tools aren't applicable...)

londiste and I believe Slony require a superuser connection in order to
manipulate some settings. Actually, Slony requires shell level access
because its logging trigger is in C, so unless the extension is already
available I don't see Slony working. I know that you get an account
similar to superuser on RDS, so it's possible that it'd work.

That said, can't you use binary replication to an EC2 instance in west-2
and then cascade that replication to an EC2 instance in East? AFAIK
that's supported. I think you can replicate into RDS as well.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com