Backup + Restore Drill

Run this drill **quarterly**. Log each run in [`drill-history.md`](drill-history.md).

Run this drill quarterly. Log each run in drill-history.md.

Rationale

A backup that has never been restored isn't a backup. WAL-G continuously streams PostgreSQL WAL to S3; the drill validates that the streams are complete, the base backups are readable, and the restore procedure itself works under time pressure.

Pre-flight

# Confirm a recent base backup exists.
wal-g backup-list
 
# Confirm WAL is being streamed.
psql -c "SELECT pg_walfile_name(pg_current_wal_lsn());"

You should see a base backup ≤ 24 h old and a WAL position that's moved within the last few minutes. If either is stale, stop and fix the live system before drilling.

Drill procedure

Provision a staging Postgres. Same major version, same TimescaleDB extension. Do NOT use production credentials; the drill must not be reachable from any production secret.
```
docker run --rm -d --name helios-drill \
  -e POSTGRES_PASSWORD=drill -p 55432:5432 \
  timescale/timescaledb-ha:pg16
```

Restore the most recent base backup.

wal-g backup-fetch /var/lib/helios-drill LATEST

Replay WAL. WAL-G's walg.target=latest setting replays through to the most recent shipped segment.
Smoke-verify. Connect to the restored instance and run:
```
SELECT count(*) FROM clients;
SELECT count(*) FROM tracked_users;
SELECT count(*) FROM status_events;
SELECT max(captured_at) FROM status_events;
```
Row counts should be within an order of magnitude of production. The max captured_at should be within minutes of production's max.
Migration check. Run alembic upgrade head against the restored instance with the current code. A migration error here means the restore branch and the deploy branch have drifted — file a P1.
Audit-chain check. The audit log is hash-chained per day. Verify:
```
SELECT date_trunc('day', occurred_at) AS day,
       count(*) AS rows,
       max(chain_hash) AS last_hash
FROM audit_log
GROUP BY 1 ORDER BY 1;
```
Each day's chain_hash should be non-NULL and unique. A NULL indicates a write that bypassed the chain helper — also a P1.
Tear down. Drop the staging container; never point a production reader at it (it carries an arbitrary point-in-time snapshot of real client data).

Log it

Append a row to drill-history.md with the date, the WAL-G backup id used, the restore wall-clock duration, and a one-line outcome. A failed drill is more valuable than a never-run drill — write it down regardless.

Recovery time targets

Pre-drill: RTO 4 h, RPO 5 min (WAL streaming cadence).
Drill-validated: RTO 1 h, RPO 5 min.
The drill exists to keep the gap between those two numbers small.