Automated database backups for a self-hosted app
The short version: a real backup pipeline is dump -> compress -> encrypt -> copy offsite -> prune old -> schedule, and it is only real once you have restored from it. Follow the 3-2-1 rule (3 copies, 2 media, 1 offsite), set the frequency from how much data you can afford to lose, automate it on a timer, and test the restore on a schedule - an untested backup is a guess, not a backup.
What a good backup looks like
Three rules carry most of the value:
- 3-2-1 - keep three copies, on two kinds of media, with at least one offsite. A dump on the same disk as the database is not a backup.
- Frequency from your RPO - back up at least as often as the amount of data you are willing to lose. Daily is a floor, not a goal.
- Tested restores - a backup you have never restored is a guess. Restore on a schedule and verify.
The pieces you need
A consistent dump
pg_dump / mysqldump / mongodump produce a point-in-time snapshot. Run against a replica if the dump load matters.
Compress and encrypt
gzip/zstd to shrink it, then encrypt (age/gpg) before it leaves the host. Keep the key separate.
An offsite copy
Push to object storage in another location/provider, out of the database's failure domain.
Prune + schedule
Delete old backups on a retention policy, and run the whole thing on a systemd timer or cron.
Set it up in three steps
Dump, compress, encrypt, push offsite
# Postgres example - one pipeline, encrypted before it leaves the host
pg_dump "$DATABASE_URL" | zstd -19 | age -r "$AGE_PUBKEY" \
> "/backups/db-$(date +%F-%H%M).sql.zst.age"
# then copy offsite (S3-compatible)
aws s3 cp /backups/db-*.age s3://my-backups/db/ --only-show-errorsPrune old backups and schedule it
# keep 14 days locally; offsite lifecycle handles the rest
find /backups -name 'db-*.age' -mtime +14 -delete
# run on a systemd timer (preferred) or cron - e.g. hourly
# OnCalendar=hourly (systemd) or 0 * * * * (cron)Write down the restore command now
# the restore is part of the backup - keep it next to the script
age -d -i key.txt db-*.age | zstd -d | psql "$RESTORE_URL"Test the restore, on a schedule
Most backup failures are discovered during a real outage - a truncated dump, a missing extension, a version mismatch, an unreadable archive. The only way to know your backups work is to restore one. Do it on a schedule, into a scratch database, and check that the row counts and schema come back.
# monthly restore drill into a throwaway DB, then verify
createdb restore_test
age -d -i key.txt "$(ls -t /backups/db-*.age | head -1)" | zstd -d | psql restore_test
psql restore_test -c "SELECT count(*) FROM your_main_table;" # sane number?
dropdb restore_testPick your interval from how much data and downtime you can tolerate - the RPO/RTO calculator turns those into concrete backup-frequency and recovery-time numbers.
Backups that run, verify, and prove themselves
The hard part of backups is not writing the dump - it is keeping them running, offsite, encrypted, and proven by restore, forever. On your own servers, Infraveil schedules and supervises your backups, surfaces a failed run instead of letting it silently stop, and records that they happened - so “we have backups” is something you can show, not hope, with the data staying on infrastructure you own.
Frequently asked questions
How often should I back up?
Match the interval to acceptable data loss (your RPO). Losing an hour is fine? Hourly is enough. Only minutes? You need continuous archiving or replication, not just periodic dumps. A common baseline is a daily full plus more frequent WAL/binlog archiving.
Where should backups be stored?
Follow 3-2-1: three copies, two media, one offsite. A dump on the same server as the database is not a backup - copy it to object storage in a different location or provider, out of the same failure domain.
Should I encrypt backups?
Yes - a dump contains everything sensitive in your app. Encrypt before it leaves the host (age or gpg, or server-side encryption at the destination) and store the key separately. It is the difference between a lost copy and a breach.
Why do I need to test restores?
Most backup failures are only found during a real outage - a corrupt dump, a missing extension, a version mismatch. Restore to a scratch environment on a schedule and verify. A backup you have never restored is an assumption, not a recovery plan.
Client-side, no signup — they run in your browser.