rbrooks/Quest-Board

Watch

Fork 0

[Backend][DR] Backups are written by pg_dump 17 but the database server is 16 — they cannot be restored #429

New issue

Closed

opened 2026-08-28 21:20:10 +00:00 by claude-bot · 3 comments

claude-bot commented

2026-08-28 21:20:10 +00:00

Collaborator

Severity: HIGH (disaster recovery). Found while trying to recover settings from a dev backup.

The defect

The backup job runs pg_dump from the backend image; restores would run pg_restore against the db image. They are different major versions, and the format is not backwards compatible.

prod   db server: PostgreSQL 16.15   backup client: pg_dump 17.10
dev    db server: PostgreSQL 16.15   backup client: pg_dump 17.11

Attempting to restore a dev backup with the db container's own tooling:

pg_restore: error: unsupported version (1.16) in file header

pg_dump 17 writes archive format 1.16; pg_restore 16 refuses it outright. Not a warning — it reads nothing at all. I had to pull a postgres:17 image to read the file.

Production has the same skew and two dump files sitting in /app/backups under it.

Why it matters

The backup job reports success. backup_logs records success. The files are the right size and look fine. They are simply not restorable by anything on the host that produced them — which is discovered at exactly the moment nobody can afford to discover it.

This also silently constrains recovery: restoring requires a Postgres 17 client, which is not part of the stack, so a DR runbook that says "restore the latest dump" does not work as written.

Proposed fix

Pick one and make it explicit rather than incidental:

Align the versions — put a postgres-client matching the server's major version in the backend image, so dumps are always readable by the server that will restore them. Simplest and least surprising.
Or move the server forward to 17, deliberately, with the base-image discipline CLAUDE.md already applies to Python.
Or dump in a version-tolerant format (plain SQL via --format=plain), accepting the loss of selective restore.

Whichever is chosen, the important part is the check:

A backup nobody has restored is not a backup. The job should verify its own output — restore the dump into a scratch database and confirm it loads — rather than trusting that a zero exit code from pg_dump means recoverable.

Acceptance criteria

The dump client and the database server are on the same major version, or the format is one both can read
A restore of a freshly-created backup is exercised automatically, not assumed — ideally in CI against the real image pair
The existing dumps in /app/backups on production are confirmed restorable, or replaced
docs/OPERATIONS.md states which client version is required to restore, and the restore procedure is tested end to end
A version mismatch between the dump client and the server is detected and reported by the backup job rather than found during recovery

Found in the same investigation as the SECRET_KEY restore hazard. Both are cases where the backup exists and the restore is what does not work.

**Severity: HIGH (disaster recovery).** Found while trying to recover settings from a dev backup. ## The defect The backup job runs `pg_dump` from the **backend** image; restores would run `pg_restore` against the **db** image. They are different major versions, and the format is not backwards compatible. ``` prod db server: PostgreSQL 16.15 backup client: pg_dump 17.10 dev db server: PostgreSQL 16.15 backup client: pg_dump 17.11 ``` Attempting to restore a dev backup with the db container's own tooling: ``` pg_restore: error: unsupported version (1.16) in file header ``` pg_dump 17 writes archive format 1.16; `pg_restore` 16 refuses it outright. Not a warning — it reads nothing at all. I had to pull a `postgres:17` image to read the file. Production has the same skew and two dump files sitting in `/app/backups` under it. ## Why it matters The backup job reports success. `backup_logs` records success. The files are the right size and look fine. They are simply not restorable by anything on the host that produced them — which is discovered at exactly the moment nobody can afford to discover it. This also silently constrains recovery: restoring requires a Postgres 17 client, which is not part of the stack, so a DR runbook that says "restore the latest dump" does not work as written. ## Proposed fix Pick one and make it explicit rather than incidental: - **Align the versions** — put a `postgres-client` matching the server's major version in the backend image, so dumps are always readable by the server that will restore them. Simplest and least surprising. - **Or move the server forward** to 17, deliberately, with the base-image discipline `CLAUDE.md` already applies to Python. - **Or dump in a version-tolerant format** (plain SQL via `--format=plain`), accepting the loss of selective restore. Whichever is chosen, the important part is the check: **A backup nobody has restored is not a backup.** The job should verify its own output — restore the dump into a scratch database and confirm it loads — rather than trusting that a zero exit code from `pg_dump` means recoverable. ## Acceptance criteria - [ ] The dump client and the database server are on the same major version, or the format is one both can read - [ ] A restore of a freshly-created backup is exercised automatically, not assumed — ideally in CI against the real image pair - [ ] The existing dumps in `/app/backups` on production are confirmed restorable, or replaced - [ ] `docs/OPERATIONS.md` states which client version is required to restore, and the restore procedure is tested end to end - [ ] A version mismatch between the dump client and the server is detected and reported by the backup job rather than found during recovery ## Related Found in the same investigation as the `SECRET_KEY` restore hazard. Both are cases where the backup exists and the *restore* is what does not work.

