A backup you have not restored is a hypothesis.

Section 01 · the honest starting point

There were none. That is the smaller of the two problems.

Before this work there was no crontab, no timer, and no off-host copy of a 255 MB production database holding every workspace, conversation and billing row on the platform. That is the easy problem: it is visible, and it takes a day to fix.

The harder problem is the one that replaces it. A team writes a nightly pg_dump, watches it produce a file, and stops thinking about it. The job reports success every night for a year. Then the disk dies, and the dump turns out to have been truncated since March, or it restores into a schema with no rows, or it is encrypted with a passphrase that only ever existed on the machine that just died.

Every one of those failures reports success right up until the moment you need it. So the design goal here was not "produce a dump on a schedule". It was: at every point, prefer a loud failure over a quiet file.

Section 02 · scope

Backing up derived data is a way of hiding from the question.

Four data stores run behind the platform. Only two are backed up, and the exclusions took more thought than the inclusions.

The test is simple: can this be reconstructed from something else you already hold? Search indexes can, by re-indexing. Cache counters can, from the event stream that produced them. Conversations cannot. Neither can append-only usage events, which is why the analytics database is in scope even though it is three orders of magnitude smaller than the search index.

Section 03 · the artifact

The dump does not count until something has read it back.

Postgres is captured with pg_dump -Fc, the custom format. It is compressed by default and it is the only format pg_restore can do selective or parallel restores from. Plain SQL looks friendlier in a text editor and is worse in every way that matters at 03:00.

