medium riskservice affecting~60 min

Runbook: Build a Streaming Replica

1 · Prerequisites

Confirm every item is in place before any state change.

A replication role on the primary, and a pg_hba.conf rule admitting it for the replication pseudo-database from the standby host
A standby host with the same PostgreSQL major version and enough storage for the whole cluster plus WAL
Network connectivity from the standby to the primary on the PostgreSQL port, tested before starting
A decision about what this standby is for: read scaling, failover capacity, or delayed recovery — because the answer changes hot_standby_feedback, recovery_min_apply_delay and the monitoring thresholds
Confirmation that the primary has a free WAL sender slot
An agreed application_name for this standby, because the default is unhelpful and the name is what monitoring will key on

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

· **Confirm wal_level is replica or logical.** SHOW wal_level; At minimal no standby can be built, and changing it requires a restart of the primary.
· Confirm a free sender slot. SELECT setting FROM pg_settings WHERE name = 'max_wal_senders'; against SELECT count(*) FROM pg_stat_replication;. The base backup itself consumes one, so you need at least two free if you also want the standby to connect immediately.
· Confirm the replication role exists and can connect. SELECT rolname, rolreplication, rolcanlogin FROM pg_roles WHERE rolreplication; and a pg_hba.conf rule of the form hostssl replication repl <standby-cidr> scram-sha-256. The replication keyword here is a pseudo-database name and does not match all.
· Test the connection from the standby host before doing anything else. psql "host=<primary> user=repl dbname=replication replication=true" -c "IDENTIFY_SYSTEM". If this fails, everything after it fails more slowly and less clearly.
· Decide on a replication slot. A slot guarantees the primary retains WAL this standby has not consumed, which prevents the standby falling irrecoverably behind. It also means a standby that goes away and is never removed will retain WAL until the volume fills. Both facts belong in the decision.
· **Check pg_wal headroom on the primary**, because a slot will hold WAL if the standby is slow or absent: du -sh <datadir>/pg_wal and df -h.
· **Decide hot_standby_feedback deliberately.** on stops the standby cancelling queries at the cost of holding the primary's vacuum horizon; off protects the primary and cancels conflicting queries on the standby. There is no neutral default.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1Create the replication slot on the primary. SELECT pg_create_physical_replication_slot('standby_02'); Naming it after the standby makes the eventual cleanup obvious.
2Confirm the slot exists and is inactive. SELECT slot_name, slot_type, active, restart_lsn, wal_status FROM pg_replication_slots;
3Take the base backup from the standby host, with an immediate checkpoint and the slot: pg_basebackup -h <primary> -U repl -D <datadir> -c fast -X stream -S standby_02 -R -P. The -c fast avoids a wait of up to checkpoint_completion_target x checkpoint_timeout — on a default cluster, 270 seconds of apparently doing nothing.
4**Read what -R wrote, and expect to correct it.** -R appends primary_conninfo and primary_slot_name to postgresql.auto.conf — but it also copies the primary's existing postgresql.auto.conf, so the file can end up with two primary_conninfo lines and every ALTER SYSTEM setting the primary had. The last assignment wins, so it works; it is also confusing to read and inherits settings nobody chose for this host.
5**Set a meaningful application_name in primary_conninfo.** The default is walreceiver, which is identical for every standby and useless in pg_stat_replication. primary_conninfo = 'host=db-primary-01.internal port=5432 user=repl application_name=standby-02'.
6**Use a DNS name rather than a literal address in primary_conninfo.** A literal address is a dependency on a network layout that will change, and the failure mode is a standby that retries silently for weeks.
7**Set hot_standby = on** if this standby will serve read queries, and set hot_standby_feedback to the value you decided on.
8**Confirm standby.signal exists** in the data directory. pg_basebackup -R creates it; without it the cluster starts as a primary rather than a standby.
9Start the standby and read its log. Expect entering standby mode, then consistent recovery state reached, then started streaming WAL from primary at <LSN> on timeline N, then database system is ready to accept read-only connections.
10Confirm from the primary that the standby appeared. SELECT application_name, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, sync_state FROM pg_stat_replication; A state of streaming and the application_name you chose.
11Confirm the slot went active. SELECT slot_name, active, active_pid, restart_lsn, wal_status FROM pg_replication_slots; wal_status should be reserved.
12Prove replication end to end, which is the only check that exercises the whole path: write a row on the primary and read it on the standby a few seconds later.
13Confirm the standby refuses writes. INSERT on the standby must fail with cannot execute INSERT in a read-only transaction. A standby that accepts a write is not a standby.
14Configure monitoring before leaving. Alert on the standby being absent from pg_stat_replication, on byte lag, and on the slot's wal_status. Then test each alert by causing the condition.

4 · Verification

Confirm the procedure actually fixed the problem.

