Point-in-Time Recovery

Point-in-Time Recovery is a powerful capability, but it's not a panacea. Like any technology, PITR has fundamental limitations that can't be engineered away—only understood and addressed through complementary strategies.

Consider these scenarios:

• Ransomware encrypts your entire infrastructure, including the backup server storing your archived WALs
• A subtle data corruption bug has been in production for 3 weeks, well beyond your archive retention window
• Recovery time for your 10TB database would take 8 hours, but your business can only tolerate 30 minutes of downtime
• Critical transactions occurred in the 5 seconds between your incident and the last archived log segment

Each scenario represents a category of limitation inherent to PITR. This page explores these constraints comprehensively, ensuring you understand not just what PITR can do, but equally important—what it cannot do and what complementary strategies fill those gaps.

PITR dramatically improves RPO compared to periodic backups, but it cannot achieve true zero data loss. Understanding the sources of data loss risk is essential.

The Irreducible Data Loss Window

Even with PITR, some data loss is theoretically possible:

1. Unarchived WAL Segments

With segment-based archiving, uncommitted or unarchived data is at risk:

Archived Segments: [1] [2] [3] [4] [5]
Current Segment:   [6 - 75% full, not yet archived]

Failure occurs → Segment 6 lost → Transactions in segment 6 lost

The amount of data at risk equals the unarchived portion of the current segment (up to 16MB typically).

2. In-Flight Transactions

Transactions that haven't committed at failure time:

• Not recorded in WAL as committed
• Cannot be recovered by PITR
• Application must retry after recovery

3. Asynchronous Replication Lag

If archiving to remote storage with async replication:

• Archive storage may lag behind database
• Disaster destroying database may also destroy unsynced archives

4. Commit Acknowledgment vs. Archive Completion

With asynchronous archiving:

Time 14:30:00.000 - Transaction commits (acknowledged to client)
Time 14:30:00.500 - WAL record written to local segment
Time 14:30:01.000 - FAILURE OCCURS
Time 14:30:05.000 - Would have archived segment

Result: Transaction was committed from application's perspective but is LOST

Achieving Near-Zero RPO

To minimize RPO within PITR capabilities:

1. Use Streaming Archival

# pg_receivewal for continuous streaming
pg_receivewal -h primary -D /archive/streaming --synchronous

2. Reduce archive_timeout

-- Force segment switch every 60 seconds maximum
ALTER SYSTEM SET archive_timeout = '60s';

3. Combine with Synchronous Replication

-- Ensure at least one synchronous standby
ALTER SYSTEM SET synchronous_standby_names = 'standby1';
ALTER SYSTEM SET synchronous_commit = 'remote_apply';

4. Archive to Multiple Destinations If either destination survives, recovery is possible.

PITR recovery time is proportional to the amount of WAL that must be replayed. For large databases with long backup intervals, RTO can be unacceptably long.

Components of PITR Recovery Time

1. Base Backup Restoration Time

The time to copy/restore the base backup to the recovery location:

Time = BaseBackupSize / TransferRate

Example:
- 500GB base backup
- 100 MB/s transfer rate
= 5000 seconds ≈ 83 minutes just for base backup

2. WAL Replay Time

The time to replay all WAL from backup to recovery target:

Time = WALVolume / ReplayRate

Example:
- 200GB of WAL to replay
- 50 MB/s replay rate
= 4000 seconds ≈ 67 minutes for replay

3. Post-Recovery Tasks

Startup time, verification, replication re-establishment:

• Database startup and cache warming
• Integrity verification
• Standby rebuild
• Application reconnection

Total RTO Example:

Base restoration: 83 minutes
WAL replay:       67 minutes
Startup/verify:   10 minutes
Total:           160 minutes = 2.7 hours

Strategies to Reduce RTO

1. More Frequent Base Backups

Reducing time between base backups reduces WAL replay volume:

Weekly backups: Up to 7 days of WAL to replay
Daily backups:  Up to 1 day of WAL to replay
Hourly backups: Up to 1 hour of WAL to replay (incremental)

