Runbook: PostgreSQL Backups

Owner: platform team Last reviewed: 2026-09-14

This runbook covers backup and restore for every PostgreSQL database the Oshun stack runs against (16 per cluster as of writing: oshun_dev, yemaya, lilith, isis, iris, sophia, hathor, bellona, calliope, tara, maat, nisaba, shakti, cybele, kalika, lakshmi). Use this when:

• You need a one-off snapshot before a risky migration or schema change.
• You're standing up a fresh staging cluster and want to seed it.
• A nightly job alerted that a backup failed and you need to verify manually.
• You're restoring after an incident.

Scripts#

• scripts/db-backup.sh — dumps every database the configured user can see as a gzipped pg_dump custom-format archive (*.dump.gz), plus a manifest.txt with per-file SHA-256.
• scripts/db-restore.sh — verifies the manifest, then restores one or more databases from a backup directory.

Both scripts are POSIX-bash and require pg_dump, psql, pg_restore, gzip, and sha256sum (shasum on macOS) on PATH.

One-off snapshot (local dev)#

# Backs up all databases in the oshun-dev-postgres container to
# ./backups/postgres/<UTC-timestamp>/
scripts/db-backup.sh

# Just one database
scripts/db-backup.sh --databases yemaya

# With verification (re-reads each archive's TOC to catch corruption)
scripts/db-backup.sh --verify

The default credentials match docker/docker-compose.dev.yml (PG_HOST=localhost, PG_USER=oshun, PGPASSWORD=oshun_dev).

Restore#

# Restore everything in a backup directory, against the current cluster.
scripts/db-restore.sh ./backups/postgres/20260528T010203Z

# Restore a single database, dropping and recreating it first.
scripts/db-restore.sh ./backups/postgres/20260528T010203Z \
  --databases yemaya \
  --create

# Restore to a different cluster (e.g. seeding staging from a prod dump).
PGPASSWORD=$STAGING_PG_PASSWORD scripts/db-restore.sh \
  ./backups/postgres/20260528T010203Z \
  --target-host staging-db.internal \
  --target-user oshun_admin \
  --create

db-restore.sh refuses to start if manifest.txt is missing or any file checksum has drifted — this catches silent S3 download corruption.

Production cadence#

The expected production schedule is:

A sample cron entry that runs nightly at 02:15 UTC and uploads to S3:

15 2 * * *  /opt/oshun/scripts/db-backup.sh --verify --output /var/backups/oshun && \
            aws s3 sync /var/backups/oshun s3://oshun-backups/postgres/ \
              --storage-class STANDARD_IA --delete

The S3 upload is intentionally not part of db-backup.sh itself — different deploy environments use different object stores (S3, GCS, MinIO, B2), and we don't want the script to grow per-vendor branches.

Capability recovery objectives#

The V1 disaster class fixes RTO at four hours (14,400 seconds). RPO is owned by the capability, not inferred ad hoc from whichever substrate an operator happens to restore:

The 90,000-second PostgreSQL limit is the nightly dump plus one hour of schedule tolerance. A family spanning PostgreSQL and a directly captured filesystem uses that slower authoritative bound. Direct off-box captures run every 21,600 seconds. A restore is not successful because its processes answer health checks: the Task 13.6 acceptance drill requires an exact per-capability census, v1-to-v2 migration, payload integrity, retained-peer preservation, and the deletion replay described below.

Qdrant is deliberately absent from the backup set. PostgreSQL owns the durable personalization vectors; the configured embedding version and index schema own the metadata. After restoring PostgreSQL, rebuild and read back the empty Qdrant target with the command in v1-dr-search-index-loss.md. Do not restore a stale Qdrant volume as authority.

Deletions across restore#

A database snapshot taken before an account deletion contains both the old data and an old “not deleted” fence set. Therefore, restoring only that snapshot can resurrect the subject. V1 closes that gap with encrypted recovery-deletion receipts:

1. After the signed deletion fan-out succeeds, the BFF writes a create-only local receipt and synchronously creates the same receipt in a dedicated, Object-Lock-enabled S3-compatible bucket in another failure domain.
2. The customer deletion cannot become completed unless the remote write and COMPLIANCE-retention readback succeed.
3. On every boot—including a clean-host restore—the BFF reconciles the remote inventory into its local cache and replays every authenticated receipt through the same idempotent deletion fan-out before constructing the HTTP app.
4. A missing key, unreachable replica, tampered receipt, inventory mismatch, or failed replay aborts boot. Never bypass this gate to restore traffic.

Provision the remote bucket before enabling account deletion. Its credential needs ListBucket, GetObject, PutObject, and HeadObject, and must be denied object deletion, retention shortening, and bucket administration. It must not be the stack's own MinIO endpoint. Set OSHUN_RECOVERY_DELETION_REMOTE_RETENTION_DAYS to at least the longest backup retention plus ceil(OSHUN_OFFBOX_MAX_PG_AGE_SECONDS / 86400); if an older cold backup remains restorable, its deletion receipt and decryption key must remain recoverable for the same horizon.

Generate the 32-byte encryption key without printing it to a terminal and move it directly into the secret manager and separately controlled recovery escrow:

umask 077
openssl rand -base64 32 > /secure/oshun-recovery-deletion-key.b64

Do not place that file in the application tree, restic snapshot, backup receipt, or source control. Do not rotate it while any receipt under its key id protects a still-restorable backup; retire that backup horizon first.

When a nightly backup fails#

1. Read the cronjob's stderr (in CloudWatch or the job runner). The script prints ERROR: ... lines on the failure path; the most common ones are listed below.
2. cannot connect to postgres at host:port — credentials rotated or the cluster is unreachable. Verify psql -h ... -U ... works manually first.
3. pg_dump failed for <db> — usually a long-running query blocking pg_dump's ACCESS SHARE lock. Find the offending PID with SELECT pid, query FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '5 minutes', decide whether to kill it, then re-run the backup for just that database with --databases <name>.
4. verification failed for <db> — the archive on disk is corrupt. Do not delete it; copy it aside (cp -a $RUN_DIR $RUN_DIR.suspect) and re-run the backup. If verification fails twice in a row from the same source cluster, escalate — it's a disk or memory issue on the backup host, not a postgres issue.
5. manifest verification failed at restore time — the backup transited storage that mutated bytes (rare; usually a bad rsync, not S3 itself). Re-download from S3 and retry. If checksums still disagree, the backup is dead and you should fall back to the most recent backup whose checksums verify.

What is not covered here#

• PITR / WAL archiving — that's managed by the database vendor. This script is for portable, off-vendor logical dumps.
• Restoring a single table or a partial row range — write a one-off pg_restore --table=<name> invocation against the archive directly. The script does whole-database restores only because that's the failure mode worth automating; everything narrower is an interactive human task.