⏪Point-in-time recovery (PITR)

Restore a base, replay the log, stop on purpose

PITR restores a physically consistent backup, then reapplies WAL (PostgreSQL), archivelogs (Oracle), or transaction logs (SQL Server) until a chosen timestamp or LSN. It is how you undo a bad migration, mass delete, or ransomware-if the log chain and base backups exist, align, and are retained long enough.

SQL

PostgreSQL recovery target (conceptual post-restore)

-- recovery.conf / patroni / managed UI equivalent:
-- recovery_target_time = '2026-04-12 09:55:00+00'
-- recovery_target_action = 'promote'

✓Illustrative result set

result
Cluster replays WAL then becomes primary at target time

⚡ Advanced / dialect note

PITR needs continuous archiving of WAL segments plus base backups, if retention is shorter than the time it takes to notice an incident, you cannot rewind far enough.

📋Runbook sketch

Practice in a sandbox before adrenaline hits.

Step	Action
1	Restore the latest base backup to an isolated staging cluster.
2	Replay archived logs until just before the mistake; validate row counts and checksums.
3	Logical dump corrected data, merge back to prod, or promote after explicit sign-off.

Database-only thinking

Broken or partial log chains after storage loss, replay stops early with no warning in app logs.
Recovery targets that land inside a long-lived uncommitted transaction, surprising visible state.
Forgetting caches, object storage, search indexes, and secrets outside the database backup.

End-to-end story

Store WAL/archives immutably offsite; monitor shipping lag and retention like a product metric.
Document how to map wall time to transaction IDs and LSNs for your engine.
Pair DB restore drills with secret rotation when compromise is possible.

RTO / RPO

Test with realistic log volume, replay is often IO-bound; long transactions widen the uncertainty window around your cut point.

Automation

Quarterly restore to a throwaway VPC catches broken checksums, wrong encryption keys, and missing extensions before production needs them.

Managed services

RDS, Cloud SQL, and Azure expose PITR through consoles and APIs, the mental model is identical even when you never touch recovery.conf by hand.

TL;DR

Point-in-time recovery (PITR) replays transaction logs (WAL/redo) on top of a base backup to restore a database to an arbitrary timestamp, bounded by log retention. It fixes operator mistakes and corruption when backups are healthy; it cannot recover data never written to durable logs. Test restore runbooks and know your RPO window (how far back logs exist).