This post covers the replication-related new features in the PostgreSQL 16 release.
Logical Replication From Standby Server
Up to PostgreSQL 15, a logical replication publication could be set up only on the primary server. If replication is configured in multiple layers, the load on the primary server increases accordingly.
Starting with PostgreSQL 16, you can set up a publication on a standby server. This lets you reduce the replication load on the primary server.
📢 A Primary →
Physical Replication → Standby → Logical Replication → Standby setup is possible starting with PostgreSQL 16.
📢 A Primary →
Logical Replication → Standby → Logical Replication → Standby setup is also possible in versions before PostgreSQL 16.
📢 For ease of explanation, the servers are labeled Primary, Standby, and Slave.
Setting up logical replication on a standby server
Starting with PostgreSQL 16, you can set up logical replication on a standby server. For this, the primary server must be set to
wal_level = logical.Primary server replication settings
📢 Because the standby server is read-only and cannot run DDL, the logical replication role and the publication are created on the primary server. The created role and publication are applied to the standby server through streaming replication.
Standby server: physical replication and logical replication setup
Slave server logical replication setup
In PostgreSQL 15, you cannot create a subscription on the slave server.
Starting with PostgreSQL 16, you can create a subscription on the slave server.
📢 pg_log_standby_snapshot() Function
This function takes a snapshot of running transactions and writes it to WAL, without waiting for the bgwriter or checkpointer to write to WAL. In other words, you can use this function to speed up logical replication slot creation.
Logical Replication Parallel
In PostgreSQL 16, logical replication can be applied by parallel apply workers. The
streming = parallel option was added for creating subscriptions, and the columns of the pg_subscription catalog changed accordingly.Change | Column | Description |
Modified | substream | Data type changed from boolean → char
f : do not allow streaming of in-progress transactions
t : allow streaming of in-progress transactions
p : apply changes directly through a parallel apply worker if one is available (same as t if no parallel apply worker is available) |
Added | subpasswordrequired | Whether a password is required for authentication |
Added | subrunasowner | Whether the subscription runs with the privileges of the subscription owner |
Added | suborigin | none : send only data created locally
any : send data created locally + by replication |
Bidirectional logical replication
Starting with PostgreSQL 16, bidirectional logical replication is possible. That is, Node 1 can receive and apply changes made on Node 2, and send changes made on Node 1 to Node 2 to be applied. Conversely, Node 2 can receive and apply changes made on Node 1, and send changes made on Node 2 to Node 1 to be applied. With bidirectional logical replication, every change made on either node is mirrored to both nodes and kept in sync.
Up to PostgreSQL 15, this feature is not supported, so the apply worker cannot tell whether data was created locally or by replication. This results in the infinite loop below.
- Changes made on Node 1 are sent to Node2 → applied to Node2 data
- Changes made on Node 2 are sent to Node1 → applied to Node1 data
- 1 → 2 → 1 → 2 → 1 → 2 → ... infinite loop
In PostgreSQL 16, the
origin parameter was introduced in the subscription creation syntax, so the apply worker can tell whether data was created locally or by replication, and the infinite loop no longer occurs.The origin parameter supports 2 values.
none: request that only data created locally be sent (prevents the infinite loop)
any: send data created locally + by replication (causes the infinite loop)
Bidirectional logical replication test
Node 1 : configure logical replication and restart
Node 2 : configure logical replication and restart
Node 1 : subscription settings
Node 2 : subscription settings
Verifying bidirectional logical replication
📢 In PostgreSQL 15, an infinite loop occurs.
Verifying bidirectional logical replication in PostgreSQL 15
WAL sender process name change
Starting with PostgreSQL 16, the name of the WAL sender process has changed. Previously it showed only logical replication information, but now the dbname is also shown.
Related articles
