Logical replication from Standbys

Introduction:

One of the most useful features in PostgreSQL 16 is the ability to perform logical replication from physical replication standbys. This feature allows users to stream data to other PostgreSQL instances, giving developers new options for workload distribution. Additionally, it enables standbys to publish logical changes to other servers.

You may find valuable insights in our previous blog post titled Unleashing PG16 New Features here

This introductory piece explored the concept of Logical replication from a physical standby instance and laid the groundwork for the more detailed installation and configuration steps covered in this blog. If you haven’t already, we recommend giving it a read to gain an understanding of the topic before proceeding further.

Example setup for Logical replication from a standby

In this example, I have three servers:

• one primary server
• two standby servers

One is used for streaming replication, while the other is for logical replication.

In Primary server

In the primary server, we must create 2 users with replication privileges. For this example, I created a user for streaming the changes to the standby, and another to publish changes to the subscribers.

postgres=# \du+
                                    List of roles
 Role name |                         Attributes                         | Description
-----------+------------------------------------------------------------+---
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS |
 pubuser   | Replication                                                           
 repuser   | Replication

Next, I create a physical replication slot to copy changes from the primary to the standby.

I created two tables and granted privileges to pubuser.

test=# GRANT SELECT ON TABLE t1,t2 TO pubuser;
GRANT

Now create publication for the tables on the primary server

test=# create publication testpub for all tables;
CREATE PUBLICATION

In Standby1 server:

I’ve already created the standby server. Connecting to this standby will confirm it’s in read-only mode.

SELECT pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t

In the postgresql.conf file, we need to enable the following parameters:

wal_level = logical
hot_standby = on
max_wal_senders = 10  
max_replication_slots = 10
hot_standby_feedback = on

In Standby2 server:

Now, let’s create the subscription on the standby2 server using standy1 as the source:

It may sometimes happen that this process isn’t complete immediately because the standy1 is still waiting to sync up the data from the Primary server. If it does happen, our recourse is to call the pg_log_standby_snapshot() on the primary, so that the replication state gets refreshed on standby1, thereby allowing other replication commands to be executed on standby1 thereafter.

We can see the following in the screenshot below:

Now we can verify the replication state in the standby1 server.

Summary

PostgreSQL’s evolution as a leading database platform continues to introduce new and robust data flow options. Logical replication from standbys, a feature that has seen continuous development and enhancements, takes a significant leap forward in PG 16, thanks to the tireless efforts of the PostgreSQL community. With this release, users will benefit from several powerful capabilities on standby servers, including the ability to:

• Create logical replication slots
• Initiate logical decoding
• Subscribe to changes on a standby
• Persist logical replication slots even after a failover

Furthermore, utilizing a standby server as a source for logical replication subscribers will be a seamless process, with only a handful of adjustments required:

• Enabling hot_standby_feedback = on
• Leveraging a physical replication slot to replicate data from the upstream to the standby
• Implementing pg_log_standby_snapshot() on the primary, ensuring subscribers don’t stall while creating a subscription to a standby.