← All guides

PITR vs logical backups: you need both

WAL-based point-in-time recovery and pg_dump archives fail differently. The matrix of incidents shows why neither alone is enough.

What each is good at

PITR (WAL archiving) rewinds the whole cluster to any second — unbeatable for 'we dropped the table at 14:32'. Logical dumps are portable, selective (one table is fine), version-upgradable, and cheap to keep for a year — unbeatable for 'this data has been subtly wrong since March' and 'load prod into staging'.

The incident matrix

Dropped table 10 minutes ago → PITR wins. Corruption discovered after 6 weeks → logical monthly archive wins (PITR window expired). Provider/account lost → whichever copy lives OUTSIDE wins. Auditor asks for evidence → the restore-tested archive with a report wins. Need one table from last Tuesday → logical wins (PITR restores everything or nothing).

A sane combined posture

Use your platform's PITR for the short window (hours–days). Add independent, encrypted logical archives on a GFS schedule for depth, portability, and proof. Verify the logical ones by actually restoring them — an unverified archive is a hope with a timestamp.

A backup you've never restored is a hope, not a backup.

Firedrill restore-tests every backup it takes — on real infrastructure, with the report to prove it.