✓pg_stat_replication on the primary contains one row for this standby with state = 'streaming' and the application_name you chose.
✓pg_stat_wal_receiver on the standby shows status = 'streaming' and names the correct sender host.
✓A row written on the primary is readable on the standby within seconds. This is the end-to-end check and nothing substitutes for it.
✓A write attempted on the standby fails with cannot execute INSERT in a read-only transaction.
✓pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) is small and stays small under load.
✓The replication slot is active with wal_status = 'reserved', and pg_wal on the primary is not growing.
✓The absence alert fires when the standby is stopped deliberately, and clears when it is started again. An alert that has never fired has never been shown to work.
✓primary_conninfo names the primary by DNS and carries the intended application_name, confirmed with SHOW primary_conninfo; on the standby.

5 · Rollback

If verification fails, undo the procedure in reverse order.

↶Stop the standby: pg_ctl -D <datadir> -m fast stop. The primary keeps retaining WAL for its slot, so this is not the end of the rollback.
↶Drop the slot on the primary. SELECT pg_drop_replication_slot('standby_02'); A slot left behind by an abandoned standby retains WAL until the volume fills, and that failure arrives days later with no obvious connection to this work.
↶Confirm pg_wal on the primary returns to its normal size after a checkpoint, which proves the retention was released.
↶Delete the standby data directory if the standby is not being retried. A stopped standby with a live slot is the worst of both states.
↶If the base backup consumed a sender slot another standby needed, confirm that standby reconnected: SELECT application_name, state FROM pg_stat_replication;
↶If monitoring was configured for this standby, remove or disable it in the same change. An alert for a standby that no longer exists becomes noise, and noise is what teaches people to mute replication alerts.

6 · Escalation

When the runbook isn't enough, contact:

· The base backup fails with a WAL segment that no longer exists: escalate and rebuild using a slot. This means the primary recycled a segment the backup still needed, and it will recur.
· The standby connects and then repeatedly disconnects: escalate to the network owner with the standby log. Repeated could not receive data from WAL stream is a path problem, not a database one.
· max_wal_senders has no free slot and raising it needs a restart of the primary: escalate to the change owner rather than stopping another standby to make room.
· The primary's pg_wal grows and does not stop after the standby catches up: escalate. A slot with a wal_status other than reserved is retaining WAL beyond the safe limit, and this ends in a full volume.
· This standby is intended as the estate's only failover target: escalate to the service owner to agree a rehearsal schedule before it is declared ready. A failover target that has never been promoted in a rehearsal is a hypothesis.
· The standby is to be made synchronous: escalate before doing it. A single synchronous standby makes the primary depend on it, and every commit hangs when it goes away — which is a deliberate trade of availability for durability and not a database-team decision alone.

Building a standby is four commands. Making it a standby somebody can rely on is the rest of this runbook: a name, a slot with an owner, a DNS address, and monitoring that can distinguish a healthy standby from an absent one.

Read what `-R` actually wrote

Give the standby a name

primary_conninfo = 'host=db-primary-01.internal port=5432 user=repl application_name=standby-02'

Two deliberate choices in one line:

application_name — the default is walreceiver, which is identical for every standby. pg_stat_replication becomes unreadable and monitoring cannot key on anything.
A DNS name, not an address. A literal IP is a dependency on a network layout that will change, and the failure mode is a standby that retries silently — logging a FATAL every five seconds that nobody reads — for as long as the monitoring lets it.

Blast radius

Action	Reversible?	What it costs if wrong
Creating a slot	Yes, drop it	A slot with no standby retains WAL until the volume fills
`pg_basebackup` from the primary	Yes	I/O and one sender slot
Starting the standby	Yes, stop it	Nothing, if the slot is cleaned up
Leaving a stopped standby’s slot in place	Deferred	A full WAL volume, days later, with no obvious connection to this work
Making the standby synchronous	Yes, with a reload	Every commit on the primary hangs when the standby goes away

The `-c fast` that saves five minutes

pg_basebackup requests a spread checkpoint by default, paced over checkpoint_completion_target x checkpoint_timeout — up to 270 seconds on a default cluster, during which it prints nothing.

pg_basebackup -h db-primary-01.internal -U repl -D /var/lib/postgresql/18/main \
  -c fast -X stream -S standby_02 -R -P

Measured on 18.6: a backup that had not started after 120 seconds completed in 2.2 seconds with -c fast.

Prove it end to end

Views agreeing with each other is not proof. A row crossing the wire is:

-- primary
CREATE TABLE IF NOT EXISTS repl_probe(at timestamptz);
INSERT INTO repl_probe VALUES (now());

-- standby, a few seconds later
SELECT max(at) FROM repl_probe;

-- standby: this must FAIL
INSERT INTO repl_probe VALUES (now());
-- ERROR:  cannot execute INSERT in a read-only transaction

Monitor absence before lag

A disconnected standby has no row in pg_stat_replication

Every lag metric derived from that view returns NULL when the standby is gone. A monitoring system that renders NULL as zero draws a perfect graph for a standby that is not replicating at all.

Alert on the count of expected standbys first:

SELECT count(*) FROM pg_stat_replication WHERE application_name = 'standby-02';

Zero is the alert. Then alert on byte lag, not on time since the last replayed commit — the latter grows with the wall clock on an idle primary and produces false pages that teach people to mute the real ones.

Then stop the standby deliberately and confirm the page arrives.