kitdocs · github

Backups and restore — nightly dumps, an off-machine copy, and a drill that proves them

raw markdown workflows/backups-and-restore.md verified 2026-09-26

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)

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)

scp skills/ship-a-site/scripts/{backup-db.sh,restore-db.sh} <server>:bin/
ssh <server> 'chmod +x ~/bin/*.sh && mkdir -p ~/backups'

Check: ssh <server> 'ls -l ~/bin/' shows both scripts executable.

2. Take and verify one dump by hand (server)

ssh <server> '~/bin/backup-db.sh <app>; 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)

ssh <server> 'crontab -e'

# stagger apps by a few minutes
0 1 * * * /home/<deploy-user>/bin/backup-db.sh <app>

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 <server> 'grep <app> ~/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.

cp skills/ship-a-site/scripts/pull-backups.sh ~/bin/ && chmod +x ~/bin/pull-backups.sh
SERVER=<server> ~/bin/pull-backups.sh <app>     # 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.

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:

launchctl list | grep pull-backups        # loaded, last exit 0
tail -3 ~/backups/<app>/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)

ssh <server> 'DB_NAME=<db-name> DB_OWNER=<db-user> ~/bin/restore-db.sh <app>'

It restores the newest dump into restore_check_<timestamp>, 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:

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=<db-user> -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)

0 3 1 * * { echo "== $(date -u '+\%F \%R') =="; DB_NAME=<db-name> DB_OWNER=<db-user> /home/<deploy-user>/bin/restore-db.sh <app>; } >> /home/<deploy-user>/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)

DB_NAME=<db-name> DB_OWNER=<db-user> APP_URL=https://<domain>/ ~/bin/restore-db.sh <app> --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

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