BimZone — Disaster Recovery Runbook (WS-SEC-BACKUP-DR)

This runbook is the operational + audit (SOC 2 A1.2 / A1.3) evidence that BimZone's PostgreSQL data is backed up, recoverable to a point in time, and that the recovery procedure is tested. It states the recovery objectives, the exact recovery commands, and how to run the monthly verification drill.

Scope. This covers the primary datastore (PostgreSQL). Object storage (uploads, fragments, point clouds) durability/versioning/cross-region replication is owned by docs/storage-r2-s3.md (WS-SEC-S3-R2-VALIDATION); enable bucket versioning + cross-region replication there. Postgres holds all relational/transactional state and is the focus here.

---

1. Recovery objectives

Objective	Target	How it is met
RTO (Recovery Time Objective)	≤ 1 hour	Restore latest base backup + replay WAL (scripts/restore-postgres.sh); or near-zero failover by promoting the streaming standby / managed read replica.
RPO (Recovery Point Objective)	≤ 5 minutes (≈ 1 min in practice)	Continuous WAL archiving with archive_timeout = 60s, so at most ~1 minute of writes is unarchived. A connected streaming standby reduces this to near-zero (synchronous-capable).
PITR window	7 days	7 days of base backups + all WAL since the oldest retained base backup are kept (see ops/postgres/postgresql.conf retention notes and BACKUP_RETENTION_DAYS=7).

These targets assume the off-region archive target and (for the near-zero RTO path) a streaming standby or managed read replica are provisioned — see §6.

---

2. Backup architecture

            ┌─────────────────────────────┐
            │  PRIMARY Postgres           │
            │  wal_level=replica          │
            │  archive_mode=on            │
            └─────┬───────────────┬───────┘
                  │ archive_command│ streaming
                  │ (every WAL seg)│ replication
                  ▼                ▼
        ┌──────────────────┐  ┌──────────────────┐
        │ WAL ARCHIVE      │  │ STREAMING STANDBY │  (warm standby / managed read replica)
        │ (off-region obj  │  │ near-zero-RPO     │
        │  storage / dir)  │  └──────────────────┘
        └────────┬─────────┘
                 │  +  nightly pg_basebackup (physical) + pg_dump (logical)
                 ▼
        ┌──────────────────────────────────────────┐
        │ BACKUP_ROOT/                              │
        │   base/<TS>/   ← physical base backups    │
        │   dumps/<db>-<TS>.dump ← logical dumps     │
        │   wal/         ← archived WAL segments     │
        │   .last-backup ← freshness marker          │
        │   .last-verify ← tested-backup marker      │
        └──────────────────────────────────────────┘

• Physical base backup + WAL = the PITR anchor (restore to any instant in the window).
• Logical pg_dump = portability, single-table recovery, cross-version migration.
• Freshness marker (.last-backup) is surfaced by GET /api/admin/backup-status so monitoring alarms if backups stall (stale = older than 26h).

---

3. Taking a backup

Run nightly (cron / systemd timer / CI schedule) on a host that can reach the primary:

PGHOST=primary.db.internal PGPORT=5432 PGUSER=bimzone PGPASSWORD=… PGDATABASE=bimzone \
BACKUP_ROOT=/var/backups/bimzone \
BACKUP_RETENTION_DAYS=7 \
OFFSITE_SYNC_CMD='aws s3 sync /var/backups/bimzone s3://bimzone-dr/bimzone/ --storage-class STANDARD_IA' \
scripts/backup-postgres.sh

This produces a physical base backup and a logical dump, prunes artifacts older than 7 days, writes the freshness marker, and (if OFFSITE_SYNC_CMD is set) copies everything off-region. WAL is archived continuously by Postgres itself via archive_command.

Confirm the marker is fresh:

cat /var/backups/bimzone/.last-backup      # ISO-8601 timestamp
curl -s -H "Authorization: Bearer <token>" https://bim.example.com/api/admin/backup-status
# → { "lastBackupAt": "...", "ageSeconds": 1234, "stale": false }

---

4. Recovery procedures

4a. Point-in-time recovery (PITR) — corruption / bad migration / accidental delete

Restore the latest base backup and replay WAL up to just before the bad event.

1. Stop writes to the damaged primary (or fail traffic over to the standby first — §4b).
2. Dry-run the restore to review every action without touching disk:

