Streaming Replication Explained

Analogy categoryAnalogies

What Is Streaming Replication

PostgreSQL streaming replication is a physical replication mechanism in which a standby server connects to a primary and continuously receives write-ahead log (WAL) records as they are generated. Because the standby replays the exact same byte-for-byte WAL stream the primary writes to disk, the standby's data files are an identical, block-level copy of the primary at every point it has caught up to. This differs fundamentally from logical replication, which decodes changes into row-level operations; streaming replication instead mirrors the entire cluster, including every database, index, and system catalog, with no per-table selectivity.

🏏

Cricket analogy: It is like a stadium's giant screen mirroring the exact ball-by-ball feed from the broadcaster's master control room, not a commentator's paraphrased summary — every no-ball and DRS review appears identically and in the same order.

Setting Up a Standby

To provision a standby, you first take a base backup of the primary with pg_basebackup, then create a standby.signal file in the data directory and configure primary_conninfo in postgresql.conf so the standby knows how to reach and authenticate with the primary. A replication slot, created on the primary with pg_create_physical_replication_slot or the primary_slot_name parameter, ensures the primary retains WAL segments the standby still needs, even if the standby temporarily disconnects, preventing the standby from falling irrecoverably behind.

🏏

Cricket analogy: It is like setting up a reserve umpire's feed before a Test match at Lord's: you first sync the reserve with the exact current match state (the base backup), then keep a dedicated radio channel open (the slot) so no over's commentary is ever missed even if the signal briefly drops.

bash

# On the standby host: take a base backup and prepare the standby
pg_basebackup -h primary.db.internal -D /var/lib/postgresql/16/main \
  -U replicator -P -R -X stream -C -S standby1_slot

# -R writes standby.signal and primary_conninfo automatically
# -C -S creates a physical replication slot named standby1_slot on the primary

# postgresql.conf on the standby (written by -R, shown for clarity)
primary_conninfo = 'host=primary.db.internal port=5432 user=replicator password=... application_name=standby1'
primary_slot_name = 'standby1_slot'
restore_command = 'cp /archive/%f %p'
hot_standby = on

Synchronous vs Asynchronous Replication

By default, streaming replication is asynchronous: the primary commits a transaction as soon as it is durable locally, without waiting for any standby to acknowledge receipt. Setting synchronous_standby_names on the primary and synchronous_commit to on or remote_write/remote_apply turns one or more standbys into synchronous replicas, meaning a client's COMMIT does not return until the named standby confirms the WAL has been received (or written, or applied). This trades some commit latency for a guarantee of zero data loss on failover, since no acknowledged transaction can be missing from the synchronous standby.

🏏

Cricket analogy: It is like a third umpire's decision going final only after the snickometer, Hawk-Eye, and ball-tracking feeds all confirm — the on-field decision (the commit) waits for the synchronous replay booth, not just the bowler's appeal.

synchronous_commit = remote_apply is the strictest setting: it waits until the standby has not just received but actually replayed the WAL, guaranteeing a read on the standby immediately after failover sees the committed data. This is the safest option for HA setups where clients may read from a promoted standby right away.

Monitoring Replication Lag

The pg_stat_replication view on the primary exposes one row per connected standby, showing the sent_lsn, write_lsn, flush_lsn, and replay_lsn positions, from which you can compute lag in bytes using pg_wal_lsn_diff or lag in time using the replay_lag, write_lag, and flush_lag columns (interval types available since PostgreSQL 10). On the standby side, pg_stat_wal_receiver and pg_last_wal_replay_lsn() let you check how far behind the standby believes itself to be, and pg_is_in_recovery() confirms whether a connection is talking to a primary or a standby.

🏏

Cricket analogy: It is like the scoreboard operator checking how many balls behind the giant screen display is from the actual bowler's delivery, measured in seconds of lag rather than guessing from the crowd's reaction time.

If a standby using a replication slot goes offline for an extended period, the primary will keep every WAL segment since the slot's last confirmed position, because it must assume the standby may reconnect and need them. An abandoned slot can silently fill the primary's disk with retained WAL until it runs out of space and halts writes entirely — always monitor pg_replication_slots.active and set max_slot_wal_keep_size or alert on slot lag.

Streaming replication ships raw WAL from primary to standby, producing a byte-identical physical copy of the whole cluster.
pg_basebackup with -R provisions a standby, writing standby.signal and primary_conninfo automatically.
Replication slots prevent WAL needed by a standby from being recycled, but must be monitored to avoid unbounded disk growth.
Asynchronous replication (the default) risks losing the last few transactions on failover; synchronous_commit trades latency for zero data loss.
synchronous_commit = remote_apply is the strongest guarantee, ensuring the standby has replayed, not just received, the WAL before commit returns.
pg_stat_replication and the write_lag/flush_lag/replay_lag columns are the primary tools for measuring and alerting on replication lag.
An idle or disconnected standby holding a replication slot is a common cause of primary disk exhaustion in production incidents.