﻿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

```toml title="runwisp.toml"
[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](/notifications/providers/slack/).

## What each setting does

- **[`cron = "30 2 * * *"`](/configuration/tasks/#cron):** off-peak, and off the full hour when other jobs
  tend to start. The time is the daemon's timezone; see
  [Scheduling](/concepts/scheduling/).
- **[`on_overlap = "skip"`](/configuration/tasks/#on_overlap):** 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"`](/configuration/tasks/#keep_for):** three months of run history and output, long enough
  to look back after a slow-burning incident.
  See [`keep_for`](/configuration/tasks/#keep_for).
- **[`notify`](/configuration/tasks/#notify):** alerts on every run your
  [`failures`](/configuration/tasks/#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"`](/configuration/tasks/#shell) and `set -o pipefail`:** RunWisp already stops the
  script at the first failing command
  ([fail-fast](/configuration/tasks/#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`](/configuration/tasks/#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`](/configuration/tasks/#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](/recipes/healthcheck/).

## Copying it off the host

A backup on the same disk dies with the host. Add a copy at the end of [`run`](/configuration/tasks/#run):

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

Or make the upload its own task with a later [`cron`](/configuration/tasks/#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:

```toml title="runwisp.toml"
[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.
