Runbook: V1 Database Loss (gd:database-loss)

Owner: workbench-platform on-call (rota:workbench-platform). Last reviewed: 2026-08-14. Version: 1.

The relational store is unavailable. It is the substrate under the search index and the scan fallback, so it takes both with it. Distinct from v1-dr-relational-corruption.md because the recovery is a restart or a restore rather than a rollback, and the recovery point is untouched.

There is a failover script, and this estate has nothing to fail over to. Read §9 before reaching for it.

1. Detection and declaration#

Detection signal. Sixteen capabilities stop at once, including the ones an actor tries first: "open an item", "save an edit", "record a review decision". slo:item-read-availability and slo:save-durability move immediately, and pg_isready against the host answers the narrow question directly.

Unlike a corruption, this announces itself. The declaration is not the hard part; §9 is.

Detection authority: workbench-platform on-call, contact rota:workbench-platform.
Declaration authority: production operations duty officer, contact rota:prod-ops.
Gap, stated. docs/operations/on-call.md describes a weekly primary/secondary rotation and names no person and no rotation config; none of the register's rota: identifiers appears in it or in docs/operations/alert-routing.md. The name above is registered; the route from it to whoever is carrying the pager is not.

2. Communications#

Audience	Within	Content
Actors	15 min	that the workbench is unavailable, and whether edits in flight were acknowledged
Operators	5 min	that this runbook is running, and by whom

Both travel over dep:notification-channel, which this scenario leaves standing — the fault is confined to the store, not to the host.

3. Dependency assumptions#

dep:blob-store is healthy and stays where it is. The rows reference blob keys; bringing the rows back against a blob store that also moved is two recoveries at once.
dep:job-queue is healthy. Its durable record is the admin_store_snapshot table inside the store that is down, so accepted work is not lost with the process — see the preamble of v1-dr-queue-loss.md.

4. Recovery infrastructure#

In place. PostgreSQL is a service in docker/docker-compose.yml. If the container is gone and the volume survives, the recovery is a restart. If the volume is gone, it is a restore, and §6 is that.

5. Secrets#

PostgreSQL credentials (PG_HOST, PG_PORT, PG_USER, PGPASSWORD), the same variables scripts/db-backup.sh and scripts/db-restore.sh use.
The restic repository and password, if the dumps come from the off-box snapshot.

Recoverable out of band: offbox-backup.sh refuses a repository local to the V1 host, and the configuration-and-secrets state class is in the capture.

6. Restore order#

dep:primary-store — restart, or restore with scripts/db-restore.sh BACKUP_DIR. It verifies every SHA-256 in manifest.txt before invoking pg_restore and aborts with exit code 2 on mismatch; it never touches the source files.
dep:primary-store-scan — the scan reads the same rows and is correct as soon as they are.
dep:search-index — last. The index is projected from the rows, and one rebuilt before the restore completes is stale in a way that looks healthy.

dep:blob-store is not in this order because it is not taken away. If it were, it would come first — a row exposed before its bytes is a dangling reference. That is the reverse of the capture order and the same rule §6 of v1-dr-blob-store-loss.md states from the other side.

7. Validation#

db-restore.sh manifest verification, which runs before any data is written.
SELECT count(*) >= 0 FROM pg_catalog.pg_class per restored database — the floor infra/hetzner/backup/box-loss-restore-drill.sh applies, and the only count floor that drill has.
A named count, recorded at a named instant. An empty restore answers every check above. Record what the estate held before the loss and compare.

8. Fencing — ABSENT for a promoted primary#

The estate has exactly one real fencing token and it does not cover a store: live_media_pipeline_job_attempts.lease_token, in libs/shared/live-media/src/sql-media-pipeline-job-store.ts, appears in the UPDATE ... WHERE of every heartbeat, lease expiry and completion as AND lease_token = $4 AND status = 'leased', so a worker whose lease was reassigned updates zero rows. That is lease fencing and it works. Nothing does the equivalent for PostgreSQL: no token rejects the writes of a primary that comes back after a replacement was promoted.

For a restart-in-place this costs nothing, because there is only ever one primary. It is §9 that makes the absence expensive, and §9 is where this estate would need it — and where the lease_token pattern is the one already in the tree to copy.

9. Failover — the script exists and there is nothing to promote#

