PostgreSQL in Practice

PostgreSQL WAL, Backup, and Replication

PostgreSQL WAL orders durable changes by LSN and feeds crash recovery, physical backup/PITR, streaming replication, and logical decoding. A reliable design treats archive continuity, slot retention, timeline history, lag semantics, and restore/failover drills as one lifecycle.

WAL-before-data is the core durability rule.
Checkpoints trade restart distance for foreground I/O and WAL.
PITR needs a consistent base backup plus an unbroken WAL chain.
Physical streaming replays cluster-level WAL.
Slots protect consumers by retaining resources.
Logical replication transfers decoded changes, not a complete cluster.

One WAL stream, several recovery consumers

Replication position and risk
Mechanism	Observe	Risk/action
physical sender	sent_lsn, write_lsn, flush_lsn, replay_lsn in pg_stat_replication	separate network, storage and replay lag
receiver	written/flushed LSN and latest_end_lsn in pg_stat_wal_receiver	connection/config drift
slot	restart_lsn, confirmed_flush_lsn, wal_status, safe_wal_size in pg_replication_slots	retained WAL/history or invalidation
archiver	archived_count, failed_count, last_* in pg_stat_archiver	archive RPO gap despite healthy replica
logical subscription	pg_stat_subscription worker/LSN timestamps	schema/identity/conflict and initial-copy state

Inspect WAL retention and replication in PostgreSQL 18

SELECT application_name, state, sync_state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_gap_bytes
FROM pg_stat_replication;
SELECT slot_name, slot_type, active, restart_lsn, confirmed_flush_lsn,
       wal_status, safe_wal_size, invalidation_reason
FROM pg_replication_slots;
SELECT archived_count, failed_count, last_archived_wal, last_failed_wal
FROM pg_stat_archiver;