Commit Log Archiving and PITR

In this lesson, you will learn how to restore a database to a specific millisecond in the past by combining base backups with a continuous stream of archived Write-Ahead Logs (WAL).

The Foundation: Base Backups

Think of a "Base Backup" as a full snapshot of your database’s physical storage files at a single point in time. While this snapshot is essential, it is already "stale" the moment it finishes because data changes constantly. To bridge the gap between that snapshot and the present, we need the database's change history.

The Mechanism: WAL Archiving

As we learned in our previous look at the Write-Ahead Logging Architecture, every transaction is recorded in a WAL file before being applied to the heap. Normally, these logs are recycled to save disk space. However, to enable Point-in-Time Recovery (PITR), we configure the database to "archive" these files.

By setting archive_mode = on and defining an archive_command, the database copies completed WAL segments to a secure, long-term storage location (like a separate disk or cloud bucket) immediately after they are filled.

Performing the Recovery

If a catastrophic data loss occurs—such as an accidental DROP TABLE at 10:05 AM—you perform PITR through these steps:

1. Restore the Base Backup: Replace your corrupted data directory with the most recent full snapshot.
2. Configure Recovery: Create a recovery.signal file (in older versions, recovery.conf) that tells PostgreSQL to enter recovery mode instead of starting normally.
3. Replay the Logs: Provide a recovery_target_time in your configuration:

   -- In your recovery configuration file:
   recovery_target_time = '2023-10-27 10:04:59'
   restore_command = 'cp /path/to/archive/%f %p'
   

4. Playback: The database reads your base backup, then sequentially replays every archived WAL file up until the timestamp provided. It stops exactly one second before the disaster, ensuring your data is reconstructed to the state it was in just before the error occurred.

Key Takeaway: PITR works by applying a stream of archived WAL changes onto a physical base backup, allowing you to "replay" history until a specific moment in time.