Backup and restore — and the drill that proves it works

#69's own framing is the right one: backups that have never been restored are not backups. This runbook is not "how to configure a backup job" — it is that, plus the scripted drill that proves the result is actually restorable, and what a successful drill looked like the one time it was actually run end to end while building this.

Decision: pg_dump, not WAL archiving

docs/architecture.md §9.2 mentions "Postgres with WAL archiving to object storage plus nightly pg_dump" in the same breath. This deployment builds only the second half, and that is a deliberate choice, not a shortcut waiting to be finished:

• This is a single VM, pre-production, with no live traffic yet.AGENTS.md records that as a standing fact of the current state, not a guess — there is no live database anywhere in this repository’s history so far. WAL archiving buys point-in-time recovery (restore to any second, not just the last nightly dump) at the cost of a second, always-on pipeline: archive_command wired into the Postgres container, a WAL-shipping agent watching it, object-storage retention for the WAL stream and periodic base backups, and a restore procedure that has to replay WAL forward from a base backup rather than run a single pg_restore. That pipeline protects against losing minutes of data since the last dump — real value once there’s real traffic to lose, none yet.

• A once-daily pg_dump plus a proven restore path is the honest tool for this deployment’s actual size. It has one moving part (pg_dump → rclone → object storage) instead of several, and this runbook’s own drill (below) is the reason "honest" is the right word here rather than "adequate" — the mechanism was actually exercised, not assumed to work because the command exits zero.

• The upgrade path is documented, not foreclosed. When real traffic and a real RPO requirement exist, archive_command = 'rclone copyto %p ...' (or a dedicated tool — pgbackrest, wal-g) layers on top of this without touching vsms-backup’s own `backup/restore subcommands (deploy/backup-tool — see "The mechanism" below): the manifest format here (taken_at, schema_migrations_applied, a pepper fingerprint) is a base-backup metadata scheme PITR tooling would reuse, not replace. Revisit this decision when the recovery-point objective actually matters, not preemptively.

The daily dump’s own recovery-point objective is what it is: up to 24h of data loss in the worst case (a failure right before the next scheduled run). That is the number this decision accepts. If that stops being acceptable before WAL archiving lands, shortening BACKUP_CRON_SCHEDULE is a one-line mitigation; it does not close the gap to zero the way PITR would.

Where backups go

Not the same disk as the database. deploy/docker-compose.yml's postgres_data volume and a same-host backup directory fail together — disk failure, host loss, or a docker volume rm typo takes both out at once. This is why the mechanism here is object storage from the start, not a local directory with "move it to S3 later" as a TODO.

The bucket is the operator’s choice, not a hardcoded vendor. The backup service (added to deploy/docker-compose.yml by this PR) uses rclone rather than a vendor-specific CLI, so the same vsms-backup backup/vsms-backup restore work unmodified against AWS S3, Backblaze B2, Google Cloud Storage, Azure Blob, MinIO, or (for a drill only — see below) a bare local path. That is a required, documented configuration, not a silently-local default:

• BACKUP_RCLONE_REMOTE (deploy/.env.example) has no default. The backup service’s own ${BACKUP_RCLONE_REMOTE:?...} in docker-compose.yml means it refuses to start rather than quietly backing up to nowhere.

• The remote’s own credentials live in deploy/secrets/rclone.conf (rclone’s own config file format — rclone config writes it interactively, or hand-write it per rclone’s docs), gitignored and mounted read-only into the backup container — the same convention docs/runbooks/deployment.adoc's "Secrets" section already uses for deploy/secrets/console-private-key.pem, including its documented threat model (protects against git history / image layers / docker inspect; does not protect against host filesystem access).

• This file must exist before the first docker compose up, full stop — production-readiness audit B2: deploy/docker-compose.yml’s `backup service mounts it as a configs: reference, not a plain volumes: bind mount, specifically because a bind mount onto a not-yet-existing host path used to be created silently as an empty directory by the Docker daemon (reproduced live), which made rclone fail obscurely against a directory where it expected a config file. configs: fails docker compose up outright ("bind source path does not exist") instead. deploy/secrets/rclone.conf.example is committed specifically so cp-ing it to deploy/secrets/rclone.conf (empty is a valid, working config for the local-path drill case above) is always step 1 of docs/runbooks/deployment.adoc, not conditional on which remote you end up using.