2. Incremental/Differential Backups

Only back up changed blocks, reducing backup size and restoration time.

3. Parallel WAL Replay

PostgreSQL 12+ supports parallel recovery:

ALTER SYSTEM SET max_parallel_workers = 8;

4. Hot Standby as Fast Failover

Maintain a continuously-recovering standby that can be promoted instantly:

• No base backup restoration needed
• Minimal WAL gap
• RTO measured in seconds, not hours

5. Pre-Staged Recovery Infrastructure

Pre-restore base backups to recovery servers:

• Base backup always ready
• Only WAL replay needed at recovery time
• Trades storage cost for recovery speed

PITR requires storing all WAL generated since the oldest needed base backup. For write-intensive databases, this can amount to massive storage requirements.

Calculating WAL Storage Requirements

WAL Generation Rate

Estimate your WAL generation:

-- PostgreSQL: Check WAL generation rate
SELECT 
    pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') / 1024 / 1024 / 1024 AS total_wal_gb,
    pg_postmaster_start_time() AS since,
    EXTRACT(EPOCH FROM now() - pg_postmaster_start_time()) / 3600 AS hours_running,
    pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0') / 1024 / 1024 / 1024 /
        (EXTRACT(EPOCH FROM now() - pg_postmaster_start_time()) / 3600) AS gb_per_hour;

Storage Requirements Formula:

Archive Storage = WAL_Rate × Retention_Period + N × Base_Backup_Size

Where:
- WAL_Rate = GB of WAL generated per day
- Retention_Period = Days of PITR capability needed
- N = Number of base backups retained
- Base_Backup_Size = Size of each base backup

Example Calculation:

WAL Rate:         50 GB/day
Retention:        30 days
Base Backups:     4 (weekly, 30-day retention)
Base Size:        500 GB each

WAL Storage:      50 × 30      = 1,500 GB
Backup Storage:   4 × 500      = 2,000 GB
Total Storage:    1,500 + 2,000 = 3,500 GB (3.5 TB)

With 3x compression:  ≈ 1.2 TB

Cost Analysis

Storage Cost Components:

Example Cost Projection (3.5TB with tiering):

Hot (Local, 7 days of WAL):       350 GB × $0.15 = $52.50/month
Warm (Standard, 30 days):         1,000 GB × $0.023 = $23.00/month
Cold (IA, base backups):          2,000 GB × $0.0125 = $25.00/month

Total Monthly:                    $100.50
Total Annual:                     $1,206

Write-Amplification Factor

High-churn tables can generate WAL far exceeding their size:

-- Table size vs. WAL generated
SELECT
    relname,
    pg_size_pretty(pg_total_relation_size(oid)) AS size,
    n_tup_ins + n_tup_upd + n_tup_del AS total_modifications
FROM pg_stat_user_tables
ORDER BY (n_tup_ins + n_tup_upd + n_tup_del) DESC
LIMIT 10;

Example: A 10GB session table updated constantly might generate 100GB of WAL per day—10x its size.

PITR protects against many failure scenarios but has fundamental gaps that require complementary strategies.

Scenarios Where PITR Is Insufficient

1. Archive Storage Failure

If the archive storage fails or is destroyed, PITR capability is lost:

• Natural disaster destroys both database and local archives
• Ransomware encrypts archive storage
• Cloud provider availability zone failure affects both database and archives

Mitigation: Multiple geographically-distributed archive destinations

2. Corruption Older Than Retention

PITR can only recover to points within archive retention:

Corruption introduced:    January 1
Corruption discovered:    February 15
Archive retention:        30 days
Oldest recoverable point: January 16

Result: Cannot recover to clean state before corruption

Mitigation: Extended retention, periodic logical backups, data validation

3. Logical Corruption Replayed in WAL

PITR replays exactly what was done—including logical errors:

• Application bug inserting incorrect data
• Malicious SQL from compromised credentials
• Configuration error causing wrong calculations

