WAL, archiving and point-in-time recovery

Why this lesson exists

"Someone ran a DELETE without a WHERE at 14:32. Can we get the data back as it was at 14:31?" With last night's pg_dump the honest answer is "as it was last night". With a base backup and archived WAL the answer is "yes, to the second". The WAL is also what crash recovery, replicas and every serious backup tool are built on - and a broken WAL archive is one of the classic ways to fill a disk. This lesson is the WAL, checkpoints, archiving, base backups and point-in-time recovery (PITR), done by hand on the lab server.

What you need to know already: the data directory and pg_wal/ (lesson 2), restart vs reload (lesson 2), pg_dump and RPO/RTO (the last lesson), shell cp and permissions.

The words you need first

• WAL (write-ahead log) - every change is first written to the WAL, sequentially; the data files are updated later. After a crash, replaying the WAL from the last checkpoint brings the data files back to a consistent state.
• WAL segment - one file in pg_wal/, 16 MB, named by timeline and position (000000010000000000000003).
• LSN (log sequence number) - a position in the WAL, like 0/3A01F28.
• Checkpoint - all changed pages are flushed to the data files; WAL before the checkpoint is no longer needed for crash recovery (it can be recycled - unless archiving, a replica or a slot still needs it).
• Archiving - copying every finished WAL segment somewhere safe (archive_command).
• Base backup - a consistent copy of the whole data directory, taken while the server runs (pg_basebackup). Base backup + all WAL since = the database at any later moment.
• PITR - point-in-time recovery: restore a base backup and replay archived WAL up to a target (a time, a transaction, an LSN, a named restore point), then stop.
• Timeline - after a recovery ends, the server starts a new history (timeline 2) so the old WAL beyond the stop point is never mixed in.

The WAL on this server

$ sudo -u postgres psql -c "select pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn())"
 pg_current_wal_lsn |     pg_walfile_name
--------------------+--------------------------
 0/29646D8          | 000000010000000000000002
(1 row)
$ sudo ls -l /var/lib/postgresql/18/main/pg_wal | head -n 5
total 81928
-rw------- 1 postgres postgres 16777216 Sep 22 20:02 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Sep 22 20:02 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Sep 22 20:02 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Sep 22 20:02 000000010000000000000005
$ sudo -u postgres psql -c "select name, setting, unit from pg_settings where name in ('wal_level', 'max_wal_size', 'min_wal_size', 'checkpoint_timeout', 'archive_mode', 'archive_command', 'full_page_writes')"
        name        | setting | unit
--------------------+---------+------
 archive_command    |         |
 archive_mode       | off     |
 checkpoint_timeout | 300     | s
 full_page_writes   | on      |
 max_wal_size       | 1024    | MB
 min_wal_size       | 80      | MB
 wal_level          | replica |
(7 rows)

wal_level = replica (the default) writes enough for archiving and physical replicas; logical adds what logical decoding needs. A checkpoint happens every checkpoint_timeout (5 min) or when max_wal_size (1 GB) of WAL has been written since the last one, whichever comes first. If you see LOG: checkpoints are occurring too frequently (N seconds apart) with HINT: Consider increasing the configuration parameter "max_wal_size", the write load wants a bigger max_wal_size - frequent checkpoints mean much more I/O (every page changed after a checkpoint is written in full to the WAL once - full_page_writes).

Watch WAL being generated by a write:

$ before=$(sudo -u postgres psql -Atc "select pg_current_wal_lsn()"); echo $before
0/29646D8
$ sudo -u postgres psql -d orders -qc "update orders set updated_at = now() where id <= 5000"
$ sudo -u postgres psql -Atc "select pg_current_wal_lsn(), pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '$before'))"
0/2B61AC8|2037 kB

The position moved by the WAL that 5,000 row updates wrote. pg_wal_lsn_diff(a, b) gives the bytes between two positions - the same arithmetic as replication lag in the next lesson.

Archiving

Three settings, two of them postmaster context:

$ sudo -u postgres mkdir -p /var/lib/postgresql/wal-archive
$ printf "archive_mode = on\narchive_command = 'test ! -f /var/lib/postgresql/wal-archive/%%f && cp %%p /var/lib/postgresql/wal-archive/%%f'\n" | sudo tee /etc/postgresql/18/main/conf.d/archive.conf
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal-archive/%f && cp %p /var/lib/postgresql/wal-archive/%f'
$ sudo systemctl restart postgresql@18-main
$ ps -u postgres -o cmd | grep archiver
postgres: 18/main: archiver last was

