postgresql when it's not your job

23:00

A Replication Cheat-Sheet

2 January 2018

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.

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

I want to only replicate some columns in a table

  1. If you are on 9.3 or earlier, use Slony.
  2. 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?

  1. Slony will have better overall performance than Bucardo or Londiste.
  2. Slony requires C-level extensions to be installed.
  3. Bucardo and Londiste are (somewhat) easier to install and administer.
  4. 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!

—

There are 11 comments.

Durga Prasad at 10:06, 3 January 2018:

Thank you for the informative article.

David Fetter at 11:39, 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 at 06:47, 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 at 16:29, 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 at 15:09, 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 at 17:17, 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 at 18:23, 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 at 10:05, 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 at 17:36, 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 at 04:25, 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 at 06:15, 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.)