## Backup Catalog - `<service>`

| Type | Source | Frequency | Retention | Tool |
|---|---|---|---|---|
| Full DB dump | postgres prod | Daily 02:00 UTC | 30 days | pg_dump + S3 |
| Logical schema | postgres prod | Hourly | 24 hours | logical replication slot |
| File store | S3 prod bucket | Continuous | 90 days | S3 versioning + cross-region |
| Audit log | append-only S3 | Continuous | 7 years | S3 + Glacier |
| Secrets / KMS keys | Vault prod | Daily | 7 days | Vault snapshot + encrypted S3 |

#!/usr/bin/env bash
set -e

BACKUP_FILE="postgres-prod-$(date +%Y%m%d).sql.gz"
BACKUP_PATH="/backups/$BACKUP_FILE"

# Take backup
pg_dump -h prod-db -U replica db_name | gzip > "$BACKUP_PATH"

# Generate SHA-256 + sign
sha256sum "$BACKUP_PATH" > "$BACKUP_PATH.sha256"
gpg --detach-sign --armor "$BACKUP_PATH"

# Upload to backup destination
aws s3 cp "$BACKUP_PATH" "s3://backup/postgres/$BACKUP_FILE"
aws s3 cp "$BACKUP_PATH.sha256" "s3://backup/postgres/$BACKUP_FILE.sha256"
aws s3 cp "$BACKUP_PATH.asc" "s3://backup/postgres/$BACKUP_FILE.asc"

# Tag with metadata
aws s3api put-object-tagging \
  --bucket backup --key "postgres/$BACKUP_FILE" \
  --tagging 'TagSet=[{Key=integrity_verified,Value=true},{Key=created,Value='$(date -Iseconds)'}]'

