Restore Postgres from backup

Prod Postgres (Railway, v18) is dumped to Cloudflare R2 every night. 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; on Railway use the pgvector Postgres template. 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. Reuse the backup service’s keys (Railway → pg-s3-backups → Variables: AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY) or mint a read-only R2 token. 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://taikan-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://taikan-db-backups/ --endpoint-url "$R2" | sort | tail -1 | awk '{print $4}')
aws s3 cp "s3://taikan-db-backups/$LATEST" /tmp/restore.tar.gz --endpoint-url "$R2"
 
gunzip -f /tmp/restore.tar.gz          # → /tmp/restore.tar
tar -tf /tmp/restore.tar | head        # sanity: should list toc.dat + *.dat

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.tar          # 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

1. Provision a new Postgres with pgvector in the Railway Taikan project (pgvector template). Note its DATABASE_URL.
2. Restore into it:

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

3. Repoint the API: set the @taikan/api service DATABASE_URL (and the PGBouncer pooled URL, if used) to the new instance, then redeploy. 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.tar 2>/dev/null || rm -f /tmp/restore.tar   # 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: the pg-s3-backups Railway service (see project memory project-db-backups-r2).
• incident-response.md · migrations.md · credentials-rotation.md