PITR

PITR, or point-in-time recovery, is a database restore technique that allows reconstructing a database to a specific moment in time, using a combination of full backups and a continuous stream of transaction logs or redo logs. The goal is to recover from data corruption, user error, or other incidents with minimal data loss.

Mechanism: A base backup provides a snapshot of the database at a known time. Ongoing transaction logs (such as WAL, redo logs, or binary logs) are archived or retained. To recover, you restore the base backup to a server, then apply the archived logs in sequence up to the desired target time; any transactions that occurred after that time are not applied.

Common workflows: On a disaster, stop writes, restore to a new instance, apply logs to the target time, then switch over. On accidental delete, perform PITR to a moment before the deletion, then export or reapply the affected data.

Implementation: Different databases implement PITR in different ways. PostgreSQL uses base backups with continuous WAL archiving and a recovery target time. MySQL uses binary logs to replay transactions. Oracle offers Flashback Database and Flashback Query. SQL Server uses full backups and a sequence of log backups to a target time.

Limitations: PITR requires timely backups and log retention, storage costs, and a plan for validating restores. There is potential data loss up to the target time if logs or backups were missing or corrupted. Restore times can be substantial for large databases.