Legendary Arena Lab

Backup and Restore

data

Migration in progress. api.legendary-arena.com and PostgreSQL are moving from Render to a self-hosted DigitalOcean Ubuntu host behind Cloudflare. Render remains rollback-ready until decommission phase.

Backup and Restore

Own the backups and prove the restores — the thing Render did for you. A restore you have not tested is not a backup.

Summary

This page owns two scripts:

  • infra/scripts/50-backup.ps1 — nightly pg_dump -Fc → integrity check (below) → local copy (last 7) → Cloudflare R2 under db-backups/YYYY/MM/DD/legendary-arena-<stamp>.dump, then grandfather-father-son pruning on R2 (35 daily / 12 weekly / monthly, via the engine’s scripts/db-backup-retention.mjs), then the same upload and pruning on the pCloud mirror. -Install puts rclone in place and registers the la-backup systemd timer (daily 09:17 UTC).
  • infra/scripts/51-restore.ps1 — recreate a scratch database (la_restore_test by default), create the dump’s extensions as postgres, restore the rest as the app role, and print per-table row counts. It refuses the live database unless RESTORE_ALLOW_LIVE_TARGET=true.

Integrity check on every dump

Before anything is uploaded, 50-backup.ps1 proves the dump is readable (engine D-24711):

  • Size. A dump of 1024 bytes or less fails the run.
  • pg_restore --list. Reads the archive’s table of contents without restoring anything. A truncated or corrupt archive makes it exit non-zero (“could not read from input file: end of file”), and an archive with no entries fails too. Either way the run fails and la-droplet-backup alerts; nothing reaches R2 or pCloud. This is the engine runbook’s integrity gate 3, run on produce instead of only at restore time.
  • SHA-256. Recorded in the journal and in the success ping body, as size=<bytes> toc_entries=<n> sha256=<hex>. To check a copy pulled from R2 or pCloud during a restore, compare sha256sum <file> with that night’s line in journalctl -u la-backup or the ping’s body in healthchecks.io.

R2 bucket lock

Since 2026-10-13 the bucket legendary-arena-db-backups has the lock rule la-db-backup-lock (prefix db-backups, 30 days, set in the Cloudflare dashboard → R2 → the bucket → Settings → Bucket lock rules). No R2 dump can be deleted or overwritten in its first 30 days, even with the droplet’s credentials: lock rules are changed through the dashboard or the account API, never the S3 token in backup.env. So a compromised droplet or a retention bug can’t wipe the recent R2 backups.

Pruning never collides with the lock. The engine’s scripts/db-backup-retention.mjs keeps every backup younger than 35 days and ignores keys it can’t parse, so rclone deletefile is only ever asked to delete dumps past the lock window. Two things stay unlocked: R2 weeklies and monthlies older than 30 days, and every pCloud copy.

Configuration: /etc/la/backup.env

Root-only (600 root:root), separate from the app’s /etc/la/.env so the running server never holds the backup credentials. Five lines:

LA_DB_NAME=la
BACKUP_R2_ACCOUNT_ID=<bare 32-hex Cloudflare account id>
BACKUP_R2_ACCESS_KEY_ID=<R2 token access key id>
BACKUP_R2_SECRET_ACCESS_KEY=<R2 token secret>
BACKUP_R2_BUCKET=<bucket>

Production adds three more lines:

BACKUP_HEALTHCHECK_URL=<healthchecks.io la-droplet-backup ping URL>
BACKUP_PCLOUD_TOKEN=<rclone pCloud token JSON>
BACKUP_PCLOUD_HEALTHCHECK_URL=<healthchecks.io la-droplet-pcloud ping URL>

The pCloud mirror (second offsite copy)

