# 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) ```bash # Backs up all databases in the oshun-dev-postgres container to # ./backups/postgres// 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 ```bash # 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: | Cadence | Tool | Retention | Notes | | ------------------- | -------------------------------------------------------- | ---------------------------------------------- | ------------------------------------------------ | | Continuous WAL | Managed (RDS / Cloud SQL / Aurora) | 35 days PITR | provider-managed, set up out of band | | Nightly full | `scripts/db-backup.sh` from a small EC2 / k8s cronjob | 14 days locally, indefinite in S3 cold storage | uploaded to `s3://oshun-backups/postgres//` | | Weekly verification | `--verify` flag + a synthetic restore into a scratch RDS | n/a | catches "we have backups but they don't restore" | A sample cron entry that runs nightly at 02:15 UTC and uploads to S3: ```cron 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: | Capability | Authoritative recovery substrates | Maximum RPO | Maximum RTO | | ------------------ | --------------------------------- | ----------: | ----------: | | conversations | PostgreSQL | 90,000 s | 14,400 s | | memory | PostgreSQL | 90,000 s | 14,400 s | | vectors | PostgreSQL + Qdrant projection | 90,000 s | 14,400 s | | index metadata | Qdrant, recreated from code | 0 s | 14,400 s | | workbench | PostgreSQL | 90,000 s | 14,400 s | | ledger | PostgreSQL | 90,000 s | 14,400 s | | schedules | PostgreSQL | 90,000 s | 14,400 s | | watchers | PostgreSQL | 90,000 s | 14,400 s | | task state | PostgreSQL + filesystem | 90,000 s | 14,400 s | | audit | PostgreSQL | 90,000 s | 14,400 s | | evidence manifests | filesystem | 21,600 s | 14,400 s | 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](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: ```bash 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 `** — 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 `. 4. **`verification failed for `** — 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=` 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.