Physical backups and point-in-time recovery with pg_basebackup and WAL archiving

PostgreSQL writes every change to the write-ahead log (WAL) before changing the data files. If you keep a copy of the data directory (a base backup) and every WAL file written since then, you can replay the WAL on top of the copy and stop at any point you like. This is point-in-time recovery (PITR), and it is how you undo “someone ran a DELETE without WHERE at 14:03”.

Physical backups copy the whole cluster: every database, plus roles. They only restore to the same major version and platform. Logical dumps from pg_dump cannot be combined with WAL replay.

Part 1: archive every WAL segment

WAL is written in segment files (16MB by default). When a segment is complete, the server runs archive_command (or an archive module set in archive_library) to copy it somewhere safe. %p is replaced by the file’s path and %f by its name:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
archive_timeout = 60

The command must exit with zero only when the file is safely archived; on a non-zero exit the server keeps the segment and retries. It should also refuse to overwrite an existing file, which is what the test ! -f part does. archive_mode needs a restart; archive_command only a reload. archive_timeout forces a segment switch on quiet systems so the archive does not fall far behind.

If archiving keeps failing, WAL piles up in pg_wal/ until the disk fills. Watch the pg_stat_archiver view (failed_count, last_failed_wal) and the free space on the WAL volume.

Part 2: take base backups

pg_basebackup copies a running cluster over the replication protocol. It needs a role with the REPLICATION attribute (or a superuser) and a replication line in pg_hba.conf:

pg_basebackup -h db1.example.com -U replicator -D /backup/base/2026-10-15 -Ft -z -X stream -P -c fast

• -D: target directory; it must be empty or not exist.
• -Ft writes tar files (-Fp, the default, mirrors the data directory layout); -z compresses the tar output.
• -X stream (the default) streams the WAL needed to make the backup consistent on its own.
• -c fast requests an immediate checkpoint instead of a spread one, so the backup starts sooner.
• -P shows progress.

Recent versions also support incremental base backups (pg_basebackup --incremental, server version 17 and later), which need WAL summarization enabled and are combined with pg_combinebackup before use.

Part 3: recover to a point in time

Warning: recovery replaces the data directory. Practice on a separate machine first, and if you recover in place, keep the old data directory (or at least any unarchived files in pg_wal/) until you have verified the result.

1. Stop the server.
2. Move the current data directory aside, if space allows.
3. Restore the base backup into an empty data directory with the right owner and permissions (postgres, mode 0700).
4. Empty pg_wal/ of files from the backup and copy in any unarchived WAL you saved.
5. Set restore_command and a recovery target in postgresql.conf.
6. Create an empty recovery.signal file in the data directory.
7. Optionally block normal users in pg_hba.conf, then start the server and watch the log.
8. Check the data. When it is right, end recovery and re-open access.

restore_command = 'cp /mnt/server/archivedir/%f %p'
recovery_target_time = '2026-10-15 14:02:00+00'
recovery_target_action = 'pause'

restore_command must return non-zero when a file is not in the archive; that is normal at the end of recovery. With recovery_target_action = 'pause' the server stops replaying at the target and lets you look around read-only; SELECT pg_wal_replay_resume(); then ends recovery, while 'promote' ends it automatically. The target must be later than the end of the base backup.

Timelines

Each completed recovery starts a new timeline, visible as the first eight hex digits of WAL file names (for example 00000002...). A small .history file records where the branch happened. Timelines stop a recovered server from overwriting WAL that belongs to the original history, so you can try several recovery targets from the same archive.

In practice

Most teams use a dedicated tool such as pgBackRest or Barman that wraps these steps: parallel and incremental backups, retention, checksums, and archive management. The concepts stay the same, and knowing them makes those tools far easier to operate.

Official documentation

• Continuous archiving and point-in-time recovery
• pg_basebackup