Postgres backups with restic, and actually restoring them

A backup you’ve never restored is a hope, not a backup. I learned that the cheap way, thankfully: during a drill, not an outage. Our nightly job had been writing a perfectly valid, perfectly empty dump for three weeks because pg_dump failed on authentication and the pipe after it didn’t care.

This post is the setup I use now for small and medium Postgres databases (up to a few hundred gigabytes): logical dumps streamed into restic, with retention, integrity checks and a restore I run every month.

Why restic#

restic gives me three things I’d otherwise script badly myself:

• client-side encryption;
• deduplication;
• a retention policy.

Deduplication matters more than you’d think for dumps. Consecutive nightly dumps of a database that changes a little each day share most of their content, so storing thirty of them costs far less than thirty times the size of one. It works with plain directories, SFTP, S3-compatible storage and more.

Setup#

restic reads the repository location and password from the environment. I keep them in a root-only file:

RESTIC_REPOSITORY=s3:https://s3.example.net/backups/pg
RESTIC_PASSWORD_FILE=/etc/restic/pg.pass
AWS_ACCESS_KEY_ID=...
AWS_SECRET_ACCESS_KEY=...

Initialize the repository once:

set -a; source /etc/restic/pg.env; set +a
restic init

The backup itself#

The naive version#

pg_dump -Fc appdb | restic backup --stdin --stdin-filename appdb.dump

This is exactly how I got my empty backups. If pg_dump fails, restic still receives a valid (short or empty) stream, exits 0 and happily records a snapshot. set -o pipefail in the script would catch the failure, but the bad snapshot is already stored by then.

The version that checks the exit code#

Since restic 0.17 there’s a better way: let restic run the command and check its exit code.

restic backup \
  --tag db:appdb \
  --stdin-filename appdb.dump \
  --stdin-from-command -- \
  pg_dump --format=custom --no-owner appdb

If pg_dump exits non-zero, restic fails the backup and doesn’t create a snapshot. That one flag would have saved me three weeks.

Dump options#

• --format=custom (-Fc) is compressed and lets pg_restore restore selectively or reorder objects. It also means restic’s deduplication works less well than with a plain SQL dump, since compression shuffles bytes around. I tested both on our data: plain text deduplicated better, but custom format was still smaller overall and is much more convenient to restore. Measure on yours.
• --no-owner makes the dump restorable into a database with different role names, which is what you want for drills.
• Roles are not included. pg_dump skips roles and other cluster-wide objects. Back those up separately with pg_dumpall --globals-only using the same pattern and a different --stdin-filename.

Retention and checks#

After the backup, the same job applies retention:

restic forget --tag db:appdb \
  --keep-daily 7 --keep-weekly 5 --keep-monthly 12 \
  --prune

and once a week, a check that reads a sample of the actual data, not just the metadata:

restic check --read-data-subset=5%

Over a few weeks that touches a good part of the repository without downloading all of it every time. The whole thing runs from a systemd timer with OnFailure= pointing at our alerting, so a failed backup pages someone the same morning.

The restore drill#

Once a month a timer on a separate machine does this:

set -Eeuo pipefail
set -a; source /etc/restic/pg.env; set +a

dropdb --if-exists appdb_drill
createdb appdb_drill

restic dump --tag db:appdb latest /appdb.dump \
  | pg_restore --no-owner --exit-on-error -d appdb_drill

psql -d appdb_drill -Atc "SELECT count(*) FROM orders WHERE created_at > now() - interval '2 days'"

Two details:

• The path is /appdb.dump because that’s how restic stores stdin backups: the filename at the root of the snapshot.
• --tag together with latest picks the newest snapshot of this particular database, which matters once more than one database lives in the repository.

pg_restore reading from a pipe can’t use -j for parallel restore, since that needs random access to the file. For big databases it’s faster to restic dump to a local file first and then run pg_restore -j 4.

What this costs#

That’s the real deliverable here: not the backup, but the boring, automated proof that the restore works.