Skip to content

Backups

The production Postgres DB on Railway is backed up once a week to S3 by a dedicated cron service. Custom-format pg_dump to a private, versioned, lifecycle-bounded bucket; cron credentials are write-only; restore reads go through a separate admin profile.

Weekly automated backups

A dedicated Railway service backend-backup (image built from docker/backup/) runs every Monday at 00:00 UTC (cron expression 0 0 * * MON), takes a pg_dump --format=custom --no-owner --no-acl, inspects the dump's TOC with pg_restore --list, and uploads to s3://<backup-bucket>/YYYY/MM/DD/vidit-<UTC-timestamp>.dump. The TOC check catches corruption of the TOC itself, not mid-DATA truncation; only the quarterly drill verifies restorability.

The bucket has versioning + SSE-S3 + all-public-access blocked; lifecycle clears noncurrent versions after 30 days, aborted multipart uploads after 7, and current objects after 365.

The cron container's pg_dump is pinned to PG 16 to match the production server. Don't bump this without bumping prod first: pg_dump 18 writes archive format 1.16 which PG 16's pg_restore refuses to read.

The service writes through a dedicated IAM user <backup-iam-user> whose only S3 permissions are PutObject / AbortMultipartUpload / ListMultipartUploadParts on <backup-bucket>/*, no Get, no Delete.

Required env vars on the backend-backup service

Var Source
DATABASE_URL Railway reference: ${{backend.DATABASE_URL}} (internal *.railway.internal host). Reference backend.DATABASE_URL, not postgres-db.DATABASE_URL: Railway injects it on consumers, not the DB service.
BACKUP_S3_BUCKET <backup-bucket>
AWS_ACCESS_KEY_ID from <backup-iam-user> IAM user
AWS_SECRET_ACCESS_KEY from <backup-iam-user> IAM user
AWS_DEFAULT_REGION eu-west-3

Restoring from a weekly backup

Use the <s3-admin> profile locally. Configure it under ~/.aws/config, pointing at IAM principal <s3-admin> in account <aws-account-id>. Ask the maintainer for the credentials.

# Pick the most recent dump from S3
aws --profile <s3-admin> s3 ls s3://<backup-bucket>/ --recursive | tail -5

# Download
aws --profile <s3-admin> s3 cp s3://<backup-bucket>/YYYY/MM/DD/vidit-<ts>.dump ./vidit.dump

# Restore (wipes the target DB)
pg_restore --clean --if-exists --no-owner --no-acl --dbname="$TARGET_DATABASE_URL" ./vidit.dump

The target DB must have the same extensions installed as production. Today the dump only references postgis, postgis_topology, postgis_tiger_geocoder, fuzzystrmatch, all present in both the stock postgis/postgis:16-3.4 image and the custom one in docker/Dockerfile. Adding vector / pg_cron / age to prod would break restores into stock Postgres.

How you find out the cron failed

A 403 on PutObject exits non-zero; Railway logs it on the backend-backup deployment view. No alert on a missed Monday dump (Sentry catches runtime exceptions only). Discovery is manual:

  1. Mondays after 00:00 UTC, eyeball the bucket: 
    aws --profile <s3-admin> s3 ls s3://<backup-bucket>/ --recursive | tail -3
    A fresh .dump under today's YYYY/MM/DD/ prefix means the cron ran. If the latest dump is from the prior week, read the backend-backup deployment logs in Railway.
  2. At the quarterly restore drill, re-list the bucket; gaps in the weekly cadence catch any failure mode that the script's own exit code missed (e.g. a successful upload of a corrupt dump).

One-time restore drill

Run this once after the first weekly backup lands, then quarterly thereafter. The drill restores into a scratch DB inside the local container (commands resolve the container ID dynamically):

# 1. Make sure the local dev DB container is up
docker compose ps                                              # should show `db` running

# 2. Pick + download the latest weekly dump from S3
aws --profile <s3-admin> s3 ls s3://<backup-bucket>/ --recursive | tail -1
aws --profile <s3-admin> s3 cp s3://<backup-bucket>/YYYY/MM/DD/vidit-<ts>.dump /tmp/vidit-drill.dump

# 3. Copy the dump into the running container and create an empty scratch DB
DB=$(docker compose ps -q db)
docker cp /tmp/vidit-drill.dump "${DB}:/tmp/vidit.dump"
docker compose exec db psql -U vision -d postgres -c "CREATE DATABASE vidit_restore_drill;"

# 4. Restore into the scratch DB (--no-owner --no-acl mirrors the dump flags)
docker compose exec db pg_restore --no-owner --no-acl \
    --dbname=postgresql://vision:vision@localhost:5432/vidit_restore_drill \
    /tmp/vidit.dump

# 5. Sanity check: row counts on the tables that matter, alembic head, PostGIS smoke test
docker compose exec db psql -U vision -d vidit_restore_drill -c "
    SELECT 'users' AS t, COUNT(*) FROM users
    UNION ALL SELECT 'events', COUNT(*) FROM events
    UNION ALL SELECT 'media', COUNT(*) FROM media
    UNION ALL SELECT 'follows', COUNT(*) FROM follows
    UNION ALL SELECT 'tags', COUNT(*) FROM tags
    UNION ALL SELECT 'invite_codes', COUNT(*) FROM invite_codes
    ORDER BY 1;
    SELECT version_num FROM alembic_version;
    SELECT ST_GeomFromText('POINT(2.349 48.864)', 4326) IS NOT NULL AS postgis_works;
"

# 6. Tear down: drop the scratch DB and clean the dump artifacts
docker compose exec db psql -U vision -d postgres -c "DROP DATABASE vidit_restore_drill;"
docker compose exec db rm -f /tmp/vidit.dump
rm -f /tmp/vidit-drill.dump

The drill works against either PG version locally: pg_restore is forward-compatible, so a PG-16 dump from the cron restores cleanly into a local PG 16 or PG 18 server.

If steps 4-5 return plausible counts and the PostGIS smoke test returns t, the dump is restorable. Record the date and dump filename in CHANGELOG.md (under ### Operations, e.g. "Restore drill verified YYYY-MM-DD against vidit-<ts>.dump").

Manual snapshot and rollback

The release ritual around a deploy that ships a migration. Migrations run as a Railway pre-deploy step (uv run alembic upgrade head); a failed one retries 3× then leaves the service failed with the schema half-applied. Get a fresh backup before any deploy that includes a migration.

Two constraints shape how this works (see engineering.md → Deployment and Particularities): prod DB public networking is off; and the backend container ships only libpq5, not the pg_dump / pg_restore client binaries (those live in the backend-backup cron image, postgres:16).

1. Snapshot before deploying. Don't wait for the Monday run; trigger the backend-backup service on demand:

Railway dashboard → project `vidit` → service `backend-backup` → Deployments → Redeploy

Confirm a fresh object lands under today's YYYY/MM/DD/ prefix before deploying.

If a deploy goes wrong, recover in this order:

  • 2a. Code-only rollback (no schema change involved): re-run the deploy workflow with the previous tag, or hit "Redeploy previous" on the Railway backend service. No DB touch.
  • 2b. Schema downgrade (undo one migration, keep data): run Alembic inside the app container, where the internal DATABASE_URL already points at the live DB and alembic is installed (it's the pre-deploy hook): 
    railway ssh --service backend -- 'uv run alembic downgrade -1'
  • 2c. Full restore (data corruption, or downgrade isn't safe): the restore drill below is the validated pg_restore procedure. Live restore: run pg_restore from a one-off postgres:16 container on the Railway network, or temporarily open public DB networking. pg_restore --clean --if-exists wipes anything added since the snapshot; for partial recovery, restore into a scratch DB and copy specific tables out.

A dedicated restore job is not yet scheduled.