Skip to content

Runbook: Backup, Restore and Restore Drills

Remediation of AGG28-04 (Critical): production PostgreSQL ran with archive_mode = off and no dumps, MinIO had no versioning, and all data sat in two docker volumes on one host. On 2026-08-04 a device purge deleted ~114 500 rows that could not be recovered.

This runbook covers what is protected, how it works, the one-time production rollout, the restore drill, and how to recover data.

Guarantees

Data Mechanism Recovery point (RPO) Kept off-host
PostgreSQL, any point in time WAL archiving (archive_timeout 300 s) plus a weekly pg_basebackup 5 min on the API host; about 10 min off-host (5-minute pull) 5 weekly base backups, so roughly 5 weeks of point-in-time recovery
PostgreSQL, logical nightly pg_dump -Fc with exact per-table row counts from the same snapshot 24 h 30 daily, 12 weekly, 12 monthly
MinIO objects bucket versioning: a delete or overwrite keeps the old version for 30 days 0 (versions are kept on the API host) nightly copy of objects changed in the last 72 h and a weekly exact sync; ZFS snapshots keep what the sync removes
Backups themselves SHA256SUMS on every backup, verified after each pull; weekly dump drill and monthly PITR drill — —

The API host has no credentials for the off-host target. The target pulls over SSH with a key that the API host restricts to read-only rrsync of the backup directory and one port-forward to MinIO. A bug or a compromise on the API host cannot delete an off-host copy.

Components

Where What File
API host, postgres container archive_command: zstd-compress each WAL segment into /pgbackup/wal. It refuses to overwrite a file that has different content. scripts/backup/pg-archive-wal.sh
API host, systemd timers pg-backup.sh dump daily 17:30 UTC; base Sunday 19:30 UTC; status every 15 min; local retention scripts/backup/pg-backup.sh, scripts/backup/systemd/
API host, minio-init bucket versioning, a 30-day non-current-version expiry rule, and a read-only xylolabs-backup-read user scripts/init-minio.sh
API host, deploy creates /var/backups/xylolabs-api/pg (owner 999, setgid group ubuntu, mode 2750) before compose up; reports the archiver state afterwards scripts/deploy-remote.sh
Off-host target (TrueNAS VM 100, pool/backup/xylolabs-api) pulls WAL, backups and status; verifies checksums; applies retention; mirrors MinIO; runs the freshness check scripts/backup/offsite-pull.sh
Off-host target restores into a throwaway container and verifies the result scripts/backup/restore-drill.sh, scripts/backup/pg-restore-wal.sh

Host directory layout (PG_BACKUP_HOST_DIR, default /var/backups/xylolabs-api/pg, mounted at /pgbackup):

Path Content
wal/ <segment>.zst, <segment>.<offset>.backup.zst, <tli>.history.zst
base/<ts>/ base.tar.zst, pg_wal.tar, backup_manifest, backup.meta (start_wal), pg_basebackup.log, SHA256SUMS
dump/<ts>/ db.dump, globals.sql, manifest.tsv (exact row counts), migrations.txt, backup.meta, SHA256SUMS
status/ host.status (archiver counters, pg_wal size, free disk), dump.status, base.status

Every backup is written under .inprogress-<ts>/ and renamed once it is complete, so a reader never sees a partial backup under its final name.

Local retention on the API host: 3 dumps, 2 base backups, and WAL back to the oldest kept base. A WAL file is never deleted locally until it is at least 14 days old, in case the puller was down. Expected local footprint is about 45 GB; the host had 238 GB free on 2026-10-06. The host writes about 6 GB of raw WAL per day (measured: 895 GB over 146 days), and zstd shrinks that substantially.

Production rollout (one time)

Every step here is a production action. Get the user's explicit go-ahead first; the agent rules in CLAUDE.md and the nas-ops safety rules both apply. Steps 1, 2 and 6 change the API host; steps 5 and 6 are security-sensitive (.env, authorized_keys).

Expected downtime: one PostgreSQL restart in step 2. archive_mode cannot change without a restart. During the restart the app returns errors for database-backed requests; devices retry and defer, as they do for any outage.

