--- title: Backups and restore — nightly dumps, an off-machine copy, and a drill that proves them summary: Verified nightly pg_dump on the server, a pull to a second machine that survives sleep, and a scheduled restore into a scratch database that compares every table with live. when_to_use: Any app with a Postgres database, before it holds real data — and again whenever you add an app to the server. prerequisites: A server with Postgres (new-server.md), an app whose DATABASE_URL lives in /home//apps//.env, a laptop or second machine that is on most days. related: [workflows/new-server.md, workflows/ship-a-site.md, skills/ship-a-site/scripts/backup-db.sh, skills/ship-a-site/scripts/restore-db.sh, reference/gotchas.md] last_verified: 2026-09-26 --- # Backups and restore ## Goal Three rules, each with a check: 1. **Every dump is verified as it is written** — an unreadable dump is deleted and logged as a failure. 2. **A copy lives off the machine** — on a laptop or another box, pulled daily, kept longer than the server keeps. 3. **A restore is run on a schedule** — into a scratch database, compared table by table with live. Until it has been restored, a backup is a hope. And a scheduled job is proven by the last line of its log, not by the line in the crontab. ## Inputs (placeholders used) | Placeholder | Meaning | |---|---| | `` | SSH alias of the server | | `` | the user apps and backups run as | | `` | app name — also the dump file prefix and the systemd unit | | `` / `` | the live database and its owning role | | `` | the app's public URL (for the post-restore check) | Scripts (in `skills/ship-a-site/scripts/`): `backup-db.sh`, `restore-db.sh`, `pull-backups.sh`, `pull-backups.plist.template`. ## Steps ### 1. Install the scripts (laptop → server) ```bash scp skills/ship-a-site/scripts/{backup-db.sh,restore-db.sh} :bin/ ssh 'chmod +x ~/bin/*.sh && mkdir -p ~/backups' ``` **Check:** `ssh 'ls -l ~/bin/'` shows both scripts executable. ### 2. Take and verify one dump by hand (server) ```bash ssh '~/bin/backup-db.sh ; tail -1 ~/backups/backup.log; ls -l ~/backups/' ``` What the script does: - reads `DATABASE_URL` from the app's `.env` and **strips `?schema=public`** — Prisma adds it, and `pg_dump`/`psql` reject it (`invalid URI query parameter: "schema"`); - `pg_dump -Fc` (compressed, custom format — lets you restore one table later); - `pg_restore --list` on the result — **a dump that cannot be listed is deleted and logged as FAIL**; - `chmod 600` — it contains every user's data; - deletes dumps older than `KEEP` (7) days. **Check:** the log's last line starts `OK:` and shows `tables_with_data=` > 0; the dump is `-rw-------`. ### 3. Schedule it (server) ```bash ssh 'crontab -e' ``` ```cron # stagger apps by a few minutes 0 1 * * * /home//bin/backup-db.sh ``` The server does not sleep, so cron is fine *here*. Also call `backup-db.sh` at the start of every deploy, before migrating (the deploy template already does). **Check (next day):** `ssh 'grep ~/backups/backup.log | tail -3'` shows a dated `OK:` line from the night. ### 4. Pull a copy to a second machine (laptop) A dump on the same disk as the database is not a backup of that disk. ```bash cp skills/ship-a-site/scripts/pull-backups.sh ~/bin/ && chmod +x ~/bin/pull-backups.sh SERVER= ~/bin/pull-backups.sh # first run by hand ``` **Schedule it with launchd on macOS, never cron.** macOS cron does not run a job whose time passed while the Mac slept — no catch-up, no log line. A real case: a 05:00 pull ran on 2 of 9 days, and one app's dumps had *never* reached the laptop. launchd's `StartCalendarInterval` runs a missed time on wake; `RunAtLoad` covers a Mac that was shut down. ```bash cp skills/ship-a-site/scripts/pull-backups.plist.template ~/Library/LaunchAgents/com.example.pull-backups.plist # edit: Label, YOU (absolute path), APP_NAME, SERVER_ALIAS launchctl bootstrap gui/$(id -u) ~/Library/LaunchAgents/com.example.pull-backups.plist crontab -l # remove any old cron line for the same job, or both run ``` On Linux, use a systemd user timer with `Persistent=true` for the same catch-up. Keep the local copy longer than the server does (script default: 14 days vs 7). `--ignore-existing` makes duplicate runs harmless. **Check:** ```bash launchctl list | grep pull-backups # loaded, last exit 0 tail -3 ~/backups//pull.log # dated OK lines, one per day ``` One `FAIL: ... exit 255` line (SSH could not connect that moment) is fine if the next run is `OK`. What matters is that failures are *logged*. ### 5. Run the restore drill (server) ```bash ssh 'DB_NAME= DB_OWNER= ~/bin/restore-db.sh ' ``` It restores the newest dump into `restore_check_`, compares `count(*)` per table against live (not `pg_stat_user_tables` — that is an estimate), reports indexes and constraints restored, then drops the scratch DB. Live is never touched. **The permission trap it handles:** dumps are 600 owned by the deploy user, but `pg_restore` runs as `postgres`, so a naive restore fails with `could not open input file ... Permission denied` — the first time you really need it. Don't `chmod 644` the dumps (that exposes every user's data). The script stages a short-lived copy owned by `postgres` and deletes it on exit: ```bash STAGED="/var/lib/postgresql/.restore-$$.dump" trap 'sudo rm -f "$STAGED"' EXIT sudo install -o postgres -g postgres -m 600 "$DUMP" "$STAGED" sudo -u postgres pg_restore --no-owner --role= -d "$DB" "$STAGED" ``` **Check:** ends with `RESULT: backup is good — every table matches live.` ("some counts differ" is expected if the app was used after the dump.) ### 6. Schedule the drill monthly (server) ```cron 0 3 1 * * { echo "== $(date -u '+\%F \%R') =="; DB_NAME= DB_OWNER= /home//bin/restore-db.sh ; } >> /home//backups/restore-drill.log 2>&1 ``` `%` must be escaped in a crontab, or cron treats it as a newline. **One drill per app** — "readable" (step 2) is not "restorable". **Check (after the 1st):** `tail -20 ~/backups/restore-drill.log` shows a dated run ending in `backup is good`. ### 7. Know the real recovery before you need it (server) ```bash DB_NAME= DB_OWNER= APP_URL=https:/// ~/bin/restore-db.sh --live ``` Built to be hard to run by accident: you must type `RESTORE`; it takes a **safety dump of current live data first** and refuses to continue if that fails; stops the service, recreates the DB, restores, starts the service, prints table counts and the site status; on errors prints the command to roll back to the safety dump. No automatic rollback — a person reads the output and decides. **Check:** you can say, without looking, where the dumps are on the server, where the off-machine copies are, and which command restores live. ## Traps | Symptom | Cause | Fix | |---|---|---| | `pg_restore: could not open input file ... Permission denied` | dump is 600, pg_restore runs as `postgres` | stage a postgres-owned copy, `trap` removes it | | `invalid URI query parameter: "schema"` | Prisma's `?schema=public` in `DATABASE_URL` | `"${DATABASE_URL%%\?*}"` | | `unsupported version (1.x) in file header` | dump written by a newer `pg_dump` than the `pg_restore` | match client versions | | `SET transaction_timeout` fails on restore | PG17+ dump into PG16 | install matching client, or strip that line from a plain dump | | Row counts off by a few | compared `pg_stat_user_tables` (estimate) | `count(*)` | | Drill counts differ from live | app used between dump and drill | expected; rerun right after a fresh dump to confirm | | Laptop backups stale though the job "is set up" | macOS cron skips runs during sleep | launchd `StartCalendarInterval` + `RunAtLoad` | | Cron line with `date +%F` does nothing | unescaped `%` in crontab | `\%` | | Restored DB has rows but the app errors | missing indexes/constraints/ownership | compare index/constraint counts; `--no-owner --role=` | | Paid snapshots piling up on the bill | snapshots bill monthly per snapshot, forever | provider's automated backups for routine; snapshot only before risky changes, delete after | ## Done when - [ ] `backup.log` has a nightly `OK:` line per app, dumps are `600` - [ ] each deploy dumps before migrating - [ ] the laptop copy updates daily (proven by `pull.log`), kept longer than the server's - [ ] the restore drill passed once by hand, per app - [ ] the monthly drill is scheduled and its log shows a run - [ ] the `--live` procedure is known and the safety-dump behaviour understood