These become part of the replayed history; PITR cannot "undo" them—only recover to before they occurred.

Mitigation: Anomaly detection, audit logging, shorter detection windows

The Ransomware Challenge

Modern ransomware specifically targets backups and archives:

Ransomware Strategy:
1. Gain persistence in environment
2. Locate backup/archive systems
3. Wait until encryption would affect all retained backups
4. Encrypt everything simultaneously

Result: Both database and archives are encrypted
PITR capability: Non-existent

Defenses:

1. Immutable Storage: S3 Object Lock, WORM storage

aws s3api put-object-lock-configuration \
    --bucket db-archives \
    --object-lock-configuration '{
        "ObjectLockEnabled": "Enabled",
        "Rule": {
            "DefaultRetention": {
                "Mode": "GOVERNANCE",
                "Days": 30
            }
        }
    }'

2. Air-Gapped Copies: Offline backups that cannot be reached by network attackers

3. Different Credentials: Archive systems use separate authentication from production

4. Delayed Deletion: Archives cannot be deleted for a period even with proper credentials

PITR introduces operational complexity that must be managed. This complexity is a real cost, even if not measured in dollars.

Configuration Management Burden

PITR requires coordinated configuration across multiple systems:

Production Database:
- archive_mode = on
- archive_command configured
- wal_level = replica
- Sufficient wal_keep_size

Archive Server:
- Receives archive_command output
- Maintains retention policies
- Monitors for gaps
- Provides restore_command source

Backup System:
- Creates base backups
- Records backup LSN/time
- Manages backup retention coordinated with archives

Recovery Environment:
- Correct PostgreSQL version
- Access to archives
- Correct restore_command
- Adequate resources for replay

Misconfiguration anywhere breaks the PITR chain.

Version Compatibility Constraints

PITR recovery requires version compatibility:

• WAL format changes between major versions
• Recovery generally must be to same or higher major version
• May require intermediate upgrade steps

Backup taken: PostgreSQL 13
Recovery to:  PostgreSQL 15 (requires upgrade, not direct recovery)
Recovery to:  PostgreSQL 13 (works)
Recovery to:  PostgreSQL 12 (impossible—older version)

Version Change Impact:

• Keep one recovery environment per major version
• Document upgrade paths for archives
• Test recovery across version upgrades

Enabling PITR capability impacts production database performance. Understanding these impacts helps set appropriate expectations.

WAL Generation Overhead

PITR requires enabling replica-level WAL, which generates more log data:

-- Minimal logging (no PITR)
wal_level = minimal

-- PITR-capable logging
wal_level = replica

Overhead: 5-20% more WAL generated depending on workload

Additional WAL means:

• More disk I/O for WAL writes
• More network bandwidth for archiving
• More storage consumption

Archive Command Latency

The archive_command runs synchronously in some configurations:

Slow archive command → Delays WAL segment recycling
→ May exhaust pg_wal disk space
→ Database stops accepting writes

Mitigation:

• Fast archive command (copy locally, async sync to remote)
• Adequate pg_wal disk space
• Monitor archive lag

Base Backup Impact

Base backups consume resources:

During base backup:
- Disk read I/O for copying data files
- Network bandwidth for backup transfer
- CPU for compression (if enabled)
- WAL generation increases (changes during backup)

Duration: Minutes to hours depending on database size

Mitigation:

• Schedule backups during low-activity windows
• Use throttling: --max-rate option in pg_basebackup
• Backup from standby rather than primary

Synchronous vs. Asynchronous Trade-offs

For near-zero RPO, synchronous operations are required:

Synchronous Commit + Synchronous Archive:

Commit latency = local_disk_write + remote_archive_confirmation

Example:
- Local write: 1ms
- Remote archive confirmation: 50ms
- Total commit latency: 51ms (50x slower than local-only)

Asynchronous Operations:

Commit latency = local_disk_write only
Data at risk: Transactions since last archive

Most production systems use asynchronous archiving with synchronous local commits—accepting seconds of RPO for transaction performance.

