Part 194: Point-in-Time Recovery (PITR) & Disaster Recovery Runbooks

Rewinding your database to the exact second right before an accidental table drop.

1. RPO and RTO Metrics

• Recovery Point Objective (RPO): The maximum acceptable data loss measured in time (e.g. 5 minutes of lost transactions).
• Recovery Time Objective (RTO): The maximum acceptable downtime to restore operations (e.g. 30 minutes).

2. How PITR Works Under the Hood

PITR combines: 1. Base Physical Backup: A snapshot of the database files taken at an earlier time (e.g. Sunday midnight). 2. Continuous WAL Archiving: Every Write-Ahead Log segment is archived to Amazon S3 / Google Cloud Storage as soon as it fills.

3. Restoring to a Specific Target Timestamp

-- In recovery.signal / postgresql.conf:
restore_command = 'cp /mnt/wal_archive/%f %p'
recovery_target_time = '2026-09-12 23:15:42'
recovery_target_action = 'pause'

The database restores the base snapshot, replays WAL changes sequentially up to 23:15:42, and pauses right before the rogue query executed!

Key Takeaways

• Backups that have never been tested in a fire-drill restoration are not backups.

In Part 195, we explore Advanced SQL Injection: Second-Order, Blind & Defense-in-Depth!