Backups and recovery

A restic backup recipe for SQLite and Postgres on one VPS

2026-11-09 · 9 min read

Most backup guides end at "run restic on /var". That produces a snapshot, not a backup you can trust. A SQLite file copied while the app is writing can be corrupt, a Postgres data directory copied while the server runs usually is, and nothing in the guide proves you can get the data back. This is the recipe we run on single servers, with the reasoning behind each step and the traps we hit while testing it.

Who needs this

A Node or Go app with a SQLite file and an uploads folder. The database is a single file, so the temptation is cp app.db /backup/. If a write lands during the copy you get a file that opens, passes a casual look and fails later with database disk image is malformed. The change: take the copy with SQLite's own backup API, check it, and only then let restic see it.

A small SaaS on Postgres with one or two databases. Here the failure is usually not corruption but missing pieces. pg_dump -Fc gives you a clean snapshot of one database and nothing else: no roles, no other databases, no server config. The first time you restore onto a fresh machine, the restore stops at role "shop" does not exist. The change: dump the roles too, and rehearse the restore on a clean box.

A side project that "has backups" from the hosting provider. Provider snapshots are useful, but they live in the same account as the server, they are crash-consistent at best for a database, and you usually cannot restore one table from them. A restic repository on a different machine or bucket gives you a second, independent copy you control. See 3-2-1 for one VPS for how the two fit together.

A server where a cron job is the only safety net. If the job fails at 03:15 and nobody reads the log, you find out when you need the backup. The change is as much about alerting and restore tests as about restic, and the last third of this post covers it.

The rules we work from

1. A backup on the same disk is not a backup. Use a repository on another machine or in a bucket (sftp:, s3:, b2:, rclone:). A second local disk is fine for a first trial.
2. Never copy a live database file. Take a consistent dump first, then back up the dump.
3. If the dump is bad, stop. A snapshot of a half-written dump is worse than no snapshot, because it looks like protection.
4. A backup you have never restored is a guess. Restore on a schedule and compare the result with production.

Everything below is an application of those four.

Set up the repository

sudo apt install -y restic sqlite3 postgresql-client
sudo install -m 600 /dev/null /root/.restic-pass
openssl rand -base64 33 | sudo tee /root/.restic-pass >/dev/null

export RESTIC_REPOSITORY=sftp:backup@storage.example.net:/srv/restic/myapp
export RESTIC_PASSWORD_FILE=/root/.restic-pass
sudo -E restic init

restic encrypts everything client-side with that password. Lose the password and the repository is unreadable, so store a copy somewhere that is not this server: a password manager, a printed sheet in a drawer. We say it twice because it is the one mistake that cannot be fixed afterwards.

For sftp: the unattended job needs an SSH key without a passphrase (or an agent) and a host entry in ~/.ssh/config or known_hosts, otherwise cron stalls on the host-key prompt. For S3-compatible storage you export AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY and use s3:https://endpoint/bucket. Any restic repository string works with the scripts below.

Dump SQLite properly

DUMP_DIR=/var/backups/dumps
install -d -m 700 "$DUMP_DIR"

sqlite3 /var/www/app/data/app.db ".timeout 10000" ".backup '$DUMP_DIR/sqlite-app.db'"
[ "$(sqlite3 "$DUMP_DIR/sqlite-app.db" 'PRAGMA integrity_check')" = ok ] || exit 1

A few notes on those two lines:

• .backup uses SQLite's online backup API. It is safe while the app keeps writing, and it understands WAL mode, which a plain cp of app.db does not (the recent writes may still sit in app.db-wal).
• .timeout 10000 makes the command wait for a lock for up to ten seconds instead of failing instantly.
• If your database is written constantly and the copy keeps restarting, VACUUM INTO '/path/copy.db' is an alternative that produces a compacted copy in one transaction.

The integrity_check line matters because it turns "the backup command exited 0" into "the copy is a valid database". It does not tell you the database has the right content. We come back to that in the restore that succeeded and returned the wrong data.

Dump Postgres properly

pg_dump -Fc -f "$DUMP_DIR/pg-appdb.dump" appdb
pg_restore -l "$DUMP_DIR/pg-appdb.dump" >/dev/null || exit 1
pg_dumpall --globals-only > "$DUMP_DIR/pg-globals.sql"
[ -s "$DUMP_DIR/pg-globals.sql" ] || exit 1

Custom format (-Fc) is compressed and lets pg_restore pick single tables later. pg_restore -l lists the archive's table of contents; if the file is truncated it fails, which is the cheapest readability check there is.

The globals dump is the part people skip. pg_dump -Fc does not include roles, so restoring into a fresh cluster with --create fails when the database's owner does not exist. pg_dumpall --globals-only contains the roles and their password hashes, so treat that file as a secret: mode 600, inside the 700 directory. Restore it first, and expect one harmless role "postgres" already exists complaint.

Run the dump as a user Postgres trusts. On a default Debian install that means sudo -u postgres or runuser -u postgres -- (peer authentication over the Unix socket), or a role in ~/.pgpass if you dump as another user.

A dump is the state at one moment. Everything written after it is lost in a restore, which with a nightly schedule is up to a day of data. If that is too much, you need WAL archiving; pg_dump vs physical backup vs PITR lays out where the line is.

