Point-in-time recovery combines a base backup with archived write-ahead log records to restore a database to a chosen boundary. Recovery can branch onto a new timeline; the branch has its own later history. A server starting successfully does not prove it stopped before an unwanted transaction, included every intended earlier transaction, or has the keys and application version needed to serve users.
Make this comfortable
Point-in-time recovery: accept a restored timeline only after business reconciliation
Operational decision
A ledger operator must recover to just before a mistaken bulk update. In an isolated environment, identify the target by an approved transaction marker or precise recovery position, preserve the original cluster and WAL archive, and restore a base backup with every required log segment and timeline history. The text record lists the evidence to collect without offering a copy-and-paste production restore command. After recovery, query a known good payout immediately before the target and the mistaken update immediately after it; the first must exist and the second must be absent. Compare a bounded list of acknowledged external payments with the restored ledger, because transactions committed after the target may need separate reconciliation. Record the new timeline ID and fence the old writer before any traffic shift. Rebuild replicas and CDC connectors against the new timeline and their actual retained offsets; do not assume their old slots can continue. Finally run a synthetic settlement with the intended application version and encryption keys. If any log segment is missing, report the last proven recovery point rather than implying the target was reached.
Ledger PITR acceptance record
Base backup ID and required WAL range: verified
Target transaction marker and recovered timeline: recorded
Before-target payout: present; mistaken update: absent
Post-target acknowledged effects: reconciled separately
Old writer: fenced; replicas and CDC: rebuilt or verified
Synthetic settlement: passed with intended keys and app versionCost and verification
A tighter recovery-point objective needs more frequent WAL archiving and storage. Repeated restore drills consume compute but expose missing files, keys, or incompatible application versions before an incident. A branch timeline complicates later restore choices; retain the history files and name the chosen branch. Measure actual restore time and the count of acknowledged effects after the target. Do not silently reintroduce the bad transaction by replaying an unreviewed downstream queue.
Common Mistakes
- Do not declare recovery complete because PostgreSQL starts.
- Do not ignore acknowledged external effects committed after the chosen target.
- Do not reconnect the old writer or CDC slot without timeline and ownership checks.
Connected lessons
- DevOps: delivery, infrastructure, and reliable operations
- Backups and disaster recovery: prove the restore path
- Failover fencing: prevent two writable database leaders
- Replication slots: bound retained WAL before a consumer outage fills the disk
- Immutable backup retention: protect recovery copies from deletion
Practice and check
Recovery execution follow-up
devops
operations