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 worksheetDefine 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.
