Difficulty: intermediate
Estimated Time: 20 minutes

Logical Replication

The default replication process in PostgreSQL is great for high-availability and read-only query distribution. But there are cases where the entire database does not require replication or being able to write to the replicas is necessary. Third-party tools have been available to PostgreSQL to allow this for quite a while (Bucardo, Slony, mimeo, etc.) But the complicated setups involved or write-amplification they can cause mean they were less than ideal solutions.

Starting in PG 9.4, the WAL stream introduced a new mode to provide logical change information via its native replication protocol. And as of version 10, a method to perform logical replication of tables has been built into PostgreSQL.

The current restrictions on logical replication are mentioned in the documentation and vary between major versions - https://www.postgresql.org/docs/current/logical-replication-restrictions.html

Let's get started!

Final Notes

This scenario hopefully gave a good introduction to logical replication in PostgreSQL. Note that there is also an extension called pglogical that may have some more advanced features that don't yet exist in PostgreSQL itself. It can also allow logical replication via the WAL stream in versions earlier than 10 as well. Just note that it is maintained by a third-party and not part of core.

Enjoy learning about PostgreSQL? Sign up for our newsletter and get the latest tips from us each month.

Logical Replication in PostgreSQL

Step 1 of 3

Setup

For logical replication to work, the wal_level setting must be set to logical so that the necessary data is added to the WAL stream. Note that if you're changing an existing database to provide logical replication, you may see a slight increase in overall WAL traffic. Also, the max_replication_slots setting should be set to at least the number of replicas + subscribers that are expected. And the max_wal_senders setting should be set slightly higher than that to allow for other replication needs such as pg_basebackup.

psql -c "ALTER SYSTEM SET wal_level = 'logical'"
psql -c "SHOW max_replication_slots"
psql -c "SHOW max_wal_senders"

Note that all settings mentioned above require a restart to put into affect when changed

sudo systemctl restart postgresql-12

Just like with standard replication, a role with the REPLICATION property must exist for the subscribers to connect with. The replica_user role has already been created on the demo database along with the proper pg_hba.conf entry to allow secure password authentication. Note that unlike regular replication, you must set it to allow the replica_user to connect to the database that contains the publisher, not the special replication database.

psql -c "\du"
sudo cat /var/lib/pgsql/12/data/pg_hba.conf