Point-in-time recovery, explained simply

Point-in-time recovery (PITR) lets you restore a database to any moment, not just the timestamp of your last backup. It's the difference between "we lost six hours of orders" and "we lost six seconds." Most self-hosted setups skip it because it looks complicated. It isn't — it's three pieces working together: a base backup, a continuous stream of write-ahead logs, and a replay step.

§1 Why it matters

A nightly pg_dump protects you from disk failure. It does not protect you from a bad migration that runs at 2:14pm and quietly corrupts rows for three hours before anyone notices. PITR closes that gap by letting you replay every committed transaction up to — but not including — the mistake.

§2 How WAL archiving works

PostgreSQL writes every change to the write-ahead log (WAL) before it touches the actual data files. If you archive those WAL segments continuously, you effectively have a moving record of the entire database's history. Recovery is just: load an old base backup, then replay WAL forward to the point you want.

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

§3 Taking the base backup

Use pg_basebackup rather than copying the data directory by hand — it talks to the running server and guarantees a consistent starting point.

pg_basebackup -D /backup/base -Fp -Xs -P -U replicator

§4 Restoring to a point in time

Drop a recovery.signal file next to your restored base backup and set a target time. On startup, Postgres will replay WAL until it hits that timestamp and then stop, leaving the database in a consistent, queryable state.

# postgresql.conf, after restoring the base backup
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-08-22 14:09:00'

§5 Common pitfalls

• Archiving to the same disk as the primary database — a single disk failure takes out both.
• Never rehearsing a restore, so the first real attempt happens under pressure.
• Forgetting to prune old WAL segments, which quietly fills the archive volume.