BACKUP_ROOT=/var/backups/bimzone \
scripts/restore-postgres.sh \
  --target-time "2026-06-21 14:29:00+00" \
  --data-dir /var/lib/postgresql/restore \
  --wal-archive /var/backups/bimzone/wal \
  --dry-run

1. Execute the restore (drop --dry-run). It unpacks the newest base backup into the target data dir and writes recovery.signal + restore_command + recovery_target_time.
2. Start Postgres against the restored data dir; it replays WAL to the target time and promotes itself:

pg_ctl -D /var/lib/postgresql/restore -l /var/lib/postgresql/restore/recovery.log start
psql -d postgres -c 'select pg_is_in_recovery();'   # f  → promotion complete

1. Repoint the app DATABASE_URL at the recovered instance and resume traffic.

Pick the base backup explicitly with --base-dir /var/backups/bimzone/base/<TS> if you need an older anchor; omit --target-time to replay all available WAL (latest state).

4b. Failover to the streaming standby — primary host/disk loss

If a streaming standby (or managed read replica) is running, this is the fast path (near-zero RTO/RPO):

1. Promote the standby: pg_ctl promote -D <standby-data-dir> (or the managed-Postgres "promote read replica" action).
2. Repoint the app DATABASE_URL at the promoted node.
3. Rebuild a fresh standby from a new base backup once the incident is over.

4c. Logical restore — single table / cross-version / portability

createdb bimzone_restore
pg_restore --no-owner --no-privileges --dbname=bimzone_restore \
  /var/backups/bimzone/dumps/bimzone-<TS>.dump
# add --table=<name> to recover a single table

4d. Full volume-loss drill (proves the whole chain)

1. docker compose stop postgres && docker volume rm bim-zone_pgdata (destroys the data).
2. Recreate the volume and run scripts/restore-postgres.sh against the base+WAL.
3. Start Postgres, then compare row counts to the pre-loss snapshot (see §5).

---

5. Monthly verification drill (the "backups are tested" evidence)

Run on the first of each month (and after any change to the backup pipeline):

PGHOST=… PGUSER=bimzone PGPASSWORD=… \
BACKUP_ROOT=/var/backups/bimzone \
scripts/verify-backup.sh

verify-backup.sh: 1. picks the newest logical dump, 2. createdbs a throwaway database and pg_restores into it, 3. runs a row-count / structural-checksum sanity check across all user tables, 4. drops the throwaway DB and writes a pass/fail marker to BACKUP_ROOT/.last-verify:

2026-06-21T03:14:00Z PASS tables=64 rows~1284322 schema_md5=ab12…

Evidence to retain for audit: - the .last-verify marker line (PASS + table/row/checksum figures), - the script's stdout/stderr log from the scheduled run, - once a quarter, a full PITR drill (§4a) restoring to an arbitrary in-window timestamp and confirming row counts match a known snapshot, - pg_stat_replication output showing a connected standby (when the replica is provisioned).

---

6. Production / managed-Postgres equivalents (infra-blocked parts)

Everything above runs end-to-end against the local docker-compose Postgres. The following require the real production environment and are the only parts not shippable today:

Local (this repo)	Production target
archive_command → local wal-archive/ dir	Off-region object storage (aws s3 cp / rclone to R2 / wal-g wal-push) in a separate region/account.
Streaming standby compose service	A managed read replica (RDS/Aurora/Cloud SQL/Crunchy/Neon) or a second host with primary_conninfo + standby.signal.
OFFSITE_SYNC_CMD copy of BACKUP_ROOT	Cross-region bucket replication + object-storage versioning (see docs/storage-r2-s3.md).

On a managed Postgres, wal_level/archive_mode/archive_timeout are set via the provider's parameter group and PITR is a built-in feature (point-in-time restore to a new instance); the RTO/RPO targets and the monthly verify-backup.sh drill still apply unchanged.

---

7. Quick reference

What	Command / file
Take a backup	scripts/backup-postgres.sh
Restore to a point in time	scripts/restore-postgres.sh --target-time '<ts>' --data-dir <dir>
Dry-run a restore	add --dry-run
Test a backup (monthly drill)	scripts/verify-backup.sh
WAL/PITR settings	ops/postgres/postgresql.conf
Backup freshness (monitoring)	GET /api/admin/backup-status → { lastBackupAt, ageSeconds, stale }
Freshness marker	BACKUP_ROOT/.last-backup
Tested-backup marker	BACKUP_ROOT/.last-verify