Skip to content

Database backup + restore

Per ADR-0006 prod hosting is Neon Postgres with Hyperdrive. Neon ships point-in-time recovery within the retention window — we don't run our own backup process for prod. Local dev is Docker-managed; if you nuke the container, prisma migrate reset re-seeds from prisma/seed.ts.

Local dev — when you need a snapshot before a destructive migration

# Snapshot
docker exec 961tech-postgres pg_dump -U postgres -d tech961 -F c \
  -f /tmp/tech961-$(date +%F).dump
docker cp 961tech-postgres:/tmp/tech961-$(date +%F).dump ./_db-backups/

# Restore
docker exec -i 961tech-postgres pg_restore -U postgres -d tech961 \
  --clean --if-exists < ./_db-backups/tech961-2026-04-28.dump

_db-backups/ is gitignored — never commit a dump.

Prod — Neon point-in-time recovery

Neon retains every committed change for the duration of the history retention window (free tier: 24 hours; paid: configurable up to 30 days). Recovery flow:

Restore to a fresh branch

  1. Open the Neon console for the 961tech project.
  2. Branches → New branch → choose source branch (main) and "From timestamp" → enter the recovery target.
  3. Branch is provisioned in seconds; gets its own DATABASE_URL.
  4. Update wrangler.jsonc Hyperdrive binding to point at the new branch's connection string.
  5. Deploy. Verify. Promote (rename branch + flip primary) once confirmed.

Restore in-place (rollback)

For a small bad-migration window — Neon supports "Reset from main" per branch. This is destructive on the target branch; never run against the live primary without a fresh branch as a holding pen.

Annual disaster-recovery test

Run end-of-Q1 against a non-prod branch:

  1. Pick a deliberate destruction (drop Listing table, truncate Click).
  2. Time the recovery — branch creation + Hyperdrive rebind + deploy.
  3. Document the wall-clock time in docs/postmortems/YYYY-Q1-dr-test.md.
  4. Compare against the RTO commitment in reference/performance-budget.md § Backend.

Target: < 30 minutes wall-clock for full restore.

What lives outside the database

  • Workers Analytics Engine custom metrics (per ADR-0015) — 90-day retention from Cloudflare, not in Postgres. Long-horizon trend lives in MetricRollup table per #164 daily aggregator.
  • Axiom Logpush archive — 30-day retention, separate restore path (Axiom export API).
  • R2 object storage (future, per #129 audit-log cold archive) — versioned by Cloudflare, restore via dashboard.

See also