June 30, 2025
Summary: We are going to walk through an example schema and database setup and offer some sample settings and code for creating logical replication from a standby.
Table of Contents
Background on replication
At a high level PostgreSQL supports two main types of replication - streaming/physical and logical. The Write-Ahead Log can stream the entire set of physical files through a connection and represents the entire database on disk. Logical replication offers a more fine-grained approach, where you can specify individual database objects, such as tables or even specific rows, to be replicated to a remote server.
A standby server in PostgreSQL is created by taking a base backup of the primary server and continuously applying all changes made on the primary. A hot standby is a standby server that can be promoted to become the primary server. PostgreSQL saves modifications in WAL (Write-Ahead Log) records and replicates them from the primary server to the standby servers. The configuration parameter hot_standby_feeedback prevents catalog rows from being deleted too early from the primary, if a standby needs them.
Example setup for replication from a standby
Let’s say we have three different PostgreSQL servers, managing inventory for a global logistics company with warehouses distributed across the globe. In the Primary we have warehouse and inventory information, the standby is a physical copy high availability machine, and a third server that is getting specific changes for reporting purposes.
Primary
In your primary instance, you need 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.
CREATE ROLE repuser WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'somestrongpassword';
CREATE ROLE pubuser WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'differentstrongpassword';
Next I create a physical slot to copy changes from the primary to the standby. Strictly speaking this is not required, but in practice it really is. Without a physical replication slot, a restart of either node or a connection loss would disrupt the replication process:
SELECT pg_create_physical_replication_slot('hot_standby_1');
We’re interested only in the inventory for the Shanghai warehouse, whose code is SH. On the primary we will create a publication inventory_requirements for the tables the inventory table depends on, and another publication named inventory_sh_pub :
Table: warehouses
- Warehouse ID
- Warehouse name
- Country ID
- Capacity
Table: inventory
- Product ID
- Warehouse ID (fk to Warehouses)
- Quantity
CREATE PUBLICATION inventory_requirements_pub
FOR TABLE regions, countries, warehouses, products;
CREATE PUBLICATION inventory_sh_pub
FOR TABLE inventory WHERE (warehouse = 'SH');
GRANT SELECT ON TABLE regions, countries, warehouses, products, inventory
TO pubuser;
Standby
At this point I can create my standby instance. We’re going to initialize our standby with pg_basebackup. Once your standby data directory has been restored, you’ll need to edit its postgresql.conf and make sure it has some parameters (as documented here):
# Provide feedback to the primary or upstream standby about
# queries currently executing on this standby
hot_standby_feedback = on
# Use the physical replication slot we created previously
primary_slot_name = 'hot_standby_1'
hot_standby = on
archive_mode = on
# If level is changed to below logical, slots will be dropped
wal_level = logical
# standby streams changes from the primary
primary_conn_info = 'host=127.0.0.1 port=5432 user=repuser password=somestrongpassword'
max_wal_senders = 10 # max number of walsender processes
max_replication_slots = 10 # max number of replication slots
# If an upstream standby server is promoted to become the new
# primary, downstream servers will continue to stream from
# the new primary
recovery_target_timeline = 'latest'
Connecting to this standby will confirm it’s in read-only mode:
SELECT pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
At this point we have:
- A primary instance replicating through a physical slot to a working standby with
hot_standby_feedback = on - Publications named
inventory_requirements_pubandinventory_sh_pub
Logical replica from standby
Now we can go to our reporting PostgreSQL instance and subscribe to changes from the standby. Before PostgreSQL 16 this operation would fail.
One major advantage of logical replication is that you can subscribe to changes from PostgreSQL servers of different versions! That gives you a lot of flexibility when working with applications using different versions of PostgreSQL.
CREATE SUBSCRIPTION inventory_requirements_sub
CONNECTION 'dbname=inventory host=127.0.0.1 port=5434 user=pubuser password=differentstrongpassword'
PUBLICATION inventory_requirements_pub;
CREATE SUBSCRIPTION inventory_sh_sub
CONNECTION 'dbname=inventory host=127.0.0.1 port=5434 user=pubuser password=differentstrongpassword'
PUBLICATION inventory_sh_pub;
If your primary server is idle, this operation will hang. This is because the standby is waiting for information from the primary. You can speed up the creation of this information by calling the new function pg_log_standby_snapshot() on the primary. In this example, we’d call it twice because we’re creating two subscriptions.
SELECT pg_log_standby_snapshot();
pg_log_standby_snapshot
-------------------------
0/23000180
That allows the replica to continue, and produces a message like this, and tells us a replication slot was created on the standby. New and improved in PostgreSQL 16!
NOTICE: created replication slot "inventory_requirements_sub" on publisher
CREATE SUBSCRIPTION
We can verify that in the pg_stat_replication system view on the standby.
SELECT pid, application_name, state, sync_state FROM pg_stat_replication;
pid | application_name | state | sync_state
-------+----------------------------+-----------+------------
23265 | inventory_sh_sub | streaming | async
23251 | inventory_requirements_sub | streaming | async
(2 rows)
As soon as the changes are replicated to the standby, they will then be copied downstream to the reporting server, and we’ll see those changes there. Notice that only the SH records replicate over.
SELECT * FROM inventory ORDER BY product_id;
warehouse | product_id | quantity
-----------+------------+----------
SH | 11 | 7
SH | 13 | 13
SH | 15 | 18
SH | 22 | 15
SH | 24 | 20
From here I can create multiple logical replicas if I need, for different locations and warehouses.