Part 5 · 3 chapters · ~18 min

WAL, Checkpoints and Recovery

WAL records, LSNs and the pg_wal layout, full page writes versus InnoDB's doublewrite, checkpoints and the IO storm, every synchronous_commit level, crash recovery and pg_control, point-in-time recovery from base backups and archived WAL, and pg_rewind with timelines after failover.

13

WAL records, LSNs and the commit path

code
SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());
-- 0/3A00128   000000010000000000000003A   (timeline 1, 16 MB segment files)

-- how much WAL a statement generates
SELECT pg_current_wal_lsn() AS before \gset
UPDATE accounts SET balance_kobo = balance_kobo + 1;
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), :'before'));

-- decode WAL records
pg_waldump -p $PGDATA/pg_wal --limit=20 000000010000000000000003
A COMMIT, DURABLY
from UPDATE to fsync: the write-ahead log makes the data files lazy
backendshared_buffersWAL bufferspg_wal (disk)data filesmodify page (dirty)
swipe the figure sideways, or tap expand for full screen
1/5
change in memory
The UPDATE modifies the page in shared_buffers, marks it dirty and stamps the page LSN. Nothing is written to the data file yet.
the page changes in memory onlydata files are written much later
14

Checkpoints and synchronous_commit

synchronous_commitCOMMIT waits forcan lose on crash
offnothing (WAL writer flushes within ~3 × wal_writer_delay)the last few hundred ms of commits; never corruption
locallocal WAL fsyncnothing locally; a replica may lag
on (default)local fsync, plus synchronous standbys' fsync if configurednothing
remote_writestandby received and wrote to its OS (not fsynced)data if the standby's OS also crashes
remote_applystandby applied it (visible to queries there)nothing; enables read-your-writes on the standby

It is settable per transaction: SET LOCAL synchronous_commit = off for an analytics write, on for money. That flexibility is one of Postgres' quiet strengths.

CHECKPOINTS AND FULL PAGE WRITES
why the first change to a page after a checkpoint writes the whole page into WAL
checkpointredo point LSN Afirst change to page Pafter Afull page image8 KB into WALnext checkpointredo point LSN B
swipe the figure sideways, or tap expand for full screen
1/4
a checkpoint
A checkpoint writes every dirty buffer to the data files and records a redo point. Recovery only needs WAL from the last redo point onward; older WAL can be recycled or archived.
flush all dirty pages, record the redo pointrecovery starts from here
15

Crash recovery, PITR and pg_rewind

code
# point-in-time recovery: a base backup plus every WAL segment since
archive_mode = on
archive_command = 'wal-g wal-push %p'        # or pgBackRest; test that it fails loudly

pg_basebackup -D /backups/base -Ft -z -P     # or wal-g backup-push / pgbackrest backup

# restore to just before the bad DELETE
restore_command = 'wal-g wal-fetch %f %p'
recovery_target_time = '2026-10-06 14:31:59+01'
recovery_target_action = 'promote'
touch $PGDATA/recovery.signal && pg_ctl start

Crash recovery: on start, the startup process reads pg_control (cluster state and the last checkpoint location), then replays WAL from the redo point to the end. Pages whose LSN is already newer than a record skip it. Timelines: when a standby is promoted, it starts a new timeline (a new history file), so WAL from before and after the fork is never confused. pg_rewind lets an old primary rejoin as a standby by copying back only the blocks that diverged since the fork, instead of a full re-clone.