OneNoughtOne
0/278
Loading content...
Read aloud is unavailable in this browser.
Imagine you're the database administrator for a major financial institution. At 3:47 PM on a busy trading day, a critical bug in a newly deployed application causes a cascade of erroneous transactions that corrupt critical account balances. The bug is discovered at 4:15 PM, but by then, thousands of transactions—some legitimate, some erroneous—have been committed.
You have your nightly backup from midnight, but restoring it would mean losing 16 hours of legitimate transactions worth millions of dollars. What you need is the ability to restore your database to exactly 3:46 PM—one minute before the disaster began—preserving every valid transaction while eliminating the corruption.
This is the problem that Point-in-Time Recovery (PITR) solves.
PITR transforms the binary choice of "restore backup and lose everything after" into a surgical precision instrument that can navigate your database to any moment in its history. It is, in essence, a time machine for your data.
By the end of this page, you will understand the foundational concepts of Point-in-Time Recovery, including how it differs from traditional backup/restore, the architectural components that enable temporal navigation, the relationship between transaction logs and PITR capability, and why PITR has become essential for mission-critical database systems.
Point-in-Time Recovery (PITR) is a database recovery technique that enables restoration to a specific moment in the database's transaction history, rather than being limited to discrete backup snapshots. PITR combines the stability of periodic backups with the granularity of continuous transaction logging to achieve temporal precision that traditional backup strategies cannot match.
At its core, PITR rests on a profound insight: a database's state at any moment is the cumulative result of all transactions executed up to that moment. This means that if we have:
We can reconstruct the database's state at any point between the backup and the present by replaying transactions up to the desired moment and stopping there.
This insight transforms transaction logs from mere recovery tools into a temporal navigation system—a complete historical record that allows us to travel to any point in the database's past.
If S₀ represents your database state at backup time, and T₁, T₂, ..., Tₙ represent all transactions committed after the backup, then the database state at any time t is: S(t) = S₀ + T₁ + T₂ + ... + Tₖ, where Tₖ is the last transaction committed before time t. PITR exploits this mathematical property of database state evolution.
PITR capability rests on three essential pillars, each of which must be properly implemented for the system to function:
1. Base Backups (Restoration Foundation)
A base backup provides the starting point for PITR. This is a complete, consistent snapshot of the database at a specific moment. Without this anchor point, there's no baseline from which to begin reconstruction.
The base backup must be:
2. Transaction Logs (Change History)
Transaction logs (also called Write-Ahead Logs, redo logs, or journal files depending on the database system) provide the complete record of all changes since the base backup. These logs must be:
3. Log Sequence Tracking (Temporal Correlation)
The system must maintain precise correlation between log records and physical time. This typically involves:
| Pillar | What It Provides | Failure Impact | Key Requirements |
|---|---|---|---|
| Base Backup | Starting state for reconstruction | Cannot begin recovery; must use older backup | Consistency, completeness, tested restorability |
| Transaction Logs | Change history from backup to target | Recovery limited to last continuous log segment | Complete archival, no gaps, corruption protection |
| LSN/Timestamp Tracking | Temporal correlation; where to stop | Cannot target specific time; recovery imprecise | Accurate clocks, LSN integrity, checkpoint correlation |
To fully appreciate PITR's significance, we must understand how it differs from traditional backup and restore approaches. The distinction goes beyond capability—it represents a fundamental shift in how we think about data protection.
Traditional backup approaches create periodic snapshots of database state. Recovery means selecting the most recent backup that precedes the failure and restoring from that snapshot. This approach has several inherent limitations:
PITR transcends these limitations by treating recovery as navigation through a continuous timeline rather than selection among discrete snapshots. The difference is illustrated in the recovery options:
PITR transforms RPO from 'backup interval' (hours or days) to 'log flush interval' (seconds or less). For many critical systems, this difference represents millions of dollars in protected transactions and operational continuity.
The shift from traditional backup to PITR represents a change in mental model:
Traditional Model: Database state exists as a series of discrete photographs taken at backup times. Recovery means selecting the right photograph.
PITR Model: Database state exists as a continuous film, with every frame preserved. Recovery means seeking to the right frame.
This shift has profound implications:
Forensic Capability: PITR enables examination of the database at multiple historical points, facilitating root cause analysis
Surgical Recovery: Instead of wholesale restoration, administrators can make informed decisions about exactly when to stop recovery
Reduced Recovery Complexity: Paradoxically, PITR simplifies many recovery scenarios by providing more options rather than forcing all-or-nothing choices
Compliance Support: Many regulatory frameworks require the ability to reconstruct database state at specific times—something only PITR can reliably provide
PITR capability doesn't emerge from a single feature—it requires a carefully orchestrated architecture with multiple interacting components. Understanding this architecture is essential for implementing, operating, and troubleshooting PITR systems.
A complete PITR implementation consists of several critical components, each with specific responsibilities:
The Log Sequence Number (LSN) is the linchpin of PITR architecture. An LSN is a unique, monotonically increasing identifier assigned to each log record. LSNs provide:
In most implementations, the LSN encodes both:
For example, in PostgreSQL, an LSN like 1/4A3B7C00 indicates file 1, offset 0x4A3B7C00.
The LSN functions as a logical clock for the database, providing Lamport-style ordering of events. Every action that modifies the database advances the LSN, creating an unambiguous timeline of all changes.
A PITR-compatible base backup differs from a simple file copy in crucial ways:
1. LSN Recording The backup process must record the LSN at which it started and completed. This creates the correlation:
2. Consistency Without Blocking Modern databases take base backups without stopping operations. This is achieved through:
3. Metadata Inclusion The backup must include:
4. Integrity Verification PITR base backups should include checksums or other verification allowing detection of corrupted backups before a recovery emergency.
12345678910111213141516171819202122
{ "backup_type": "full_base_backup", "database_version": "15.4", "backup_start_time": "2024-01-15T00:00:05.283Z", "backup_end_time": "2024-01-15T00:47:22.891Z", "start_lsn": "2/1A000028", "end_lsn": "2/1A3F7B60", "checkpoint_lsn": "2/1A000028", "wal_start_segment": "000000010000000200000001A", "required_wal_segments": [ "000000010000000200000001A", "000000010000000200000001B", "000000010000000200000001C" ], "tablespace_map": { "16384": "/data/pg_default", "16385": "/data/pg_global" }, "backup_size_bytes": 157286400, "backup_checksum": "sha256:e3b0c44298fc1c14...", "compatible_for_pitr": true}The gap between base backups and recovery targets is bridged by archived transaction logs. Log archival is the process of preserving log segments beyond their normal lifecycle, creating the continuous record that enables PITR.
In a database without PITR, transaction logs follow a simple lifecycle:
This recycling is essential—without it, logs would grow unbounded. But recycling destroys the historical record needed for PITR.
Log archival interrupts this lifecycle by copying segments to archive storage before recycling:
The critical invariant: No log segment is recycled until successfully archived.
If archiving fails and the database continues operating, logs will eventually be recycled to prevent disk exhaustion. This creates an 'archive gap'—a period with no log coverage—which permanently limits PITR capability. You can only recover up to the last continuous log segment before the gap.
The choice of archive storage profoundly affects PITR reliability and recovery speed:
Local File System
Network Attached Storage (NAS)
Object Storage (S3, Azure Blob, GCS)
Tape/Cold Storage
| Storage Type | Durability | Retrieval Speed | Cost | Best For |
|---|---|---|---|---|
| Local Filesystem | Low (single disk) | Fastest (<1ms) | Medium | Development, basic DR |
| NAS/SAN | Medium (RAID) | Fast (1-10ms) | Medium-High | Operational recovery |
| Object Storage | Very High (11 nines) | Moderate (100ms-1s) | Low | Primary archive tier |
| Cold/Tape | Very High | Slow (minutes-hours) | Very Low | Long-term compliance |
Databases offer two approaches to WAL archival:
Segment-Based Archival The traditional approach where complete log segments are archived as units:
Continuous (Streaming) Archival Log records streamed continuously to archive storage:
Modern enterprise deployments often combine both: streaming archival for minimal RPO, with segment-based archival as a backup mechanism.
The ability to recover to a specific time (rather than a specific LSN) is what makes PITR practically useful. Administrators think in terms of "restore to 3:46 PM before the incident" rather than "restore to LSN 5/2A7C3400." This requires accurate correlation between wall-clock time and log positions.
Every transaction commit records a commit timestamp alongside its LSN. This creates a mapping:
LSN | Commit Timestamp | Transaction
------------|-------------------------|------------
5/2A000100 | 2024-01-15 15:45:22.103 | T1
5/2A000228 | 2024-01-15 15:45:22.847 | T2
5/2A000390 | 2024-01-15 15:45:23.201 | T3
5/2A000510 | 2024-01-15 15:45:24.592 | T4
...
When you specify a recovery target like "2024-01-15 15:45:23," the recovery process:
The result is the database state as it existed at the target time—all transactions committed before that moment are present, and none committed after.
PITR uses transaction commit timestamps, not start timestamps. A transaction that started at 15:44 but committed at 15:46 will be excluded if the recovery target is 15:45. This matches the database's durability guarantees—a transaction isn't durable until committed.
Time-based recovery introduces a hidden dependency: clock accuracy. The timestamps in log records reflect the database server's clock at commit time. If that clock is wrong, the mapping between "real" time and LSN is distorted.
Consider this scenario:
Mitigation strategies:
PITR recovery targets the boundary between transactions, not arbitrary points within transactions. This is a fundamental constraint, not a limitation:
Why Transaction Boundaries Matter:
Practical Implications:
Example:
Target Time: 15:45:30.000
Transaction Commits:
T1 committed at 15:45:29.847 <- Last transaction BEFORE target
T2 committed at 15:45:30.291 <- First transaction AFTER target
Recovery Result: Database state includes T1, does not include T2
Actual recovery time: 15:45:29.847 (snapped to T1 commit)
Modern PITR implementations offer multiple ways to specify recovery targets, each suited to different scenarios:
The most intuitive mode—specify a wall-clock timestamp:
-- PostgreSQL example
recovery_target_time = '2024-01-15 15:45:30 America/New_York'
Best for: Incidents with known approximate times, compliance requirements Precision: Transaction boundary nearest to specified time Requirements: Accurate server clocks, known timezone
Specify an exact position in the log sequence:
recovery_target_lsn = '5/2A7C3400'
Best for: Precise recovery after log inspection, repeatable recovery procedures Precision: Exact transaction boundary at specified LSN Requirements: Prior log analysis to determine target LSN
Specify a transaction ID (XID) as the stopping point:
recovery_target_xid = '12847294'
Best for: Recovering to include/exclude specific known transactions Precision: Exact transaction boundary Requirements: Knowledge of specific transaction identifiers
Recover to a named restore point created by the application:
-- Creating a restore point in PostgreSQL
SELECT pg_create_restore_point('before_migration_v5.2');
-- Recovering to that point
recovery_target_name = 'before_migration_v5.2'
Best for: Planned procedures with intentional checkpoints, release deployments Precision: Exact point where restore point was created Requirements: Proactive restore point creation before significant operations
| Mode | Target Specification | Precision | Use Case |
|---|---|---|---|
| Time | Timestamp with timezone | Transaction boundary | Known incident times |
| LSN | Log sequence number | Exact boundary | Post-analysis recovery |
| XID | Transaction identifier | Exact transaction | Specific transaction |
| Named Point | Restore point name | Exact point | Planned procedures |
Most databases offer an 'inclusive' option for recovery targets. With inclusive=true, recovery includes the transaction at the target; with inclusive=false (default), it stops just before. This is crucial when you want to include a specific transaction's effects.
PITR exists within a broader ecosystem of database protection technologies. Understanding the relationships and distinctions helps administrators select appropriate tools for different scenarios.
Flashback (Oracle) and Temporal Tables (SQL:2011, SQL Server, PostgreSQL) provide row-level time travel within the running database:
PITR:
Key Distinction: Flashback is for querying history; PITR is for restoring to history.
Logical Replication streams logical changes (row changes) to replicas:
PITR:
Key Distinction: Replication distributes current state; PITR preserves historical states.
CDP (often a storage-layer feature) provides continuous capture of all I/O:
PITR:
Key Distinction: CDP captures physical blocks; PITR ensures logical consistency.
We've established the foundational understanding of Point-in-Time Recovery. Let's consolidate the essential concepts:
What's next:
Now that we understand the conceptual foundations of PITR, we'll examine Log Archiving in depth. The next page explores the mechanics of preserving transaction logs, archive storage strategies, monitoring archival health, and the critical operational practices that ensure PITR capability is maintained through the routine operation of the database system.
You now understand the foundational concepts of Point-in-Time Recovery. You've learned what PITR is, how it differs from traditional backup/restore, the architectural components that enable it, and the role of log sequence numbers in temporal navigation. Next, we'll explore the critical process of log archiving that makes PITR possible.