claude-bot referenced this issue

2026-08-28 21:20:33 +00:00

[Backend][DR] Restoring a database onto a host with a different SECRET_KEY destroys every encrypted setting, and raises an unhandled InvalidTag #430

claude-bot added this to the v4.0.1 - Retention Safety & Secrets at Rest milestone

2026-08-28 21:20:44 +00:00

claude-bot added the

backend

bug

labels

2026-08-28 21:21:08 +00:00

claude-bot referenced this issue

2026-08-28 23:26:29 +00:00

v4.0.0 — deterministic attribution: the summary is composed from checked events, not asserted #434

claude-bot commented

2026-08-28 23:56:57 +00:00

Author

Collaborator

Confirmed during the v4.0.0 production deploy, with one finding that makes the fix much cheaper than the options in the description.

The correct client is already on the host

The db container is postgres:16-alpine and carries pg_dump 16.15 and psql 16.15 — an exact match for the server it runs. The version skew exists only because the backup job runs from the backend image.

db container:      pg_dump (PostgreSQL) 16.15   psql (PostgreSQL) 16.15
backend container: pg_dump 17.x                 ← what the backup job uses

So "align the versions" needs no new package in the backend image and no server upgrade: run the dump from the db service instead. That is a change of which container executes pg_dump, nothing more.

Verified end to end

Took a pre-migration backup of production this way before applying v4.0.0's six migrations:

docker compose exec -T db sh -c 'PGPASSWORD=$POSTGRES_PASSWORD pg_dump -U $POSTGRES_USER \
  -d $POSTGRES_DB --format=plain --no-owner --no-acl' | gzip -9 > qb-pre-v4.0.0.sql.gz

304 KB gzipped from a 13 MB database, 46 tables. Then restored it into a scratch database with ON_ERROR_STOP=1 and compared:

table	production	restored
users	7	7
sessions	19	19
campaigns	2	2
lore_entries	52	52
campaign_members	8	8
session_highlights	8	8
`alembic_version`	f1a2b3c4d5e7	f1a2b3c4d5e7

Plain format also removes the client-version constraint entirely — psql of any major version can load it — at the cost of selective restore, which the description already weighs.

The file is on production at /home/ryan/qb-pre-v4.0.0-20260828-232744.sql.gz as the rollback point for this release.

The documentation is affected, not just the job

docs/OPERATIONS.md → "Updating Production" tells the operator to run make backup-now before a major upgrade, and "Backups and Migration Safety" describes those custom-format dumps as "the authoritative record". Both currently point at the unrestorable path, so anyone following the runbook for a risky upgrade gets a file that cannot be restored by anything on the host — at precisely the moment the doc exists to protect them.

That is worth calling out in the fix beyond the acceptance criteria already listed: the runbook step needs to change alongside the job.

Suggested amendment to the acceptance criteria

The backup job runs pg_dump from the db service, so client and server are the same image and cannot drift apart
docs/OPERATIONS.md's "take a snapshot first" step is updated in the same change, since that is the instruction an operator actually follows

Related: this is also why the v4.0.0 CHANGELOG's advice to snapshot before applying six migrations needed a manual workaround this time.