Snapshot, retention, check

restic backup --tag app --exclude-caches /var/www/app/uploads /etc/caddy "$DUMP_DIR"
restic forget --tag app --prune --keep-daily 7 --keep-weekly 4 --keep-monthly 6
restic check

--tag app lets you filter later, which matters once the repository holds more than one kind of snapshot. The forget line keeps the newest snapshot of each of the last 7 days, 4 weeks and 6 months; what that actually keeps, and the traps in it, is the subject of restic retention policies explained. restic check verifies the repository structure; add --read-data-subset=10% on a weekly run to read back a tenth of the actual data.

Rule of thumb: clean the dump directory first, dump second, snapshot last.

Delete the old dump files at the start of every run. In our tests a database that had been removed from the config kept appearing in new snapshots because its last dump was still in the directory. A one-line find "$DUMP_DIR" -maxdepth 1 -type f -delete at the top of the script fixes it.

Make it one script that stops on the first failure

#!/usr/bin/env bash
set -euo pipefail
umask 077
# ... dumps as above, each followed by its check ...
restic backup --tag app --quiet /var/www/app/uploads "$DUMP_DIR"
restic forget --tag app --prune --quiet --keep-daily 7 --keep-weekly 4 --keep-monthly 6
restic check --quiet
curl -fsS -m 10 --retry 3 -o /dev/null "$HC_URL" || true   # success ping

set -euo pipefail is the important line. With it, a failed dump ends the script before restic backup runs, so no snapshot is created. Without it the script carries on and snapshots whatever is lying in the dump directory.

Schedule it with flock so two runs cannot overlap, for example if one night's run takes longer than expected:

15 3 * * *  root  flock -n /run/backup.lock /opt/backup/backup.sh  >> /var/log/backup.log 2>&1
30 4 * * 0  root  /opt/backup/restore-test.sh                      >> /var/log/restore-test.log 2>&1

Pick the time on purpose: after your heaviest batch jobs, and before the hour you would want to be asleep for a restore. The Sunday line is the restore test.

The restore test

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

That proves the files are readable. For Postgres the stronger proof is to restore into a scratch database and count rows:

sudo -u postgres createdb scratch
sudo -u postgres pg_restore --no-owner --exit-on-error -d scratch /path/to/pg-appdb.dump
sudo -u postgres psql -d scratch -c 'select count(*) from orders'
sudo -u postgres dropdb scratch

Two details we found by doing it. The dump directory is mode 700, which is right, but after restic restore the postgres user cannot read a dump inside it until you open the file as root and feed it on stdin, or copy it somewhere postgres can read. And the extracted path mirrors the original one, so the dump sits at $d/var/backups/dumps/..., not at $d/.... Better to meet both on a quiet Tuesday than during an outage.

Mistakes we see

• Backing up the data directory of a running Postgres. /var/lib/postgresql copied with the server running is usually unusable. A file-level copy only works if it is paired with WAL handling, which is a different technique.
• Excluding the dump directory by accident. An exclude pattern like *.db or *.dump meant for temp files also removes your dumps. The restore test catches it; a green backup log does not.
• Forgetting everything that is not a database. Uploads, /etc/caddy, your env files (encrypted or in a password manager), cron entries and systemd units. A restored database with no app config is half a server.
• Keeping the repository password only on the server. The server is exactly what you will be missing.
• Running restic unlock to silence a lock error. The lock may belong to a backup that is still running. Check ps first.
• Letting the repository grow without forget --prune. Snapshots are cheap because of deduplication, but never deleting any is still unbounded.
• Treating restic check as a restore test. It checks the repository's own consistency. It does not know whether your dump contains the right database.

Tools compared

We prefer the second row for one VPS because the check sits between the dump and the snapshot, and that is where this technique earns its keep.

FAQ

Is it safe to run restic while the app is running?

Yes, for the dump-then-snapshot pattern. The dump tools take a consistent copy while the app keeps working, and restic reads the finished dump files and your uploads, which change rarely. Avoid running restic directly on a live database file.

How big will the repository get?

It depends on your data and your change rate, so measure instead of guessing. restic deduplicates in chunks, so an unchanged uploads folder costs almost nothing per extra snapshot, while a database dump that changes throughout costs more. Check restic stats after the first week and set expectations from that.

Should I use pg_dump or pg_basebackup?

For a small database and a tolerance of up to a day of loss, pg_dump -Fc plus the globals is simpler and restores onto any machine with a compatible Postgres. pg_basebackup plus archived WAL is the route for point-in-time recovery, and it needs more setup and monitoring.

How do I know the backup ran last night?

A log file is not enough, because a job that never started writes nothing. Have the script ping a dead-man's-switch URL on success and alert when the ping is missing; monitoring backups so silence is not success goes through the options.

Can I put the repository on the same VPS?

For a first trial, yes. For anything you care about, no: a dead disk or a deleted server takes the data and its backup together.

The scripts we run add a config file, failure alerts to Telegram or a webhook, the retention settings and a test suite that includes a corrupted SQLite file and an SFTP repository. They are packaged as the VPS Deploy & Backup Kit, and the recipe above is enough to build your own.

backupresticsqlitepostgres