%p is the segment's path, %f its file name. The command must:

• return 0 only when the file is safely stored - PostgreSQL then may recycle the segment;
• refuse to overwrite an existing file (test ! -f ...), so two servers can never mix their WAL into one archive unnoticed;
• be fast enough to keep up with the write rate.

In production the archive is not a local directory - it is object storage or a backup server, usually through a tool: pgbackrest archive-push %p, wal-g wal-push %p, barman-wal-archive. (18 can also use an archive_library module instead of a shell command.)

$ sudo -u postgres psql -Atc "select pg_switch_wal()"
0/2B61AC8
$ sudo ls /var/lib/postgresql/wal-archive/
000000010000000000000002
$ sudo -u postgres psql -c "select archived_count, last_archived_wal, failed_count, last_failed_wal from pg_stat_archiver"
 archived_count |    last_archived_wal     | failed_count | last_failed_wal
----------------+--------------------------+--------------+-----------------
              1 | 000000010000000000000002 |            0 |
(1 row)

pg_switch_wal() closes the current segment early (otherwise a quiet server archives only when 16 MB are full - archive_timeout forces it every N seconds). Watch failed_count: when the command fails, PostgreSQL retries forever and keeps every unarchived segment in pg_wal/ - the disk fills, and the log repeats archive command failed with exit code 1 with the command's own error. A failing archive is a disk-full incident in the making.

A base backup

$ sudo -u postgres pg_basebackup -D /var/lib/postgresql/backups/base -Fp -X stream -c fast -P
69652/69652 kB (100%), 1/1 tablespace
$ sudo ls /var/lib/postgresql/backups/base
PG_VERSION       base          pg_dynshmem   pg_notify    pg_snapshots  pg_subtrans  pg_wal
backup_label     global        pg_logical    pg_replslot  pg_stat       pg_tblspc    pg_xact
backup_manifest  pg_commit_ts  pg_multixact  pg_serial    pg_stat_tmp   pg_twophase  postgresql.auto.conf
$ sudo cat /var/lib/postgresql/backups/base/backup_label
START WAL LOCATION: 0/3000028 (file 000000010000000000000003)
CHECKPOINT LOCATION: 0/3000080
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2026-09-22 20:10:22 UTC
LABEL: pg_basebackup base backup
START TIMELINE: 1

-X stream copies the WAL needed to make the copy consistent alongside it; -c fast asks for an immediate checkpoint instead of waiting for the next one; -Ft -z would write compressed tar files instead of a directory. backup_label records where the backup started - the server reads it when it starts from this directory. Since 17 there are also incremental base backups (summarize_wal = on, pg_basebackup --incremental=<manifest>, pg_combinebackup), and pg_verifybackup checks a backup against its backup_manifest.

Point-in-time recovery

The accident - at a moment we note:

$ sudo -u postgres psql -d orders -Atc "select count(*) from customers"
4000
$ t=$(date -u '+%Y-%m-%d %H:%M:%S+00'); echo "the last good moment: $t"
the last good moment: 2026-09-22 20:11:24+00
$ sudo -u postgres psql -d orders -c "delete from customers where country = 'NL' and id not in (select customer_id from orders)"
DELETE 0
$ sudo -u postgres psql -Atc "select pg_switch_wal()"
0/3407CF8

To get the rows back we restore the base backup as a second cluster next to production (never on top of it), replay the archive to just before the DELETE, and copy the rows over:

