Skip to content
SRE & DevOpsDeep Dive Published Updated 7 min readViews unavailable

PostgreSQL PITR: Verify the Base-Backup-to-WAL Recovery Chain

Build and rehearse a PostgreSQL point-in-time recovery chain from a physical base backup, continuous WAL archive, timeline history, and isolated restore test.

A PostgreSQL point-in-time recovery plan is a chain, not a backup file. It needs a usable physical base backup, every WAL segment required from at least the backup’s start through the desired recovery target, the timeline history needed to follow the correct branch, a working restore command, and a tested recovery configuration. If any link is missing, the cluster can stop replaying before the intended point even when the base-backup directory appears complete.

This guide follows PostgreSQL 18’s official documentation and covers whole-cluster physical recovery. pg_dump is a logical export and cannot be used as the base of continuous-archive WAL replay. PITR also does not restore an individual table or database by itself; for selective object recovery, restore the cluster elsewhere and extract the needed data through a separate procedure.

Establish WAL archiving before taking the backup

PostgreSQL writes an ordered stream of write-ahead log records and groups them into segment files. The default segment size is normally 16 MB, although it can be changed at cluster initialization. Archiving happens for completed segments, so a quiet system may have a delay between a transaction committing and the active segment becoming available in the archive. If the archiver falls behind or the destination is unavailable, PostgreSQL retains required WAL locally; prolonged failure can consume pg_wal space and eventually threaten the primary’s ability to continue writing.

Configure and observe the archive path before the first base backup. The archive operation should preserve each segment under the requested %f filename, transfer it to storage that survives loss of the database host, and report success only after the destination has durably accepted the file. Make retries safe: a repeated archive request for an already-stored segment must not silently replace different bytes. Use access controls that let the database service publish WAL but do not let routine database credentials delete the only retained recovery copy.

Monitor archive failures, age of the newest durable segment, local WAL growth, and available destination capacity. A “backup completed” alert without archive health can hide a broken recovery chain. Likewise, the existence of many files named like WAL segments does not prove they belong to the correct PostgreSQL cluster or timeline. Record the server version, cluster identity, timeline, segment name, object size, and a cryptographic digest in an external inventory where possible.

Once archiving is active, take a physical base backup with pg_basebackup. For example, pg_basebackup -D /srv/backup/base-2026-10-12 -X stream writes a plain-format backup to an empty target directory and streams the WAL needed while the backup runs. In a real environment, select authentication, TLS, tablespace mapping, compression, target naming, and output format according to the documented deployment. Protect credentials; do not put passwords in shell history or a published command example. -X stream handles WAL needed during backup, but it does not remove the need to retain archived WAL after that backup for later PITR.

Keep the backup history file and manifest with their backup. PostgreSQL’s history metadata identifies the start and end context and helps determine which WAL segments complete the physical backup. pg_verifybackup checks the backup against its manifest and can parse the WAL records required to recover that backup; for PostgreSQL 18, WAL parsing is supported only for plain-format backups. When verifying a tar-format backup, use the documented --no-parse-wal option and recognize that WAL parsing was skipped. The utility does not verify every later archived segment needed to reach a future PITR target, and PostgreSQL explicitly still recommends a test restore. Treat a backup’s metadata, tablespace locations, and the matching WAL range as one recoverable set.

Preserve the WAL range and its timeline

The essential retention rule is that the archived WAL sequence must extend back at least to the start of the base backup and continue through the target time. PostgreSQL needs WAL generated during the base-copy operation to bring that filesystem-level image to a consistent state, as well as later records needed to reach the desired recovery point. Do not delete older segments using file age alone. Retain a base backup together with its full required WAL dependency range, and keep multiple complete recovery sets if the retention policy requires more than one restore point.

Timelines make branching recovery manageable, but they also make filenames more than a simple global timestamp. After recovery completes and the cluster accepts writes, PostgreSQL creates a new timeline. A history file records where the branch came from and is needed when the archive contains more than one history. Keep .history files alongside WAL segments and include them in archive verification. When selecting a target timeline, confirm that it is a descendant reachable from the base backup; a recovery cannot follow a branch that diverged before that backup.

Also back up what WAL replay does not represent. PostgreSQL documents that continuous archiving does not restore manual changes to configuration files such as postgresql.conf, pg_hba.conf, or pg_ident.conf. Keep versioned copies of configuration, extension packages and versions, service settings, certificates/keys where appropriate, and external secrets in their own controlled recovery plan. A valid database data directory with missing configuration or extension files may still fail to start or behave differently after recovery.

Restore into an isolated target and prove the result

Rehearse against a clean, isolated restore host or disposable environment, not over the production cluster. Preserve the source backup and archive as read-only inputs, restore with the matching PostgreSQL major version, and reconstruct tablespace paths carefully. Before starting the restored service, restrict client access so applications cannot write into a recovery copy while you are still inspecting it.

For an archive-based restore, PostgreSQL requires restore_command. The documented command string receives %f for the requested archive filename and %p for the destination path where PostgreSQL should place it; it must return nonzero when a requested file is absent. A simple local illustration is:

restore_command = 'cp /srv/pg-archive/%f %p'
recovery_target_time = '2026-10-12 13:22:00+00'

The path is illustrative only. A production retrieval wrapper must handle credentials, object-store errors, atomic placement, logging, and safe concurrent access. Do not confuse an ordinary “file not found” request at the end of available WAL with a missing segment in the middle of the required range. A normal recovery can ask for the next segment and stop when the archive has no later file; successful completion still needs to be confirmed from the recovery logs and the requested target.

Restore the base backup and required WAL, configure the desired recovery target, and create recovery.signal in the cluster data directory. PostgreSQL 18 can stop at a chosen time or other supported recovery target; decide in advance whether the test should pause for inspection or complete recovery. Start with ordinary application access blocked. Read the startup and recovery logs, verify the reported target and timeline, then run application-level invariants such as expected schema versions, business markers before and after the target, row counts, and a representative consistency query. Only after these checks should a recovered cluster be considered a candidate for service.

Record the measured recovery time and data-loss window. Compare the latest acknowledged transaction before the target, the intended recovery target, and the final replayed position. That turns abstract RPO/RTO targets into evidence. A successful pg_verifybackup result validates the backup manifest; a successful isolated restore demonstrates that the required WAL, timeline history, extension environment, tablespaces, and recovery command worked together at least once.

The safe operational unit is therefore a tested recovery set: base backup, verified file manifest, complete and durable WAL range, retained timeline history, compatible configuration and extension inventory, and a rehearsed restore procedure. Keep those pieces together in the inventory and alert on any missing segment or archive lag before an incident turns it into an irreversible gap.

Related:

Sources:

Comments