Restore Postgres from backup

Prod Postgres (Render, v18) has two recovery sources: Render’s own point-in-time recovery, and a pg_dump copied off Render to Cloudflare R2 every 6 hours. This runbook is the tested procedure for getting that data back — both the “prod is gone, bring it back” disaster case and the “I just need to inspect/recover one table” case.

A backup you haven’t restored isn’t a backup. This procedure was last executed end-to-end on 2026-06-13 (clean pg_restore, all tables + row counts matched the source). Re-run the drill (§5) quarterly.

What we have

⚠️ Two things that will bite you

1. The restore target MUST have pgvector + pg_trgm. The schema has vector(1024) columns on exercises, workouts, programs, ai_conversations, member_profiles. Restoring into a vanilla postgres:18 silently drops 5 tables (75 ignored errors, CREATE EXTENSION vector fails). Use an image/instance that has the extensions — locally pgvector/pgvector:pg18; Render Postgres has both. The dump itself is complete; it’s the target that needs the extensions.
2. Use pg_restore, not psql. The dump is tar-format, not plain SQL.

0. Prerequisites

• R2 read credentials: mint a read-only R2 token for fitkit-db-backups. The backup job’s own token lives only in the GitHub prod-backup environment and cannot be read back. See credentials-rotation.md.
• aws CLI, pg_restore (v18), Docker (for a throwaway target).
• R2 needs these to avoid S3 checksum incompatibilities:

export AWS_ACCESS_KEY_ID=<r2-key>
export AWS_SECRET_ACCESS_KEY=<r2-secret>
export AWS_DEFAULT_REGION=auto
export R2=https://74e1f4e430cf29df86bd66ff89e4d1c0.r2.cloudflarestorage.com
export AWS_REQUEST_CHECKSUM_CALCULATION=when_required
export AWS_RESPONSE_CHECKSUM_VALIDATION=when_required

1. Find and download the dump

# newest first
aws s3 ls s3://fitkit-db-backups/ --endpoint-url "$R2" | sort | tail -5
 
# grab the latest (or pick a specific object for point-in-history)
LATEST=$(aws s3 ls s3://fitkit-db-backups/ --endpoint-url "$R2" | grep '\.dump$' | sort | tail -1 | awk '{print $4}')
aws s3 cp "s3://fitkit-db-backups/$LATEST" /tmp/restore.dump --endpoint-url "$R2"
 
pg_restore --list /tmp/restore.dump | grep -c 'TABLE DATA'   # sanity: one per table

2. Restore

Scenario A — inspect / recover into a scratch DB (the drill, and single-table recovery)

docker run -d --name restore-test -e POSTGRES_PASSWORD=pw -p 55999:5432 pgvector/pgvector:pg18
sleep 5
createdb -h localhost -p 55999 -U postgres restored   # PGPASSWORD=pw
 
pg_restore -h localhost -p 55999 -U postgres -d restored \
  --no-owner --no-privileges /tmp/restore.dump         # PGPASSWORD=pw

Expect pg_restore exit 0 with 0 error lines. If you see relation "public.X" does not exist / extension "vector" is not available, your target is missing pgvector — start over with the right image.

For a single-table recovery, restore into this scratch DB, then copy the rows you need into prod (\copy / INSERT ... SELECT over a dblink or a dump of just that table).

Scenario B — disaster: prod DB is gone

0. Try Render point-in-time recovery first (Render dashboard → the Postgres instance → Recovery). It restores to a new instance at any moment inside the retention window, with minutes of data loss instead of up to 6h. Use the R2 dump below when PITR is unavailable or the Render account itself is the problem.
1. Provision a new Postgres 18 with vector and pg_trgm available (Render Postgres has both). Note its DATABASE_URL.
2. Restore into it:

   pg_restore --no-owner --no-privileges \
     --dbname "<NEW_DATABASE_URL>" /tmp/restore.dump

3. Repoint the API: set the Render API service’s DATABASE_URL and DIRECT_DATABASE_URL to the new instance, then redeploy. Recreate the read-only taikan_backup role and update the BACKUP_DATABASE_URL secret in the GitHub prod-backup environment, and put the taikan-smoke box’s IP on the new instance’s allow-list, or the next backup run fails. The API needs NODE_ENV=production for Sentry/PostHog — don’t drop it.
4. Run pending migrations that landed after the dump: pnpm db:migrate against the new DB. Watch the _journal.json monotonicity trap — see migrations.md.
5. Smoke test (§3).

3. Verify the restore

# extensions present (must include vector + pg_trgm)
psql "$DB" -c "SELECT extname FROM pg_extension ORDER BY 1;"
 
# table count + per-table row counts
psql "$DB" -c "SELECT count(*) FROM information_schema.tables WHERE table_schema='public';"
psql "$DB" -Atc "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY 1;"

• Drill (Scenario A): counts must match the source the dump came from. Baseline at the 2026-06-13 drill was 79 tables / 1457 rows — that number grows over time, so compare against current prod, not this figure.
• Disaster (Scenario B): prod is gone, so you can’t diff against it. Instead confirm: all expected extensions present, table count looks right (≈79+), foreign keys created without error, and spot-check that the newest rows (recent payments / check-ins) are present up to ~the dump timestamp. Then hit GET /health (expects "status":"ok") and log in.

4. Clean up

docker rm -f restore-test
shred -u /tmp/restore.dump 2>/dev/null || rm -f /tmp/restore.dump   # contains real member/billing data

Prod dumps hold real billing/compliance data — delete local copies when done; never leave them in /tmp or commit them.

5. Quarterly restore drill

Once a quarter, run Scenario A against the latest object and confirm §3 passes. This is the only thing that proves the backups are still restorable as the schema evolves. Log the date + result here:

Related

• Backup setup + monitoring: .github/workflows/cd-db-backup.yml, dispatched by deploy/ci-clock, watched by the Sentry cron monitor prod-db-backup.
• incident-response.md · migrations.md · credentials-rotation.md