infra/yemaya/postgres-ha/failover.sh is a real standby-promotion script: pg_ctl promote, a replication-lag check, a PgBouncer repoint. It does not apply to the V1 estate, and reaching for it under pressure is the failure mode this section exists to prevent.

Three measured facts, in the order an operator meets them:

There is no standby. docker/docker-compose.yml declares exactly one PostgreSQL service and no service named for a standby, a replica or a pooler. (Search the file text rather than its service names and you will match KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR, which is not a Postgres replica — an answer about the wrong subject in the right shape.) Nothing outside infra/yemaya/postgres-ha/ references that directory at all — not the V1 compose, not a deploy script, not CI. The configuration with archive_mode = on in infra/yemaya/postgres-ha/primary.conf is not the configuration this estate runs.
The script refuses, correctly. check_standby runs SELECT pg_is_in_recovery() and exits with "This server is not in recovery mode (not a standby)". This is the honest failure and it is the one you will get.
If it did run here, two of its steps would mislead you. update_pgbouncer() logs PgBouncer update requires manual configuration or automation and returns 0 — it does nothing and reports success, so do_failover continues as though the pooler had been repointed. And promote_standby calls read -p for confirmation when replication lag exceeds sixty seconds, so it cannot run unattended: under automation it blocks on stdin rather than promoting.

So the V1 answer to a database loss is restart or restore, not failover. If a standby is ever added, this section and promotesAStandby in §13 must change together, and §8 stops being free: failover.sh prompts Force failover anyway? This may cause split-brain! and proceeds on yes, with no token rejecting the old primary's writes. A human typing yes is the only thing between that script and two primaries.

10. Reconciliation#

Run scripts/operations/v1-restore-reconcile.mjs before resuming autonomous or effectful work, and treat it as mandatory when the recovery was a restore rather than a restart. A restart loses nothing the store had committed; a restore rolls the estate back to the last dump, and everything the outside world observed after that instant is now unrecorded.

Give it expected counts. Empty reconciles clean.

11. Failback#

There is no alternate topology to return from, because §9 established there was nowhere to go. Failback is the resumption of writes, gated on the reconciler returning no blockers, followed immediately by a fresh backup: after a restore, the estate's most recent capture is the one it was restored from and it predates the incident.

12. What this runbook does not cover#

A store that answers with wrong rows — see v1-dr-relational-corruption.md.
A total host loss — see v1-disaster-recovery.md.
Standing up replication. infra/yemaya/postgres-ha/README.md documents that setup; adopting it for V1 is a change, not a recovery step.

13. Machine-checkable statement#

json

{
  "id": "runbook:v1-database-loss",
  "scenarioId": "gd:database-loss",
  "version": "1",
  "detectionAuthority": {
    "role": "service on-call",
    "name": "workbench-platform on-call",
    "reachableOver": ["dep:notification-channel"]
  },
  "declarationAuthority": {
    "role": "operations duty officer",
    "name": "production operations duty officer",
    "reachableOver": ["dep:notification-channel"]
  },
  "communications": [
    {
      "audience": "actors",
      "channelDependencyId": "dep:notification-channel",
      "withinMs": 900000
    },
    {
      "audience": "operators",
      "channelDependencyId": "dep:notification-channel",
      "withinMs": 300000
    }
  ],
  "assumes": ["dep:blob-store", "dep:job-queue"],
  "recoveryInfrastructureDependencyId": null,
  "secrets": [
    {
      "id": "PGPASSWORD",
      "heldInDependencyId": "dep:primary-store",
      "recoverableOutOfBand": true
    },
    {
      "id": "restic repository password",
      "heldInDependencyId": "dep:primary-store",
      "recoverableOutOfBand": true
    }
  ],
  "restoreOrder": [
    "dep:primary-store",
    "dep:primary-store-scan",
    "dep:search-index"
  ],
  "fencing": null,
  "promotesAStandby": false,
  "validation": [
    "db-restore.sh verifies every SHA-256 in manifest.txt and aborts with exit code 2 on mismatch",
    "each restored database answers SELECT count(*) >= 0 FROM pg_catalog.pg_class",
    "recorded counts from before the loss are compared against the restored counts"
  ],
  "failback": "resume writes once v1-restore-reconcile.mjs returns no blockers, then take a fresh backup; there is no alternate topology to return from because there is no standby"
}