A robust data protection architecture combines PITR with complementary strategies that address its limitations.

Strategy Matrix

1. Hot Standby (Addresses RTO)

Maintain a continuously-recovering replica ready for immediate promotion:

Primary → Streaming Replication → Hot Standby
                                        ↓
                              Ready for instant promotion
                              RTO: Seconds instead of hours

Combine with PITR:

• Standby provides fast failover
• PITR provides time-travel capability standby lacks
• PITR provides backup if standby is also affected

2. Cross-Region Replication (Addresses Regional Disasters)

Primary (Region A) → Async Replication → Standby (Region B)
         ↓                                      ↓
    Archives (Region A)               Archives (Region B)

Either region can be lost while maintaining recovery capability.

3. Immutable Backups (Addresses Ransomware)

# AWS S3 Object Lock
aws s3api put-object \
    --bucket secure-archives \
    --key wal/00000001000000050000002A \
    --body /archive/wal/00000001000000050000002A \
    --object-lock-mode GOVERNANCE \
    --object-lock-retain-until-date 2024-03-15T00:00:00Z

Object cannot be deleted or modified until retention expires, even by administrators.

4. Logical Backups (Addresses Long-Term Corruption)

# Weekly logical backup for long-term retention
pg_dump -Fc production > backup_$(date +%Y%m%d).dump

# Store for years (much longer than WAL retention)
aws s3 cp backup_*.dump s3://long-term-archive/ --storage-class DEEP_ARCHIVE

Logical backups:

• Cross-version compatible
• Verify data readability (unlike physical backups)
• Allow partial/table-level restore

The Comprehensive Protection Architecture

Enterprise deployments typically combine multiple strategies:

┌─────────────────────────────────────────────────────────────────┐
│                       PRIMARY DATABASE                          │
│                              │                                  │
│    ┌────────────────────────┼────────────────────────┐         │
│    │                        │                        │         │
│    ▼                        ▼                        ▼         │
│ ┌──────────┐          ┌──────────┐          ┌──────────┐       │
│ │Streaming │          │   WAL    │          │ Logical  │       │
│ │Standby   │          │ Archive  │          │  Backup  │       │
│ │(Same AZ) │          │(Local)   │          │ (Weekly) │       │
│ └────┬─────┘          └────┬─────┘          └────┬─────┘       │
│      │                     │                     │             │
│      │                     ▼                     ▼             │
│      │              ┌──────────┐          ┌──────────┐         │
│      │              │  Remote  │          │Long-term │         │
│      │              │ Archive  │          │ Archive  │         │
│      │              │(Cross-AZ)│          │ (Years)  │         │
│      │              └────┬─────┘          └──────────┘         │
│      │                   │                                     │
│      ▼                   ▼                                     │
│ ┌──────────┐      ┌──────────┐                                 │
│ │DR Standby│      │Immutable │                                 │
│ │(Cross-   │      │ Archive  │                                 │
│ │ Region)  │      │(Object   │                                 │
│ └──────────┘      │  Lock)   │                                 │
│                   └──────────┘                                 │
└─────────────────────────────────────────────────────────────────┘

RTO: Seconds (standby) to hours (PITR)
RPO: Seconds (streaming) to days (logical)
Retention: Days (WAL) to years (logical)

Point-in-Time Recovery is a powerful capability, but understanding its limitations is as important as understanding its capabilities. Let's consolidate the key insights:

Module Complete:

You have now completed the comprehensive study of Point-in-Time Recovery. From foundational concepts through technical implementation to understanding limitations, you're equipped to design, implement, and operate PITR systems that provide robust temporal recovery capability.

Key competencies acquired:

• Understanding PITR architecture and principles
• Configuring and managing log archiving
• Executing complete recovery procedures
• Specifying precise recovery targets
• Recognizing PITR limitations and selecting complementary strategies

This knowledge forms a critical foundation for database reliability engineering, enabling you to protect against data loss and ensure business continuity in the face of failures.