SQL Academy · Lesson

Point-in-Time Recovery

Restore to any moment with WAL.

What Is Point-in-Time Recovery?

Point-in-Time Recovery (PITR) lets you restore a database to any specific moment in the past, not just to the time of the last backup snapshot.

This is possible because PostgreSQL continuously writes every change to a stream called the Write-Ahead Log (WAL). By replaying WAL records on top of a base backup you can land exactly where you need to be.

The Write-Ahead Log (WAL)

Every INSERT, UPDATE, DELETE, and DDL statement is first written to the WAL before it touches the actual data files. This guarantees durability even if the server crashes mid-write.

For PITR, archived WAL segments are the time-machine tapes. The base backup is the starting point; the WAL fills in everything that happened after it.

-- Check current WAL write location
SELECT pg_current_wal_lsn() AS current_lsn,
       pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file;

All lessons in this course

← Back to SQL Academy