$ sudo -u postgres cp -a /var/lib/postgresql/backups/base /var/lib/postgresql/18/pitr
$ sudo pg_createcluster 18 pitr -d /var/lib/postgresql/18/pitr -p 5433
Configuring already existing cluster (configuration: /etc/postgresql/18/pitr, data: /var/lib/postgresql/18/pitr, owner: 114:114)
Ver Cluster Port Status Owner    Data directory              Log file
18  pitr    5433 down   postgres /var/lib/postgresql/18/pitr /var/log/postgresql/postgresql-18-pitr.log
$ printf "restore_command = 'cp /var/lib/postgresql/wal-archive/%%f %%p'\nrecovery_target_time = '$t'\nrecovery_target_action = 'pause'\narchive_mode = off\n" | sudo tee /etc/postgresql/18/pitr/conf.d/recovery.conf
restore_command = 'cp /var/lib/postgresql/wal-archive/%f %p'
recovery_target_time = '2026-09-22 20:11:24+00'
recovery_target_action = 'pause'
archive_mode = off
$ sudo -u postgres touch /var/lib/postgresql/18/pitr/recovery.signal
$ sudo pg_ctlcluster 18 pitr start
$ sudo tail -n 8 /var/log/postgresql/postgresql-18-pitr.log
2026-09-22 20:11:59.050 UTC [17519] LOG:  starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080, on timeline ID 1
2026-09-22 20:11:59.050 UTC [17519] LOG:  redo starts at 0/3000128
2026-09-22 20:12:55.050 UTC [17520] LOG:  restored log file "000000010000000000000003" from archive
2026-09-22 20:12:55.050 UTC [17520] LOG:  consistent recovery state reached at 0/3000128
2026-09-22 20:12:55.050 UTC [17519] LOG:  database system is ready to accept read-only connections
2026-09-22 20:12:55.050 UTC [17520] LOG:  recovery stopping before commit of transaction 783, time 2026-09-22 20:11:54.550+00
2026-09-22 20:12:55.050 UTC [17520] LOG:  pausing at the end of recovery
2026-09-22 20:12:55.050 UTC [17520] HINT:  Execute pg_wal_replay_resume() to promote.

Read the recovery log: starting point-in-time recovery to the time we saved, restored log file "..." from archive for each segment, recovery stopping before commit of transaction N, time ... - the first transaction after the target was the DELETE - and pausing at the end of recovery. With recovery_target_action = 'pause' the server stays read-only, so you can look before you commit to that point:

$ sudo -u postgres psql -p 5433 -d orders -Atc "select count(*) from customers"
4000
$ sudo -u postgres psql -p 5433 -Atc "select pg_is_in_recovery()"
t

The rows are there. Copy them back into production (only the missing ones), then throw the recovery cluster away:

$ sudo -u postgres psql -p 5433 -d orders -Xc "\copy (select * from customers where country = 'NL') to '/var/lib/postgresql/nl-customers.csv' with (format csv)"
COPY 666
$ sudo -u postgres psql -d orders -c "create temp table nl (like customers)" -c "\copy nl from '/var/lib/postgresql/nl-customers.csv' with (format csv)" -c "insert into customers overriding system value select * from nl where id not in (select id from customers)"
CREATE TABLE
COPY 666
INSERT 0 0
$ sudo -u postgres psql -d orders -Atc "select count(*) from customers"
4000
$ sudo pg_dropcluster 18 pitr --stop

Had you wanted the recovered cluster to become the database (a full rollback of everything after 14:31), you would select pg_wal_replay_resume() (or set recovery_target_action = 'promote'): recovery ends, the server starts timeline 2, writes 00000002.history and accepts writes. Targets other than time: recovery_target_xid, recovery_target_lsn, recovery_target_name (set earlier with select pg_create_restore_point('before-migration')), and recovery_target_inclusive = off to stop just before the target instead of just after.

In practice a backup tool does the restore (pgbackrest restore --type=time --target='2026-09-22 14:31:00+00') and managed services offer "restore to a point in time" as a button that creates a new server. The mechanics underneath are exactly these.

In an interview: "Someone deleted rows an hour ago - how do you recover them?" - PITR: restore the last base backup into a separate cluster, set restore_command (archived WAL) and recovery_target_time just before the delete, recovery.signal, start it, check the log says it stopped before that transaction, verify the rows, copy them back to production. That needs continuous WAL archiving and base backups set up before the accident - a nightly pg_dump only gets you last night.

What you can do now

• Explain WAL, LSNs, segments, checkpoints and max_wal_size, and read the checkpoint warning.
• Set up WAL archiving correctly and watch pg_stat_archiver for failures.
• Take a base backup with pg_basebackup and know what backup_label is for.
• Run a point-in-time recovery into a second cluster and bring data back.
• Name the recovery targets and actions, and what a timeline is.