Measured on 2026-10-07: about 100 s, not 5–30 s. The PostgreSQL restart itself was quick, but compose recreated postgres, minio and app together (the image pins changed the postgres and minio references too). It stopped the old postgres and minio first, then waited for the old app container to stop: that took 88 of its 90 s stop_grace_period. Only then did it start the new containers. The API answered nothing from 11:08:57 to 11:10:36 UTC, and MinIO was down for about 94 s. Any future deploy that recreates app waits for that same grace period.

Fixed 2026-10-09. The 88 s was an open dashboard SSE stream holding the app's graceful drain until the SIGKILL. SSE streams now end on shutdown, in-flight HTTP gets at most 10 s, and the app exits in under a second. deploy-remote.sh now converges postgres and minio first (and only when their config changed), so the database is never down while compose waits for the app. See Deployment Guide › Deploy or redeploy.

Status: rolled out on 2026-10-07. Steps 1–9 and 11 are done. The first full MinIO mirror started at 12:32 UTC. Step 10 (the Grafana alert) is still open.

  1. Pre-checks (read-only). bash ssh -i ~/.ssh/xylolabs-api.pem ubuntu@api.xylolabs.com \ 'df -h /; cd /opt/xylolabs-api && docker compose ps'
  2. Backup user, then deploy. Append two lines to /opt/xylolabs-api/.env (choose the values, and note them for step 5): MINIO_BACKUP_ACCESS_KEY=xylolabs-backup-reader MINIO_BACKUP_SECRET_KEY=<40 random characters> Then run bash scripts/deploy.sh from the repo. deploy-remote.sh creates the backup directory, compose up recreates postgres with archiving on, and minio-init enables versioning, adds the lifecycle rule and creates the read-only user. Verify: bash ssh -i ~/.ssh/xylolabs-api.pem ubuntu@api.xylolabs.com ' cd /opt/xylolabs-api docker compose exec -T postgres psql -U xylolabs -d xylolabs -Atc "show archive_mode" -c "select * from pg_stat_archiver" docker compose logs minio-init | grep -E "PROTECTION-WARNING|versioning|lifecycle" || true bash scripts/backup/pg-backup.sh status; cat /var/backups/xylolabs-api/pg/status/host.status ls /var/backups/xylolabs-api/pg/wal | tail -3' Pass when archive_mode is on, failed_count is 0, WAL files appear within about 5 minutes, and minio-init printed no PROTECTION-WARNING.
  3. Timers. bash ssh -i ~/.ssh/xylolabs-api.pem ubuntu@api.xylolabs.com \ 'bash /opt/xylolabs-api/scripts/backup/install-host.sh'
  4. First backups, now rather than at the first timer. I/O-heavy; run outside peak hours. The base backup reads the whole 18 GB cluster. bash ssh -i ~/.ssh/xylolabs-api.pem ubuntu@api.xylolabs.com ' sudo systemctl start xylolabs-pg-base.service xylolabs-pg-dump.service ls /var/backups/xylolabs-api/pg/base /var/backups/xylolabs-api/pg/dump'
  5. Off-host target (TrueNAS, as hletrd). bash N="ssh -i ~/.ssh/hletrd-nas.pem hletrd@172.30.60.100" # Datasets through the middleware (not raw `zfs create`), so TrueNAS knows them. $N 'midclt call pool.dataset.create "{\"name\": \"pool/backup\"}" >/dev/null; midclt call pool.dataset.create "{\"name\": \"pool/backup/xylolabs-api\", \"compression\": \"ZSTD\", \"recordsize\": \"1M\", \"atime\": \"OFF\"}" && sudo chown hletrd:hletrd /mnt/pool/backup/xylolabs-api && mkdir -p /mnt/pool/backup/xylolabs-api/{.ssh,log} /mnt/pool/scripts/xylolabs-backup && ssh-keygen -t ed25519 -N "" -C xylolabs-backup-pull@truenas \ -f /mnt/pool/backup/xylolabs-api/.ssh/xylolabs-backup-pull && sudo docker pull postgres:18 && sudo docker pull rclone/rclone:1.75.1' scp -i ~/.ssh/hletrd-nas.pem scripts/backup/{lib.sh,offsite-pull.sh,restore-drill.sh,pg-restore-wal.sh} \ hletrd@172.30.60.100:/mnt/pool/scripts/xylolabs-backup/ Create /mnt/pool/backup/xylolabs-api/offsite.env (mode 600) from scripts/backup/nas/offsite.env.example, filling in the MinIO pair from step 2. Add a ZFS periodic snapshot task on pool/backup/xylolabs-api (daily, 35-day lifetime) in the TrueNAS UI. This is what keeps objects that the weekly exact sync removes, and it guards against bugs in the puller.
  6. Authorize the pull key on the API host. Append the line from scripts/backup/nas/authorized_keys.example, with the public key from step 5, to ~ubuntu/.ssh/authorized_keys. Pin the host key on the target (compare it with ssh-keygen -lf /etc/ssh/ssh_host_ed25519_key.pub on the API host), then confirm that the key can only rsync: bash $N 'ssh-keyscan -t ed25519 api.xylolabs.com >> ~/.ssh/known_hosts K=/mnt/pool/backup/xylolabs-api/.ssh/xylolabs-backup-pull rsync --list-only -e "ssh -i $K" ubuntu@api.xylolabs.com:wal/ | tail -2 # works ssh -i $K ubuntu@api.xylolabs.com id || echo "shell refused (expected)"'
  7. First pull and first full mirror. bash $N 'B=/mnt/pool/scripts/xylolabs-backup C=/mnt/pool/backup/xylolabs-api/offsite.env $B/offsite-pull.sh --config $C wal && $B/offsite-pull.sh --config $C pull' # 80 GiB / 8.4 M objects: hours, about 80 GB of EC2 egress. Detach it: $N 'nohup /mnt/pool/scripts/xylolabs-backup/offsite-pull.sh --config /mnt/pool/backup/xylolabs-api/offsite.env \ minio --full > /mnt/pool/backup/xylolabs-api/log/minio-initial.log 2>&1 &'
  8. Schedule. Add scripts/backup/nas/crontab.example to hletrd's crontab (crontab -e). TrueNAS middleware jobs must run as hletrd, never as root.
  9. Restore drill: the AGG28-04 exit criterion. See the next section. Record both result files in the platform repo's review aggregate.
  10. Alerting. node-exporter on TrueNAS (:9101, textfile dir /var/lib/prometheus/node-exporter) exports xylolabs_backup_* after the first check. Add a Grafana alert on any of: xylolabs_backup_ok == 0, xylolabs_backup_archiver_failing == 1, or time() - xylolabs_backup_check_timestamp_seconds > 7200.
  11. Bookkeeping. In nas-ops, update scheduled-tasks.md, zfs-datasets.md, change-notes.md and CHANGELOG.md, and copy the scripts into its scripts/. In xylolabs-platform, update the AGG28-04 note in AGENTS.md: the exit criterion is met once steps 2, 7 and 9 have passed.

