Replication is a key part of creating an “Enterprise” Postgres deployment, to support high availability, failover, disaster recovery, or scale-out queries.
Built-in streaming replication was added to PostgreSQL in version 9.0, but the Postgres community has had a number of trigger based replication options for many years, with the big 3 being Slony-I, Londsite, and Bucardo (we’re biased towards Slony-I with it’s high performance C-language triggers — and not just because of the cute slonik logo or that the original author works at OpenSCG). And a whole new generation of logical replication has been introduced in the pglogical project.
How do you choose which replication solution? Use this checklist to decide which Postgres replication solution to use.
1. Version Support
- Are all of your PostgreSQL instances (master and all slaves) the same version? (Streaming replication requires same PG version so cannot be used for upgrades)
- Are you using a recent Postgres version?
- Are you using an ancient Postgres 7.x?
The different solutions have very different version compatibility so if you are working with an old Postgres version or upgrading to a new major version you can cross some off:
|Streaming Replication||WAL file||9.0+ (all nodes must be same version)|
|Slony-I v1.2.23||Triggers||7.3.3+ up to 8.4|
|PG Logical||WAL decoding||9.4+|
If you’re trying to upgrade an ancient version 7.x or < 8.3, you’ll probably need a 2-step upgrade using Slony-I (Sorry. Slony-I 1.2.23 works on 7.3.3 to 8.4. Slony-I 2.x supports 8.3+). Starting from 8.1 or 8.2 Bucardo is likely the best way to get to a modern version.
- Do you need bi-directional replication or master-> slave?
- Are you trying to replicate a single database or the entire Postgres instance?
Slony-I, Bucardo, and pglogical can all be configured to replicate a single database or individual tables, while Streaming replication works on the entire database instance (cluster). Streaming replication only supports master -> slave while the other solutions can be used to support multi-master replication — although care must be taken in configuration and to ensure no conflicts are generated by concurrent changes on different write servers.
- How heavily loaded is your PostgreSQL server?
- Are you trying to replicate across a WAN connection?
WAL based streaming replication places the smallest CPU load on the master database server. Trigger based approaches will have some performance impact on write throughput. BUT, streaming replication sends the entire WAL file to the slaves which on a heavy database server will saturate WAN connections to a disaster recovery data center and lead to significant delay or replication lag on the slaves. Trigger based and logical replication send much less data over the wire, so perform better for remote replica scenarios.
There are several good PostgreSQL replication solutions you can choose from, and your specific situation will guide your choice. Creating local read replicas and stand-by servers using streaming replication on a LAN is easy using just the PostgreSQL core. Upgrading with a trigger based replication like Slony-I can provide a nearly zero-downtime upgrade process. Disaster recovery or multi-datacenter replicas work best with a lower-volume Slony-I or pglogical replication. It’s easy to install and configure Slony-I from the integrated Postgres by BigSQL distribution – so what are you waiting for? Replicate already!!