A Replication Cheat-Sheet

So many types of replication, so little time! What kind of PostgreSQL replication should you use?

I want a standby server to take over if the primary fails.

Use streaming replication.

I want to do read-only queries for load balancing.

Use streaming replication, but be aware that the replica receiving the queries may fall behind the primary.

I want to replicate over a slow or unreliable network connection.

Use WAL Shipping.

I want to upgrade from before 9.4 to 9.4 or higher.

Use Slony, Londiste, or Bucardo.

I want to upgrade from 9.4 or later.

Use pglogical.

I want to consolidate multiple servers down to a single data warehouse, or only replicate some tables/databases/etc.

If you are on 9.3 or earlier, use Slony, Bucardo, or Londiste. (But you should probably upgrade first.)
If you are on 9.4, 9.5, or 9.6, use pglogical.
If you are on 10, use built-in logical replication.

I want to only replicate some columns in a table

If you are on 9.3 or earlier, use Slony.
If you are on 9.4 or later, use pglogical.

I want to replicate tables that do not have a primary (or other unique) key.

Upgrade to version 10 and use built-in logical replication.

Should I use Slony, Bucardo, or Londiste?

Slony will have better overall performance than Bucardo or Londiste.
Slony requires C-level extensions to be installed.
Bucardo and Londiste are (somewhat) easier to install and administer.
Bucardo and Londiste are in Perl and Python, respectively, if you really strongly care about what language the tool use.

I need multi-master, where two servers can both take writes and replicate to each other.

Use Bucardo.

I want an Amazon PostgreSQL RDS server to be the target for logical replication.

Use Bucardo or Londiste.

I want to replicate from RDS to a non-RDS database.

No current way of doing this. *

I want an RDS database to be the source for logical replication.

No current way of doing this. *

Wait, the RDS documentation says it “supports logical replication”.

It only supports the test plugin that PostgreSQL provides as part of the contrib/ modules, which doesn’t actually do replication.

I want to use Amazon’s Data Migration Service for PostgreSQL-to-PostgreSQL replication.

You probably don’t. It doesn’t suport important PostgreSQL types, like TIMESTAMP WITH TIME ZONE.

I want to use both streaming and logical replication at the same time.

This is possible, but there are complexities beyond the scope of this cheat-sheet as to how to successfully fail over if your primary goes down.

This is awfully complicated.

Not really! The basic rule is to use streaming replication for high availability, and logical replication for data warehousing or other data distribution problems. The rest is all details!

–

Amazon Data Migration Service does allow replication from RDS to non-RDS PostgreSQL. It has significant limitations, such as not supporting the TIMESTAMPTZ type. Bucardo can be used with RDS as a source without those limitations. Currently, Amazon PostgreSQL RDS does not support general logical replication, such as pglogical.

Comments

Durga Prasad · 3 January 2018

Thank you for the informative article.

David Fetter · 3 January 2018

Thanks for putting this together! I've managed to use both Bucardo and AWS DMS to replicate from an RDS DB to elsewhere. There are a lot more asterisks with DMS than with Bucardo.

Marco · 4 January 2018

Thanks a lot! Beautiful and usefull post. Do you know this two other packages? What do you think about them? 1) Zalando Patroni: https://patroni.readthedocs.io/en/latest/ 2) Citus: https://www.citusdata.com/ Ciao.

Robins Tharakan · 4 January 2018

RDS Postgresql does support Logical Replication, and it does allow you to propagate tables onward to outside on-prem Postgres instances... So the two queries where RDS Postgresql is the source should mention Logical Replication.

Xof · 5 January 2018

Do you have a link to documentation on this? The current RDS documentation indicates that only the test plugin is supported, and AWS support confirms this. [See Jignesh's comment for more details. The current set of plugins that AWS supports are not fully-functional logical replication solutions.]

Jignesh Shah · 5 January 2018

Amazon RDS for PostgreSQL supports wal2json (since 9.6.3) and decoder_raw (since 9.6.5) along with test_decoder in the latest versions of 9.6. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.version965

David Fetter · 5 January 2018

Bucardo works because it doesn't require an actual database superuser privilege. Of course, it has all the write amplification that drove development of logical WAL, which is a large factor to consider. For now, if you really need logical replication out of RDS, it will work.

Xof · 6 January 2018

It's great it supports those now! Neither of those is a fully-functional logical replication solution, though, unless you are planning to do some pretty heavy lifting to fill out the missing parts.

Michael Paquier · 8 January 2018

Regarding the read-only query part on standbys. In Postgres 10 and upwards you can use synchronous_commit = remote_apply so as the primary waits for the WAL of the transaction commit to be applied on the standby. This protects from delays, at the cost of waiting potentially more. This of course does not play well with any settings on the standby side which delay WAL replay.

Colin 't Hart · 9 January 2018

Does logical replication in Postgres 10 require both sides to be on the same major version going forward? If it does, this will be a deal breaker for some sites as it requires upgrading both sides at the same time -- a dependency that anyone larger than the smallest companies won't like.

Xof · 18 March 2018

No, it won't; you will be able to upgrade between major versions using logical replication. (Of course, there's only one major version that supports it right now.)