Point-in-Time Recovery (PITR) Mechanics

The Time Machine

Imagine a user accidentally runs DROP TABLE users; at 10:00:00 AM. You notice it at 10:05 AM. If you restore last night's backup, you lose all data from midnight until 10:00 AM — potentially hours of orders, signups, and transactions. That's unacceptable.

With PITR, you can say: "Restore the database to the state it was in at 09:59:59 AM" — one second before the accident.

How PITR Works Step by Step

Midnight          09:59:59 AM    10:00:00 AM    10:05 AM
    |                  |               |             |
[Base Backup]    [Target Point]  [DROP TABLE]  [Incident Noticed]
    |<-------------- WAL Stream ----------------->|

1. Retrieve Base Backup: The system fetches the last physical snapshot (taken at midnight). This is the starting point.
2. Download WAL Files: All WAL archive files generated between midnight and the target time are downloaded.
3. Replay WAL: The database engine replays each WAL record in order — every INSERT, UPDATE, DELETE, and DDL statement — advancing the database state forward through time.
4. Stop at Target: When the replay reaches 09:59:59, it stops. The recovery_target_time parameter controls this.
5. Promote: The database opens in read-write mode, now containing all data up to one second before the accident.

In PostgreSQL 12+, this is driven by a recovery.signal marker file plus a handful of recovery settings: a restore_command telling PostgreSQL how to fetch archived WAL segments, and a recovery_target_time that tells it exactly when to stop replaying.

The WAL Retention Window

PITR is only possible for the period covered by your WAL archive. If you retain 30 days of WAL, you can recover to any second within the last 30 days. If a WAL segment is missing or corrupt, the replay stops at that gap — you cannot skip over missing WAL files.

This is why WAL archive integrity monitoring is as important as the backup itself. A gap in the WAL stream silently destroys your PITR capability for everything after that gap.