Database backups that actually restore: the pgdata I lost

I deleted the Postgres data directory. It lived in a bind mount on the host, there was no named volume protecting it, and there was no backup anywhere else. The data was simply gone. My backup plan, if I am honest, was hope.

This is part 9 of a series on hardening a solo-built SaaS in production. The setup and the full list of weak spots are in the pillar article. This one is the incident that taught me a backup you have never restored is not a backup. It is a hypothesis.

How the data was stored, and why that was fragile

Persistent data sat in host bind mounts:

db:
  volumes:
    - ./postgres/data:/var/lib/postgresql/data
storage:
  volumes:
    - ./minio/data:/data

Bind mounts are fine in themselves. The fragility was everything around them: no named volume, no snapshot, and nothing copied off the machine. So the data had exactly one copy, on one disk, on one VPS. Any single mistake (a wrong rm, a bad docker compose down -v, a docker system prune --volumes, which I warned about in the disk-space article) meant permanent loss. That is not a backup problem yet, it is a “single copy of everything” problem, and it is the precondition for the outage.

What a real backup looks like on one VPS

You do not need managed RDS or a backup product. On a single VPS with a small budget, three layers cover you.

1. Logical backups with pg_dump

The simplest, most portable backup is a logical dump. It produces a file you can restore into any Postgres, and it is perfect for small to medium databases:

# nightly, compressed, custom format (restorable with pg_restore)
pg_dump -Fc -d "$DATABASE_URL" -f "/backups/db-$(date +%F).dump"

Schedule it (cron on the host, or a small sidecar container), keep a few days locally, and move on to the part that actually protects you.

2. Ship it off the box with restic

A backup that lives on the same disk as the database dies with that disk. The backup has to leave the machine. restic is ideal here: it is a single binary, it encrypts and deduplicates, and it talks to cheap object storage (S3, Backblaze B2, any S3-compatible bucket):

restic backup /backups /var/lib/minio/data     # dumps + object storage
restic forget --keep-daily 7 --keep-weekly 4 --prune

The forget --prune line is not optional. It enforces a bounded retention window, which keeps cost down and, as the GDPR retention guide explains, is also what lets deleted user data actually age out of your backups instead of living there forever.

That covers the classic 3-2-1 rule: at least three copies, on two kinds of media, with one off-site. The restic repo in external object storage is your off-site copy.

3. Point-in-time recovery, when a day of loss is too much

pg_dump gives you last night’s state. If losing a day of data is unacceptable, step up to physical backups: pg_basebackup plus continuous WAL archiving gives you point-in-time recovery, the ability to restore to any moment, not just the last snapshot. It is more moving parts, so add it only when your recovery point objective demands it. For many small products, a nightly dump shipped off-site is genuinely enough.

The step everyone skips: test the restore

Here is the part that turns a hopeful backup into a real one. A backup you have never restored is a hypothesis. Backups fail silently in a hundred ways: a dump that errored halfway, a cron that stopped running months ago, an encryption key nobody saved, a file that is there but corrupt. You find out which one applies at the worst possible moment, unless you test.

So automate a restore. Periodically, pull the latest backup, restore it into a throwaway container, and run a sanity check:

# restore the newest dump into a scratch Postgres and verify it is real
pg_restore -d "$SCRATCH_DB" "$(ls -t /backups/*.dump | head -1)"
psql -d "$SCRATCH_DB" -c "select count(*) from users;"   # sane number?

If that runs green on a schedule, you have a backup. If you have never run it, you have a folder of files you are hoping are backups. The difference only becomes visible during a disaster, which is the one time you cannot afford to discover it.

Close the loop: named volumes and a deletion guard

Backups make the underlying storage non-fatal, but fix the storage too. Prefer named volumes over ad-hoc bind mounts: they are managed by Docker, harder to rm by accident, and clearly separated from your project directory. And treat the destructive commands as loaded weapons: never run docker compose down -v or docker system prune --volumes on a box you have not audited, because both will happily delete the data you are relying on.

The layered answer is: named volumes so casual mistakes do not wipe data, off-site restic backups so a disk or a machine loss is survivable, and a tested restore so you actually know the backups work.

The takeaway

I lost a database because it had one copy, in a bind mount, with no backup behind it and no way to get it back. The fix is not exotic: a nightly pg_dump, restic shipping it off the machine with a bounded retention, and a scheduled restore test that proves the whole chain works. Do the restore test especially, because it is the one step that separates people who have backups from people who have hope. The series overview tracks the rest.