Back to Glossary

PostgreSQL Backup & Recovery

PITR (Point-in-Time Recovery)

Learn how PostgreSQL PITR works with base backups and WAL archiving, when to use it, and how to test restores against real RPO/RTO targets.

Definition

Recovery of a cluster to an exact point in time or LSN using a base backup plus archived WAL and a recovery target.

How PITR Works

PostgreSQL writes every committed change to WAL (Write-Ahead Log). A base backup captures the database state at one point in time, and WAL archives record all subsequent changes.

During recovery, PostgreSQL restores the base backup first, then replays WAL entries until it reaches your recovery target. This model underpins serious disaster recovery strategies.

  • Step 1: Restore base backup
  • Step 2: Fetch archived WAL files via restore_command
  • Step 3: Stop replay at recovery_target_time, recovery_target_lsn, or another recovery target

When Teams Need PITR

PITR is critical for production systems where restoring to the latest backup is not enough. If a bad migration or accidental delete happened at 14:03, you need the option to recover to 14:02:59.

It is commonly used for incident response, compliance workflows, and recovery from operator mistakes. Teams implementing branch-based workflows often pair PITR with database branching to reduce blast radius.

PITR vs Snapshot Restore

PITR and snapshots solve related but different problems. Snapshot restore is often faster for full-environment rollback. PITR is more precise when you need to recover to a specific second before corruption.

Mature teams usually use both: snapshots for broad rollback operations and PITR for timeline precision.

How To Validate PITR Readiness

Run scheduled restore drills in isolated environments and measure both data correctness and time to service readiness.

If measured recovery time exceeds your RTO target, adjust storage, backup cadence, or orchestration immediately.

  • Define RPO/RTO per workload tier
  • Test recovery monthly and after major infra changes
  • Track restore duration, replay duration, and post-restore validation checks

Related Glossary Topics

pgbench pg_dump pg_restore pg_basebackup Physical vs Logical Backup Primary Key Partitioning ACID

Frequently Asked Questions

Is PITR the same as a normal PostgreSQL restore?
No. A standard restore typically brings you back to backup time. PITR replays WAL so you can recover to a specific point after the backup.
Do I need both base backups and WAL archiving for PITR?
Yes. Base backups provide the starting state, and WAL archives provide the change history needed to reach an exact recovery target.
How often should PITR be tested?
At least monthly for full restore drills, plus after major PostgreSQL, storage, or orchestration changes.
Can PITR recover only one table instead of the whole cluster?
Not directly. PITR restores a full cluster timeline. Teams usually restore to an isolated environment, then export just the required tables or rows.
What is the difference between recovery_target_time and recovery_target_lsn?
recovery_target_time stops replay at a timestamp, while recovery_target_lsn stops at an exact WAL position. LSN gives tighter precision when sequence boundaries matter.