Write a nightly pg_dump into the postgres data volume so backups always contain a consistent snapshot

[jonfroehlich]

Problem

The production database lives in the db container's persistent named volume (db-data → /var/lib/postgresql/data, see docker-compose.yml). Backups of that volume happen at the filesystem/volume level — and a filesystem-level copy of a live postgres data directory is not guaranteed to be transaction-consistent: restoring one can behave like recovering from a hard power-off, or in the worst case fail to start. Our hosting infrastructure admins recommended the standard mitigation: have the database write its own consistent dump on a schedule, into the same persistent volume, so every volume-level backup automatically carries a known-good restore point.

Everything else on the site is already safe in this respect — media/ is plain files, and the schema/code are in git — the database contents are the only piece that needs this.

Design

Two tiers, which is the point of the whole exercise:

Tier	Cadence	Guarantee
CSE IT's ZFS snapshot of the raw volume	hourly	probably restorable — Postgres crash recovery handles the power-cut case
Our pg_dump inside that volume	daily	guaranteed consistent restore point

Note that the dump cadence is the guaranteed worst-case RPO regardless of how often IT snapshots: every hourly snapshot between two dumps contains the same dump, so more snapshots give more copies of one restore point, not more restore points.

Components

1. db-backup sidecar service in docker-compose.yml. Uses the same ${POSTGRES_IMAGE} as db so the pg_dump binary version always matches the server, runs as user: postgres, and overrides entrypoint so the postgres image's own entrypoint never touches PGDATA.

2. scripts/pg_backup.sh — a single-pass script (no internal loop): dump today's file if it doesn't exist yet, prune past retention, write status. Compose owns the scheduling loop. This split matters operationally: a deploy only recreates a container whose config changed, so if the loop lived inside the bind-mounted script, script edits would never take effect on a running server. Re-invoking the script each pass means logic changes deploy normally.

3. Dump target /var/lib/postgresql/data/pg_backups/makeability-YYYY-MM-DD.sql.gz — inside the data volume, exactly as CSE IT suggested, so it is covered by the existing volume backup with no new volume for them to enroll. Plain pg_dump | gzip rather than -Fc: restore is gunzip -c … | psql, no pg_restore needed, and the file is greppable.

4. Retention 14 days, tunable by env var. That is deliberately short because IT keeps volume snapshots for a year — a snapshot from six months ago contains that day's dump — so in-volume retention only needs to cover the recent window.

5. Status surface. The sidecar writes a status JSON (on failure too, with exit code and a stderr tail) to a small volume shared read-only with the website container. Django surfaces it as:

   • a superuser-gated callout on the admin dashboard, shown only when stale or failed (an always-green banner becomes invisible chrome),
   • a detail panel on the Data Health dashboard: last success, age, size, retained count, last error,
   • last_backup_at / backup_age_hours / backup_ok on /version.json for scripted checks.

   This is not optional polish. The dumps land in a Docker named volume on a host the maintainer has no shell on, and PGDATA is mode 700/uid 999 while the website container runs as uid 48 — so without this, there is no way to observe whether backups are happening at all.

Decisions, and what changed from the original proposal

• Sidecar, not host cron (the original text leaned host cron). The maintainer has no shell on grabthar or docker-test2, so host cron would mean a permanent CSE IT dependency for every schedule or retention change, and no way to see whether it was still running. A compose service ships on a normal git push and lands on -test first automatically.
• No pre-migration / per-deploy dump, considered and rejected. It would only beat the nightly dump when a destructive deploy lands on the same day as meaningful content entry; on a site where edits arrive in bursts, the marginal data recovered is usually zero rows. It also costs a second dump code path (postgresql-client in the website image, dumping over TCP) and DJANGO_ENV gating so it wouldn't fire on every push to -test. The real control for destructive one-shots is what the repo already does: make them idempotent and comment them out once they've run (see Re-standardize legacy talk/poster/pub filenames that were never renamed #1401, Store original uploaded filename and show it (admin-only) for talks/posters/publications #1391).
• No pg_dumpall --globals-only. The only role is admin, recreated from POSTGRES_USER/POSTGRES_PASSWORD on any fresh container.

Security

The dump contains Person.email, which is deliberately excluded from the public API. It must never land under media/, static/, or any other web-served path. The status JSON is safe to surface in the admin; the dumps stay in the volume.

Acceptance

• A dated, gzipped dump appears in the volume daily and old ones are pruned.
• Status is visible on the admin dashboard and /version.json, and correctly reports a failed dump, not just a missing one.
• A restore from a dump into a fresh volume is tested once and written up in docs/BACKUPS.md — including that initdb refuses a non-empty directory, so the dump has to be copied out before the DB can initialize.
• CSE IT confirms a pg_backups/*.sql.gz file is visible inside a volume snapshot.

Follow-up, deliberately not in scope

Because there is no shell on grabthar, there is also no way to take an ad-hoc dump before something risky. If that ever bites, the cheap fix is a "Back up now" button on the Data Health page writing a request marker the sidecar picks up on its next pass. Not worth building on speculation.

🤖 Generated with Claude Code — Opus 5 (1M context), claude-opus-5[1m]