How to set up cross-regional AWS RDS replica for PostgreSQL

Question

I have an RDS setup for PostgreSQL in ASIA and would like to have a read copy in the USA.

But, unfortunately, just found from the official site, that only RDS for MySQL has a cross-regional replica, but not for PostgreSQL.

And I saw this page introduced other ways to transfer data to and from RDS for PostgreSQL.

If you are not buying EC2 to install PostgreSQL yourself in the USA, is there a way to synchronize data from ASIA RDS with US RDS?

Answer 1

It all depends on the purpose of your replication. Does this provide a local data source and avoid network latencies?

Assuming your goal is to have replication across multiple regions, you have several options.

User instances of EC2

You can create your own EC2 instances and install PostgreSQL so that you can customize replication behavior.

I registered the master-slave replication setting with PostgreSQL on my blog: http://thedulinreport.com/2015/01/31/configuring-master-slave-replication-with-postgresql/

Of course, you lose some of the advantages of AWS RDS, namely the automatic reservation of several AZs, etc., and now you suddenly have to be responsible for maintaining your configuration. This is far from ideal.

Biphasic fixation

An alternative is to create replication in your application. One approach is to use a database driver that can do this, or perform its two-phase commit. If you use Java, some ideas are described here: JDBC - connecting multiple databases

Use SQS to unblock database records

Okay, so this is the one that I would personally prefer. For all your records in the database, you must use SQS and have background recording scripts that list messages from the queue.

You will need to have a writer in Asia and a writer in the US regions. To publish to SQS by region, you can use the SNS configuration, which posts messages in several queues: http://docs.aws.amazon.com/sns/latest/dg/SendMessageToSQS.html

Of course, unlike two-phase commit, this approach is error prone, and your American database may fail. You will need to complete the reconciliation process - a simple one could be pg_dump from Asia and pg_restore in the USA on a weekly basis to re-synchronize it, for example. Another approach can do something like a fix for reading Cassandra: every 10 are read from your database in the USA, deploy a background process to run the same query with an Asian database, and if they return different results, you can start the process to play some messages.

This approach is common, in fact, and I saw it on Wall St.

---

So, take your battle: either create your own EC2 instances, or take responsibility for the configuration and devops (yuck), implement a two-phase commit that ensures consistency, or reduces sequence requirements and uses SQS and asynchronous writes.

Answer 2

Now it is directly supported by RDS .

An example of creating a cross-region replica using the CLI:

aws rds create-db-instance-read-replica \ --db-instance-identifier DBInstanceIdentifier \ --region us-west-2 \ --source-db-instance-identifier arn:aws:rds:us-east-1:123456789012:db:my-postgres-instance