PostgreSQL 16 Streaming Replication with pg_basebackup

2026年9月19日 LOG admin

Streaming replication gives you a byte-for-byte standby that replays the primary's write-ahead log in near real time, which means read scaling, geographic redundancy and a failover target that does not need a restore. The failure modes are equally well defined: a standby that was never given a replication slot, a base backup taken over a blocked port, and a promoted standby that nobody can rejoin. This walkthrough builds the whole thing properly on PostgreSQL 16.

Before you start

Primary and standby must run the same PostgreSQL major version. Streaming from a 15 primary to a 16 standby does not work.
Create a dedicated replication role — never use the postgres superuser for streaming.
Allow the standby's exact address in pg_hba.conf against the pseudo-database replication.
Plan WAL retention: a replication slot is the difference between "the standby catches up" and "the standby must be rebuilt".

-- On the primary
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'a_strong_password';

Step 1: Configure the primary

# /etc/postgresql/16/main/postgresql.conf  (Debian/Ubuntu path)
listen_addresses        = '*'
wal_level               = replica        # minimum for streaming replication
max_wal_senders         = 10
max_replication_slots   = 10
wal_keep_size           = 512MB
hot_standby             = on
archive_mode            = off            # enable if you also want PITR
# synchronous_standby_names = 'standby1' # only for zero-data-loss setups

# /etc/postgresql/16/main/pg_hba.conf
# TYPE      DATABASE      USER        ADDRESS         METHOD
host        replication   replicator  10.0.0.20/32    scram-sha-256

sudo systemctl reload postgresql
# If a host firewall is active, allow the standby:
sudo ufw allow from 10.0.0.20 to any port 5432

Step 2: Create the replication slot

SELECT pg_create_physical_replication_slot('standby1_slot');
SELECT slot_name, slot_type, active FROM pg_replication_slots;

The slot tells the primary to retain WAL until the standby confirms it consumed it. Without one, a standby that is offline longer than wal_keep_size allows will find the WAL it needs has been recycled — and at that point the only fix is a fresh base backup.

Step 3: Bootstrap the standby with pg_basebackup

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/*        # destructive: it is a clone, not a copy

sudo -u postgres pg_basebackup   -h 10.0.0.10 -U replicator   -D /var/lib/postgresql/16/main   -S standby1_slot   -R -P -X stream

Flag	Meaning
-h / -U	Primary address and the replication role
-D	Target data directory (must be empty)
-S	Replication slot to use for WAL streaming
-R	Writes `primary_conninfo` into `postgresql.auto.conf` and creates `standby.signal`
-X stream	Streams WAL during the backup so the copy is consistent on a busy primary
-P	Progress reporting

grep primary_conninfo /var/lib/postgresql/16/main/postgresql.auto.conf
ls /var/lib/postgresql/16/main/standby.signal

Both must exist. The standby.signal file is what makes PostgreSQL boot in recovery mode instead of accepting writes.

Step 4: Start and verify

sudo systemctl start postgresql

-- On the primary
SELECT client_addr, state, sent_lsn, replay_lsn,
       (sent_lsn - replay_lsn) AS replay_lag,
       sync_state
FROM pg_stat_replication;

-- On the standby
SELECT pg_is_in_recovery();              -- t = standby, f = primary
SELECT now() - pg_last_xact_replay_timestamp() AS replay_delay;

state = streaming plus matching sent_lsn and replay_lsn means the standby is caught up. Anything else: state = startup or catchup means the standby is rebuilding after downtime, and state = backup means another base backup is in flight.

Step 5: Failover and rejoining

# Promote the standby
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main

# Bring the old primary back cheaply instead of a full rebuild
sudo -u postgres pg_rewind   --target-pgdata=/var/lib/postgresql/16/main   --source-server="host=10.0.0.20 port=5432 user=replicator"
# then recreate standby.signal and point primary_conninfo at the new primary

pg_rewind requires wal_log_hints = on or data checksums enabled before the failure, so turn one of them on at build time rather than during an incident.

Troubleshooting the usual three

could not connect to server — listen_addresses still localhost, no replication line in pg_hba.conf, or a firewall. Test with psql -h 10.0.0.10 -U replicator -d replication from the standby.
WAL removed / standby cannot catch up — no slot, or max_slot_wal_keep_size exceeded. Add a slot; note that an abandoned slot is the most common cause of a full primary disk, so monitor it.
The slot exists but is inactive forever — primary_slot_name missing from the standby's connection settings, so it consumes no slot at all.

Build it with a slot, verify with pg_stat_replication, and enable wal_log_hints up front: those three choices are what make the second failover an event instead of a project.