Pitr_recovery

Chapter 45: Point-in-Time Recovery

Recovering to a Specific Point in Time

---

45.1 Understanding PITR

Point-in-Time Recovery (PITR) allows you to recover to any point in time using base backup + WAL archives.

                    PITR Process
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                   Point-in-Time Recovery                            │
    │                                                                      │
    │   Components:                                                       │
    │   ───────────                                                       │
    │   1. Base backup - Full database snapshot                          │
    │   2. WAL archives - Transaction logs                               │
    │   3. Recovery target - Time or transaction ID                     │
    │                                                                      │
    │   Process:                                                          │
    │   ───────                                                          │
    │   1. Stop PostgreSQL                                               │
    │   2. Restore base backup                                          │
    │   3. Configure recovery                                            │
    │   4. Start PostgreSQL                                             │
    │   5. PostgreSQL replays WAL up to target                         │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

---

45.2 Enabling WAL Archiving

-- postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /wal_archive/%f'
archive_timeout = 300  -- 5 minutes

-- Or use compression
archive_command = 'gzip < %p > /wal_archive/%f.gz'

---

45.3 Performing PITR

# 1. Stop PostgreSQL
sudo systemctl stop postgresql

# 2. Clean data directory
rm -rf /var/lib/postgresql/15/main/*

# 3. Restore base backup
pg_basebackup -h primary -D /var/lib/postgresql/15/main -P -U replicator

# 4. Create recovery configuration
cat > /var/lib/postgresql/15/main/postgresql.auto.conf << EOF
restore_command = 'gunzip < /wal_archive/%f.gz > %p'
recovery_target_time = '2024-06-15 14:30:00'
recovery_target_action = 'promote'
EOF

# 5. Create recovery signal file
touch /var/lib/postgresql/15/main/recovery.signal

# 6. Start PostgreSQL
sudo systemctl start postgresql

---

45.4 Monitoring Recovery

-- Check recovery status
SELECT pg_is_in_recovery();

-- Check recovery progress
SELECT
    now() - pg_last_xact_replay_timestamp() as replication_lag;

-- Check timeline
SELECT * FROM pg_current_wal_lsn();

---

Summary

Component	Purpose
Base backup	Initial full copy
WAL archives	Transaction logs
Recovery target	Point to restore to

---

Next: Chapter 46: Authentication & Users