Point-in-Time Recovery (PITR)

WHAT THIS TESTS Whether you understand that fine-grained recovery comes from replaying a transaction log over a base image, not from snapshots alone, and can weigh the operational cost.

WHY IT MATTERS A nightly backup limits you to losing up to a day of data and recovering only to the snapshot instant. Point-in-Time Recovery lets you rewind to any moment within your retention window, for example the second before a DROP TABLE ran, dramatically shrinking data loss.

A GOOD ANSWER COVERS PITR needs two ingredients: periodic base backups and a continuous archive of the write-ahead log (the WAL in PostgreSQL, binlogs in MySQL). To recover, you restore the most recent base backup taken before your target time, then replay archived log records forward, stopping at the specified timestamp, transaction id, or named restore point. The log is what supplies the per-transaction granularity. The tradeoffs are real: continuous WAL archiving consumes storage that grows with write volume, adds I/O and shipping overhead, and demands an unbroken log chain, because a single gap in the archive caps how far forward you can replay. Recovery time also scales with how much log must be replayed since the base backup.

COMMON WRONG ANSWERS Believing snapshots alone enable arbitrary recovery, forgetting the WAL archive, or ignoring that a broken log chain silently limits the recoverable range.

LIKELY FOLLOW-UPS How base-backup frequency trades storage against replay time; how this defines recovery point objective; how to test recovery regularly.

ONE CONCRETE EXAMPLE At 14:32 an engineer accidentally deletes a table. With PITR, the team restores Sunday's base backup and replays WAL up to 14:31:59, recovering all data right before the deletion. Without archived logs, they could only fall back to the last nightly snapshot and lose hours of writes.