PostgreSQL Base Backup and Point-in-Time Recovery

The idea: an old copy plus every WAL record since

PostgreSQL writes every change into the WAL (write-ahead log) before it changes a data file. Keep a copy of the data files, the base backup, and keep every WAL segment written since, the WAL archive. Then the database can be rebuilt as it was at any moment after the backup: restore the copy and replay WAL up to that moment. This is point-in-time recovery (PITR). It is how you undo a DELETE without a WHERE, which a replica cannot do: the replica deleted the rows too.

What pg_basebackup does

1. It connects with the replication protocol and calls pg_backup_start, which runs a checkpoint. The checkpoint's redo point is the WAL position replay must start from. It goes into backup_label as START WAL LOCATION.
2. It copies the data directory while the server keeps running. Pages change during the copy, so some files are older than others: a fuzzy copy. That is fine, because every change is also in WAL.
3. pg_backup_stop writes a BACKUP END record and forces a WAL switch, so the segment holding it is archived at once. A small backup history file (like 000000010000000000000001.00000C28.backup) also goes to the archive.

The copy is usable only with all WAL from the start location to the backup end. Replaying that range is what makes it consistent. The page uses -X none, so this WAL must come from the archive. The default -X stream also streams that WAL into the backup, so the backup works on its own; you still need the archive to go past the backup end. Demo: base backup + WAL archiving.

WAL archiving

With archive_mode = on, every finished segment file gets a .ready marker in pg_wal/archive_status/. The archiver process runs archive_command for it, with %p = the file's path and %f = its name, and marks it .done only if the command returns 0. A failing command is retried, and the WAL stays in pg_wal until it succeeds. Since PostgreSQL 15, archive_library can replace the shell command.

Only finished segments are archived. If the server is lost, the commits in the segment being written are lost with it. archive_timeout forces a switch after that many seconds, which bounds the loss; streaming to a replica bounds it further. Demo: no target, replay all archived WAL loses order #10 for this reason.

The recovery settings

Setting	What it does
recovery.signal	An empty file in the data directory: start in targeted archive recovery (before PostgreSQL 12 this was recovery.conf). standby.signal instead makes a standby that never stops.
restore_command	How to fetch one WAL file: cp /archive/%f %p. The startup process copies each segment to pg_wal/RECOVERYXLOG and replays it.
recovery_target_time	Stop before the first commit later than this time. Also: recovery_target_xid, _lsn, _name (a pg_create_restore_point), or recovery_target = 'immediate' (stop as soon as consistent).
recovery_target_inclusive	Default on: a commit exactly at the target time is replayed.
recovery_target_action	Default pause: stay read-only so you can check the data, then pg_wal_replay_resume(). The page uses promote. shutdown also exists.

The startup process reads backup_label, starts redo at its redo point, and fetches segment after segment. Only commit records are checked against the target; when the next commit is later than the target, recovery stops right before it. Demo: PITR to just before the DELETE.

Timelines

After recovery stops early, the database's history differs from what is in the archive: timeline 1 continued with the DELETE and more orders. If the restored server wrote new WAL into the same file names, it would overwrite that other history. So at promotion it picks a new timeline ID, 2. New WAL files are named 00000002… (the first 8 hex digits of a segment name are the timeline), and 00000002.history records where timeline 2 left timeline 1. The first timeline-2 segment starts with a copy of the old one up to the branch point.

Recovering again later follows the history files: with recovery_target_timeline = 'latest' (the default since PostgreSQL 12) it replays timeline 1 up to the branch point and then timeline 2. Standbys use the same files when a new primary is promoted. Demo: timeline 2 after PITR.

When recovery fails

Message	Cause
requested recovery stop point is before consistent recovery point	The target is earlier than the backup end. Use an older base backup. Demo: target inside the base backup.
recovery ended before configured recovery target was reached	A segment the replay needs is missing from the archive (PostgreSQL 13+; older versions promoted silently). Demo: a segment missing from the archive.
WAL ends before end of online backup	Even the WAL up to the backup end is missing: the backup is unusable.

Test restores regularly: an archive with a gap is only found when you need it. Tools such as pgBackRest and Barman manage backups, archives and retention, and check the archive for gaps.

See also PostgreSQL WAL internals, PostgreSQL checkpoints, PostgreSQL streaming replication, Write-Ahead Logging and ARIES Crash Recovery and, for MySQL, MySQL hot backup.

What the page leaves out

A segment holds 4 records instead of 16 MB of WAL, and each order is one record. The page restores once per run, uses -X none and recovery_target_action = 'promote', and does not copy the unarchived files from the old pg_wal, which you would do if the old server's disk survived. It also skips tablespaces, incremental backups (pg_basebackup --incremental, PostgreSQL 17), the .partial file archived at promotion, archive cleanup (pg_archivecleanup) and retention.

References

Continuous Archiving and Point-in-Time Recovery (PostgreSQL documentation)

pg_basebackup

Recovery target settings: recovery_target_time, recovery_target_action

The Internals of PostgreSQL, chapter 10: base backup and PITR