BACKUPS & DISASTER RECOVERY

- Full backup: Weekly (Sunday 2am)
- Incremental: Daily
- WAL archiving: Continuous
- Retention: 30 days

Maximum acceptable data loss:
- RPO = 1 hour: Backup every hour
- RPO = 0: Synchronous replication + WAL archiving

Maximum acceptable downtime:
- RTO = 4 hours: Restore from backup is acceptable
- RTO = 1 minute: Need hot standby with automatic failover

- 3 copies of data (primary + 2 backups)
- 2 different storage types (disk + object storage)
- 1 offsite location (different region)

Industry statistics:
- 34% of companies do not test backups
- 77% of those that test find failures
- Average cost of data loss: $150,000 per incident

Step 1: Spin up new database instance
Step 2: Restore from latest backup
Step 3: Apply WAL logs to bring up to date
Step 4: Verify data integrity (checksums, row counts)
Step 5: Run application test suite against restored DB
Step 6: Measure total time (compare to RTO)
Step 7: Document results and issues

def verify_backup():
    # Restore to test environment
    restore_db = restore_backup(latest_backup, target="test-db")

    # Run integrity checks
    assert restore_db.checksum() == expected_checksum
    assert restore_db.row_count("users") > 0

    # Run sample queries
    assert restore_db.query("SELECT COUNT(*) FROM orders") > 0

    # Cleanup
    restore_db.destroy()

    alert_success("Backup verification passed")

Timeline:
- 2:00 AM: Full backup completed
- 9:00 AM: Normal operations
- 9:15 AM: Developer runs DELETE without WHERE clause
- 9:16 AM: 10 million rows deleted
- 9:20 AM: Team notices
- Goal: Restore to 9:14 AM

# Restore base backup
pg_restore --dbname=restored /backups/base_backup.sql

# Apply WAL logs up to specific time
recovery_target_time = '2025-01-15 09:14:00'

- Enable WAL archiving on all production databases
- Ship WAL to durable storage every 1 minute
- Retain WAL for at least 7 days
- Test PITR quarterly

DATACENTER FAILURE RUNBOOK

1. DETECT (0-5 minutes)
   - Monitoring alerts on region failure
   - Oncall acknowledges alert

2. ASSESS (5-15 minutes)
   - Confirm failure scope
   - Estimate recovery time of primary
   - Decision: Wait vs Failover

3. FAILOVER (15-30 minutes)
   - Promote DR replica to primary
   - Update DNS/load balancer
   - Verify application connectivity

4. VERIFY (30-45 minutes)
   - Run smoke tests
   - Monitor error rates
   - Confirm data consistency

5. COMMUNICATE (Ongoing)
   - Status page update
   - Customer notification
   - Internal updates

Bad:
1. Deploy code that expects new column
2. Run migration to add column
3. Application errors for 30 seconds

Good:
1. Run migration to add column
2. Wait for replication
3. Deploy code that uses new column

-- Bad: One transaction with 1M updates
BEGIN;
UPDATE users SET status = 'active';  -- 1M rows
COMMIT;

-- Good: Small batches, separate transactions
UPDATE users SET status = 'active' WHERE id BETWEEN 1 AND 10000;
UPDATE users SET status = 'active' WHERE id BETWEEN 10001 AND 20000;
-- etc.

# Bad: Assumes instant replication
def create_and_read(data):
    db.insert(data)
    return db.query(data.id)  # May not exist on replica

# Good: Read from leader after write
def create_and_read(data):
    db.insert(data)
    return leader_db.query(data.id)

"We have backups" is not the same as "We can restore from backups"

-- Bad: Could return billions of rows
SELECT * FROM events;

-- Good: Always limit
SELECT * FROM events WHERE created_at > NOW() - INTERVAL '1 day' LIMIT 1000;