Cameroon hosting note. AGENTS.md's open question #1 (Law No. 2024/017, cross-border personal-data transfer) applies to backups exactly as much as it applies to the live database — a msisdn/msisdnHash column doesn’t stop being personal data because it’s inside a .dump file in a bucket. Whatever the eventual answer to "where does the database live" is, the backup bucket’s region has to match it. Not resolved here; flagging it so the bucket choice doesn’t quietly reopen a question §10 says needs a lawyer.

The mechanism

Rewritten in Rust as of the containerize-tooling PR — the maintainer’s "no bash script survives in this repo" direction (AGENTS.md's xtask section) reached this last corner of deploy/. deploy/backup.sh, restore.sh, restore-drill.sh, and backup-entrypoint.sh are gone — a hard cutover, not a parallel path. deploy/backup-tool (a small, separate Cargo workspace — see its own Cargo.toml/src/main.rs module doc for why it isn’t part of the root workspace or backends/apps/) is one binary, vsms-backup, with five subcommands: backup, restore, restore-drill, schedule, healthcheck (production-readiness audit S10, added after this section was first written). Same mechanism, same manifest shape, same guarantees — the shell orchestration is what changed, not what it does.

Still shipped inside deploy/backup.Dockerfile (postgres:16-alpine
rclone — the exact pg_dump/pg_restore this stack’s own Postgres major version needs; unlike backends/apps/sms-migrate, this one has no substitute for a real postgres client toolchain, so it stays on that base image rather than distroless). The runtime image is smaller than the shell-era one in one concrete way: no bash, no openssl — the restore-drill fallback pepper (SMS_HASH_PEPPER unset) is generated by the rand crate now, not openssl rand, and there is no shell script left in the image for bash to interpret.

• vsms-backup backup — pg_dump --format=custom (pg_restore’s own format: built-in compression, selective/parallel restore — a plain SQL dump gets none of that), plus a small unencrypted manifest next to it (taken_at, postgres_version, schema_migrations_applied, and a pepper fingerprint — see below), pushed to $BACKUP_RCLONE_REMOTE via rclone copyto, then prunes anything in that remote older than $BACKUP_RETENTION_DAYS (default 30) via rclone delete --min-age. Scheduled by the backup compose service’s vsms-backup schedule entrypoint — an in-process cron-expression scheduler (deploy/backup-tool/src/schedule.rs, the cron crate), not Alpine’s busybox crond any more; see that module’s own doc for why a hand-rolled scheduler had to think about this container’s own SIGTERM handling as PID 1, a property crond used to absorb for free just by being exec'd into. Also runs once immediately on container start ($BACKUP_RUN_ON_START=true by default) so a fresh deploy has a restorable backup right away rather than waiting up to 24h for the first scheduled tick.

• vsms-backup restore — the reusable restore primitive. Pulls a named backup (--dump-name), --latest, or a --local <path> dump already on disk, checks its manifest’s pepper fingerprint against the caller’s own $SMS_HASH_PEPPER (warns, never blocks — see "the pepper is part of the recoverable state" below), and runs pg_restore --clean --if-exists --no-owner --no-privileges into $DATABASE_URL. --clean drops and recreates every object inside the target database; it never touches the database itself, so this is safe to point at an empty or already-populated database alike.

• vsms-backup restore-drill — the actual gate. Seeds a recognisable marker row, records an exact row count for every table (a real SELECT count(*) per table via a real postgres::Client connection — deploy/backup-tool/src/db.rs — not pg_stat_user_tables's n_live_tup estimate and not a shelled-out psql per table), calls backup's own logic directly in-process, destroys everything in the target database, calls restore's own logic, and diffs both the row counts (as a real BTreeMap, not a hand-built comparison string) and the marker row’s exact content before vs after. Non-zero exit means the mechanism is broken, not that the drill tool itself failed for some unrelated reason. Requires an explicit --yes-i-understand-this-destroys-the-target-database flag and reads $DATABASE_URL — there is no default target, on purpose. Run it against a scratch database, never production.

• vsms-backup healthcheck — deploy/backup.Dockerfile’s own `HEALTHCHECK, run against the long-running schedule container every 15 minutes. A real signal that backups are actually landing, not just "is the process alive": fails once the last successful backup’s own health-file mtime is older than 2x the schedule’s own period (derived empirically from the next two upcoming cron occurrences, not parsed off the expression’s syntax). One missed backup alone doesn’t fail this — schedule already retries on the very next tick — two in a row past the cadence is the real, actionable signal. Review round 1, item 15: a second start-marker file, touched unconditionally the instant schedule starts (before the very first backup even begins), gives this the same 2x-period grace on every restart, not just the first — without it, BACKUP_RUN_ON_START=false (or a container still running a genuinely slow first backup) reported unhealthy on every single restart, indistinguishable from backups having actually stopped working. See `deploy/backup-tool/src/schedule.rs’s own module doc for the full mechanism.

Why DROP SCHEMA public CASCADE; CREATE SCHEMA public; rather than a real dropdb/createdb as the drill’s destructive step: it needs only the one $DATABASE_URL connection the drill already has (no separate superuser/admin connection to a different database to issue DROP DATABASE), and it works against any Postgres, including a managed one where the application’s own role can never hold DROP DATABASE — an honest stand-in for "destroy the database," not a shortcut around it. A production incident restoring after a genuinely lost VM or corrupted data directory is a different, larger procedure (provision a new Postgres, CREATE DATABASE, then vsms-backup restore --latest) — the drill proves the pg_dump/pg_restore round trip is sound; it does not simulate losing the VM itself.

The pepper is part of the recoverable state, not just the database

Message.msisdnHash/Message.bodyHash are HMAC-SHA256 keyed by SMS_HASH_PEPPER (backends/crates/sms-api/src/pepper.rs, landed in #134). A pg_dump backup captures the hashes, never the plaintext msisdn/body that produced them (that’s the point of hashing them) — which means the backup is only useful for opt-out matching and dedupe if it is restored alongside the same pepper that was active when the dump was taken. Restore the same rows under a different pepper and every msisdnHash/bodyHash silently stops matching anything — not an error, just rows that look like "never opted out" / "not a duplicate" to code that only ever compares hashes computed under whatever pepper is currently configured. pepper.rs's own module doc calls this the "rotation consequence"; a restore under the wrong pepper is the same failure mode by a different route.

Two things follow from that, both implemented here:

1. The pepper must be backed up too, separately from the database, and restorable to the same place at the same time as a database restore. This runbook does not solve secret-backup for you — SMS_HASH_PEPPER belongs wherever the operator already keeps POSTGRES_PASSWORD and ORANGE_CM_CLIENT_SECRET (a password manager, a sealed secret, before docs/runbooks/deployment.adoc's own eventual sops migration). The concrete instruction: whatever backs up deploy/.env, back it up on the same cadence as the database, and treat "restore the database" and "restore the matching .env" as one operation, not two.

2. Every backup carries a verifiable fingerprint of the pepper it was taken under, never the pepper itself. vsms-backup backup’s manifest (`deploy/backup-tool/src/manifest.rs) stores sha256(SMS_HASH_PEPPER) in the clear. That is deliberately safe to leave unencrypted: HashPepper's own minimum is 32 bytes of openssl rand output, so — unlike a raw MSISDN hash, brute-forceable in seconds over Cameroon’s ~10^7-candidate numbering space (AGENTS.md's #134 section) — a fingerprint of 256 real bits of entropy is not meaningfully brute-forceable. vsms-backup restore hashes the caller’s own $SMS_HASH_PEPPER the same way and compares; a mismatch warns loudly and proceeds rather than blocking, because restoring under a deliberately different pepper is a legitimate choice in some DR scenarios (e.g. a compromised pepper being rotated as part of the same incident) — it must never be a silent one.

Running the drill

docker network create vsms-69-net
docker run -d --name vsms-69-pg --network vsms-69-net -p 15503:5432 \
  -e POSTGRES_USER=vsms -e POSTGRES_PASSWORD=vsms -e POSTGRES_DB=vsms \
  postgres:16-alpine

DATABASE_URL=postgres://vsms:vsms@localhost:15503/vsms cargo run -q -p sms-migrate

docker build -f deploy/backup.Dockerfile -t vsms-backup-drill .

docker run --rm --network vsms-69-net \
  -e DATABASE_URL=postgres://vsms:vsms@vsms-69-pg:5432/vsms \
  -e BACKUP_RCLONE_REMOTE=<a real remote, or omit for a throwaway local one> \
  vsms-backup-drill \
  restore-drill --yes-i-understand-this-destroys-the-target-database

Omitting BACKUP_RCLONE_REMOTE makes restore-drill use a throwaway local directory of its own so the drill has zero external dependencies by default; passing a real rclone remote (with deploy/secrets/rclone.conf mounted the same way the backup service mounts it) exercises the actual upload/download path against real object storage instead, which is the stronger drill to run periodically once a real remote exists.

Run it again periodically, not just once. A drill that passed the day this PR merged says nothing about whether a schema change six months from now broke pg_restore's ability to replay it, or whether an operator’s rclone.conf silently expired. restore-drill is designed to be re-run by hand or wired into a scheduled job against a disposable database — no design for automatically scheduling it exists yet; that is a reasonable follow-up once this deployment has somewhere non-production to run it against on a cadence.

Verification actually performed for the containerize-tooling rewrite

Every finding and result below this line is from the Rust rewrite (deploy/backup-tool, this PR) — the bash-era results this section used to report are superseded, not merely repeated. Run exactly as shown above, against a real, network-isolated Postgres 16 migrated by the real backends/apps/sms-migrate binary (not ./ci/apply-migrations.sh, deleted in the same PR), with a real domain row (a providers row with recognisable, non-default field values: key='orange_cm_drill', cost_per_segment_xaf=12.5, max_tps=10, healthy=true) inserted before the drill, and BACKUP_RCLONE_REMOTE pointed at a named rclone remote (type = local in a mounted rclone.conf, not the bare-local-path shortcut) so the actual rclone lsf/copyto code paths — the ones a real S3 remote would exercise — were proven, not bypassed.

Step	Result
providers row inserted before the drill	present
Row counts across every public table + schema_migrations + the marker table, before vs after	identical, table-by-table exact count(*) — e.g. operator_prefix_rules=14, message_state_transitions=25, job_state_transitions=9, providers=1
Marker row (backup_drill_marker) content, before vs after	identical (note = 'restore-drill-proof')
Spot-checked providers row, before vs after	identical on every checked column — id, key, display_name, cost_per_segment_xaf, healthy, max_tps all round-tripped exactly
vsms-backup restore-drill's own exit code	0, printing RESTORE DRILL PASSED
Pepper fingerprint check, matching pepper	pepper fingerprint matches
Pepper fingerprint check, deliberately wrong pepper (negative test)	WARNING — SMS_HASH_PEPPER does not match..., restore still completed (warn, not block — as designed)
Retention pruning (BACKUP_RETENTION_DAYS=0, forcing everything to qualify as "older than 0 days")	confirmed deletion actually happens, not just a clean exit: the backup directory was empty immediately after the run that produced it

A guard-failure proof was run for this rewrite too, house standard (AGENTS.md): a real bug was introduced on purpose, the drill was shown to actually fail, then it was reverted. pgtools::restore_custom_format was temporarily replaced with a no-op (return Ok(()) before the real pg_restore call) — simulating a regression where the restore step silently does nothing. The drill did not merely report "row counts differ" (though they would have): it failed one step earlier and louder than that, at the final marker-row read, with a hard error — relation "public.backup_drill_marker" does not exist — because the schema the destructive step had just dropped was never restored at all. That is a stronger result than the row-count-mismatch path was designed to catch, worth recording explicitly per this file’s own "if a guard turns out to fail even earlier than intended, say so" standard rather than quietly claiming only the intended check fired. Reverted, rebuilt (confirmed byte-identical image manifest to the pre-sabotage build), and re-run clean — RESTORE DRILL PASSED again.

Not verified:

• A real cloud object-storage backend (S3, B2, GCS, Azure) — only rclone's own local backend, both as a bare path and through a named remote in a config file. The rclone copyto/lsf/delete calls deploy/backup-tool/src/rclone.rs uses are backend-agnostic by rclone’s own design, and the named-remote pass above proves the config-file-driven path (not just the "happens to be a filesystem path" shortcut), but a real network round trip to an actual bucket was not exercised in this environment.

• deploy/docker-compose.yml's backup service running for a real BACKUP_CRON_SCHEDULE tick under vsms-backup schedule — the BACKUP_RUN_ON_START path was exercised indirectly (this drill calls backup's own logic directly, the same code path schedule calls, but not through the scheduler loop or its SIGTERM-handling itself).

• Restoring into a different, freshly created database/VM standing in for "the original VM is gone" — the drill proves the dump/restore round trip against the same running Postgres instance, not a full disaster-recovery rehearsal that also re-provisions Postgres itself.

• Scheduled/automated re-running of the drill itself (see "Run it again periodically" above) — no cron/CI wiring exists yet for restore-drill.