Skip to content

Latest commit

 

History

History
884 lines (753 loc) · 44.1 KB

backup-restore.md

File metadata and controls

884 lines (753 loc) · 44.1 KB

Backing up and restoring the Overflow database

This runbook covers the production overflow database on the local PostgreSQL cluster. The overflow.service unit and the deployment procedure live in README.md; this file is only about the database.

Values used throughout: the database is overflow (the name in the app's OVERFLOW_DATABASE_URL); the application role on this host is overflow_app — not overflow, which is only the database's name. The role name is host-specific: it is whichever login role owns the application's tables, the one whose credentials the app's OVERFLOW_DATABASE_URL carries. On another host substitute that role everywhere below; every command and SQL block here runs verbatim on this one.

(a) What is backed up

The whole overflow database, dumped with pg_dump --format=custom by scripts/db-backup.sh, into overflow-<UTC timestamp>.dump files. The stamp has one-second resolution, so a second run landing in the same UTC second installs overflow-<stamp>-1.dump and a third overflow-<stamp>-2.dump instead of replacing the first run's dump, and the name is taken atomically, so runs racing each other cannot collide on it either. A custom-format dump restores selectively (pg_restore --list, table-level -L/-T listing and filtering) and compressed; it restores only into the same major version, which is the cluster's own (17). The dump reads the database through a normal connection and takes no lock beyond ACCESS SHARE, so the application keeps serving during the backup.

Not backed up: roles, and anything outside the overflow database (other databases, cluster-wide settings such as postgresql.conf and pg_hba.conf). Those are host state; recreate them from this runbook and the deploy procedure, or dump them separately with pg_dumpall --globals-only if the host is ever migrated.

(b) The least-privilege backup role

The steady state dumps through a dedicated role that can read, and nothing else. Run as a database superuser (sudo -u postgres psql):

CREATE ROLE overflow_backup LOGIN PASSWORD '<generated password>'
  NOSUPERUSER NOCREATEDB NOCREATEROLE;
GRANT CONNECT ON DATABASE overflow TO overflow_backup;

then, connected to the overflow database (\c overflow):

GRANT USAGE ON SCHEMA public TO overflow_backup;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO overflow_backup;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO overflow_backup;
ALTER DEFAULT PRIVILEGES FOR ROLE overflow_app IN SCHEMA public
  GRANT SELECT ON TABLES TO overflow_backup;
ALTER DEFAULT PRIVILEGES FOR ROLE overflow_app IN SCHEMA public
  GRANT SELECT ON SEQUENCES TO overflow_backup;

The default privileges name FOR ROLE overflow_app because migrations create new tables as the application role, so grants that only cover existing objects go stale at the next migration; the FOR ROLE form covers future objects. The backup role owns nothing and creates nothing.

Its credentials live only in /etc/overflow/backup.env (root:root 0600, section (c)). Generate the password with openssl rand -hex 24 and never reuse the application role's password. Hex, not base64: base64 output contains a raw / in a large share of draws, and a raw / in the userinfo ends the authority section of a postgresql:// URL, so the parse garbles; hex characters are URL-safe. One accepted exposure: the scripts pass the connection string to the client tools as a command-line argument, which is briefly visible in the process list to other local processes. The file it is sourced from is 0600 and the box is single-administrator; if that trade ever stops being acceptable, the fix is parsing OVERFLOW_DATABASE_URL into PG* environment variables inside the scripts, which libpq reads without publishing it to the process list.

(c) Scheduled backups

deploy/overflow-backup.timer fires overflow-backup.service daily at 01:30 UTC (Persistent=true, so a backup missed while the host was down runs at the next boot). The service runs scripts/db-backup.sh as root with EnvironmentFile=/etc/overflow/backup.env — a dedicated file carrying the backup role's OVERFLOW_DATABASE_URL, not the application's /etc/overflow/overflow.env, so the backup path never holds the application role's credentials or any other secret the app needs.

Create the environment file (as root):

install -d -o root -g root -m 0700 /etc/overflow
[ -e /etc/overflow/backup.env ] \
  || install -o root -g root -m 0600 /dev/null /etc/overflow/backup.env
chmod 0600 /etc/overflow/backup.env
chown root:root /etc/overflow/backup.env

Put one line in it:

OVERFLOW_DATABASE_URL=postgresql://overflow_backup:<password>@127.0.0.1:5432/overflow

<password> is the hex string generated above: hex embeds in the URL safely, which is one reason the recipe is rand -hex and not rand -base64.

Then install and enable the units:

install -o root -g root -m 0644 \
  /srv/overflow/deploy/overflow-backup.service /etc/systemd/system/
install -o root -g root -m 0644 \
  /srv/overflow/deploy/overflow-backup.timer /etc/systemd/system/
systemd-analyze verify /etc/systemd/system/overflow-backup.service \
  /etc/systemd/system/overflow-backup.timer
systemctl daemon-reload
systemctl enable --now overflow-backup.timer
systemctl list-timers overflow-backup.timer

enable --now is right for a timer — there is no serving process to switch — unlike the deploy procedure's warning against it for the web service.

Verify the first scheduled run in the journal:

journalctl -u overflow-backup.service -n 50 --no-pager

and that a dump file appeared in /var/backups/overflow. A failed run notifies the maintainer by email through OnFailure= — see failure alerts.

(d) Backup location and retention

Backups land in /var/backups/overflow, root:root 0700, created by the script on first use; dump files inside are 0600 because the script sets umask 077 before creating anything — a manual drill run has no unit involved and produces 0600 all the same. The unit's UMask=0077 is defense in depth for the same property, not the mechanism. db-backup.sh prunes overflow-*.dump files older than 14 days (--retention-days, default 14) after each successful dump — 15 daily dumps are retained at the steady state, and nothing not matching overflow-*.dump in the directory is ever deleted. The pattern covers the -1, -2 suffixed names a same-second extra run takes, so those are retained and pruned on the same schedule. The exception: a run's partial, .overflow-<pid>.dump.incomplete, older than one day is reclaimed by the next run's sweep.

The directory is on the same filesystem as the database. That is fine for the failure modes this runbook targets — a bad migration, a bad deploy, a dropped table — where the database volume itself survives; it is not protection against a lost disk. If off-host copies are wanted later, rsync or rclone out of /var/backups/overflow from a second timer; the dump files are plain files.

(e) Restoring

scripts/db-restore.sh [--allow-live] TARGET_DATABASE DUMP_FILE restores a dump with pg_restore --clean --if-exists --no-owner --no-privileges. The connection comes from OVERFLOW_DATABASE_URL with the target name substituted; a target equal to the database OVERFLOW_DATABASE_URL names is refused unless --allow-live is passed, because --clean drops existing objects. The archive is streamed on stdin, so the dump file only has to be readable by whoever runs the script.

(e.1) Drill: restore into a scratch database and compare

The quarterly drill (section (g)). Everything here is read-only against production; the scratch database is dropped afterwards. As root, from the deployment tree. First run README.md's configuration-loading block in the same shell; the later URL derivation uses its normalized value:

# Named before the drill runs, because the later blocks of this section — the
# comparison, the grant, the listing, the dropdb — need it, and a subshell
# cannot hand a variable back to the shell it was typed in.
scratch="overflow_drill_$(date +%s)"

(
  set -e
  # Take the run's LAST stdout line, which is the path it installed. The run's
  # status is read before the line is picked, because `$(… | tail -1)` reports
  # tail's status and would let a failed backup carry on with an empty path,
  # surfacing much later as "install: cannot stat ''".
  output=$(bash scripts/db-backup.sh)
  dump=$(printf '%s\n' "$output" | tail -1)
  # $dump is e.g. /var/backups/overflow/overflow-<stamp>.dump — or
  # overflow-<stamp>-1.dump when another run had already taken the plain name
  # in that second. Copy that exact path; do not reconstruct it from the
  # timestamp.
  [ -n "$dump" ] || { printf '%s\n' "no dump path printed — the backup run failed" >&2; exit 1; }
  # Print it here, inside the subshell where it lives: the subshell's variables
  # die with it, so a path that is only assigned is a path nobody sees, and
  # (e.2) below tells the operator to use the dump the run printed. This line
  # is what a successful drill leaves on the terminal.
  printf 'drill dump: %s\n' "$dump"

  # Everything below is gated on the backup having worked, so a failed run stops
  # HERE rather than leaving an overflow_drill_<epoch> database behind for the
  # cleanup at the end of this section to never reach.
  sudo -u postgres createdb "$scratch"

  # The backup directory is root-only, and the deployed restore script is 0750
  # root:overflow, unreadable by postgres. Stage postgres-readable copies of
  # both; the restore runs as the postgres OS user over peer auth.
  install -o postgres -g postgres -m 0400 \
    "$dump" /tmp/overflow-drill-dump-staging.dump
  install -o postgres -g postgres -m 0500 \
    /srv/overflow/scripts/db-restore.sh /tmp/overflow-drill-restore.sh
  sudo -u postgres env OVERFLOW_DATABASE_URL=postgresql:///"$scratch" \
    bash /tmp/overflow-drill-restore.sh --allow-live "$scratch" \
    /tmp/overflow-drill-dump-staging.dump
)

The subshell is what makes the gate safe to paste: the set -e and the exit belong to it, and it is the operator's shell that keeps running afterwards. This runbook sets no set -e in the shell the operator is standing in, and an exit typed there would close it — which is why the refusal above is a subshell's exit 1 and not a bare one.

A drill dump: <path> line means the drill is proceeding; no such line means it stopped. On failure nothing was created, and because the shell survived, the blocks below would otherwise run on against a $scratch that was never made — the comparison printing MISMATCH for every table. If there is no drill dump: line, stop here and read the backup's stderr; the rest of this section has nothing to work from.

Note the --allow-live: the target equals the database the drill's OVERFLOW_DATABASE_URL names, so the safety guard demands the flag be typed on purpose; nothing live is named. Then compare, per public table, production against the scratch:

for t in $(sudo -u postgres psql -d overflow -tAc \
    "select tablename from pg_tables where schemaname='public' order by tablename"); do
  p=$(sudo -u postgres psql -d overflow -tAc "select count(*) from \"$t\"")
  s=$(sudo -u postgres psql -d "$scratch" -tAc "select count(*) from \"$t\"")
  printf '%-45s prod=%-9s scratch=%-9s %s\n' "$t" "$p" "$s" \
    "$([ "$p" = "$s" ] && echo match || echo MISMATCH)"
done

A restored copy is EXPECTED to lag the tree, and by how much is the drift between the dump and the deploys since it was taken — the same count of migrations (e.2) has to apply before its replacement can serve, which is why the drill records it. scripts/deploy-migration-status.ts prints one line per migration the tree carries that the database it names does not record, and prints nothing at all when there is no lag. Point it at the scratch copy the way (e.2) points at its replacement — the environment is already loaded from this section's first block, so only the database name changes — but read the copy as the application role, and the restore left it unreadable to that role: db-restore.sh runs with --no-owner --no-privileges, so every table in the scratch belongs to postgres with default ACLs and the application role holds nothing on it. Grant it the read (as superuser, the same identity the comparison loop above already uses, which is why that loop is unaffected):

sudo -u postgres psql -q -d "$scratch" \
  -c "GRANT USAGE ON SCHEMA public TO overflow_app; GRANT SELECT ON ALL TABLES IN SCHEMA public TO overflow_app"

then the listing, with its status recorded alongside it:

scratch_url="${OVERFLOW_DATABASE_URL%/*}/$scratch"
pending="$(OVERFLOW_DATABASE_URL="$scratch_url" node scripts/deploy-migration-status.ts)"
status=$?
printf 'pending migrations in %s (listing exit %s):\n%s\n' \
  "$scratch" "$status" "${pending:-(none)}"

That printf is what makes the recording trustworthy rather than merely present. A listing that could not be produced prints nothing, which is exactly what no lag also prints, so the two would otherwise reach the drill log looking alike — the same trap (e.2)'s gate refuses on for the same reason. Carrying the command's exit status in the line distinguishes them: 0 beside (none) is a current copy, and any other value beside it is a listing that never arrived, which is a failed drill step and not a measurement.

The scratch copy is NOT migrated: the drill compares data, and migrating it would measure nothing about the restore. The migration step belongs to (e.2). Record the outputs — dump bytes, backup and restore durations, per-table counts, pg_restore stderr, the pending-migration line above — in the drill log. Append the entry to /var/backups/overflow/drill-log.md (root:root 0600). Then clean up, keeping the dump:

sudo -u postgres dropdb "$scratch"
rm /tmp/overflow-drill-dump-staging.dump /tmp/overflow-drill-restore.sh

The 2026-09-28 drill measured db-backup.sh at 48.8 s and db-restore.sh at 75.1 s wall clock against a 167.7 MB dump covering 28 public tables and about 1,342,000 rows. Each drill's full measurements and per-table comparison are entries in /var/backups/overflow/drill-log.md.

(e.2) Replacing the live database

The path for "the current database is lost, or must be rolled back". Stop the application first so no writes go to the old database mid-swap:

systemctl stop overflow.service

As root and from the deployment tree (/srv/overflow), create the replacement owned by the application role and restore as the application role, using its own OVERFLOW_DATABASE_URL: every command below is relative to that tree. --no-owner then makes the app role own every restored object, which is the production shape, so this path needs no ownership fixups at all. First run README.md's configuration-loading block in the same shell, before restoring or deriving the replacement URL:

sudo -u postgres createdb -O overflow_app overflow_replacement
bash scripts/db-restore.sh overflow_replacement \
  /var/backups/overflow/overflow-<stamp>.dump

Substitute the dump you mean, spelled exactly as the backup run printed it: the plain overflow-<stamp>.dump, or the overflow-<stamp>-1.dump that run installed when another run already held the plain name for that second.

The restored copy carries the schema the dump was taken with, and a dump carries schema_migrations as of the moment it ran — 01:30 UTC, before whatever deploys have landed since. Deploys apply migrations (README.md section 10), so a restored database is normally behind the tree, and that is exactly the state the readiness endpoint refuses: it answers 200 only when every migration the served build bundles is recorded applied, so a schema behind the build answers 503 {"status":"unavailable"} while a row count still passes. Migrate the replacement before anything else touches it:

cd /srv/overflow
replacement_url="${OVERFLOW_DATABASE_URL%/*}/overflow_replacement"
OVERFLOW_DATABASE_URL="$replacement_url" pnpm db:migrate

Three things about that block are traps rather than preferences:

  • The inline assignment wins over the tree's .env. db:migrate is node --env-file-if-exists=.env scripts/migrate.ts, and node --env-file does not override a variable already present in the process environment — so the replacement is the target even on a tree that carries a .env naming production.
  • It runs from /srv/overflow, not from a checkout. The migrations applied must be the ones the SERVING build bundles, and src/lib/db/migration-manifest.ts is the served build's own record of them, pinned equal to db/migrations/*.sql by tests/db/migration-manifest.test.ts. A restore driven from any other tree can leave a schema the readiness probe still refuses.
  • It runs before the rename, not after. The live overflow is untouched while it runs, the replacement is still verifiable under its own name, and a failing migration aborts before anything has been swapped. The readiness endpoint cannot answer at this point — the service is stopped — so the schema gate below is the pre-rename check, and the end-to-end confirmation stays where deploy/README.md section 7 puts it, after the restart.

The restore carries no privileges: --no-privileges skips every ACL the dump recorded, so the replacement database has neither section (b)'s backup-role table SELECT nor its DEFAULT PRIVILEGES, and the first nightly backup after the swap would fail with "permission denied" for overflow_backup. Re-apply section (b)'s grants to the replacement now, after the migration above rather than before it. GRANT SELECT ON ALL TABLES IN SCHEMA public is a snapshot of the tables that exist at the moment it runs; reaching objects created after it is the ALTER DEFAULT PRIVILEGES half's job, and that half covers objects the application role creates in public and nothing else. On this path that is enough — migrations run as the application role, in public, so a swap made in the other order would still leave the backup role able to read every table the migration added, and the order is not covering a failure this path has today. It is the general rule written down: migrate, then grant, so the snapshot covers the whole schema whatever ends up creating it, so a future migration creating an object under another role, or outside public, is covered by the same block rather than by default privileges. As superuser, connected to the replacement:

sudo -u postgres psql -d overflow_replacement <<'SQL'
GRANT CONNECT ON DATABASE overflow_replacement TO overflow_backup;
GRANT USAGE ON SCHEMA public TO overflow_backup;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO overflow_backup;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO overflow_backup;
ALTER DEFAULT PRIVILEGES FOR ROLE overflow_app IN SCHEMA public
  GRANT SELECT ON TABLES TO overflow_backup;
ALTER DEFAULT PRIVILEGES FOR ROLE overflow_app IN SCHEMA public
  GRANT SELECT ON SEQUENCES TO overflow_backup;
SQL

CONNECT is usually already effective — it is granted to PUBLIC by default and --no-privileges revokes nothing — so that line only matters where a cluster has revoked it; every statement is idempotent.

The target (overflow_replacement) differs from the database the URL names (overflow), so no --allow-live is needed. Verify as in (e.1), with overflow_replacement in the scratch's place, schema first. A row count answers "can the app role read" and says nothing about "can the served build run", and those are exactly the two things a stale restore separates: the count passes on the database the readiness endpoint refuses. scripts/deploy-migration-status.ts prints one line per migration the tree carries that this database does not record — the name, a tab, then a marker: -, or review when the file carries the literal overflow: mixed-version review — and prints nothing at all when the database is current. Here the listing says only one thing: which migrations the swap would go live without. The marker is the deploy procedure's own mixed-version signal (README.md section 10) and it does not bind a restore — the previous release is stopped, so nothing serves against the old schema while this runs, and there is no mixed-version window to review:

pending="$(OVERFLOW_DATABASE_URL="$replacement_url" node scripts/deploy-migration-status.ts)"
status=$?
if [ "$status" -ne 0 ]; then
  printf 'could not list the pending migrations; the replacement is NOT verified — do not swap it in\n' >&2
  false
elif [ -n "$pending" ]; then
  printf 'the replacement is behind the tree by:\n%s\ndo not swap it in — re-run the migration block above from /srv/overflow, then this gate\n' "$pending"
  false
fi

Three properties of that gate are deliberate. The status half follows README.md section 10, which stops on a failed status command in both its forms — the deploy script wraps the same call in || { …; exit 1; }, and the manual path says to run it by hand and stop if it fails. It matters because deploy-migration-status.ts prints nothing to stdout when it cannot run at all — node off PATH, the wrong working directory, a database that is not there, a database carrying no schema_migrations — and puts the reason on stderr with a nonzero status, so an empty listing is both "current" and "could not ask". All four are refused here, and two of them would have been caught anyway by the row count below, which errors on an absent database and on one carrying no schema_migrations rather than printing a count. The one with nothing behind it is a listing that could not be produced at all — node off PATH, the wrong working directory — because the ledger and the tables are intact there, the row count answers, and a replacement two migrations behind would have gone live exactly as (e.2) exists to prevent. The elif half is the other side of the same command and is not covered by that precedent: the script exits 0 whether or not it printed anything, because the deploy procedure reads its listing rather than its status, so only the output can tell "current" from "behind". The status is captured into a variable rather than tested inline with || because the second branch has to be reached on the same condition — an || { …; false; } in front of it runs that branch and then falls straight through the if on an empty listing, which ends the pasted block 0 and reads as a pass. And nothing here calls exit: no block in this runbook sets set -e, and an exit inside a pasted block closes the shell the operator is standing in.

A refusal here is a cheap state to be in, because it happens before the rename: nothing has been swapped, the live overflow is exactly as it was, and the whole recovery is systemctl start overflow.service — the database was never touched, only the service was stopped. Fix whatever the gate named, the migration it could not apply or the listing it could not produce, and run this section again from the top — the stop first, since the service is running again, then sudo -u postgres dropdb overflow_replacement. The replacement is disposable until the rename and the live database is not, and a half-restored replacement cannot simply be restored over: --clean drops only the objects the dump names, so a table a migration has added since the dump still holds its foreign key onto the parent the restore is trying to drop, the second restore errors out on that drop and leaves less behind than it started with, and the retry ends in a worse state than the refusal it was recovering from. Drop the database and the section starts again from an empty one.

Then the smallest real check that the replacement serves before the rename — the app role can authenticate, and the restored tables answer a read — with the environment and $replacement_url already loaded from the blocks above:

psql "$replacement_url" -tAc "select count(*) from issues"

It must print a row count, not an error. Then swap the names and start the service:

sudo -u postgres psql -c "alter database overflow rename to overflow_old_<epoch>"
sudo -u postgres psql -c "alter database overflow_replacement rename to overflow"
systemctl start overflow.service

and verify the service per deploy/README.md section 7. To repoint by configuration instead of renaming, skip the two alter database lines and change OVERFLOW_DATABASE_URL in /etc/overflow/overflow.env to name overflow_replacement before the restart — the rename exists so the environment file, the unit and the deploy procedure stay untouched. Drop overflow_old_<epoch> only after a soak period: the old database is the rollback for a bad restore, and dropping it is the point of no return.

(e.3) Ownership fixups

Only needed when a dump was restored by a role that is not the intended owner — the drill path restores as postgres. As superuser, in the restored database:

DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
    EXECUTE format('ALTER TABLE public.%I OWNER TO overflow_app', r.tablename);
  END LOOP;
  FOR r IN SELECT sequencename FROM pg_sequences WHERE schemaname = 'public' LOOP
    EXECUTE format('ALTER SEQUENCE public.%I OWNER TO overflow_app', r.sequencename);
  END LOOP;
END $$;

plus, if the restoring role is not already the database owner: ALTER DATABASE <name> OWNER TO overflow_app;. Ownership is not the only thing a --no-privileges restore leaves behind: ACLs are skipped too, so a database restored on this path also needs section (b)'s grants re-applied — exactly what (e.2) does before its rename — or the nightly backup fails with "permission denied" for overflow_backup. The replacement path (e.2) needs neither repair.

(e.4) Restoring the encrypted off-host copy

The path for "the host is gone" — or for a restore anywhere the reduced set is enough. Its order carries a step the full-dump paths never need — a forced full rederivation of every registered repository afterwards — and a limit the full dumps do not have.

The limit first, because it bounds what this path recovers: a forge object deleted since the backup keeps its settlement row but loses its evidence facts. The copy excludes section (i)'s nine tables by data, and the forced rederivation rebuilds their rows from the forge as it stands when it runs — rows the forge no longer has are not recovered by this path. Every other table restores as of the backup moment, schema_migrations among them, so a restored copy is behind the tree by exactly the deploys since the backup, and (e.2)'s migration discussion applies unchanged.

The restore order. Fetch the newest overflow-reduced-<UTC timestamp>.sql.xz.age attachment from the dedicated backups channel (section (i)). Retrieve the age private key from the fleet's #credentials store — restoring anywhere requires it (section (i)) — and write its AGE-SECRET-KEY- line to a scratch identity file, mode 0600, removed when this section is done. age reads identity files of AGE-SECRET-KEY- lines, comments allowed around them.

Decrypt, decompress, and restore into an EMPTY database. The copy is plain SQL, not a pg_restore archive, so db-restore.sh does not apply here: its --clean, --if-exists and --allow-live machinery is for (a)'s custom-format dumps. The empty target is what stands in for those guards — nothing pre-exists for a restore to drop or half-overwrite, and the live database is never named:

# The stamp is the one in the fetched attachment's name.
stamp=20261005T021000Z
age -d -i overflow-offhost-age.txt \
  < "overflow-reduced-$stamp.sql.xz.age" \
  | xz -d > "overflow-reduced-$stamp.sql"
sudo -u postgres createdb -O overflow_app overflow_from_offhost
sudo -u postgres psql -v ON_ERROR_STOP=1 -d overflow_from_offhost \
  -f "overflow-reduced-$stamp.sql"

ON_ERROR_STOP=1 because a restore that silently continues past a failed statement is a half-restore: a missing role or a failed grant should stop it, not be skipped.

Roles first, though. The dump carries no roles — (a) — and its ownership statements name overflow_app while the table grants it carries name overflow_backup, so both roles must exist before the restore runs. On the same host they do; on a replacement host recreate them first, from section (b) and the deploy procedure. Restored as a superuser with both roles present, the dump's own ALTER ... OWNER TO statements leave every object owned by overflow_app — no (e.3) fixup — and the grants section (b) applied to the tables come back with the dump, so the first nightly backup against a reduced restore does not fail the way (e.2) warns its swap can. (e.3) applies on this path only where the restore ran as a role that is neither superuser nor overflow_app.

Migrate and verify as (e.2) has it. The restored schema_migrations records the backup moment, so the copy is behind the tree, and (e.2)'s migration block — run from /srv/overflow with the inline OVERFLOW_DATABASE_URL — plus its schema gate apply here unchanged. On a replacement host the tree comes from the public repository per deploy/README.md.

Then rebuild what the copy left out. Request a forced full rederivation for every registered repository — POST /api/moderation/rederivation with {"repositoryId": "<uuid>"} as a moderator, from the moderation page's rederivation control or with a moderator API token as the bearer credential:

curl -X POST https://<public-host>/api/moderation/rederivation \
  -H "Authorization: Bearer <moderator api token>" \
  -H "Content-Type: application/json" \
  -d '{"repositoryId":"<uuid>"}'

Each request makes the repository's next fold a full pass (rederive: true), which rebuilds the excluded tables from the forge. The restored registered_repositories rows carry every id, so the list of repositories to request comes from the restored database itself:

sudo -u postgres psql -d overflow_from_offhost \
  -c "select id, owner_name from registered_repositories order by owner_name"

Compare per-table counts as in (e.1), production against the restore — reading the nine excluded tables as populated only to the forge's current state. Swapping the restore in for the live database is (e.2)'s swap: its rename block and the service start, after the migration and its gate.

After a host loss, the restored copy is not yet a working service. The order above restores the data, but the host took its environment file with it, and two of its entries exist nowhere else: OVERFLOW_TOKEN_ENCRYPTION_KEY and AUTH_SECRET live only in /etc/overflow/overflow.env on the host, and their custody is deliberately not the #credentials store that keeps the age private key (section (i)). What the two keys protect travels only as ciphertext in this copy — sponsors' OAuth tokens, forge credentials and webhook secrets — so a host-loss restore without them cannot decrypt any stored credential. The forced full rederivation cannot run either: it decrypts the sponsor's OAuth token unconditionally for GraphQL cost accounting before any forge read (src/lib/fold/reconcile.ts:272), so it fails closed before reaching the forge.

The host-loss path. Restore the data as the order above gives it. Generate a NEW AUTH_SECRET — every session signed under the old one ends. Each sponsor signs in again, which stores a fresh OAuth token. Forge registrations are redone and webhook secrets are replaced. Every registered GitHub repository reads through its sponsor's token — no App carve-out remains. For each repository in the list below, reconciliation and webhook verification stay broken until its sponsor returns:

  • Nitjsefnie/ai-researcher
  • Nitjsefnie/claudit
  • Nitjsefnie/gh-widgets
  • Nitjsefnie-Actions/pr-gate

Key custody. The two keys are kept by the maintainer; location to be named. They never join the #credentials store: the off-host copy already carries the credential ciphertext those keys open, and section (i)'s custody rule — a reader of one store holds neither both — forbids one store holding both halves.

(f) RPO and RTO

RPO (data at risk): up to 24 hours. The timer fires daily; a failure at any moment loses the commits since the previous 01:30 UTC dump. This is the accepted tradeoff. To tighten it: add more OnCalendar= lines to overflow-backup.timer (for example every six hours) — retention only needs lowering if disk pressure says so. For a sub-hour RPO, PostgreSQL WAL archiving is the real mechanism and is out of scope here.

RTO (time to restored service): machine time seconds, end-to-end minutes. The drill of 2026-09-28 measured, against the 167.7 MB production dump (28 tables, about 1,342,000 rows): db-backup.sh 48.8 s and db-restore.sh 75.1 s wall clock, with empty pg_restore stderr. At comparison time, 26 public tables matched; the write-active repository_reconciliation_dirty_subjects (29 production vs 25 scratch) and webhook_deliveries (13,686 vs 13,675) tables had drifted between the dump and the live count. Machine time scales with the dump size; the dominant RTO terms are the operator steps of (e.2) — create the replacement, migrate it, verify, rename, restart the service — so budget tens of minutes including human response time, not seconds.

(g) Restore-testing cadence

  • Per change: the automated restore test tests/db/backup-restore.test.ts runs in CI on every PR: it seeds a containerized Postgres, runs both scripts against it with the client tools exec'd inside the container, mutates the source after the dump, and asserts the restored rows equal the seed.
  • Manual drill: at least quarterly. Run (e.1) end to end, compare all public tables, and record the outputs where the deployment records live. Re-run the drill and re-measure when the newest dump has grown to roughly twice the size recorded in the latest drill-log entry. A restore that has not been rehearsed is an assumption; the drill is what keeps this runbook true.

(h) Failure alerts

A failed overflow-backup.service run starts overflow-alert@overflow-backup.service.service — the alert template with the failed unit's full name as the instance — through the unit's OnFailure=, which mails the backup unit's current-boot journal tail — the pg_dump error among it — to the address in /etc/overflow/alert-recipient. That file is host configuration (root only, one line) and is never committed; README.md section 12 installs the route and verifies it.

The drill in (e.1) proves a restore works on the day it runs; the alert route is what tells you a backup failed between drills. Without it, a nightly backup can fail for weeks unnoticed — an expired backup-role password, or the grants gap section (e.2) warns about: a swap that skipped the grants fails the first nightly backup with "permission denied" for overflow_backup.

The template covers overflow.service as well as the backup unit (README.md section 12), so an alert can also carry the service itself — but not the other way round. A restore that skipped (e.2)'s migration step leaves a service that is up, healthy to every data check, and answering 503 at /api/readiness on a schema behind its build; nothing about that state fails the unit, so nothing alerts. Two checks stand between the dump and that state, and neither is the alert route: (e.2)'s schema gate refuses the swap before the rename, while the live database is untouched and a wrong answer costs a re-run, and the readiness curl in README section 7 confirms it end to end once the service is restarted. A swap that reached a 503 readiness skipped both.

(i) The encrypted off-host copy

A second, smaller copy of the database leaves the host nightly, so a lost host no longer loses every backup with it. overflow-offhost-backup.timer fires overflow-offhost-backup.service daily at 02:10 UTC — forty minutes after the 01:30 UTC full backup, so the reduced copy always reflects a completed full dump and the two jobs never contend for the database or the backup directory — and Persistent=true catches up a run missed while the host was down. The service runs scripts/db-offhost-backup.sh as root with the same EnvironmentFile=/etc/overflow/backup.env as the full backup, plus the entries this section stands up.

The copy is a reduced set: one pg_dump --format=plain of every table's schema, with data excluded for exactly the 9 tables whose rows a forced full rederivation rebuilds or that hold only work-queue state. Their schema still travels with the copy; only their rows are left out. The 9, in the order the script excludes them:

  • repository_reconciliation_evidence_facts
  • repository_reconciliation_evidence
  • webhook_deliveries
  • repository_policy_violations
  • repository_reconciliation_dirty_subjects
  • repository_reconciliation_jobs
  • repository_reconciliation_usage
  • reconciliation_sweep_pages
  • reconciliation_sweep_walk_subjects

reconciliation_sweep_pages stores a held sweep pass's absence-listing records; the forge is the source of truth. A pass restored without them lists those subjects again on its next run, so their rows are left out of the copy whose encrypted size the size guard bounds. reconciliation_sweep_walk_subjects holds the open pass's staged evidence and is also excluded; completing or discarding the pass deletes those rows by cascade.

Every other table keeps its data, for two reasons: authoritative rows refer to the forge-derived projections by id — settlement_override_requests refers to issues, and settlements and self_work_calibrations refer to pull_request_issues — and a rebuild mints new ids that would orphan them; and reconciliation_changes and reconciliation_runs are local history no forge replay recreates.

The dump is compressed with xz -9e, encrypted with age -r to the operator's age public key — the only key material on the host — and installed in /var/backups/overflow as overflow-reduced-<UTC timestamp>.sql.xz.age, mode 0600 in the root-only 0700 directory like (d)'s dumps. The job then posts the file as the osc identity to the dedicated private Discord channel through the mailbox CLI, bringing a connector up for itself and stopping it afterwards when none was running. Retention is 14 days on both copies: the job deletes its own posted messages older than 14 days from that channel — one generous page per run, and a message the page misses is swept on a later run — and prunes local overflow-reduced-*.sql.xz.age files older than 14 days after a dump has installed and cleared the size guard, before it posts — earlier in the run than (d)'s prune, so a run whose post fails has already pruned.

The size guard comes before anything is posted: an encrypted file at or over OVERFLOW_BACKUP_MAX_BYTES (default 9961472 bytes — 9.5 MiB, under Discord's attachment limit) is never posted and never split. The run fails instead, so OnFailure= alerts through overflow-alert@ exactly as (h) documents for the full backup, and the local encrypted file stays behind as a good local copy — delete it once the limit is raised; it is not in the posted series.

Key custody. Only the age public key is on the host: the job cannot read what it writes, and neither can anything that takes the host. The private key is stored once, in the fleet's Discord #credentials store — never in the backups channel, never on the host. The backups channel holds ciphertext; #credentials holds the one key that opens it; a reader of one store holds neither both. Restoring anywhere requires retrieving the key from there, which is (e.4)'s first step.

The unit's sandbox is the full-backup shape with one addition the full backup never needs: the mailbox CLI, a python3 script under /root, reached through a single BindReadOnlyPaths entry with ProtectHome spelled tmpfs rather than yes — the pairing systemd.exec(5) sanctions for a bind destination under a protected directory, probed on this host's systemd (the unit file's comments carry the record). Nothing else under /root is reachable. The osc token reaches the CLI as a systemd credential: the unit loads ~/.agent-bundle/discord/osc.token at every start and the job's script bridges it to DISCORD_TOKEN in the process environment, so the environment file carries no token and a rotation needs no Overflow-side edit.

Operator setup. The steps off the host come first (any machine that runs the fleet's mailbox CLI — the host does), then the host.

Off the host. Generate the age keypair — the private half must not stay wherever this runs, so plan its removal before generating:

umask 077
age-keygen -o overflow-offhost-age.txt
# prints: Public key: age1...

Store the private key once in #credentials, through the mailbox CLI's creds subcommand — never in the backups channel:

~/.agent-bundle/scripts/discord_mb.py creds osc add overflow-backup age \
  "$(grep -v '^#' overflow-offhost-age.txt)"

The key rides the command line here, the one-time exposure (b) already accepts for the backup role's connection string on a single-administrator host — and unlike (b)'s password, this key's custody is off-host the moment the command lands. Then remove the file the keypair was generated into; if that was the host, removing it is what keeps the custody promise:

rm overflow-offhost-age.txt

Still in Discord: create the dedicated backups channel and make it private. The CLI creates the channel but cannot set permission overwrites, so restricting who may view it — only the osc identity's account and the maintainer's own — is the operator's Discord-side step, in the channel's settings. The job posts ciphertext and file names there and nothing else, ever: never point OVERFLOW_BACKUP_DISCORD_CHANNEL at a chat channel.

~/.agent-bundle/scripts/discord_mb.py channels osc create overflow-backups \
  --topic "Overflow encrypted off-host database backups"
~/.agent-bundle/scripts/discord_mb.py channels osc list

Copy the numeric channel id from the list output.

On the host. Add the off-host entries to /etc/overflow/backup.env (root:root 0600, created in (c)). OVERFLOW_BACKUP_AGE_RECIPIENT is the public key age-keygen printed (age1...) and OVERFLOW_BACKUP_DISCORD_CHANNEL is the numeric channel id:

OVERFLOW_BACKUP_AGE_RECIPIENT="age1..."
OVERFLOW_BACKUP_DISCORD_CHANNEL="<numeric channel id>"

No DISCORD_TOKEN line: the token is not configured in the file at all. The unit loads ~/.agent-bundle/discord/osc.token as a systemd credential at every start and the job bridges it to DISCORD_TOKEN in the process environment, so rotating the osc token edits only that canonical file and needs no Overflow-side edit — the next unit start picks the new value up.

The optional OVERFLOW_BACKUP_MAX_BYTES overrides the 9.5 MiB posting limit; nothing else in the file changes, and the full backup keeps running unchanged.

Install and enable the units, the same shape as (c):

install -o root -g root -m 0644 \
  /srv/overflow/deploy/overflow-offhost-backup.service /etc/systemd/system/
install -o root -g root -m 0644 \
  /srv/overflow/deploy/overflow-offhost-backup.timer /etc/systemd/system/
systemd-analyze verify /etc/systemd/system/overflow-offhost-backup.service \
  /etc/systemd/system/overflow-offhost-backup.timer
systemctl daemon-reload
systemctl enable --now overflow-offhost-backup.timer

enable --now is right for a timer, as (c) says — there is no serving process to switch.

Read back what a run leaves, after the first 02:10 UTC firing:

systemctl list-timers overflow-offhost-backup.timer
systemctl show overflow-offhost-backup.service -p OnFailure
journalctl -u overflow-offhost-backup.service -n 50 --no-pager

and that an overflow-reduced-<UTC timestamp>.sql.xz.age file appeared in /var/backups/overflow and the message appeared in the backups channel. A failed run alerts through the same OnFailure= route as (h). The drill that proves this path end to end — fetch, decrypt, restore, compare, rederive — is (e.4) read against the posted copies.