Rollback: install-host.sh --uninstall removes the timers. scripts/rollback.sh to a pre-backup revision restores the old compose file (archiving off) on the next up. Backup files are never deleted by either.

Restore drill

Runs on the target. The drill never touches production. It restores into a throwaway container and volume, then removes both (--keep leaves them for inspection).

B=/mnt/pool/scripts/xylolabs-backup C=/mnt/pool/backup/xylolabs-api/offsite.env
$B/restore-drill.sh --config $C dump                 # newest verified dump
$B/restore-drill.sh --config $C pitr                 # newest base + all WAL
$B/restore-drill.sh --config $C pitr --target-time '2026-10-06 03:00:00'   # UTC

Pass criteria, written to drill/results/<ts>-<mode>.result (status=PASS|FAIL):

  • dump: pg_restore --exit-on-error succeeds, and every table's restored row count equals manifest.tsv. Those counts were taken inside the dump's own exported snapshot, so they match exactly. The migration list also equals migrations.txt.
  • pitr: recovery completes and promotes, every table and migration of the newest dump exists, and without a target the last replayed transaction is within MAX_PITR_LAG_S (1 h) of the newest archived WAL. With a target, recovery stops at or before it. The result also lists per-table counts and duration_seconds, which is the measured restore time (RTO) to record.

