Database backups that actually restore

A backup is not a file you made. A backup is a file you restored. Everything here is aimed at that distinction.

What to back up

• The database (all of it: schema and data).

• The files (uploads, themes, config) — separately, but on the same schedule.

• The secrets needed to use the data (env files, encryption keys) — stored securely, NOT in the same archive as the data.

Take a consistent dump

PostgreSQL:

pg_dump -Fc -f /backups/appdb-$(date +%F-%H%M).dump appdb

MySQL/MariaDB:

mysqldump --single-transaction --routines --triggers appdb \
  | gzip > /backups/appdb-$(date +%F-%H%M).sql.gz

--single-transaction gives a consistent snapshot for InnoDB without locking writes.

Automate it

#!/usr/bin/env bash
set -euo pipefail
DATE=$(date +%F-%H%M)
pg_dump -Fc -f /backups/appdb-$DATE.dump appdb
# copy off-site immediately — a backup on the same host is not a backup
rclone copy /backups/appdb-$DATE.dump remote:backups/appdb/
# keep 7 daily, 4 weekly, 6 monthly
find /backups -name 'appdb-*.dump' -mtime +7 -delete

Put it in cron. Watch that it succeeds — a silently failing backup job is worse than none, because you believe you are covered.

Encryption

If the dump leaves the machine, encrypt it (age, gpg, or restic's built-in encryption). A readable database dump in object storage is a data breach waiting to happen.

Test the restore — the whole point

createdb appdb_restore_test
pg_restore -d appdb_restore_test /backups/appdb-2026-01-01-0300.dump
psql -d appdb_restore_test -c 'SELECT count(*) FROM users;'
dropdb appdb_restore_test

Do this on a schedule, not once. Restores fail for mundane reasons (wrong version, missing extension, permissions) and you want to discover those on a Tuesday afternoon, not during an incident.

Retention and the 3-2-1 rule

Keep 3 copies, on 2 media, with 1 off-site. Practically: the live database, a local dump, and an off-site copy in object storage. See Back up your data.

Related reading

• Back up your data

• PostgreSQL basics