Every production backup is mirrored to pCloud under the same db-backups/YYYY/MM/DD/ key layout and the same pruning rule as R2. That gives 3-2-1: the live database, R2, and pCloud on a second vendor. The engine’s Render-era db-backup.yml did the same, and the droplet keeps it permanently.

  • Its own check. A mirror failure, or a missing BACKUP_PCLOUD_TOKEN, pings la-droplet-pcloud as failed with the reason in the body. The backup itself still pings la-droplet-backup green, because R2 already holds the dump. This keeps the posture of the engine’s PR #2641: a failed backup means the primary copy failed, which is urgent, while a mirror problem gets its own alert.

  • The token is minted on the operator’s desktop with rclone authorize "pcloud". Each authorization is independent, so the droplet gets its own token, and revoking another one doesn’t break it. The output contains the token, so it never goes into chat or a ticket. The account is US (api.pcloud.com); an EU account needs BACKUP_PCLOUD_HOSTNAME=eapi.pcloud.com.

  • Production data only. Install the token only when the droplet’s database is production. Before cutover a test database’s dumps would land in the production pCloud folder, and the shared pruning would treat them as real backups (the same rule as the R2 bucket above).

  • Account ID is the 32-hex id in the dashboard URL (dash.cloudflare.com/<id>/…) — not the S3 endpoint and not a token. The script rejects anything else.

  • R2 token: an Account API token named la-droplet-backup, Object Read & Write, scoped to the rehearsal bucket and the production backup bucket only.

  • Bucket: legendary-arena-db-backups-rehearsal until cutover. Never point a non-production droplet at the production backup bucket: the key layout and pruning are shared with the engine’s nightly GitHub workflow, so a test database’s dumps would mix with real backups and the retention pass could prune real ones.

The operator writes the file and installs it personally, so the secret goes nowhere else:

scp -i $HOME\.ssh\id_ed25519 "<folder>\backup.env" operator@<droplet-ip>:/tmp/backup.env
ssh -i $HOME\.ssh\id_ed25519 operator@<droplet-ip> "sudo install -m 600 -o root -g root /tmp/backup.env /etc/la/backup.env && rm /tmp/backup.env"

A file saved on Windows has CRLF line endings; the script tolerates them, and sudo sed -i 's/\r$//' /etc/la/backup.env removes them.

Execute

sudo pwsh -File infra/scripts/50-backup.ps1 -Install    # once: rclone + la-backup.timer
sudo pwsh -File infra/scripts/50-backup.ps1             # one backup now
sudo RESTORE_FROM_R2=latest pwsh -File infra/scripts/51-restore.ps1
sudo RESTORE_DUMP=/path/to/file.dump pwsh -File infra/scripts/51-restore.ps1

Compare 51’s row counts against the source before trusting a restore.

Rehearsed (2026-10-11 / 12)

CheckResult
50 local-only drill on the droplet’s own DBPass — 51 round trip, 41 tables, row counts identical; 51 refuses the live DB without its flag
Restore of a production copyPass after a fix — production carries pg_stat_statements, which the app role cannot create; 51 now pre-creates the dump’s extensions as postgres (PR #23). 41 tables / 6,320 rows identical to production
50 -InstallPass — rclone v1.73.3, la-backup.timer daily 09:17 UTC
50 upload to R2 (rehearsal bucket)Pass — db-backups/2026/10/12/legendary-arena-20261012T042346Z.dump
51 with RESTORE_FROM_R2=latestPass — downloaded from R2, restored, row counts identical
Retention pruningPass — on a synthetic 120-day history it keeps 47 and flags 73; on R2 with one backup it flags none

Cutover

  1. Change BACKUP_R2_BUCKET in /etc/la/backup.env to the production backup bucket (the token already covers it).
  2. Run 50-backup.ps1 once and confirm the object lands in the production bucket.
  3. Retire the engine repo’s .github/workflows/db-backup.yml nightly job: its source, Render’s database, stops being the truth at cutover, and the droplet’s Postgres is localhost-only, so the workflow cannot reach it.
  4. After the rollback window, delete the rehearsal bucket.

References

  • Plan section 4 (backup-restore.md stub) and section 9 (Cutover)
  • Engine repo .github/workflows/db-backup.yml (the Render-era nightly backup this replaces)