EMZETT.
Login

Emzett Wiki

Point-in-time recovery: rolling a database back to a moment

Properties1
tagsdatenbank, backup

2 min read

Point-in-time recovery: rolling a database back to a moment

In short: Point-in-time recovery (PITR) restores a database to any moment in the past by replaying the transaction log on top of a base backup up to that point.

In more detail: A nightly backup helps little if a table is dropped by accident at 14:03: half a day is missing. PITR closes that gap by keeping every single change in addition to the backup.

In Depth

Principle

Databases write every change first to a write-ahead log (WAL). With PITR there is a base backup (full copy of the data) plus the continuously archived WAL. To restore you put the base backup back and let the WAL run until just before the mistake (recovery_target_time). Result: the state of 14:02:59 instead of last night.

Implementation (PostgreSQL)

  • archive_mode = on and archive_command copy finished WAL segments to a safe place.
  • Base backup with pg_basebackup (or tools such as pgBackRest, Barman, WAL-G).
  • Restore: put the backup in place, create recovery.signal, set the target time, start the server.
  • Managed services (for example Neon, Supabase, RDS) offer PITR as a feature, often with a limited window (hours to days depending on the plan).

Important notes

  • PITR protects against operator errors and faulty updates, not against storage failure if the WAL archive sits in the same place: store it off-site.
  • Test the restore; an untested backup is only hope.
  • You usually restore into a new instance and take over only the data you need.
  • Retention period to match retention duties and privacy (deleted personal data disappears from backups only after the period).
  • Key figures: RPO (how much data loss is acceptable) and RTO (how long the restore may take).

See also: pg_dump and psql, Transactions and ACID, Neon

Backlinks