PostgreSQLadvanced

PostgreSQL Streaming Replication

Setting up a physical standby, choosing between slots and archives for WAL retention, and configuring synchronous commit safely.

2 min readAdvancedUpdated 31 Jul 2026Edit this page

PostgreSQL

Streaming replication ships WAL from a primary to standbys that replay it continuously. It is the basis of both high availability and read scaling in PostgreSQL.

PostgreSQL streaming replication with an archive

Primary configuration

wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 1GB              # a floor even when slots are used
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD :'password';

Add a hostssl replication replicator <standby-ip>/32 scram-sha-256 line to pg_hba.conf for each standby.

Building the standby

bashResource intensive

pg_basebackup -h primary.internal -U replicator -D /srv/pgdata/17/main -Fp -Xs -P -R -C -S standby_1

Takes a base backup and writes the standby's connection settings. -C -S standby_1 creates a replication slot named standby_1. This reads the entire database over the network — expect it to add load to the primary for the duration.

-R writes primary_conninfo and creates standby.signal, so the server starts as a standby.

Slots versus archives

A replication slot guarantees the primary retains WAL a standby has not consumed. That guarantee is also the hazard:

An inactive replication slot fills the primary's disk

If a standby is decommissioned without dropping its slot, the primary retains WAL forever and eventually runs out of disk. Set max_slot_wal_keep_size so the slot is invalidated instead, and alert on inactive slots:

SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots ORDER BY 3 DESC;

WAL archiving is the safer complement: the standby falls back to fetching archived segments via restore_command if it drops too far behind, and the archive is what point-in-time recovery needs anyway.

Monitoring lag

-- On the primary: lag per standby, in bytes and in time.
SELECT application_name, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)   AS send_lag_bytes,
       pg_wal_lsn_diff(sent_lsn, replay_lsn)             AS apply_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

Distinguish the components: send lag is network, flush lag is standby I/O, replay lag is single-threaded apply. Only replay lag responds to reducing conflicting queries on the standby.

Synchronous commit

synchronous_standby_names = 'ANY 1 (standby_1, standby_2)'
synchronous_commit = on

ANY 1 of two candidates means a commit waits for either standby, so losing one does not stop the primary. Naming a single standby couples the primary's availability to that standby.

synchronous_commit levels, weakest to strongest: off, local, remote_write, on, remote_apply. Only remote_apply guarantees the write is visible on the standby when the commit returns — the setting to use when read-after-write on a replica must be exact.

It can be set per transaction, which is the practical approach: strict for payments, relaxed for telemetry.

SET LOCAL synchronous_commit = 'remote_apply';

Standby query conflicts

A long query on a standby can conflict with WAL replay. Either replay is delayed (max_standby_streaming_delay) or the query is cancelled. hot_standby_feedback = on prevents cancellations by holding back the primary's vacuum horizon — which trades replica query stability for bloat on the primary. Choose deliberately; both settings have a real cost.