PostgreSQL Streaming Replication

The idea: the standby replays the primary's WAL

Every change on a PostgreSQL primary is written to the write-ahead log (WAL) before the data files. A physical standby is a byte-for-byte copy of the whole cluster that stays up to date by replaying that same WAL. On the primary a walsender process reads WAL from pg_wal and streams it over a normal connection. On the standby a walreceiver writes it to the standby's own pg_wal and fsyncs it, and the startup process replays it into the data files, exactly as crash recovery would, but without ever stopping.

The page runs one primary and one standby with a table accounts of one row. Commit updates the row in a new transaction, VACUUM removes dead row versions, Checkpoint removes old WAL, Standby SELECT starts a long read query on the standby, and Stop Standby takes it down. The options switch the replication slot and hot_standby_feedback.

walsender, walreceiver, startup

A standby starts life as a base backup: pg_basebackup -D /data -R -S standby1 copies the data directory and writes primary_conninfo, primary_slot_name and an empty standby.signal file. When the standby starts it sees standby.signal, stays in recovery, and its walreceiver connects to the primary with START_REPLICATION SLOT standby1 PHYSICAL <lsn>, asking for the first record it does not have. The primary starts a walsender for that connection (it counts against max_wal_senders) and from then on sends each new piece of WAL as soon as it is flushed (Demo: a commit streams to the standby).

The standby is read-only. Its data files are changed only by the startup process, so it is always an exact copy of the primary as of some point in the WAL.

Four positions and lag

WAL positions are LSNs (log sequence numbers), byte offsets such as 0/3000148; the page uses short numbers. The standby's walreceiver sends status replies with three of them, and the walsender adds the fourth:

Column of pg_stat_replication	Meaning
sent_lsn	The walsender has sent WAL up to here.
write_lsn	The standby has written it to its pg_wal (not yet fsynced).
flush_lsn	The standby has fsynced it: it survives a standby crash.
replay_lsn	The startup process has applied it: queries on the standby can see it.

The gaps between them are the lag: network (sent → write), disk (write → flush) and replay (flush → replay). Replay lag is the one users notice, because a read on the standby can return old data. Check it on the primary:

SELECT application_name, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

Replication slots keep WAL

WAL is removed at checkpoints once the primary no longer needs it for crash recovery. A standby that is down or slow may still need it. A physical replication slot is a small record on the primary that remembers the standby's position (restart_lsn, moved by the status replies), and a checkpoint never removes WAL after it. A standby that was down for a while can then connect again and catch up (Demo: standby down, the slot keeps WAL).

Without a slot nothing protects that WAL (apart from wal_keep_size, 0 by default). When the standby comes back it asks for a segment that is gone and fails with ERROR: requested WAL segment 000000010000000000000002 has already been removed. It can never catch up by streaming; it needs a new base backup, or WAL from an archive via restore_command (Demo: no slot, WAL removed, standby lost).

The price of a slot is disk: a standby that never comes back holds WAL forever, and pg_wal can fill the disk and stop the primary. max_slot_wal_keep_size caps it; watch pg_replication_slots and drop slots of standbys that are gone. PostgreSQL Logical Decoding shows the same slot mechanics for logical slots.

Hot standby and query conflicts

With hot_standby = on (the default) the standby takes read-only queries while it replays. That creates a problem. A query on the standby has a snapshot: it must keep seeing the row versions that were visible when it started. On the primary, VACUUM does not know about that query, so it removes those versions once no transaction on the primary needs them, and logs a PRUNE record with the newest xid it removed (latestRemovedXid). When the standby replays that record while the query still reads one of those versions, it is a recovery conflict.

The startup process then waits, at most max_standby_streaming_delay (30 s by default). All WAL behind the conflict waits too, so replay lag grows. If the query is still running after that time, the standby cancels it with ERROR: canceling statement due to conflict with recovery (Demo: VACUUM cancels a standby query). Other conflicts work the same way: an ACCESS EXCLUSIVE lock (for example from DROP TABLE), a dropped tablespace or database. pg_stat_database_conflicts counts them.

hot_standby_feedback

With hot_standby_feedback = on the walreceiver also sends the oldest xmin of the standby's queries. The primary keeps it on the walsender, or in the slot's xmin when there is a slot, and treats it like the snapshot of a local transaction: VACUUM keeps every version that xmin could still see. The query finishes, but dead rows stay on the primary until it ends, so a long report on the standby causes bloat on the primary (Demo: hot_standby_feedback keeps the rows). A slot keeps that xmin even while the standby is down.

Setting	Standby queries	Primary	Replay
defaults	may be cancelled	no extra bloat	waits at most 30 s
hot_standby_feedback = on	no cleanup conflicts	dead rows kept while queries run	does not wait for cleanup
max_standby_streaming_delay = -1	never cancelled	no extra bloat	may lag without limit

Synchronous replication

By default replication is asynchronous: COMMIT returns once the WAL is flushed on the primary, and a crash of the primary can lose the last transactions the standby had not received. Naming the standby in synchronous_standby_names makes COMMIT wait for its reply. synchronous_commit picks which position it waits for: remote_write (write_lsn), on (flush_lsn) or remote_apply (replay_lsn, so a read on the standby right after the commit sees it). Primary–Replica Replication animates the sync vs async trade-off and failover.

See also PostgreSQL WAL Internals for how WAL records are written and flushed, PostgreSQL Checkpoints for when segments are removed, PostgreSQL Process Architecture for the other server processes, and MySQL Replication Internals for the binlog-based MySQL design.

A replica cannot undo a mistaken DELETE, but a base backup and the WAL archive can; see PostgreSQL Base Backup and Point-in-Time Recovery.

What the page leaves out

One WAL record per transaction (real WAL has a heap UPDATE record and a separate COMMIT record), four records per segment and short LSNs. Status replies are sent every wal_receiver_status_interval (10 s) and after each write, not once per batch; the page lumps write and flush together. The 30 s wait is shown as one step, not on a clock. Checkpoints also keep WAL since the last redo point and up to max_wal_size, and write a checkpoint record; the standby makes restartpoints. Not shown: WAL archiving and restore_command, cascading standbys, synchronous replication, promotion and pg_rewind, timelines, lock and snapshot conflicts, max_slot_wal_keep_size, and the startup process reading WAL from the archive when streaming fails.

References

Log-Shipping Standby Servers and Streaming Replication (PostgreSQL documentation)

Hot Standby and query conflicts (PostgreSQL documentation)

pg_stat_replication (PostgreSQL documentation)

Replication settings (PostgreSQL documentation)