Point-in-time recovery (PITR) restores a PostgreSQL cluster to a chosen moment or transaction after corruption, accidental changes, or malicious damage. A full backup alone restores only the state when that backup was taken; a DROP TABLE in the afternoon can otherwise lose everything written since a morning backup. section 9.2 section 9.7
PITR combines a physical base backup with continuously archived write-ahead log (WAL) files. The base backup is a binary snapshot of the entire cluster, while WAL provides a replayable record of transactions after that snapshot. Recovery replays archived WAL until the selected recovery target, stopping just before the damaging change. section 9.2
A workable PITR setup requires:
- A base backup, commonly produced with pg_basebackup. section 9.2
- WAL archiving configured with archive_command and retention of WAL from the start of the base backup through its completion and afterward. section 9.2
- A recovery target time chosen before the known damage. section 9.2
- Automation, monitoring, and restore testing rather than a manual procedure remembered by one operator. section 9.3 section 9.7
Do not try to pair a pg_dump logical backup with WAL files: PITR requires a physical base backup and its WAL history. Filesystem snapshots, RAID, and periodic full backups also leave a gap between snapshots, so they cannot reliably undo an event at an arbitrary later time. section 9.3 section 9.7
Recovering from an afternoon DROP TABLE
Suppose a verified safe base backup exists from the morning and the table is dropped in the afternoon. Restore the base backup, make the archived WAL available, and set a recovery target immediately before the drop. WAL replay can recover the cluster beyond the morning snapshot without replaying the destructive transaction. section 9.2
Figure 9.2 A sample PostgreSQL backup setup with a dedicated Barman server and geographical redundancy showing the possible transfer paths
A dedicated Barman server can collect base backups and WAL, with alternative archive paths and a geographically separate copy for redundancy. section 9.3
Dedicated PostgreSQL-aware tools such as Barman and pgBackRest can schedule backups, retain recovery history, support multiple locations, and reduce the risk of ad hoc scripts. Automation is not proof of recoverability: perform full restores to verify that the backup chain works. section 9.3 section 9.7
