Starlab

A tested restic backup recipe for SQLite and Postgres

· 3 min read

Most backup guides end at "run restic on /var". That produces a snapshot, not a backup you can trust: a live SQLite file copied mid-write can be corrupt, a Postgres data directory copied while the server runs usually is, and nothing proves you can restore. This is the recipe I use and the traps I hit while testing it.

The rules

  1. A backup on the same disk is not a backup. Use a repository on another machine or bucket (sftp:, s3:), or at least another disk for a first test.
  2. Never copy a live database file. Take a consistent dump first.
  3. If the dump is bad, stop. A snapshot of a half-written dump is worse than none, because it looks like protection.
  4. A backup you have never restored is a guess. Restore on a schedule.

Dumps

SQLite: use the online backup, then check the copy.

sqlite3 /var/www/app/data/app.db ".backup '/var/backups/dumps/app.db'"
[ "$(sqlite3 /var/backups/dumps/app.db 'PRAGMA integrity_check')" = ok ] || exit 1

Postgres: custom format, then prove the archive is readable.

pg_dump -Fc -f /var/backups/dumps/appdb.dump appdb
pg_restore -l /var/backups/dumps/appdb.dump >/dev/null || exit 1

Keep the dump directory at mode 700. Two details from testing: pg_dump -Fc does not include roles, so restoring into a fresh cluster fails with role "shop" does not exist unless you also save pg_dumpall --globals-only; and with a 700 directory the postgres user cannot read the dump after a restic restore, so restore it to a path postgres can read.

Snapshot, retention, check

restic backup --tag app /var/www/app/uploads /var/backups/dumps
restic forget --tag app --prune --keep-daily 7 --keep-weekly 4 --keep-monthly 6
restic check

Delete old dump files at the start of every run. In my tests a database that I had removed from the config kept appearing in new snapshots because its last dump was still sitting in the directory.

The restore test

d=$(mktemp -d)
restic restore latest --tag app --target "$d"
sqlite3 "$d/var/backups/dumps/app.db" 'PRAGMA integrity_check'   # expect: ok
pg_restore -l "$d/var/backups/dumps/appdb.dump" >/dev/null        # expect: no error
restic check --read-data-subset=10%
rm -rf "$d"

For Postgres the real proof is restoring into a scratch database and counting rows. Run this weekly from cron, and make cron send a ping to a healthcheck URL on success, so silence raises an alarm instead of looking like success.

What the tests showed

In a clean Debian 12 container with restic and PostgreSQL 15: a corrupted SQLite file aborted the backup and no new snapshot was created; a missing dump was caught by the restore test; a wrong repository was rejected; the Postgres and SQLite dumps restored with the expected rows; and the same flow worked over an sftp: repository. What I did not test: S3 and rclone backends, and Postgres point-in-time recovery, which this recipe does not provide (it restores to the moment of the dump).

From sketch to something you can run unattended

The snippets above are the idea, simplified. The scripts I run add configuration, failure alerts to Telegram or a webhook, retention settings and the full container test suite (59 checks). They are packaged as the VPS Deploy & Backup Kit. If you only want the restore check, there is a free standalone script on the free tools page.