Nightly backup

A nightly Postgres dump that alerts you when it fails. The same shape works for MySQL, SQLite, or anything you can back up from a shell script.

The task

[tasks.backup-postgres]
group       = "Backups"
description = "Nightly logical dump of the production database"
cron        = "30 2 * * *"   # 02:30 every day
on_overlap  = "skip"          # never two dumps at once
keep_for    = "90d"
notify      = ["slack-ops"]
shell       = "/bin/bash"     # for pipefail
# timeout   = "4h"            # optional, see below

run = """
set -o pipefail
TS=$(date -u +%Y%m%dT%H%M%SZ)
DEST=/srv/backups/postgres
FILE="$DEST/app_production-$TS.dump.gz"
mkdir -p "$DEST"

PGPASSWORD="$BACKUP_DB_PASSWORD" pg_dump \\
  --host=db.internal --username=backup \\
  --format=custom --no-owner --no-privileges \\
  app_production | gzip --best > "$FILE"

gzip -t "$FILE"   # the archive reads end to end
echo "Wrote $FILE ($(du -h "$FILE" | cut -f1))"
"""

slack-ops is a notifier you declare once; see the Slack provider.

What each setting does

• cron = "30 2 * * *": off-peak, and off the full hour when other jobs tend to start. The time is the daemon’s timezone; see Scheduling.
• on_overlap = "skip": if last night’s dump is somehow still running, the new one doesn’t start. It’s recorded as skipped, and you get no fresh dump, which is the signal you want.
• keep_for = "90d": three months of run history and output, long enough to look back after a slow-burning incident. See keep_for.
• notify: alerts on every run your failures policy counts as failed. By default that includes a timeout, a crash, and a missed run (the daemon was down at 02:30). The bell in the Web UI gets it too.
• shell = "/bin/bash" and set -o pipefail: RunWisp already stops the script at the first failing command (fail-fast), but a pipeline only reports its last command. Without pipefail, a pg_dump that dies halfway leaves a truncated file and a green run. pipefail is a bash feature; the default /bin/sh rejects it.
• No timeout: a safe limit depends on your database size. The next night’s firing already acts as a ceiling. If you want a hard stop, set timeout to about 1.5 times your slowest dump.
• No retries: a retry five minutes later hides the fact that the database was unreachable at 02:30. Let the alert fire and look at it. Retries suit probes; see Health checks.

Copying it off the host

A backup on the same disk dies with the host. Add a copy at the end of run:

aws s3 cp "$FILE" "s3://my-backups/postgres/$(hostname)/" --storage-class GLACIER_IR

Or make the upload its own task with a later cron, so a failed upload and a failed dump alert separately.

Checking that it restores

A backup you’ve never restored is only a hope. Restore last night’s dump into a scratch database every morning:

[tasks.backup-restore-test]
group       = "Backups"
description = "Restore last night's dump into a scratch DB and run a smoke query"
cron        = "0 5 * * *"
on_overlap  = "skip"
notify      = ["slack-ops"]
shell       = "/bin/bash"

run = """
set -o pipefail
LATEST=$(ls -1t /srv/backups/postgres/app_production-*.dump.gz | head -n1)
test -n "$LATEST" || { echo "no dump found"; exit 1; }

psql -h db.internal -U backup -d postgres -c 'DROP DATABASE IF EXISTS app_restore_test'
psql -h db.internal -U backup -d postgres -c 'CREATE DATABASE app_restore_test'
gunzip -c "$LATEST" | pg_restore --no-owner --no-privileges --dbname=app_restore_test
psql -h db.internal -U backup -d app_restore_test -c 'SELECT count(*) FROM users'
"""

If a dump can’t be restored, you know within a day.