Building a standby · PostgreSQL

Building a standby is five steps. Verifying one is four checks, and the checks matter more, because a standby that starts and does not replicate looks exactly like a standby that works.

On the primary

-- 1. a role that can stream WAL and nothing else
CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD '...';

-- 2. a slot, so the primary retains WAL this standby has not consumed
SELECT pg_create_physical_replication_slot('standby1');

# 3. pg_hba.conf, above any reject rules
host    replication   repl  10.0.0.0/24   scram-sha-256

# 4. postgresql.conf. wal_level=replica is the default in 18.
wal_level = replica
max_wal_senders = 10          # default 10; one per standby plus headroom
max_replication_slots = 10
wal_log_hints = on            # required for pg_rewind later (Part XV)

max_wal_senders needs headroom beyond the standby count, because pg_basebackup -Xstream consumes one for the duration of a backup, and so does each pg_receivewal.

On the standby

Service impact possiblethe base backup that becomes a standby— EXECUTION VALIDATED on 18.6. The target PGDATA must be empty and mode 700.

$ pg_basebackup -h rbpg-prim -U repl -D /var/lib/postgresql/18/docker \
  -Fp -Xstream -c fast -P -R -S standby1

23650/23650 kB (100%), 1/1 tablespace

-Fp (plain) because a standby needs a data directory, not a tarball. -R writes the recovery configuration. -S standby1 binds the backup and the subsequent streaming to the slot, so no WAL is missed between the two.

Read-only / Safewhat -R wrote— EXECUTION VALIDATED on 18.6, abbreviated. The password is in plain text.

$ cat postgresql.auto.conf; ls standby.signal

primary_conninfo = 'user=repl password=replpass channel_binding=prefer
 host=''rbpg-prim'' port=5432 sslmode=prefer ...'
primary_slot_name = 'standby1'

standby.signal

standby.signal is what makes it a standby. Its relationship to recovery.signal from lesson XIII-06 is exact and worth stating: both put the cluster into recovery, and they differ in what happens at the end of the available WAL. recovery.signal means “recover to a target, then stop.” standby.signal means “recover, and keep waiting for more.” A directory containing neither starts as a normal primary and ignores every recovery setting.

Then add whatever the standby needs locally:

listen_addresses = '*'
hot_standby = on                      # default on; allows read queries
hot_standby_feedback = off            # lesson XIV-07
max_standby_streaming_delay = 30s     # lesson XIV-07
restore_command = 'cp /archive/%f %p' # the fallback path

The four checks

Starting is not evidence. These are.

Read-only / Safe1. the standby believes it is a standby— EXECUTION VALIDATED on 18.6.

$ SELECT pg_is_in_recovery();

 pg_is_in_recovery
-------------------
t

Read-only / Safe2. the primary can see it, and it is streaming— EXECUTION VALIDATED on 18.6. Run this on the PRIMARY.

$ SELECT application_name, state, sync_state FROM pg_stat_replication;

 application_name |   state   | sync_state
------------------+-----------+------------
walreceiver      | streaming | async

Read-only / Safe3. data actually arrives— EXECUTION VALIDATED on 18.6. Written on the primary, read on the standby.

$ -- primary:  INSERT INTO t DEFAULT VALUES;
-- standby:
SELECT count(*) AS rows_on_standby FROM t;

 rows_on_standby
-----------------
             1

Read-only / Safe4. it refuses writes— EXECUTION VALIDATED on 18.6, on the standby.

$ INSERT INTO t DEFAULT VALUES;

ERROR:  cannot execute INSERT in a read-only transaction

Check 2 is the one people skip and the one that catches the most. pg_is_in_recovery() returning true only means the standby is in recovery — it says nothing about whether anything is arriving. A standby replaying from an archive with a dead streaming connection passes check 1 and fails check 2.

Stop the target cluster before replacing its data directory

The first attempt at building this standby failed, and the failure is worth keeping:

Service impact possiblewhat happens when the old postmaster is still alive— Real error from 18.6.

$ tail /tmp/sb.log

FATAL:  database system identifier differs between the primary and standby
DETAIL:  The primary's identifier is 7678833575722270763, the standby's identifier is 7678833643757821995.

PGDATA was wiped and repopulated by pg_basebackup while the target’s own postmaster was still running. On shutdown, that postmaster wrote pg_control from memory, overwriting the identifier the backup had installed.

Stop the cluster. Confirm it stopped. Then replace the directory.

Slot, archive, or both

The standby needs WAL the primary has not yet recycled. Three arrangements, with different failure modes:

Arrangement	If the standby is down for a long time	Risk
Slot only	Primary retains WAL indefinitely	`pg_wal` fills the disk
Archive only	Standby catches up from the archive	Archive must be working
Slot + `max_slot_wal_keep_size`	Slot is invalidated at the limit	Standby needs a rebuild
Slot + archive	Slot capped, archive covers the gap	Most moving parts

The last row is the robust one and lesson XIV-06 is about why the first row is dangerous.

Cascading standbys, and the settings that must not shrink

A standby can itself feed further standbys. Point the downstream primary_conninfo at the intermediate standby rather than at the primary, and it works with no additional configuration.

The value is real on wide topologies: one primary feeding twelve replicas across three regions needs twelve walsenders and twelve outbound WAL streams from the primary. One walsender per region, feeding local cascades, needs three.

Two properties are worth knowing before relying on it.

Cascaded replicas replay behind their parent, necessarily. Lag accumulates down the chain, because an intermediate standby can only forward what it has received.

A cascading standby cannot be a synchronous standby for the primary. Synchronous replication is negotiated over the primary’s own connections, and the primary has no connection to a cascaded replica.

Separately, and independently of cascading, a set of settings on any standby must be at least as large as on the primary: max_connections, max_worker_processes, max_locks_per_transaction, max_prepared_transactions and max_wal_senders. WAL records assume the primary’s shared-memory sizing, so a standby with less cannot replay them. Setting one too small produces a refusal to start naming the parameter, which is loud and easy to fix.

The failure mode that is not loud is raising one of these on the primary and forgetting the standby. Everything continues working until the standby restarts — perhaps weeks later, perhaps during the incident you were relying on it for — and then it will not come up. Change these on the standby first, then the primary.

What to take from this

Five steps: role, slot, pg_hba.conf, primary settings, pg_basebackup -R.
Set wal_log_hints = on before you need pg_rewind, not after.
standby.signal means “keep waiting for more WAL”; recovery.signal means “stop at a target”.
Four checks: in recovery, visible on the primary as streaming, data arrives, writes refused. Check 2 is the one people skip.
Stop and confirm before replacing a data directory.
Slot plus archive is the robust arrangement.
max_connections and friends must be at least as large on the standby — raise them there first.

Cross-course references

Ansible for Production Sysadmins — Part XII (Idempotency) covers building a standby the same way twice, which is what makes a rebuild during an incident survivable.
Secrets, PKI & Certificate Management — Part XIII (Dynamic credentials) covers the replication credential, and Part VII (TLS for operators) covers encrypting the stream it authenticates.