Introduction
Point-in-Time Recovery (PITR) allows a database administrator to restore a database to its exact state at any arbitrary moment in the past.
This capability is fundamental to disaster recovery and its mechanics are deeply intertwined with the write-ahead logging (WAL) infrastructure that most modern databases rely on for crash recovery.
PITR is not simply "restore from backup." A base backup captures a snapshot of the data files at a single moment, but the database state between backups would be lost without a mechanism to replay the intervening changes.
PITR fills this gap by combining a base backup with a continuous archive of WAL (or redo log) records, enabling recovery to any point between the backup timestamp and the most recent archived log segment.
Foundations: Write-Ahead Logging
The WAL protocol requires that every modification to a data page be recorded in a log entry before the modified page is written to durable storage.
Each log record carries a monotonically increasing identifier, typically called a Log Sequence Number (LSN).
The LSN provides a total order over all changes and serves as the temporal coordinate system that makes PITR possible.
A WAL record typically contains:
- The LSN of the record.
- The transaction ID that produced the change.
- The type of operation (insert, update, delete, page split, etc.).
- After-image data describing the new state of the affected page (the redo information). Before-image data, used for undo, is stored separately — in an undo log (Oracle, InnoDB) or via MVCC heap structures (PostgreSQL) — not in the WAL record itself.
- A pointer (prevLSN) to the previous log record for the same transaction, used during undo traversal.
Because the WAL captures every state transition, replaying WAL records from a known-good starting state deterministically reproduces every subsequent state the database has passed through.
Components of PITR
Base Backup
A base backup is a copy of the database's data files taken at a known LSN.
The backup does not need to represent a perfectly consistent snapshot if the WAL records covering the backup window are available.
PostgreSQL, for example, requests a checkpoint at backup start, records the resulting starting LSN (the "redo point"), and then copies data files while the database continues to operate.
Pages may be copied in an inconsistent state (some pages from before a transaction commits, some from after), but this is acceptable because WAL replay from the redo point will bring every page to a consistent state.
WAL Archiving
Between base backups, the database continuously generates WAL segments.
PITR requires that these segments be archived to durable storage (separate from the database host) before the database recycles them.
In PostgreSQL this is handled by archive_command.
In MySQL, the InnoDB redo log handles crash recovery internally, while the binary log (binlog) — a separate, higher-level logical log — serves the PITR role; both layers are needed for full MySQL PITR.
Oracle uses archived redo logs managed by the ARCn background processes.
The chain of archived WAL segments, starting from the base backup's redo point, forms the recovery timeline.
Recovery Target Specification
The operator specifies a target for recovery using one of several coordinate systems:
- Timestamp: recover to a wall-clock time (e.g.,
2024-06-15 14:30:00 UTC). - LSN/SCN: recover to a specific log sequence number or system change number.
- Transaction ID: recover up to and including (or excluding) a named transaction.
- Named restore point: recover to a label previously inserted into the WAL stream.
The recovery process replays WAL records sequentially and halts when it reaches the specified target.
Walkthrough
The following step-by-step procedure describes PITR recovery mechanics at the engine level.
The steps are generalized across PostgreSQL, Oracle, and similar systems.
PITR_RECOVERY(base_backup, wal_archive, target):
1. RESTORE base_backup to the data directory.
- Data files are now at the state captured during the backup,
possibly containing torn pages.
2. READ the backup metadata to obtain redo_start_lsn,
the LSN at which WAL replay must begin.
3. LOCATE the WAL segment in wal_archive that contains redo_start_lsn.
4. SET current_lsn = redo_start_lsn.
5. LOOP:
a. READ the next WAL record R at current_lsn.
b. IF R.lsn > target:
BREAK // Target reached; stop replay.
c. FETCH the data page P referenced by R from the buffer pool
(reading from disk if necessary).
d. IF P.page_lsn >= R.lsn:
SKIP // Page already contains this change (idempotency).
ELSE:
APPLY R to P.
SET P.page_lsn = R.lsn.
e. SET current_lsn = next LSN after R.
6. After replay halts:
a. IDENTIFY all transactions that were in-progress (uncommitted)
at the target LSN.
b. ROLLBACK each in-progress transaction using undo information.
- Oracle and InnoDB use a dedicated undo log for this purpose.
- PostgreSQL relies on its MVCC heap visibility mechanism:
tuples written by aborted or in-flight transactions are
simply not visible to new connections, with no explicit
undo log traversal required.
c. This ensures the recovered state contains only committed
transaction effects.
7. MARK the database as open for new connections.
- Write a new checkpoint.
- Optionally create a new WAL timeline to distinguish
post-recovery WAL from the original history.
Idempotency and the Page LSN Check
Step 5d deserves emphasis.
Because the base backup may contain pages that were flushed to disk after the redo point, some pages already reflect changes recorded in early WAL records.
The page LSN check ensures that a WAL record is applied only if the page has not already incorporated that change.
This makes replay idempotent, which is critical for correctness: without it, applying a physiological log record (e.g., "insert tuple at offset 3 on page 42") twice could corrupt the page.
Timeline Management
PostgreSQL introduces the concept of a "timeline" to handle the branching history that PITR creates.
When you recover to a point in the past and then begin accepting new writes, the new WAL sequence diverges from the original history.
The timeline ID (encoded in WAL file names) prevents confusion between pre-recovery and post-recovery WAL.
This also enables recovery from a previously recovered state, supporting chains of recoveries without ambiguity.
Considerations and Tradeoffs
Storage Costs
PITR requires retaining all WAL segments generated since the last base backup.
A write-heavy workload can produce terabytes of WAL per day.
Organizations must balance backup frequency (which determines how much WAL must be retained) against the storage cost of base backups themselves.
Recovery Time Objective (RTO)
Recovery time is proportional to the volume of WAL that must be replayed.
A base backup taken one week ago requires replaying one week of WAL, which can take hours.
Incremental base backups or more frequent full backups reduce replay volume and improve RTO at the cost of additional backup storage and I/O overhead.
Logical, Physical, and Physiological WAL
Log records fall into three broad categories:
- Physical: describe byte-level changes to specific pages and offsets. Simplest to replay, but is tightly coupled to storage layout.
- Physiological: identify a specific page but describe the change in a logical, operation-oriented way (e.g., "insert tuple at slot 3"). This is the dominant mode in production systems such as PostgreSQL and InnoDB, balancing replay efficiency with some abstraction from raw byte offsets.
- Logical: describe changes in terms of table-level operations (e.g., "insert row with key=7 into table T"), independent of physical layout. Useful for logical replication and cross-version recovery, but requires more complex ordering and concurrency handling during replay.
Most production PITR systems use physiological logging for the redo path.
Interaction with Tablespace and Schema Changes
DDL operations (CREATE TABLE, ALTER INDEX, DROP COLUMN) are also recorded in the WAL.
PITR replay must handle these correctly, reconstructing catalog state as it existed at each point during replay.
If a table was dropped at 15:00 and the recovery target is 14:30, the replay process must not apply the DROP, leaving the table intact in the recovered state.
Continuous Archiving Failures
A gap in the WAL archive is fatal to PITR.
If segment N is missing, recovery cannot proceed past segment N-1, regardless of the target.
Monitoring WAL archival lag and completeness is operationally essential.
PostgreSQL's pg_stat_archiver view and equivalent facilities in other systems expose archival status.
Key Points
- PITR combines a base backup with a sequential replay of archived WAL records to reconstruct the database state at any arbitrary past moment.
- WAL records carry after-image (redo) information; before-image data for undo is maintained separately in undo logs, or MVCC structures depending on the engine.
- The page LSN check during WAL replay ensures idempotency, preventing double-application of changes to pages that were already up-to-date in the base backup.
- Recovery targets can be specified by timestamp, LSN, transaction ID, or named restore point, giving operators flexible control over the exact recovery destination.
- Recovery time is directly proportional to the volume of WAL that must be replayed; more frequent base backups reduce this volume and improve RTO.
- A gap in the archived WAL sequence makes recovery past that gap impossible, making continuous archival monitoring a critical operational requirement.
- After replay halts at the target, uncommitted transactions must be rolled back (or their effects made invisible) to ensure the recovered state reflects only committed work.
- Timeline (or incarnation) management prevents ambiguity when new WAL is generated after a recovery, enabling chains of point-in-time recoveries.
References
C. Mohan, Don Haderle, Bruce Lindsay, Hamid Pirahesh, and Peter Schwarz. "ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging." ACM Transactions on Database Systems, 17(1), March 1992.
Jim Gray and Andreas Reuter. "Transaction Processing: Concepts and Techniques." Morgan Kaufmann, 1993.
PostgreSQL Global Development Group. "Chapter 25: Backup and Restore." PostgreSQL Documentation. https://www.postgresql.org/docs/current/backup.html
Oracle Corporation. "Oracle Database Backup and Recovery User's Guide." Oracle Documentation, 2023.
Ramakrishnan, Raghu and Johannes Gehrke. "Database Management Systems." 3rd Edition, McGraw-Hill, 2003.