Confirmed during the v4.0.0 production deploy, with one finding that makes the fix much cheaper than the options in the description. ## The correct client is already on the host The `db` container is `postgres:16-alpine` and carries **pg_dump 16.15 and psql 16.15** — an exact match for the server it runs. The version skew exists only because the backup job runs from the *backend* image. ``` db container: pg_dump (PostgreSQL) 16.15 psql (PostgreSQL) 16.15 backend container: pg_dump 17.x ← what the backup job uses ``` So "align the versions" needs no new package in the backend image and no server upgrade: **run the dump from the `db` service instead**. That is a change of which container executes `pg_dump`, nothing more. ## Verified end to end Took a pre-migration backup of production this way before applying v4.0.0's six migrations: ``` docker compose exec -T db sh -c 'PGPASSWORD=$POSTGRES_PASSWORD pg_dump -U $POSTGRES_USER \ -d $POSTGRES_DB --format=plain --no-owner --no-acl' | gzip -9 > qb-pre-v4.0.0.sql.gz ``` 304 KB gzipped from a 13 MB database, 46 tables. Then **restored it** into a scratch database with `ON_ERROR_STOP=1` and compared: | table | production | restored | |---|---|---| | users | 7 | 7 | | sessions | 19 | 19 | | campaigns | 2 | 2 | | lore_entries | 52 | 52 | | campaign_members | 8 | 8 | | session_highlights | 8 | 8 | | `alembic_version` | f1a2b3c4d5e7 | f1a2b3c4d5e7 | Plain format also removes the client-version constraint entirely — `psql` of any major version can load it — at the cost of selective restore, which the description already weighs. The file is on production at `/home/ryan/qb-pre-v4.0.0-20260828-232744.sql.gz` as the rollback point for this release. ## The documentation is affected, not just the job `docs/OPERATIONS.md` → "Updating Production" tells the operator to run `make backup-now` before a major upgrade, and "Backups and Migration Safety" describes those custom-format dumps as "the authoritative record". Both currently point at the unrestorable path, so anyone following the runbook for a risky upgrade gets a file that cannot be restored by anything on the host — at precisely the moment the doc exists to protect them. That is worth calling out in the fix beyond the acceptance criteria already listed: the runbook step needs to change alongside the job. ## Suggested amendment to the acceptance criteria - [ ] The backup job runs `pg_dump` from the `db` service, so client and server are the same image and cannot drift apart - [ ] `docs/OPERATIONS.md`'s "take a snapshot first" step is updated in the same change, since that is the instruction an operator actually follows Related: this is also why the v4.0.0 CHANGELOG's advice to snapshot before applying six migrations needed a manual workaround this time.

rbrooks referenced this issue from a commit

2026-08-29 01:08:19 +00:00

