Backup runbook — Postgres/TimescaleDB → MinIO

Status 2026-08-13: DEPLOYED AND VERIFIED. ahu-minio had held 0 objects since
2026-07-13; it now holds real backups of the audit store.

Deployed to the GPU host (192.168.83.20), because that is where the DBs actually run —
observatory and ahu_ocr_staging are not on ahu-gov-01/ahu-int-01 yet. Move the job
with them.

What this protects

Both currently live on the GPU host (192.168.83.20), not on ahu-gov-01/ahu-int-01.
The backup job runs where the DBs are; move it when they move.

Design decisions

restic, not raw pg_dump to S3. Client-side encryption means the audit dump is unreadable
on the backup host even to whoever holds MinIO's root credentials. Plus dedup (measured
10.6× compression on representative data), retention, and check --read-data integrity.

Logical dumps, not WAL/PITR. archive_mode requires restarting the crown-jewels DB.
Zero backups is today's problem; PITR is the upgrade once the DB moves to ahu-gov-01 and a
restart window exists. Be honest about RPO: up to 24h, not continuous.

Staged-then-validated, not streamed. See the comment block in pgbackup.sh — streaming
pg_dump | restic commits a snapshot before the pipeline's exit status is known, so a failed
dump lands a 0-byte snapshot under a real tag. That was observed in testing, not theorised.
The script now dumps to a staging file and refuses to store it unless it passes a size floor
and the PGDMP magic check. Staging files are chmod 700 and removed on every exit path
(they are plaintext NIK while they exist).

Deploy

1. TLS on MinIO (do first — it changes the endpoint scheme)

On ahu-backup-01:

cd ~/ahu-minio
./gen_minio_tls.sh                 # private CA + IP-SAN leaf into ./tls
docker compose -f compose.minio.yaml up -d
curl -fsS --cacert tls/ca.crt https://192.168.82.126:9000/minio/health/live

Safe to do now: the store has 0 objects and no clients, so nothing breaks. Distribute
tls/ca.crt only to backup clients — never ca.key or private.key.

Create the bucket and a scoped (non-root) service account:

mc alias set b https://192.168.82.126:9000 "$ROOT_USER" "$ROOT_PW"   # CA at ~/.mc/certs/CAs/
mc mb b/ahu-backups
mc admin user svcacct add b "$ROOT_USER"     # use THIS key pair in pgbackup.env, not root

2. Backup job (on the DB host)

mkdir -p ~/ahu-pgbackup && cd ~/ahu-pgbackup
# copy pgbackup.sh, render_pgbackup_env.sh, and ahu-backup-01's tls/ca.crt (as ./ca.crt)
# put ROLE_ID/SECRET_ID in pgbackup-approle.env (chmod 600)
./render_pgbackup_env.sh
./pgbackup.sh                      # first run initialises the repo
sudo cp pgbackup.service pgbackup.timer /etc/systemd/system/
sudo systemctl daemon-reload && sudo systemctl enable --now pgbackup.timer

OpenBao needs secret/backup/pgbackup with: restic_repository, restic_password,
minio_access_key, minio_secret_key, plus a pgbackup AppRole bound to a read-only policy.

⚠️ Escrow restic_password in OpenBao before the first run. Losing it makes every
snapshot permanently unrecoverable — there is no recovery path, by design.

Restore

⛔ TimescaleDB: pre_restore/post_restore are MANDATORY

A plain pg_restore into a TimescaleDB database silently loses hypertable rows.
Measured on the real 2026-08-13 backup: COPY failed for table "_hyper_1_10_chunk",
and the restored ai_calls was short by exactly 293 rows — the full contents of that
one chunk. pg_restore exits 0 with only an "errors ignored" warning, restic reports a
healthy snapshot, and restic check --read-data passes. Every signal says fine while
audit rows go missing. The dump is not at fault; the restore procedure is.

With the two calls below: 0 errors, and all three hypertables matched live exactly.

export RESTIC_REPOSITORY=... RESTIC_PASSWORD=... AWS_ACCESS_KEY_ID=... AWS_SECRET_ACCESS_KEY=...
# NOTE: no -i on docker run unless you are feeding it stdin; -i will eat a piped heredoc.
R="docker run --rm --network host -e RESTIC_REPOSITORY -e RESTIC_PASSWORD \
   -e AWS_ACCESS_KEY_ID -e AWS_SECRET_ACCESS_KEY -v $PWD/ca.crt:/ca.crt:ro restic/restic:0.17.3"

$R snapshots --cacert /ca.crt                        # pick a snapshot for the tag you want
$R dump <SNAPSHOT_ID> /observatory.dump --cacert /ca.crt > restored.dump

docker cp restored.dump <db-container>:/tmp/r.dump
docker exec <db-container> psql -U observatory -d postgres -c 'CREATE DATABASE restored;'

# 1. suspend the extension's hooks so chunk data can load into the raw tables
docker exec <db-container> psql -U observatory -d restored -c 'SELECT timescaledb_pre_restore();'
# 2. restore
docker exec <db-container> pg_restore -U observatory -d restored /tmp/r.dump
# 3. hand control back to the extension
docker exec <db-container> psql -U observatory -d restored -c 'SELECT timescaledb_post_restore();'

For a plain Postgres target (ahu_ocr_staging) steps 1 and 3 are unnecessary.

Verified against the real 2026-08-13 production backup:

Compare with a cut-off bound (WHERE ts <= <max ts in the restored set>), otherwise live
traffic arriving after the snapshot looks like restore loss and hides the real thing.

Restore into a scratch DB first, never over the live one. Rehearse quarterly — an
unrehearsed backup is a hypothesis, and the point of this document is to stop having those.

Verify it is actually working

systemctl list-timers pgbackup.timer
journalctl -u pgbackup.service -n 50
$R snapshots --cacert /ca.crt        # expect a fresh snapshot per tag, per day
$R check --read-data --cacert /ca.crt # full integrity read; run monthly

MinIO exposes minio_cluster_usage_object_total on /minio/v2/metrics/cluster
(MINIO_PROMETHEUS_AUTH_TYPE=public). Alert on it being 0, and on the timer's last run
being stale — those two checks are what would have caught the current situation on day one.

Known gaps

1. RPO is 24h. Upgrade to pgBackRest + WAL archiving when the DB moves and can restart.
2. No off-site copy. ahu-backup-01 is one host in one rack; a site loss takes the
   backups with it. restic copy to a second repo is the cheap fix.
3. Backups are not yet monitored. Wire the two checks above into Keep before calling this
   done — see [EWS notifications memory]; Keep still has 0 providers, so pages go nowhere.
4. Single-drive MinIO, no parity. Acceptable for copies, not for primary data.