# CI cron: weekly random sample
- cron: "0 4 * * 1"  # Monday 04:00 UTC
  job:
    - name: Pick random backup
      run: |
        DAYS=(1 7 14 30)
        DAYS_AGO=${DAYS[$RANDOM % ${#DAYS[@]}]}
        BACKUP=$(date -d "$DAYS_AGO days ago" +%Y%m%d)
        echo "BACKUP=postgres-prod-$BACKUP.sql.gz" >> $GITHUB_ENV

    - name: Verify integrity
      run: |
        aws s3 cp s3://backup/postgres/$BACKUP.sha256 .
        aws s3 cp s3://backup/postgres/$BACKUP .
        sha256sum -c "$BACKUP.sha256"

    - name: Restore to test DB
      run: |
        gunzip "$BACKUP"
        psql -h test-db -U test -f "${BACKUP%.gz}" db_test

    - name: Spot check
      run: |
        psql -h test-db -U test db_test -c "SELECT COUNT(*) FROM orders WHERE created_at > NOW() - INTERVAL '1 day'"
        # Verify count > 0 (or whatever invariant fits)

# Single-table extract + restore
pg_restore --table=orders --data-only \
  -h test-db -U test -d db_test \
  postgres-prod-backup.dump

aws s3 cp \
  s3://backup-versioned/object-key \
  --version-id "VERSION_ID_AT_DESIRED_TIME" \
  ./restored-object

def test_backup_replicated_to_dr_region():
    # Take a backup in primary region
    backup_path_primary = take_backup_to(region="us-east-1")

    # Wait for replication SLA
    deadline = time.time() + 300  # 5 min SLA
    while time.time() < deadline:
        if exists_in(region="us-west-2", path=backup_path_primary):
            return
        time.sleep(10)

    pytest.fail("Cross-region replication exceeded 5min SLA")

def test_old_backups_purged_per_retention_policy():
    # 30-day retention; 100-day-old backup should not exist
    target = (datetime.utcnow() - timedelta(days=100)).strftime("%Y%m%d")
    obj_key = f"postgres/postgres-prod-{target}.sql.gz"

    response = s3.head_object(Bucket="backup", Key=obj_key)
    # Should 404
    pytest.fail(f"Backup {obj_key} still exists past 30-day retention")

def test_recent_backups_present():
    # Last 30 days should have at least one daily backup each
    for d in range(30):
        date = (datetime.utcnow() - timedelta(days=d)).strftime("%Y%m%d")
        key = f"postgres/postgres-prod-{date}.sql.gz"
        s3.head_object(Bucket="backup", Key=key)  # raises if missing

def test_backup_encrypted_with_correct_key():
    obj = s3.head_object(Bucket="backup", Key=key)
    assert obj["ServerSideEncryption"] == "aws:kms"
    assert obj["SSEKMSKeyId"] == EXPECTED_KMS_KEY_ARN

## Customer-Induced Backup Restore Test

1. Customer requests demo restore via support ticket.
2. SRE picks a random recent backup; restores to clean isolated env.
3. Customer verifies their data via read-only SQL or UI.
4. Cleanup: tear down env, sanitize logs.
5. Document: ticket + timestamps + verification artifacts → audit log.

import subprocess, time
import pytest

@pytest.mark.benchmark
def test_postgres_restore_time_under_rto():
    # Setup: clean target DB
    subprocess.run(["psql", "-h", "test-db", "-c", "DROP DATABASE IF EXISTS db_test"])
    subprocess.run(["psql", "-h", "test-db", "-c", "CREATE DATABASE db_test"])

    backup = "postgres-prod-latest.sql.gz"

    start = time.time()
    subprocess.run(
        ["bash", "-c", f"gunzip -c {backup} | psql -h test-db -d db_test"],
        check=True,
    )
    elapsed = time.time() - start

    RTO_BUDGET_SECONDS = 4 * 3600  # 4 hours
    # The 0.5 split (restore gets half the RTO, the rest goes to provision +
    # verify + cutover) is a planning choice, NOT a standard. Set the fraction
    # from your own per-segment RTO budget (Step 1).
    RESTORE_SEGMENT_FRACTION = 0.5
    budget = RTO_BUDGET_SECONDS * RESTORE_SEGMENT_FRACTION
    assert elapsed < budget, f"Restore took {elapsed:.0f}s; budget {budget:.0f}s"

# pg_restore parallel
pg_restore -j 8 -d db_test backup.dump  # 8 parallel workers

# WAL-E / pgbackrest parallel restore
pgbackrest --stanza=prod --process-max=8 restore

def test_parallel_restore_faster_than_serial():
    serial = run_restore(parallel_jobs=1)
    parallel = run_restore(parallel_jobs=8)

    speedup = serial / parallel
    # 3.0x is an illustrative target; real speedup depends on I/O saturation,
    # CPU count, and backup format. Set the expected ratio from your own
    # measured serial-vs-parallel baseline rather than this placeholder.
    assert speedup > 3.0, f"Parallel restore only {speedup:.1f}x faster"

def test_pitr_to_5min_ago_under_30min():
    target_time = datetime.utcnow() - timedelta(minutes=5)

    # 1. Lay down the PRE-EXISTING base backup into a clean data dir
    #    (untar the retained base backup; do not take a fresh one here).
    restore_retained_base_backup(dest="/restore")

    # 2. PG12+ recovery config: restore_command pulls archived WAL,
    #    recovery_target_time is the stop point, and recovery.signal triggers
    #    targeted recovery (recovery.conf was removed in PG12).
    write_conf("/restore/postgresql.auto.conf", {
        "restore_command": "cp /wal_archive/%f %p",
        "recovery_target_time": f"'{target_time.isoformat()}'",
        "recovery_target_action": "promote",
    })
    Path("/restore/recovery.signal").touch()

    # 3. Time the restore + WAL replay: this is the real PITR latency.
    start = time.time()
    subprocess.run(["pg_ctl", "start", "-D", "/restore", "-w"], check=True)
    wait_for_recovery_complete(timeout=1800)
    elapsed = time.time() - start

    # 1800s is illustrative; set the budget from your service's RTO segment SLA.
    assert elapsed < 1800, f"PITR took {elapsed:.0f}s; budget 30min"

def test_partial_object_restore_under_5_min():
    keys_to_restore = sample_500_keys_from_inventory()

    start = time.time()
    for key in keys_to_restore:
        s3.copy_object(
            Bucket="restore-target",
            Key=key,
            CopySource={"Bucket": "backup-versioned", "Key": key, "VersionId": ...},
        )
    elapsed = time.time() - start

    assert elapsed < 300, f"500-object restore took {elapsed:.0f}s"

def emit_restore_time_metric(elapsed_seconds, backup_size_bytes):
    metrics_client.gauge("dr.restore_time_seconds", elapsed_seconds)
    metrics_client.gauge("dr.backup_size_bytes", backup_size_bytes)
    metrics_client.gauge("dr.restore_throughput_bytes_per_sec",
                          backup_size_bytes / elapsed_seconds)

def test_post_restore_smoke_under_5_min():
    do_restore()

    start = time.time()
    run_smoke_suite("dr-environment")
    elapsed = time.time() - start

    assert elapsed < 300, f"Smoke tests took {elapsed:.0f}s; budget 5min"

def test_cold_start_latency_within_sla():
    # Restore complete; app started; first requests
    latencies = []
    for _ in range(100):
        start = time.time()
        requests.get("https://dr-env.svc/api/products")
        latencies.append(time.time() - start)

    p99_cold = sorted(latencies)[99]
    # 2.0s is a placeholder; set the cold-start bound from your service's SLO.
    assert p99_cold < 2.0, f"Cold-start p99 {p99_cold:.2f}s exceeds 2s SLA"