Design PostgreSQL point-in-time recovery

Design PostgreSQL PITR with base backups, continuous WAL archives, recovery targets, key custody and a repeatable restore acceptance worksheet.

Last updated

In short

  • PITR requires a usable base backup and an unbroken WAL sequence.
  • A replica does not replace recovery from accidental data changes.
  • Measure restore time and verify business data at the chosen target.
On this page (6)

Architectures · Part of PostgreSQL

Editable record for backup chain, target data and measured restore timing.

Download the PITR acceptance worksheet

Define the recovery question precisely

PostgreSQL point-in-time recovery combines a base backup with archived write-ahead log records to reconstruct the database up to a selected recovery target. It is useful when the latest state is not the desired state, such as after an accidental bulk update. A replica may faithfully reproduce that mistake, so replication and historical recovery solve different problems.

This is a reference design and restore exercise, not a measured recovery report. The current PostgreSQL PITR manual explains the underlying mechanism. Use the manual for the exact major version deployed; backup format and recovery features must be compatible with the binaries used for restoration.

Specify RPO and RTO in operational terms

Recovery point objective describes the acceptable amount of missing committed work. Recovery time objective describes how long restoration may take before the service is usable. Neither is established by a backup schedule alone.

Requirement Design input Evidence to collect
Maximum lost work Business-approved interval or transaction boundary Age of recoverable archived WAL
Time to service restoration Provisioning, download, replay and validation time Timed end-to-end restore exercise
Historical window How far back a valid target must remain available Retained base backups plus required WAL
Location boundary Approved backup and recovery locations Storage inventory and contract evidence
Key availability Who can recover encryption credentials Isolated restore using approved key access

Include application validation in the RTO. A database accepting connections is not sufficient if the application schema is incompatible or the restored records are wrong for the incident.

Keep a complete recovery chain

Artifact Purpose Failure mode
Base backup Starting database state Corrupt or incompatible backup
WAL archive Changes after the base backup Missing segment breaks the recovery chain
Backup manifest Integrity and inventory evidence Checks omitted or not retained
Configuration Authentication, extensions and operational settings Restored service starts with unsafe defaults
Extension binaries Database functionality Required extension unavailable on recovery host
Encryption material Access to protected backups Backup exists but cannot be decrypted

The WAL configuration documentation describes archiving and recovery-target settings. Design the archive operation to report failures accurately and avoid overwriting different WAL content under the same name. Monitor archive failures, archive age and local WAL growth; a stalled archive can consume storage before anyone attempts a restore.

Retention must consider dependencies. Deleting an older base backup or WAL segment without checking the retained recovery window can remove the only valid path to a required target. Let the chosen backup system manage that dependency graph under a reviewed policy rather than deleting files by age alone.

Protect backup credentials separately

A production database administrator should not automatically possess an unrestricted ability to delete every historical backup. Separate backup-writing, retention-management and restore-reading permissions where the storage system supports that design. Encrypt data in transit and at rest, and define how recovery operators obtain the required keys during an outage.

Backups can contain all database information, including data that ordinary application users cannot query. Keep restore environments isolated and avoid pointing a restored application at real email, payment or webhook destinations before validation. A successful recovery should not accidentally repeat external actions.

Use a controlled restore scenario

Create a synthetic table or approved test dataset in a nonproduction environment. Record three events: a baseline row, a later valid change and an intentionally incorrect change. Capture their transaction or timestamp evidence with timezone information. Choose a target that includes the valid change but excludes the incorrect one.

Restore the base backup into a separate data directory or host using compatible binaries. Supply the required archive access and recovery target through the supported mechanism. Do not reuse production storage or start a second writable server against the same data directory.

Acceptance check Expected result
Backup integrity Manifest checks pass with the appropriate tool
WAL availability Required sequence is retrievable without gaps
Recovery target Valid synthetic change present; incorrect change absent
Application compatibility Expected schema and required extensions available
Access control Production-like restrictions restored deliberately
Timing Provisioning, replay and validation durations recorded separately

pg_verifybackup can check base-backup integrity. It does not replace starting the restored cluster and validating the intended data. Keep both kinds of evidence.

Plan promotion and reconciliation

Before making the recovered database authoritative, stop or fence the old writer and establish which endpoint applications will use. Record the selected recovery timeline and prevent clients from writing to two competing histories. Transactions after the chosen target may include valid work that needs business reconciliation; recovering to an earlier point intentionally excludes them.

Rollback from a failed recovery attempt should preserve the original database and the candidate restore while the incident team decides the next target. Avoid overwriting the only available copy. If the candidate has already accepted new writes, switching back becomes another reconciliation exercise, not a simple connection-string reversal.

Review the procedure after version upgrades, extension changes, storage changes and key rotation. The deliverable is a recovery record showing a specific target, actual elapsed time and verified data—not a general promise that backups are enabled.

Sources and further reading

Use the linked documentation to confirm the version, edition and contract that apply to your project.

Set our database recovery targets

Bring the required recovery window, acceptable data loss, database size and the people authorized to restore it.