flowchart TB
  CRON["cron · every 6h at :30"] --> PG["pg_dump -Fc omazy_middleware"]
  PG --> TOC{"pg_restore --list
  reads it?"}
  TOC -->|"no"| FAIL["FAIL · Slack alert
  this is not a backup"]
  TOC -->|"yes"| CH["ClickHouse
  BACKUP DATABASE omazy_analytics"]
  CH --> ENC{"passphrase set?"}
  ENC -->|"yes"| GPG["gpg AES256"]
  ENC -->|"no"| WARN["log: not encrypted"]
  GPG --> UP
  WARN --> UP
  UP{"bucket configured?"} -->|"yes"| R2["upload to R2"]
  UP -->|"no"| LOCAL["keep local copy
  and say so"]

Immediately after writing the file, the job reads its table of contents back with pg_restore --list. This costs milliseconds and it is the single highest-value line in the script, because it converts the most dangerous failure mode, a corrupt artifact that exists, into an ordinary alert.

# A dump that pg_restore cannot read is worse than no dump,
# because it looks like success. Verify the TOC before it counts.
if ! docker exec "$PG_CONTAINER" pg_dump -U "$PG_USER" -d "$PG_DB" \
      -Fc --no-owner --no-acl > "$out"; then
  FAILURES+=("postgres dump failed")
  return 1
fi

if ! docker exec -i "$PG_CONTAINER" pg_restore --list < "$out" >/dev/null 2>&1; then
  FAILURES+=("postgres dump is not readable by pg_restore")
  return 1
fi

Section 04 · the part that matters

Once a week, we actually restore it.

A backup nobody has restored is a guess with a filename. Every Sunday a second job takes the most recent dump, restores it into a throwaway container, and refuses to pass unless three separate things hold.

flowchart TB
  A["latest dump on disk"] --> B["gpg decrypt if needed"]
  B --> C["throwaway postgres:16 container"]
  C --> D["pg_restore"]
  D --> E["pg_dump --schema-only
  same sed normalisation as CI"]
  E --> F{"byte-identical to
  schema.golden.sql?"}
  F -->|"no"| X1["FAIL · Slack"]
  F -->|"yes"| G{"do load-bearing
  tables have rows?"}
  G -->|"no"| X2["FAIL · an empty DB
  also matches a schema"]
  G -->|"yes"| OK["PASS · container destroyed"]

The first check is that pg_restore can read the artifact end to end. The second is that the restored schema matches the repository's golden schema byte for byte. The third is the one people skip.

An empty database also matches a schema

A structural comparison passes perfectly against a restore that produced every table and not one row. That is a real failure mode: a dump taken with the wrong flags, or a restore that silently stopped after the DDL. So the drill also asserts that load-bearing tables came back populated, and treats an empty workspaces table as a hard failure rather than a curiosity.

# Structural check is not enough: an empty database also matches
# the schema. Assert the tables that would make a restore worthless
# if they came back bare.
for t in workspaces omazy_apps users chat_sessions messages; do
  n=$(psql -tAc "SELECT count(*) FROM ${t}" 2>/dev/null || echo "ERR")
  [ "$n" = "ERR" ] && fail "table ${t} missing from the restore"
  [ "$t" = "workspaces" ] && [ "$n" = "0" ] && fail "workspaces restored empty"
done

Here is the first run against a real dump. The omazy_apps count matches production exactly, which is the difference between "a file was produced" and "the data is recoverable":

[verify] verifying /home/ubuntu/backups/20260801-024022/postgres-omazy_middleware-20260801-024022.dump
[verify] restoring…
[verify]   schema matches golden
[verify]   workspaces: 50 rows
[verify]   omazy_apps: 129 rows
[verify]   users: 498 rows
[verify]   chat_sessions: 457 rows
[verify]   messages: 2307 rows
[verify] restore verification PASSED

Section 05 · one definition of correct

The backup and the migrations answer to the same file.

The schema the drill compares against is not written for the drill. It is db/schema.golden.sql, the same baseline the migration CI gate diffs against when it runs every migration up, then all the way down, then up again.

flowchart LR
  MIG["db/migrations/**
  125 files"] --> GATE["migration CI gate
  up · down · up"]
  GATE --> GOLD[("db/schema.golden.sql")]
  DUMP["nightly pg_dump"] --> DRILL["weekly restore drill"]
  DRILL --> GOLD
  GOLD --> TRUTH["one definition of
  a correct schema"]
  subgraph BOTH["both questions, one answer"]
    Q1["are the migrations sound?"]
    Q2["does the backup restore?"]
  end

This falls out of a property worth designing for: a check is only as trustworthy as the thing it compares against. A restore drill with its own private expectation of the schema is a second source of truth that will drift from the first, and the drift will be discovered during a recovery. Pointing both at one file means "the migrations are sound" and "the backup restores" cannot disagree without something going red.

It also has to be enforced in the boring details. Both the drill and the CI job normalise the dump through an identical sed chain that strips pg_dump version banners, because otherwise a patch-release difference between two machines shows up as schema drift and trains everyone to ignore the alert.

Section 06 · recovery point

At this size, incremental buys you nothing you actually want.

"Incremental backups" is usually reached for as a storage optimisation. It is worth doing the arithmetic before inheriting that instinct.

A full compressed dump of this database is about 68 MB. Thirty of them is roughly 2 GB, which on commodity object storage costs around three cents a month. There is no storage pressure here, so incremental saves nothing meaningful. What it actually buys is a shorter recovery point: how much data you lose when the disk dies.

For a product where customers are having live conversations, losing a full day of them is the part worth thinking hardest about. Running the same simple job every six hours instead of once a day cuts the worst case by four, costs pennies, and adds no new machinery. Continuous WAL archiving is the correct answer if six hours is still too wide, but it is a separate project with its own restore runbook, and pretending a cron job is equivalent to it would be the actual mistake.

# BEGIN omazy-cx-backup
# Backups every 6h at :30 (off-peak, clear of apt-daily timers).
30 */6 * * * /home/ubuntu/backups/bin/backup.sh >> /home/ubuntu/backups/cron.log 2>&1
# Weekly restore drill, Sundays 04:15 UTC.
15 4 * * 0 /home/ubuntu/backups/bin/backup-verify.sh >> /home/ubuntu/backups/cron.log 2>&1
# END omazy-cx-backup

Section 07 · the decisions that survive contact

Small choices that decide whether this still works in a year.

A bug worth keeping in the comments

The first version logged to stdout. A helper function returned a filename on stdout too, so every caller that captured that filename also swallowed the log line, and the not encrypted warning silently disappeared from the console output. It was still in the log file, which is the worst case: present enough to feel covered, absent where anyone would look.

# Logs go to STDERR on purpose. Helpers like maybe_encrypt return a
# filename on stdout, so anything that also logs to stdout gets swallowed
# by the caller's command substitution and warnings vanish silently.
log() { printf '%s  %s\n' "$(date -u +%Y-%m-%dT%H:%M:%SZ)" "$*" | tee -a "$LOG_FILE" >&2; }

The fix is one character. The reason it is worth a comment is that the same shape recurs in any shell script that mixes return values and diagnostics on one stream, and warnings that vanish are the ones you find out about last.

Section 08 · restoring

Into a scratch database, never straight over production.

The recovery path is deliberately the same one the weekly drill walks, so it is exercised fifty-two times a year rather than discovered under pressure.

# 1. Pull the artifact
aws s3 cp s3://omazy-backups/omazy-cx/TS/postgres-omazy_middleware-TS.dump.gpg . \
  --endpoint-url https://ACCOUNT.r2.cloudflarestorage.com

# 2. Decrypt
gpg --batch --decrypt --output restore.dump postgres-...dump.gpg

# 3. Restore into a SCRATCH database first. Never straight over prod.
docker run -d --name restore-target -e POSTGRES_PASSWORD=x -p 55440:5432 postgres:16-alpine
docker cp restore.dump restore-target:/tmp/
docker exec restore-target pg_restore -U postgres -d postgres --no-owner --no-acl /tmp/restore.dump

Only after inspecting the scratch restore should anyone consider promoting it. The instinct during an incident is to restore directly over the damaged database, which destroys the evidence of what went wrong and removes the option to change your mind. A scratch target costs one command and preserves both.