Postgres Backup for Self-Hosted Apps: From pg_dump to PITR
Logical dumps, WAL archiving, off-site encryption & real restore tests
Why pg_dump alone isn't enough
Almost every self-hosted setup starts its backup the same way: a pg_dump in a nightly cron job, the result dumped somewhere on the same disk. That's better than nothing – but it only covers the simplest failure case. Three problems remain:
- Granularity: A daily dump means up to 24 hours of data loss in the worst case. Fine for a knowledge base, not for an app with real user input.
- Off-site: If the backup sits on the same machine, a disk failure, ransomware or an accidental
docker volume rmtakes it down together with the database. - Trust: A backup that has never been restored is an assumption, not a backup.
Here is a three-tier setup that closes exactly these three gaps – deliberately pragmatic, not over-engineered.
My setup
A Django app, Postgres as postgres:16-alpine in a Docker container, data in a named volume. Nothing exotic – exactly the setup most homelab apps run:
services:
db:
image: postgres:16-alpine
environment:
POSTGRES_DB: appdb
POSTGRES_USER: app
POSTGRES_PASSWORD_FILE: /run/secrets/db_password
volumes:
- pgdata:/var/lib/postgresql/data
- ./wal_archive:/wal_archive
restart: unless-stopped
volumes:
pgdata:We only need the ./wal_archive bind mount for tier 3 – I add it now so the container doesn't have to be restarted for it later.
Tier 1: Logical dump with pg_dump
The logical dump is the foundation: portable, version-independent enough and ideal for the everyday case – „I need last night's database back“. The script dumps from the container, compresses on the fly and cleans up old local copies:
#!/usr/bin/env bash
set -euo pipefail
STAMP=$(date +%F_%H%M)
OUT="/srv/backups/appdb_${STAMP}.sql.gz"
docker exec -t db \
pg_dump --no-owner --no-privileges -U app appdb \
| gzip -9 > "$OUT"
# keep only the last 7 days locally
find /srv/backups -name 'appdb_*.sql.gz' -mtime +7 -delete--no-owner and --no-privileges make it easier later to restore into a fresh DB where the roles may have different names. Then into the crontab:
30 2 * * * /srv/scripts/pg_backup.sh >> /var/log/pg_backup.log 2>&1Tier 2: Off-site & encrypted with restic
Local dumps save you from operator error, not from a hardware or site-wide disaster. restic pushes the dumps encrypted to a remote target (S3-compatible bucket, another server, external drive) and deduplicates along the way, so you keep many versions for little storage:
export RESTIC_REPOSITORY="s3:https://s3.example.com/my-backup-bucket"
export RESTIC_PASSWORD_FILE="/etc/restic/password"
# Bucket credentials come from the environment / a secrets file,
# never hardcode them in the script.
restic backup /srv/backups
# Retention: 7 daily, 5 weekly, 12 monthly – the rest is deleted
restic forget \
--keep-daily 7 --keep-weekly 5 --keep-monthly 12 \
--prunerestic always encrypts the repository. But the password now stands between you and your data: lose it and the backup is worthless. Store it separately from the backup target – e.g. in your password manager.
Tier 3: WAL archiving for point-in-time recovery
This is where it gets interesting. With Write-Ahead-Log (WAL) archiving you back up not just snapshots but every single transaction. That lets you roll back to any point in time between two base backups – for example „one minute before the faulty migration script ran“. In postgresql.conf (or as command args in the container):
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /wal_archive/%f && cp %p /wal_archive/%f'
archive_timeout = 300 # write a WAL segment at least every 5 minPlus a regular base backup as the starting point – pg_basebackup pulls a consistent state of the entire cluster:
docker exec -t db \
pg_basebackup -U app -D - -Ft -X fetch \
| gzip > /srv/backups/base_$(date +%F).tar.gzRestore logic: unpack the base backup, set wal_archive as the restore_command, create a recovery.signal – on startup Postgres then replays the WALs up to the desired recovery_target_time. The exact procedure is in the next section.
WAL archiving pays off when data loss measured in minutes hurts. For a blog or a knowledge base, tiers 1+2 are often plenty. Weigh it honestly instead of setting up everything on principle – PITR also has to be operated and tested.
Restore: the part everyone skips
The most common restore is the simplest: replay a logical dump into a fresh database. First create an empty DB, then read the dump in:
docker exec -i db psql -U app -c 'CREATE DATABASE appdb_restore;'
gunzip -c /srv/backups/appdb_2026-06-11_0230.sql.gz \
| docker exec -i db psql -U app -d appdb_restoreImportant: restore into a new DB, don't steamroll the running one. That way you can check the restored state first and only switch over afterwards. Only once the restore path has run cleanly once do you know your dump was actually complete.
The restore test as a cron job
This is the step that turns „I have backups“ into „I have a backup I trust“. A throwaway container pulls the latest dump, replays it and runs a sanity check on whether there is any data in it at all. If the test fails, it should be loud:
#!/usr/bin/env bash
set -euo pipefail
LATEST=$(ls -t /srv/backups/appdb_*.sql.gz | head -1)
# Throwaway Postgres, isolated from the production container
docker run --rm -d --name pg_verify \
-e POSTGRES_PASSWORD=verify postgres:16-alpine
sleep 10
docker exec -i pg_verify psql -U postgres -c 'CREATE DATABASE verify;'
gunzip -c "$LATEST" | docker exec -i pg_verify psql -U postgres -d verify
# Sanity check: does the most important table have rows?
ROWS=$(docker exec -t pg_verify psql -U postgres -d verify -tAc \
'SELECT count(*) FROM auth_user;')
docker stop pg_verify
if [ "${ROWS//[[:space:]]/}" -gt 0 ]; then
echo "Restore test OK ($ROWS rows in auth_user)"
else
echo "Restore test FAILED – check the backup!" >&2
exit 1
fiRun this script weekly via cron, pipe the result into your monitoring – and you'll learn about a broken backup before you need it, not in the middle of an emergency.
Monitoring: do I know when it breaks?
A backup job that fails silently is more dangerous than none at all – because it lulls you into a false sense of security. The cheapest solution is a dead man's switch: the script pings a healthcheck service at the end; if the ping fails to arrive, the service raises an alarm. One line at the end of the script is enough:
# ping only on success (at the end of the backup script; set -e aborts earlier on failure)
curl -fsS -m 10 --retry 3 https://hc.example.com/ping/YOUR-CHECK-UUID > /dev/nullWhat I left out
- Streaming replication / hot standby: High availability is a different problem than backup. For a homelab, a second live instance is overkill – and it doesn't replace backups anyway (a
DROP TABLEreplicates right along). - pgBackRest / barman: Powerful tools with built-in PITR and retention. If you run several clusters, take a look. For a single app I found the pg_dump + restic combo more transparent and easier to debug.
- Backing up the secrets: The best DB backup is useless if
.env, the restic password and the compose files aren't backed up too (separately!). That deserves its own honest article.
Conclusion
Three tiers, cleanly staggered: pg_dump for everyday use, restic for off-site and encrypted, WAL archiving when minutes hurt. The real win, though, isn't in the backup but in the automated restore test – it's the difference between hope and certainty. In the next article I'll tackle securing the secrets and volumes, so that not just the database but the whole setup comes back reproducibly.
Ad · Affiliate link – if you buy through it, I may earn a commission. It doesn’t change the price for you.