Runbook: Restore drill procedure

Purpose

This drill verifies that the daily pg_dump backup stored in R2 can be successfully restored to a clean Postgres instance. Run at MVP exit and quarterly thereafter. Record results in restore-drill-log.

Prerequisites

• AWS CLI configured with R2_ACCESS_KEY_ID / R2_SECRET_ACCESS_KEY / R2_ENDPOINT for the target env.
• A clean Postgres 18 instance (can be Docker: docker run --rm -e POSTGRES_PASSWORD=test -p 5434:5432 pgvector/pgvector:pg18).
• pg_restore / psql available locally or in a utility container.

Procedure

1. Identify the backup to restore

AWS_ACCESS_KEY_ID="$R2_ACCESS_KEY_ID" \
AWS_SECRET_ACCESS_KEY="$R2_SECRET_ACCESS_KEY" \
aws s3 ls "s3://$R2_BUCKET_MEDIA/pg_dumps/" \
  --endpoint-url "$R2_ENDPOINT" \
| sort \
| tail -5

Note the most recent pg_dump_{env}_{timestamp}.sql.gz filename.

2. Download the dump

DUMP_FILE="pg_dump_prod_<timestamp>.sql.gz"

AWS_ACCESS_KEY_ID="$R2_ACCESS_KEY_ID" \
AWS_SECRET_ACCESS_KEY="$R2_SECRET_ACCESS_KEY" \
aws s3 cp "s3://$R2_BUCKET_MEDIA/pg_dumps/$DUMP_FILE" /tmp/$DUMP_FILE \
  --endpoint-url "$R2_ENDPOINT"

3. Start a clean Postgres instance

docker run -d --name pg-restore-test \
  -e POSTGRES_PASSWORD=drillpass \
  -e POSTGRES_DB=keepcadence_restore \
  -p 5434:5432 \
  pgvector/pgvector:pg18

# Wait for it to be ready
until docker exec pg-restore-test pg_isready -U postgres; do sleep 1; done

4. Restore

gunzip -c /tmp/$DUMP_FILE | \
  psql "postgres://postgres:drillpass@localhost:5434/keepcadence_restore"

5. Verify integrity

psql "postgres://postgres:drillpass@localhost:5434/keepcadence_restore" <<'SQL'
-- Row counts on key tables
SELECT 'media_file' AS tbl, COUNT(*) FROM media_file
UNION ALL SELECT 'media_job', COUNT(*) FROM media_job
UNION ALL SELECT 'voice_clone', COUNT(*) FROM voice_clone
UNION ALL SELECT 'user', COUNT(*) FROM "user";
SQL

Expected: row counts consistent with the production database at the time of the backup.

6. Cleanup

docker rm -f pg-restore-test
rm /tmp/$DUMP_FILE

7. Record results

Fill in a row in restore-drill-log:

Date	Env	Dump timestamp	Restore duration	Row count match	Operator
YYYY-MM-DD	prod	…	…s	yes/no	name

Pass criteria

• Restore completes without errors.
• Row counts on media_file, media_job, voice_clone, user are non-zero and consistent with known production scale.
• No schema errors (all tables, indexes, extensions present).