Setting Up Cross-Region Replication of AWS RDS for PostgreSQL

~ Oleg Dulin

As of today AWS RDS for PostgreSQL1 does not offer cross-region replication. Short of switching to one of the RDS offerings that do support it, there is a few options to consider.

1. Custom Configured EC2 Instances with Master-Slave Replication

Master/slave light rail
Master/slave light rail

This setup sacrifices the benefits of AWS RDS service in exchange for greater control over replication settings. In this setup, one region hosts a master PostgreSQL host, and another region hosts a slave which can also act as a read-replica2.

Advantages

Greater control over replication settings.

Disadvantages

  • Give up all the advantages of running in AWS RDS environment.
  • Writes can only be performed in the master region.

2. Software-defined Two-phase Commit

Commitment Photo credit: Ed Schipul
Commitment
Photo credit: Ed Schipul

In this setup there are two independent AWS RDS instances. The application, however, utilizes a two-phase commit protocol3 to guarantee that all writes make it into both databases in a transactional fashion.

Advantages

  • Simple configuration
  • Does not sacrifice any of the AWS RDS advantages

Disadvantages

  • Responsibility for ensuring that writes make it into all regions fall onto the application itself.
  • Increased application code complexity.
  • Write performance is sacrificed since all regional databases must participate synchronously.

3. Asynchronous Writers

Fanning Out Photo credit: Tim Haynes
Fanning Out
Photo credit: Tim Haynes

In this approach each region hosts an asynchronous writer that listens on an SQS queue4. All writes are published on the SNS topic that is configured with all regional writer queues as subscriptions5. When the application running in any of the regions wants to write into the database it publishes a message on this SNS topic which then fans it out to all of the regional SQS queues.

Advantages

  • Simple configuration
  • Does not sacrifice any of the AWS RDS advantages
  • Does not sacrifice write performance

Disadvantages

  • Subject to software bugs
  • Subject to SNS and SQS bugs and outages
  • No guarantee of consistency
  • Requires a mechanism for periodically reconciling differences between regions
  1. AWS RDS for PostgreSQL ↩
  2. Configuring Master/Slave Replication with PostgreSQL ↩
  3. Distributed Transactions and the Transaction Manager ↩
  4. Amazon Simple Queue Service Documentation ↩
  5. SQS Queues and SNS Notifications – Now Best Friends ↩

Published by Oleg Dulin

Chief Software Architect, Staff+ Engineer, CTO, Trusted Advisor. I build PaaS and write my thoughts about it. All opinions are mine and do not represent my employers or customers.