Database backup and restore runbook

Source: g2labs-in/linode-infra@3b83259 · docs/runbooks/database-restore.md · updated 2026-09-24

Last verified: 2026-09-13 20:30 UTC on billuu-app (first unattended weekly run, dump billuu-db-20260913T193001Z.dump, both dead man endpoints answered 200)

What runs

Job	Schedule (UTC)	Script	Output
Production database dump	19:30 daily	/usr/local/bin/billuu-db-backup.sh	r2:billuu-pg-backup/db/, 30 days
Dokploy database dump	19:45 daily	/usr/local/bin/billuu-dokploy-backup.sh	r2:billuu-pg-backup/dokploy/, 30 days
Host config and Redis volume	19:50 daily	/usr/local/bin/billuu-volume-backup.sh	r2:billuu-pg-backup/volumes/, 30 days
Restore verification, both dumps	20:30 Sunday	/usr/local/bin/billuu-db-verify.sh	log line with counts, alert on failure

Supporting files on billuu-app:

/etc/billuu/backup.env        root 600, holds the database URL, R2 keys, alert URLs
/etc/cron.d/billuu-*          one file per job
/var/log/billuu-*.log         logrotate weekly, 8 kept
/var/backups/billuu/          local database dumps, 3 days
/var/backups/dokploy/         local Dokploy dumps, 3 days
/var/backups/volumes/         local config and Redis tarballs, 3 days

Recovery point: the last 01:00 IST run, so at most 24 hours of data loss.

Alerting hooks

Both scripts ping a dead man’s switch when HC_URL (backup) and HC_VERIFY_URL (verification) are set in /etc/billuu/backup.env. Empty values disable the ping.

1. create a check at healthchecks.io, period 1 day, grace 2 hours   -> paste its URL into HC_URL
2. create a second check, period 7 days, grace 6 hours              -> paste into HC_VERIFY_URL

A dump that never runs, or fails, then raises an email instead of failing silently.

The host has a third switch, added 2026-09-13: billuu-heartbeat.sh runs every five minutes and pings HC_HOST_URL while the public health endpoint answers 200. Silence means the host itself is gone.

Since 2026-09-24 the script probes the endpoint twice, two seconds apart, before it pings /fail, and its ping bodies carry curl’s exit code plus the phase timings. A /fail ping alerts at once, ahead of the grace, so a single slow round used to page for a host that never went down. The probe timings say which layer broke (rc=28 with conn= set is a connection that opened and then answered nothing). host/heartbeat-check.sh runs the failure and success paths against a local sink.

3. create a check, period 5 minutes, grace 10 minutes  -> paste its URL into HC_HOST_URL

Until HC_HOST_URL is set the script writes unconfigured to /var/log/billuu-heartbeat.log and does nothing else. Set it, then reboot the host once and watch for the first green ping.

Restore into a scratch database

Never restore over a live database. Use a throwaway container:

sudo bash -c '
set -a; . /etc/billuu/backup.env; set +a
F=$(ls -t /var/backups/billuu/*.dump | head -1)
docker rm -f billuu-restore-test >/dev/null 2>&1
docker run -d --name billuu-restore-test -e POSTGRES_PASSWORD=scratch \
  -e POSTGRES_DB=scratch postgres:17 >/dev/null
sleep 10
docker exec -i billuu-restore-test pg_restore -U postgres -d scratch \
  --no-owner --no-acl < "$F"
docker exec billuu-restore-test psql -U postgres -d scratch -tAc \
  "select count(*) from information_schema.tables where table_schema not in (postgres reserved schemas)"
docker rm -f billuu-restore-test
'

Restore from R2 instead of the local copy:

sudo bash -c '
set -a; . /etc/billuu/backup.env; set +a
rclone copy --s3-no-check-bucket "r2:$R2_BUCKET/db/<file>.dump" /var/backups/billuu/'

Restore into a real target (same major version, no traffic):

pg_restore --no-owner --no-acl --clean --if-exists \
  -d "$TARGET_URL" /var/backups/billuu/<file>.dump

For a full recovery, restore into a new Supabase project, then point DATABASE_URL and CONTROL_PLANE_DATABASE_URL at it in Dokploy and redeploy.

Restore the Dokploy database

The Dokploy dump holds deploy settings and the app environment variables.

# inspect it without touching the live instance
docker run -d --name dokploy-inspect -e POSTGRES_PASSWORD=x -e POSTGRES_DB=x postgres:17
sleep 10
docker exec -i dokploy-inspect pg_restore -U postgres -d x --no-owner < /var/backups/dokploy/<file>.dump
docker exec dokploy-inspect psql -U postgres -d x -tAc 'select email from "user"'
docker exec dokploy-inspect psql -U postgres -d x -tAc 'select name from application'
docker rm -f dokploy-inspect

Full restore on a rebuilt host: install Dokploy, then load the dump into its own Postgres container before opening the panel.

Verification performed

2026-09-13, dump taken from the live Mumbai project:

restore check: tables=552, tenant_schemas=7, database_jobs=10906  (matches production)
dokploy dump:  211 KB uploaded to r2:billuu-pg-backup/dokploy/

First unattended weekly run, same day, 20:30 UTC, dump billuu-db-20260913T193001Z.dump (3.36 MB):

20:30:01Z start verify: billuu-db-20260913T193001Z.dump (3356397 bytes)
20:30:15Z production result tables=552 tenant_schemas=7 database_jobs=10906
20:30:15Z dokploy verify: dokploy-db-20260913T194501Z.dump (212086 bytes)
20:30:25Z dokploy result users=3 applications=1 with_env=1
20:30:25Z OK

Both dead man endpoints answered 200 when tested by hand the same evening, so a silent week will raise an alert rather than pass unnoticed.

The job fails loudly, and pings the /fail endpoint, when any of these is not true:

production      400 or more tables, 5 or more tenant schemas, at least 1 row in database_jobs
dokploy         1 or more users, 1 or more applications

The three counts above are the fingerprint of a healthy restore. A sharp drop means the dump or the restore is broken, even if the job reports OK. Compare against this table when in doubt.

pg_restore prints three harmless errors about supabase_vault and vault.secrets, a Supabase-only extension that the plain postgres:17 image does not have. The dump still carries those rows, they simply cannot be loaded outside Supabase. They are the only errors the job tolerates: every count check runs after the restore and decides the outcome.

What the verification does not prove

The scratch restore is a plain PostgreSQL 17 server, not Supabase. So the weekly proof covers the public schema, all tenant schemas, and the table data. It does not cover:

• Supabase specific extensions, vault.secrets among them
• storage buckets, database roles, and connection pooler settings
• anything that only exists inside Supabase’s own infrastructure

A recovery into a new Supabase project therefore needs a manual pass over those three items after the dump is loaded.

Gotchas

• Bucket-scoped R2 tokens cannot HEAD or create buckets. Every rclone call needs --s3-no-check-bucket, otherwise the upload fails with AccessDenied on CreateBucket.
• pg_dump across regions is slow but small here (46 MB database, about 4 s).
• The dump is a full logical dump, all tenant schemas included.
• The postgres image starts Postgres twice on first boot. Readiness checks must confirm two successful queries with a gap, or a restore can hit the temporary server.