Point-in-Time Recovery: Rewind Your Database to a Specific Second

WHY IT EXISTS: Traditional backups restore a database to the time of the backup, like midnight. If a critical failure happens at 3 PM, you lose 15 hours of data. PITR was invented to drastically shrink this data loss window (the Recovery Point Objective) from hours to mere seconds.

THE MENTAL MODEL: Think of PITR like a security camera system. A full backup is a single photo of a room taken once a day. The transaction log is a continuous video recording of every change. To see how the room looked at 2:35:15 PM, you don't scrub through days of video. You grab the last daily photo and play the video forward from there until you reach the exact second you need.

HOW IT WORKS: PITR is a two-step process. First, you restore the most recent full database backup taken before your target recovery time. This provides a stale but consistent state. Second, you apply a continuous log of all subsequent transactions (like PostgreSQL's Write-Ahead Log or MySQL's binlog) up to your precise point in time. The database replays these transactions, rolling forward to the exact state it was in at that moment.

WHEN TO USE IT: PITR is your "undo" button for logical data corruption. Use it to recover from human error, like an UPDATE query without a WHERE clause, a buggy code deployment that corrupts records, or an accidental DROP TABLE. It saves you from losing all work done since the last nightly backup.

WHEN NOT TO USE IT: PITR is not a high-availability solution. For surviving hardware failure, you should fail over to a hot standby replica, which is nearly instantaneous. A full PITR process can take hours for large databases. It's also overkill for systems where some data loss is acceptable, like caches or non-critical analytics data.

ONE CANONICAL EXAMPLE: PostgreSQL implements PITR using base backups and continuous archiving of its Write-Ahead Log (WAL). An admin takes a base backup, then configures the server to archive every WAL file to durable storage. To recover, they restore the base backup and provide a command that tells Postgres how to fetch and replay the archived WAL files until a specified timestamp.