How PITR database recovery works for Postgress database
For Point-in-Time Recovery (PITR) to work, the PostgreSQL system needs to be backing up continuously.
Here is how that works and why the two components—Base Backups and WALs—are both essential for continuous recovery:
The PITR Continuous Loop 🔄
Point-in-Time Recovery relies on two separate, but connected, processes to capture the database's state continuously:
1. Base Backup (The Starting Point) 💾
The physical backup (or "base backup") is the full snapshot of the database files taken at a specific moment. This is the foundation of your recovery.
- Analogy: This is like taking a single, full-length movie recording of a street at 8:00 AM on Monday.
- Need: You need a full base backup to have a starting state to restore from. If you didn't have the base backup, you'd have no foundation to apply the changes to.
<hr>
2. Continuous WAL Archiving (The Continuous Changes) 📝
The Write-Ahead Log (WAL) is a journal of every single change (insert, update, delete) made to the database. These small log files are generated constantly. For PITR, the system is configured to archive these small files as soon as they are filled up.
- Analogy: This is like continuously collecting every frame of the movie recording after 8:00 AM on Monday.
- Continuous Nature: Because the WAL files are constantly being generated and archived, they create an unbroken timeline of every transaction. This is the continuous backup part.
<hr>
How the Recovery Works
To perform a PITR, you tell PostgreSQL the exact date and time you want to go back to (e.g., Tuesday at 1:17 PM).
- Restore the Base Backup: PostgreSQL first restores the last complete physical base backup (e.g., the one from Monday at 8:00 AM).
- Apply the WALs: It then starts loading and applying the archived WAL files in order, replaying every transaction.
- Stop at the Point-in-Time: When the system reaches the requested time (1:17 PM), it stops applying the changes.
Since the WAL files contain the record of changes up to the very last second, you can effectively rewind the database to any arbitrary second between your base backups.