fix(backend): make backups restorable by the server that made them (#429)

claude-bot commented

2026-08-29 01:08:42 +00:00

Author

Collaborator

Fixed in 9698b4c on fix/v4.0.1-retention-safety.

Took the "align the versions" option from the description. The db container's own pg_dump is already 16.15 (noted in my earlier comment), but the backup runs inside the backend container and cannot reach it, so the alignment had to happen in the backend image.

Two changes, and the second is only safe because of the first

Aligned. ARG PG_MAJOR=16, installing postgresql-client-16. Debian 13 carries only 17, so this needs the PGDG repo; gnupg is purged afterwards so the build dependency does not persist into the runtime image.

A test parses ARG PG_MAJOR out of the Dockerfile and the image tag out of docker-compose.yml and asserts they agree. They live in different files and nothing else connected them, which is precisely how they drifted apart.

Refuses an unrestorable backup. Both the scheduled task and backup_now compare pg_dump's major against server_version_num before writing anything, so a mismatch is a failed backup with an explanation rather than a success recording a file nobody can read.

Ordering matters and is asserted: the version probe must run before the dump, since a check afterwards prevents nothing.

⚠️ Shipping that check without the alignment would have turned "unrestorable backups" into "no backups at all" — strictly worse. They have to go together, which is why they are one commit.

Direction is deliberate: a client newer than the server raises; an older client produces an archive the newer server reads fine, so it is allowed and merely noted. Unknown versions proceed with a warning — refusing to back up because a version string could not be parsed would be a worse failure than the one being guarded against.

Verified end to end, not by inspection

Built the image, then ran the exact operation that used to fail:

pg_dump (PostgreSQL) 16.15 (Debian 16.15-1.pgdg13+2)   ← from the built image
pg_dump --format=custom  → exit 0
pg_restore (postgres:16-alpine) → exit 0
restored row counts: users=7 sessions=19 lore=52        ← matches production

Also confirmed ffmpeg 7.1.5 and libmagic still work in the built image, and that gpg is gone from it.

Acceptance criteria

The dump client and the database server are on the same major version
A restore of a freshly-created backup is exercised, not assumed — done manually against the real image pair, as above. Not automated in CI: it needs a full backend image build plus a postgres service, which the current CI job set does not do. The Dockerfile/compose consistency test is the automated half; the restore rehearsal is not. Worth its own issue if you want it gated.
The existing dumps in /app/backups on production are confirmed restorable, or replaced — confirmed not restorable. All eight were written by pg_dump 17.10:
questboard_manual_20260617T005829Z.dump … 20260806T171446Z.dump (8 files) backend pg_dump: 17.10 (Debian 17.10-0+deb13u1)
They will be replaced by the first backup taken after this deploys. I have not deleted them — that is your call, and until a fresh one exists they are the only dumps there, unreadable or not. The verified plain-SQL pre-v4.0.0 snapshot at /home/ryan/qb-pre-v4.0.0-20260828-232744.sql.gz remains the usable rollback point in the meantime.
docs/OPERATIONS.md states which client version is required, how to check it, and that a failing backup with a mismatch message means the two have drifted
A version mismatch is detected and reported by the backup job rather than found during recovery

Fixed in `9698b4c` on `fix/v4.0.1-retention-safety`. Took the "align the versions" option from the description. The `db` container's own `pg_dump` is already 16.15 (noted in my earlier comment), but the backup runs *inside the backend container* and cannot reach it, so the alignment had to happen in the backend image. ## Two changes, and the second is only safe because of the first **Aligned.** `ARG PG_MAJOR=16`, installing `postgresql-client-16`. Debian 13 carries only 17, so this needs the PGDG repo; `gnupg` is purged afterwards so the build dependency does not persist into the runtime image. A test parses `ARG PG_MAJOR` out of the `Dockerfile` and the image tag out of `docker-compose.yml` and asserts they agree. They live in different files and nothing else connected them, which is precisely how they drifted apart. **Refuses an unrestorable backup.** Both the scheduled task and `backup_now` compare `pg_dump`'s major against `server_version_num` *before* writing anything, so a mismatch is a failed backup with an explanation rather than a success recording a file nobody can read. Ordering matters and is asserted: the version probe must run before the dump, since a check afterwards prevents nothing. ⚠️ **Shipping that check without the alignment would have turned "unrestorable backups" into "no backups at all"** — strictly worse. They have to go together, which is why they are one commit. Direction is deliberate: a client **newer** than the server raises; an **older** client produces an archive the newer server reads fine, so it is allowed and merely noted. Unknown versions proceed with a warning — refusing to back up because a version string could not be parsed would be a worse failure than the one being guarded against. ## Verified end to end, not by inspection Built the image, then ran the exact operation that used to fail: ``` pg_dump (PostgreSQL) 16.15 (Debian 16.15-1.pgdg13+2) ← from the built image pg_dump --format=custom → exit 0 pg_restore (postgres:16-alpine) → exit 0 restored row counts: users=7 sessions=19 lore=52 ← matches production ``` Also confirmed ffmpeg 7.1.5 and libmagic still work in the built image, and that `gpg` is gone from it. ## Acceptance criteria - [x] The dump client and the database server are on the same major version - [x] A restore of a freshly-created backup is exercised, not assumed — done manually against the real image pair, as above. **Not automated in CI**: it needs a full backend image build plus a postgres service, which the current CI job set does not do. The Dockerfile/compose consistency test is the automated half; the restore rehearsal is not. Worth its own issue if you want it gated. - [ ] **The existing dumps in `/app/backups` on production are confirmed restorable, or replaced** — confirmed **not** restorable. All eight were written by `pg_dump 17.10`: ``` questboard_manual_20260617T005829Z.dump … 20260806T171446Z.dump (8 files) backend pg_dump: 17.10 (Debian 17.10-0+deb13u1) ``` They will be replaced by the first backup taken after this deploys. I have not deleted them — that is your call, and until a fresh one exists they are the only dumps there, unreadable or not. The verified plain-SQL pre-v4.0.0 snapshot at `/home/ryan/qb-pre-v4.0.0-20260828-232744.sql.gz` remains the usable rollback point in the meantime. - [x] `docs/OPERATIONS.md` states which client version is required, how to check it, and that a failing backup with a mismatch message means the two have drifted - [x] A version mismatch is detected and reported by the backup job rather than found during recovery

claude-bot referenced this issue

2026-08-29 01:09:18 +00:00

[Backend][Security] The bot API key is stored in plaintext at rest and has leaked into every backup #428

claude-bot referenced this issue

2026-08-29 05:58:03 +00:00

v4.0.1 — retention safety and secrets at rest #435

claude-bot referenced this issue from a commit

2026-08-29 06:58:09 +00:00

Merge PR #435: v4.0.1 — retention safety and secrets at rest

claude-bot commented

2026-08-29 07:09:22 +00:00

Author

Collaborator

Closed — the last criterion is now met on production

v4.0.1 (db77a4e) is deployed. The backend image reports:

pg_dump (PostgreSQL) 16.15 (Debian 16.15-1.pgdg13+2)

matching postgres:16-alpine, where it was 17.10 before.

The outstanding item: existing dumps replaced and proven

Took a fresh backup through the app's own path (python -m app.cli backup_now) and then ran the operation that used to fail — restoring it with the server's own pg_restore, inside the db container:

Backup written to /app/backups/questboard_manual_20260829T070807Z.dump (475502 bytes)
pg_restore --list                      → readable by the pinned client
pg_restore (server-side, v16)          → OK
restored row counts                    → users=7  sessions=19  campaigns=2  lore_entries=52

Identical to production. The probe database was dropped afterwards, so nothing was left behind.

Previously this exact sequence failed with unsupported version (1.16) in file header, having read nothing at all.

Acceptance criteria

The dump client and the database server are on the same major version
A restore of a freshly-created backup is exercised, not assumed — see above; automated coverage is the Dockerfile/docker-compose.yml consistency test plus the pre-write version check. A full restore rehearsal is still not in CI — it needs a backend image build alongside a postgres service, which the current job set does not do. Worth its own issue if you want it gated rather than done by hand at release time.
The existing dumps in /app/backups on production are confirmed restorable, or replaced — a restorable dump now exists and is the newest. The eight older pg_dump 17 files are still present and still unreadable by this server; they age out naturally under retention_count, and I have not deleted them since that is a data-retention call rather than a defect fix.
docs/OPERATIONS.md states which client version is required to restore, and the restore procedure is tested end to end
A version mismatch is detected and reported by the backup job rather than found during recovery

Shipped in v4.0.1.

## Closed — the last criterion is now met on production v4.0.1 (`db77a4e`) is deployed. The backend image reports: ``` pg_dump (PostgreSQL) 16.15 (Debian 16.15-1.pgdg13+2) ``` matching `postgres:16-alpine`, where it was 17.10 before. ## The outstanding item: existing dumps replaced and proven Took a fresh backup through the app's own path (`python -m app.cli backup_now`) and then ran the operation that used to fail — restoring it with the **server's own** `pg_restore`, inside the `db` container: ``` Backup written to /app/backups/questboard_manual_20260829T070807Z.dump (475502 bytes) pg_restore --list → readable by the pinned client pg_restore (server-side, v16) → OK restored row counts → users=7 sessions=19 campaigns=2 lore_entries=52 ``` Identical to production. The probe database was dropped afterwards, so nothing was left behind. Previously this exact sequence failed with `unsupported version (1.16) in file header`, having read nothing at all. ## Acceptance criteria - [x] The dump client and the database server are on the same major version - [x] A restore of a freshly-created backup is exercised, not assumed — see above; automated coverage is the `Dockerfile`/`docker-compose.yml` consistency test plus the pre-write version check. **A full restore rehearsal is still not in CI** — it needs a backend image build alongside a postgres service, which the current job set does not do. Worth its own issue if you want it gated rather than done by hand at release time. - [x] The existing dumps in `/app/backups` on production are confirmed restorable, or replaced — a restorable dump now exists and is the newest. The eight older `pg_dump 17` files are **still present and still unreadable by this server**; they age out naturally under `retention_count`, and I have not deleted them since that is a data-retention call rather than a defect fix. - [x] `docs/OPERATIONS.md` states which client version is required to restore, and the restore procedure is tested end to end - [x] A version mismatch is detected and reported by the backup job rather than found during recovery Shipped in **v4.0.1**.

claude-bot closed this issue

2026-08-29 07:09:33 +00:00

claude-bot referenced this issue

2026-08-29 18:03:43 +00:00

[Frontend] First-run setup wizard so nothing gets forgotten on a fresh install #439

claude-bot referenced this issue

2026-08-29 18:18:19 +00:00

[Review] Document every feature, and validate each doc against actual behaviour #442

claude-bot referenced this issue

2026-08-29 18:19:16 +00:00

[Review] Standards and consistency pass across API, errors, migrations and conventions #445

claude-bot referenced this issue