The weekly dump drill and monthly PITR drill run from cron. check alerts if either has not passed within 8 or 32 days. Run a targeted PITR by hand at least once a quarter.

Recovering data

Rows deleted or corrupted by an application action (the 2026-08-04 case). Do not restore over production. Bring up the state just before the incident on the target, export the affected rows, and load them back:

$B/restore-drill.sh --config $C pitr --target-time '2026-08-04 05:59:00' --keep
# The drill prints the container name (xylo-drill-pitr-...).
sudo docker exec <name> pg_dump -U xylolabs -d xylolabs --data-only \
  -t 'public.metadata_streams' > /mnt/pool/backup/xylolabs-api/restore-<incident>.sql
# Review the file, copy it to the API host, and load it inside a transaction.
sudo docker rm -f <name>; sudo docker volume rm <name>

Full database loss. Restore the newest base backup and the WAL into a new volume on the API host, then point compose at it. Rough steps: stop app; stop postgres; leave the old pgdata18 volume in place; rsync base/<ts>/ and wal/ from the target to /var/backups/xylolabs-api/restore/; extract into a new volume and add recovery.signal and restore_command exactly as restore-drill.sh does; start it with the image entrypoint bypassed (--entrypoint postgres -D ...) until it promotes; then switch the compose volume. Rehearse this as part of the quarterly drill before relying on it. The measured PITR duration_seconds plus about 18 GB of transfer is the RTO.

A deleted MinIO object (within 30 days).

docker compose exec -T minio sh -c 'mc alias set l http://127.0.0.1:9000 "$MINIO_ROOT_USER" "$MINIO_ROOT_PASSWORD" >/dev/null
  mc ls --versions l/xylolabs-audio/<key>
  mc cp --vid <version-id> l/xylolabs-audio/<key> l/xylolabs-audio/<key>'

After 30 days, copy the object from the target's mirror (/mnt/pool/backup/xylolabs-api/minio/xylolabs-audio/<key>) or from a ZFS snapshot under .zfs/snapshot/.

Troubleshooting

  • xylolabs_backup_archiver_failing 1, or WARNING: WAL archiving is FAILING from pg-backup.sh status. PostgreSQL keeps every unarchived segment in pg_wal, so nothing is lost, but the disk fills. Check the postgres logs (docker compose logs postgres | grep xylo-archive-wal) and the ownership of /var/backups/xylolabs-api/pg/wal (must be 999, mode 2750). Re-run pg-backup.sh prepare-dirs. Archiving resumes on its own.
  • refusing to overwrite in the postgres log. A second cluster is writing into the same archive, which happens if a restored copy was started with archiving on. Stop that cluster. Never delete archive files to "unstick" this.
  • A base backup failed checksum verification on the target. It is retried on the next pull and is never used while unverified. Unverified copies older than 7 days are dropped.
  • Verify a base backup with PostgreSQL's own tool: pg_verifybackup -n -i pg_basebackup.log -i backup.meta -i SHA256SUMS -i .verified <dir>.

Known limits

  • MinIO object lock is not enabled. MinIO can only turn it on when a bucket is created. Versioning plus the off-host mirror plus ZFS snapshots cover the same failure modes. Object lock would need a new bucket and a migration.
  • The minio/minio and minio/mc images can no longer be pulled. docker-compose.yml pins the exact images production runs, by digest. Their only copies are the API host's image store and the off-host archive on the NAS. Never prune them. The restore and source-rebuild procedures are in RUNBOOK-IMAGES.md. The e2e test runs the same pinned images.
  • The target is a single site (the TrueNAS raidz2 pool). A second copy (cloud object storage, or a second site) would complete 3-2-1 and is a follow-up.
  • The purge endpoint still hard-deletes. AGG28-04 remediation item 5 (refuse a purge unless a recent verified backup exists, and echo a confirmation token) is separate application work.

Tests

  • bats tests/backup/lib.bats: retention selection, WAL naming and pruning.
  • tests/backup/e2e.sh: the production docker-compose.yml services plus the real scripts end to end, covering archiving, base backup, dump, pull with tamper detection, the dump drill, PITR to the end and to a target, archive overwrite refusal, MinIO versioning, lifecycle and the read-only user, the mirror, retention, and the freshness check. Needs only Docker.