Skip to main content

Backup & Restore

PostgreSQL is the system of record; object storage holds media. Both must be backed up.

Backups​

  • Postgres: scripts/backup-postgres.sh takes a pg_dump custom-format backup and prunes old dumps beyond a retention count (default 14). It runs pg_dump inside the Postgres container itself (docker exec / podman exec) rather than relying on a host-installed pg_dump — this matters in practice, not just in theory: a host tool newer than the pinned server version can emit SQL (e.g. SET transaction_timeout = 0;, added in PG17+) that an older server's pg_restore rejects, which is exactly what happened while building this tooling. In Coolify, also enable scheduled volume backups as a second, storage-level layer; ship dumps to off-site object storage.
  • MinIO/S3: bucket versioning + periodic sync to a second region/bucket. Not yet automated by a script in this repository.
  • Keycloak/OpenFGA: back the Postgres they use (or their own stores).

Restore procedure​

  1. scripts/restore-postgres.sh <dump-file> <target-db-name> restores a dump into an existing target database, using pg_restore inside the Postgres container for the same version-matching reason as the backup script.
  2. Point DATABASE_URL at the restored instance; run api migrate up (idempotent).
  3. Restore MinIO bucket from the versioned copy.
  4. Verify /readyz, run smoke tests, confirm audit/outbox continuity.

Verifying a restore​

scripts/verify-backup-restore.sh automates the actual drill end to end: it takes a fresh backup, creates a throwaway database (never the real one), restores the backup into it, compares row counts against the source for a set of key tables, then always drops the throwaway database — including on failure, via a trap — so a failed drill never leaves stray state behind. It exits non-zero on any mismatch or failure, so it is safe to run on a schedule and alert on a non-zero exit code.

DATABASE_URL=postgres://buddora:buddora@localhost:5432/buddora?sslmode=disable \
    POSTGRES_CONTAINER=buddora_postgres_1 \
    ./scripts/verify-backup-restore.sh ./backups

This is what makes a backup verified rather than merely produced — a corrupt or version-incompatible dump is only caught here, not by pg_dump exiting 0.

Current status: the backup, restore, and verified-restore-drill scripts exist, are tested against real Postgres, and are safe to run on a schedule. What is not yet done: actually wiring one of them into a Coolify scheduled task or cron. This is now the only thing standing between "the drill works" and "backups happen" — the sentence here until 2026-08-28 said the repository "has not been deployed through Coolify at all yet", which stopped being true well before then. It is deployed; nothing schedules the backup.

MinIO/S3 backup automation is also still absent.

Real drill evidence (2026-07-31)​

The full drill was actually run against the local dev stack's live shared Postgres (buddora_postgres_1) — not a description of what it would do — timed end to end, with row counts and a specific record's field values compared between source and restored copy. The scratch/throwaway restore targets were dropped immediately after verification; the live buddora database was never written to.

Run 1 — scripts/verify-backup-restore.sh end to end (backup → create throwaway db → restore → compare 6 tables' row counts → drop throwaway db):

$ time POSTGRES_CONTAINER=buddora_postgres_1 \
    DATABASE_URL=postgres://buddora:buddora@localhost:5432/buddora?sslmode=disable \
    ./scripts/verify-backup-restore.sh ./backups

backup-postgres: done (312K) -> ./backups/buddora-20260731T070541Z.dump
verify-backup-restore: creating throwaway database dbrestore_drill_20260731070540_16320
verify-backup-restore: restoring backup into throwaway database
verify-backup-restore: OK: animal row count matches (617)
verify-backup-restore: OK: organization row count matches (108)
verify-backup-restore: OK: organization_membership row count matches (55)
verify-backup-restore: OK: safety_lost_report row count matches (212)
verify-backup-restore: OK: audit_event row count matches (471)
verify-backup-restore: OK: schema_migrations row count matches (16)
verify-backup-restore: RESTORE DRILL PASSED
verify-backup-restore: dropping throwaway database dbrestore_drill_20260731070540_16320

real  0m9.569s

Run 2 — backup and restore timed separately, plus a field-level check the script itself doesn't do (it only compares counts): a specific real animal record (c224482f-41c1-44c8-adcd-bd104dff4773, "Rex", a Labrador) compared byte-for-byte between the live database and a restored copy in a scratch database named buddora_restore_drill (dropped immediately after), and row counts extended to 8 tables:

StepReal measured time
backup-postgres.sh (pg_dump, 312 KB dump)2.08 s
restore-postgres.sh (pg_restore into a fresh scratch db)4.17 s
Full drill incl. verification + cleanup (Run 1)9.57 s

animal, organization, audit_event, safety_lost_report, account_suspension, outbox_event, moderation_report, and privacy_request all matched exactly (617 / 108 / 471 / 212 / 24 / 520 / 116 / 14 rows respectively); the sampled record's id/name/species/breed/created_at were identical between source and restored copy.

Against ROADMAP.md's stated targets (RPO 15 min, RTO 2 h): both are cleared by a wide margin at this scale — a ~2 s backup step means backups can run far more often than every 15 minutes if desired, and a ~10 s full restore-and-verify drill is nowhere near the 2-hour RTO budget. Read that margin honestly, not as a production capacity claim: the database backed up here is ~1,600 total rows across the tables checked and a 312 KB dump. A production database will be orders of magnitude larger, and pg_dump/pg_restore duration scales with data volume, not row count alone (large bytea/JSON payloads, indexes to rebuild, etc. all add real time pg_restore has to spend that this drill's tiny dataset never exercises). This run proves the mechanism is correct and the tooling works end to end for real — it does not prove a multi-GB production database restores within 2 hours; that requires a drill against production-scale data volume, which does not exist yet in this environment (there is no such environment yet — see "Current status" above).

Cadence​

Automate daily; rehearse a full restore quarterly and record the RTO/RPO achieved. scripts/verify-backup-restore.sh can run this drill on any cadence you schedule it — daily is cheap enough in practice that there is little reason not to.

Nothing schedules it today. docs/architecture/OBSERVABILITY.md records that backup/restore-drill staleness deliberately has no alert rule for exactly this reason: there is no signal to alert on until something runs the drill on a schedule. Treat the cadence above as the intent, not as a description of what is happening.