Point-in-Time Recovery
Restoring a database to its exact state at any specific moment in time by replaying write-ahead log (WAL) segments on top of a base backup.
Description
Point-in-Time Recovery (PITR) combines a physical base backup with archived WAL segments to restore a database to any moment between the base backup and the last archived WAL. This is critical for recovering from human errors like accidental DELETE or DROP TABLE statements, where you need to restore to the moment just before the mistake occurred. Without PITR, you can only restore to the time of your last full backup, potentially losing hours of data.
In PostgreSQL, PITR works by: (1) taking a base backup using pg_basebackup, (2) continuously archiving WAL segments to durable storage using archive_command or pg_receivewal, and (3) during recovery, restoring the base backup and replaying WAL segments up to a specified target time, transaction ID, or named restore point. The recovery target is set via recovery_target_time, recovery_target_xid, or recovery_target_name in postgresql.conf (or recovery.conf in older versions).
Managed database services typically provide PITR as a built-in feature with a configurable retention window (e.g., AWS RDS supports up to 35 days). For self-managed PostgreSQL, you must configure WAL archiving, monitor archive lag, and ensure sufficient storage for WAL segments. A production database generating 10 GB of WAL per day needs substantial archive storage. Use pgBackRest or WAL-G for efficient WAL compression, encryption, and parallel backup/restore operations.
Prompt Snippet
Configure PostgreSQL continuous archiving using WAL-G with S3 as the archive target. Set archive_mode = on, archive_command to invoke wal-g wal-push, and wal_level = replica. Take daily base backups via wal-g backup-push and retain 7 days of base backups plus 14 days of WAL archives. Document the PITR runbook: restore base backup with wal-g backup-fetch, set recovery_target_time in postgresql.conf to the desired timestamp, start PostgreSQL in recovery mode, verify data integrity, then pg_wal_replay_resume(). Test the full PITR procedure quarterly against a staging environment.
Tags
Related Terms
Database Backup Strategies
Systematic approaches to creating, storing, and verifying database backups to protect against data loss from hardware failure, human error, or security incidents.
Database Replication
Copying data from one database server to another in real-time or near-real-time to provide redundancy, failover capability, and read scaling.
Schema Versioning
Tracking and managing the evolution of database schema over time through versioned migration files and metadata tables.