Legendary Arena Lab

Backup and Restore

wiki

Backup and Restore

Migration in progress. api.legendary-arena.com and its PostgreSQL are moving off Render onto a self-hosted DigitalOcean Ubuntu droplet fronted by Cloudflare, per docs/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):

  1. pg_dump -Fc of LA_DB_NAME (default la) as the postgres OS user over the local socket. No database password is involved.
  2. Fails if the dump is 1 KB or smaller (the same rule as the engine repo’s db-backup.yml).
  3. Keeps the newest 7 dumps in /var/backups/la/ (BACKUP_LOCAL_KEEP).
  4. 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.
  5. 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.
  6. Pings BACKUP_HEALTHCHECK_URL on success and <url>/fail on 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>, or RESTORE_FROM_R2=latest (the newest key under db-backups/).
  • Target: RESTORE_TARGET_DB, default la_restore_test. It is dropped WITH (FORCE) and recreated, owned by the app role.
  • Restoring into the live database (la) is refused unless RESTORE_ALLOW_LIVE_TARGET=true. That is the cutover import, done with la-server stopped.
  • pg_restore --no-owner --no-acl --role=la --exit-on-error: Render’s role names are dropped, every object ends up owned by la exactly 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 la role 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.com and rejects anything else.
  • Never the public images bucket. BACKUP_R2_BUCKET=legendary-images is refused.
  • Restore --exit-on-error on 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