Backup and Restore
Backup and Restore
Migration in progress.
api.legendary-arena.comand its PostgreSQL are moving off Render onto a self-hosted DigitalOcean Ubuntu droplet fronted by Cloudflare, perdocs/PLAN.md. Until decommission, Render stays warm as the rollback target.
Summary
Self-hosting Postgres means owning backups. 50-backup.ps1
dumps the database every night to a local copy and to the private Cloudflare R2 backup bucket (optionally pCloud too). 51-restore.ps1
restores a dump into a fresh database and prints row counts. A passing restore drill is the Phase 4 exit gate: no cutover without one (docs/PLAN.md §6.1
).
Mechanics
Backup (50-backup.ps1):
pg_dump -FcofLA_DB_NAME(defaultla) as thepostgresOS user over the local socket. No database password is involved.- Fails if the dump is 1 KB or smaller (the same rule as the engine repo’s
db-backup.yml). - Keeps the newest 7 dumps in
/var/backups/la/(BACKUP_LOCAL_KEEP). - Uploads with rclone to
r2:<bucket>/db-backups/YYYY/MM/DD/legendary-arena-<UTC stamp>.dump. This is the same key layout the Render-era GitHub workflow writes, so old and new backups sit side by side. - Prunes R2 (and pCloud) with the engine repo’s own
scripts/db-backup-retention.mjs(grandfather-father-son retention: every daily for 35 days, weekly to 12 weeks, monthly to 12 months), read from the deployed checkout. - Pings
BACKUP_HEALTHCHECK_URLon success and<url>/failon failure.
Credentials live in /etc/la/backup.env (root-only, 600), separate from the app’s /etc/la/.env, so the running server never holds backup-bucket keys. rclone takes them from environment variables, so no rclone config file with secrets is written. -Install puts rclone v1.73.3 in /usr/local/bin and registers la-backup.timer, daily at 09:17 UTC, the slot the Render-era workflow uses.
BACKUP_LOCAL_ONLY=true skips the offsite upload. Use it only for rehearsals; a production run without R2 settings fails instead of quietly keeping the only copy on the box.
Restore (51-restore.ps1):
- Source:
RESTORE_DUMP=<file>, orRESTORE_FROM_R2=latest(the newest key underdb-backups/). - Target:
RESTORE_TARGET_DB, defaultla_restore_test. It is droppedWITH (FORCE)and recreated, owned by the app role. - Restoring into the live database (
la) is refused unlessRESTORE_ALLOW_LIVE_TARGET=true. That is the cutover import, done withla-serverstopped. pg_restore --no-owner --no-acl --role=la --exit-on-error: Render’s role names are dropped, every object ends up owned bylaexactly as migrations would create it, and any failed statement fails the restore.- Finishes with a per-table row-count listing to compare against the source.
Interactions
- PostgreSQL Setup
— the cluster and the
larole the restore targets. - Deploy Server — at rehearsal and cutover, restore first, then deploy (migrations then run against the restored data).
- Monitoring — the on-box check alerts when the newest backup is older than 26 hours.
- Migration Checklist — Phase 4 gate and the cutover import.
- Engine repo
.github/workflows/db-backup.yml— keeps backing up Render until cutover. After cutover, Postgres listens only on localhost, so CI cannot reach it. Disable that workflow (or point it at nothing) once this timer has produced its first good backup.
Edge Cases
- R2 account id must be the bare 32-hex id. The script strips
https://and.r2.cloudflarestorage.comand rejects anything else. - Never the public images bucket.
BACKUP_R2_BUCKET=legendary-imagesis refused. - Restore
--exit-on-erroron a Render dump. If a dump from Render trips on a statement the droplet cannot run, the drill catches it. Fix the cause; do not drop the flag. - Restore is destructive to its target. The default target is a scratch database, and the live database needs the explicit flag.
Execute
# One-time: rclone + daily timer
sudo pwsh -File /opt/la-lab/infra/scripts/50-backup.ps1 -Install
# Back up now
sudo pwsh -File /opt/la-lab/infra/scripts/50-backup.ps1
# Restore drill: newest R2 backup into la_restore_test, then compare counts with production
sudo RESTORE_FROM_R2=latest pwsh -File /opt/la-lab/infra/scripts/51-restore.ps1
# Rehearsal (Phase 3): a Render dump into the scratch database
sudo RESTORE_DUMP=/tmp/render-final.dump pwsh -File /opt/la-lab/infra/scripts/51-restore.ps1
# Cutover import (Phase 5) — la-server stopped
sudo systemctl stop la-server
sudo RESTORE_DUMP=/tmp/render-final.dump RESTORE_TARGET_DB=la RESTORE_ALLOW_LIVE_TARGET=true \
pwsh -File /opt/la-lab/infra/scripts/51-restore.ps1
Verify
systemctl list-timers la-backup.timer --no-pager # next run scheduled
sudo ls -l /var/backups/la/ # newest dump present, > 1 KB
journalctl -u la-backup --no-pager -n 30 # "Backup complete."
The restore drill passes when 51-restore.ps1 exits 0 and its row counts match the source database table for table. The provision smoke workflow
runs a backup → restore round trip and diffs every table’s count, and it checks the live-database refusal.
References
infra/scripts/50-backup.ps1,infra/scripts/51-restore.ps1— owning artifacts.docs/PLAN.md— §1 backups decision, §6.1 Phase 4 exit criteria.- Engine repo
docs/ops/DISASTER_RECOVERY.md— RPO/RTO and the retention policy this mirrors.