[Ops] Upgrade PostgreSQL 16 → 18: compose, client, CI, and a dump/restore upgrade for existing data

claude-bot commented

2026-10-27 17:32:12 +00:00

Collaborator

Renovate's #627 (postgres tag → 18) cannot be merged as is. It bumps 3 of 4 pins and gives existing data no way to move. This issue replaces it with one PR. Investigation 2026-10-27; nothing is built yet.

Why #627 fails

Backend: test_backup_client_version.py::test_the_image_and_the_compose_db_agree: "Dockerfile installs postgresql-client-16 but the db service runs postgres:18". Renovate cannot see ARG PG_MAJOR=16 (Dockerfile:44).
E2E x3: db exits (1) on start. Likely cause: the 18 image moved PGDATA to /var/lib/postgresql/18/docker and expects the volume at /var/lib/postgresql, while ours mount at /var/lib/postgresql/data (docker-compose.yml:53, docker-compose.e2e.yml:77). The db log was not pulled, so this is unverified.

The trap

If the mount moves to /var/lib/postgresql on the same volume, 18 finds nothing at 18/docker and runs initdb. The result is an empty cluster in the old volume, migrate builds an empty schema, and the app looks like its data is gone. Use a new volume (postgres18_data).

Plan

Upgrade path: dump/restore (not pg_upgrade: the tianon images are glibc and ours are alpine/musl, so text indexes risk silent collation breakage; it would also need checksum matching).

Operator steps (make pg-upgrade, run from the new tag before up):

make backup-now.
Stop the app services.
Dump with the old db container's own pg_dump 16.
Bring up the new db on postgres18_data; init.sh creates the roles.
pg_restore 18 with the documented flags, then ANALYZE.
Verify: row counts, alembic at head, /ready, /api/meta, and make backup-now working again.
up -d.

Rollback: check out the previous tag. The old volume is untouched.

Release note warning: the new backend image (client 18) refuses backups against a 16 server (check_dump_client_compatibility), so migrate-safe fails safe until the db is upgraded. The release notes must say: do not up -d before make pg-upgrade.

Repo changes

docker-compose.yml and docker-compose.e2e.yml: postgres:18-alpine and postgres18_data:/var/lib/postgresql. Keep postgres_data declared.
Dockerfile: PG_MAJOR=18, and e2e-db built FROM postgres:18-alpine.
ci.yml:98: postgres:18.
The compose-agreement test also asserts the mount path.
CLAUDE.md test recipes (postgres:16 → 18).
scripts/pg-upgrade.sh with a Makefile target and tests (migrate-safe.sh style), refusing when the old volume is missing or empty or the new one already holds data.
docs/OPERATIONS.md "Upgrading PostgreSQL", plus updates at :1482 and :2978, and ARCHITECTURE.md:53.
A changelog fragment.
A Renovate rule grouping postgres across managers with majors disabled, and optionally a regex manager for PG_MAJOR.
Rehearse on dev with an rc. Prod only on the owner's say.

Questions for the owner

Should a plain up -d without the upgrade be made to hard-fail, or are the script guard and a docs warning enough?
Prod downtime window. Minutes at our size.
How long to keep the old postgres_data volume before removing it?
Its own release, or inside v4.7.0?
Hold postgres majors in Renovate permanently?

Supersedes #627.

Renovate's #627 (`postgres` tag → 18) cannot be merged as is. It bumps 3 of 4 pins and gives existing data no way to move. This issue replaces it with one PR. Investigation 2026-10-27; nothing is built yet. ## Why #627 fails - **Backend:** `test_backup_client_version.py::test_the_image_and_the_compose_db_agree`: "Dockerfile installs postgresql-client-16 but the db service runs postgres:18". Renovate cannot see `ARG PG_MAJOR=16` (`Dockerfile:44`). - **E2E x3:** `db` exits (1) on start. Likely cause: the 18 image moved PGDATA to `/var/lib/postgresql/18/docker` and expects the volume at `/var/lib/postgresql`, while ours mount at `/var/lib/postgresql/data` (`docker-compose.yml:53`, `docker-compose.e2e.yml:77`). The db log was not pulled, so this is unverified. ## The trap If the mount moves to `/var/lib/postgresql` on the **same** volume, 18 finds nothing at `18/docker` and runs initdb. The result is an empty cluster in the old volume, `migrate` builds an empty schema, and the app looks like its data is gone. **Use a new volume** (`postgres18_data`). ## Plan **Upgrade path: dump/restore** (not pg_upgrade: the tianon images are glibc and ours are alpine/musl, so text indexes risk silent collation breakage; it would also need checksum matching). **Operator steps** (`make pg-upgrade`, run from the new tag before `up`): 1. `make backup-now`. 2. Stop the app services. 3. Dump with the **old db container's own** `pg_dump` 16. 4. Bring up the new db on `postgres18_data`; `init.sh` creates the roles. 5. `pg_restore` 18 with the documented flags, then `ANALYZE`. 6. Verify: row counts, alembic at head, `/ready`, `/api/meta`, and `make backup-now` working again. 7. `up -d`. **Rollback:** check out the previous tag. The old volume is untouched. **Release note warning:** the new backend image (client 18) refuses backups against a 16 server (`check_dump_client_compatibility`), so `migrate-safe` fails safe until the db is upgraded. The release notes must say: do not `up -d` before `make pg-upgrade`. ## Repo changes - `docker-compose.yml` and `docker-compose.e2e.yml`: `postgres:18-alpine` and `postgres18_data:/var/lib/postgresql`. Keep `postgres_data` declared. - `Dockerfile`: `PG_MAJOR=18`, and `e2e-db` built `FROM postgres:18-alpine`. - `ci.yml:98`: `postgres:18`. - The compose-agreement test also asserts the mount path. - CLAUDE.md test recipes (`postgres:16` → 18). - `scripts/pg-upgrade.sh` with a Makefile target and tests (`migrate-safe.sh` style), refusing when the old volume is missing or empty or the new one already holds data. - `docs/OPERATIONS.md` "Upgrading PostgreSQL", plus updates at `:1482` and `:2978`, and `ARCHITECTURE.md:53`. - A changelog fragment. - A Renovate rule grouping postgres across managers with majors disabled, and optionally a regex manager for `PG_MAJOR`. - Rehearse on dev with an rc. Prod only on the owner's say. ## Questions for the owner 1. Should a plain `up -d` without the upgrade be made to hard-fail, or are the script guard and a docs warning enough? 2. Prod downtime window. Minutes at our size. 3. How long to keep the old `postgres_data` volume before removing it? 4. Its own release, or inside v4.7.0? 5. Hold postgres majors in Renovate permanently? Supersedes #627.

claude-bot added this to the v4.7.0 - Browse the Rules milestone

2026-10-27 17:38:59 +00:00

claude-bot commented

2026-10-27 17:39:07 +00:00

Author

Collaborator

Owner answers, 27 Oct 2026:

Q2, downtime: prod can be down as long as the upgrade needs, within an agreed window (today's was until about 17:00 Central). Book the window when v4.7.0 is ready.
Q3, old volume: keep the old postgres_data in backups. It can be removed once that is done. So make pg-upgrade should also archive the old volume (or at least the pre-upgrade pg_dump 16 dump) into the backups location, and the docs give the removal command for afterwards.
Q4, release: ships in v4.7.0, unless a point release is needed urgently for something else.
Q5, Renovate: do not hold majors. Keep taking updates and fix whatever breaks. So no "majors disabled" rule. Still group postgres across managers, and add a regex manager for ARG PG_MAJOR so the next major arrives as one complete PR.
Q1 (a plain up -d): explained to the owner, answer pending.

**Owner answers, 27 Oct 2026:** - **Q2, downtime:** prod can be down as long as the upgrade needs, within an agreed window (today's was until about 17:00 Central). Book the window when v4.7.0 is ready. - **Q3, old volume:** keep the old `postgres_data` in backups. It can be removed once that is done. So `make pg-upgrade` should also archive the old volume (or at least the pre-upgrade `pg_dump` 16 dump) into the backups location, and the docs give the removal command for afterwards. - **Q4, release:** ships in **v4.7.0**, unless a point release is needed urgently for something else. - **Q5, Renovate:** do **not** hold majors. Keep taking updates and fix whatever breaks. So no "majors disabled" rule. Still group postgres across managers, and add a regex manager for `ARG PG_MAJOR` so the next major arrives as one complete PR. - **Q1** (a plain `up -d`): explained to the owner, answer pending.

claude-bot commented

2026-10-27 17:49:35 +00:00

Author

Collaborator

Q1 answered (owner, 27 Oct 2026): (b), hard stop. A small one-shot guard service runs before migrate (migrate depends on it completing successfully). It mounts the old postgres_data volume read-only and the new postgres18_data volume. It exits non-zero, with a message telling the operator to run make pg-upgrade first, when the old volume holds a cluster (PG_VERSION 16) and the new one holds none. A fresh install (neither volume has data) and an upgraded install (the new one has data) pass straight through. Removable in a later release once hosts are known to be upgraded. Tests: the three volume states.

**Q1 answered (owner, 27 Oct 2026): (b), hard stop.** A small one-shot guard service runs before `migrate` (`migrate` depends on it completing successfully). It mounts the old `postgres_data` volume read-only and the new `postgres18_data` volume. It exits non-zero, with a message telling the operator to run `make pg-upgrade` first, when the old volume holds a cluster (`PG_VERSION` 16) and the new one holds none. A fresh install (neither volume has data) and an upgraded install (the new one has data) pass straight through. Removable in a later release once hosts are known to be upgraded. Tests: the three volume states.

claude-bot commented

2026-10-30 19:29:51 +00:00

Author

Collaborator

The owner, 2026-10-30, on the downtime window (Q2): do the prod upgrade as part of the v4.7.0 release ("I don't have a game coming up for a while"). Plan: build the repo side (guard service, make pg-upgrade, compose/CI/Dockerfile on 18, Renovate grouping), rehearse it on dev, then upgrade prod during the 4.7.0 prod deploy.

**The owner, 2026-10-30, on the downtime window (Q2):** do the prod upgrade **as part of the v4.7.0 release** ("I don't have a game coming up for a while"). Plan: build the repo side (guard service, `make pg-upgrade`, compose/CI/Dockerfile on 18, Renovate grouping), rehearse it on dev, then upgrade prod during the 4.7.0 prod deploy.

rbrooks referenced this issue from a commit

2026-10-31 17:51:08 +00:00

feat(repo): PostgreSQL 16 to 18, with make pg-upgrade and a hard-stop guard (#1245)

claude-bot referenced this issue from a pull request that will close it,

2026-10-31 17:51:17 +00:00

feat(repo): PostgreSQL 16 to 18, with make pg-upgrade and a hard-stop guard (#1245) #1299

claude-bot commented

2026-10-31 17:51:35 +00:00

Author

Collaborator

PR #1299 is open. What it contains:

18 on a new volume, with the old one kept as the rollback copy;
a guard that stops a 16-only install from starting;
make pg-upgrade, which dumps, restores, compares counts and checks the revision;
docs for the prod order.

Verified on docker-test:

the full backend suite on 18: 10417 passed, 0 failed;
a rehearsal on a copy of a dev backup: all 115 tables matched, plus the failure, re-run and fresh-install cases.

An independent review of the upgrade path is running before merge. The prod upgrade happens at the 4.7.0 deploy, on the owner's say.

PR #1299 is open. What it contains: - 18 on a new volume, with the old one kept as the rollback copy; - a guard that stops a 16-only install from starting; - `make pg-upgrade`, which dumps, restores, compares counts and checks the revision; - docs for the prod order. Verified on docker-test: - the full backend suite on 18: 10417 passed, 0 failed; - a rehearsal on a copy of a dev backup: all 115 tables matched, plus the failure, re-run and fresh-install cases. An independent review of the upgrade path is running before merge. The prod upgrade happens at the 4.7.0 deploy, on the owner's say.

rbrooks referenced this issue from a commit

2026-10-31 18:02:53 +00:00

fix(repo): pg-upgrade review fixes (#1245)

rbrooks referenced this issue from a commit

2026-10-31 18:02:53 +00:00

style: ruff format (#1245)

claude-bot closed this issue

2026-10-31 18:38:32 +00:00

claude-bot referenced this issue from a commit

2026-10-31 18:38:33 +00:00

Merge pull request 'feat(repo): PostgreSQL 16 to 18, with make pg-upgrade and a hard-stop guard (#1245)' (#1299) from feat/1245-postgres-18 into main

claude-bot commented

2026-10-31 19:28:03 +00:00

Author

Collaborator

Done in the repo: #1299 merged 31 Oct at 18:38, on the owner's say, after CI went green.

It includes the three fixes from the independent review:

an early failure never removes the old db;
the guard counts anything in the old volume as data;
the old and new volumes can't be the same.

Still to do, as part of the 4.7.0 release (the owner's call, 30 Oct):

Rehearse make pg-upgrade on dev with the release candidate. That is the first real run of the new cleanup paths.
Before the prod window, check PGDATA and any extra roles on prod.
The prod upgrade itself, on the owner's say: make backup-now, check out v4.7.0, docker compose pull, make pg-upgrade, then docker compose up -d.

Those steps are tracked in the 4.7.0 release issue when it's cut, so this issue stays closed.

Done in the repo: #1299 merged 31 Oct at 18:38, on the owner's say, after CI went green. It includes the three fixes from the independent review: - an early failure never removes the old db; - the guard counts anything in the old volume as data; - the old and new volumes can't be the same. **Still to do, as part of the 4.7.0 release** (the owner's call, 30 Oct): 1. Rehearse `make pg-upgrade` on dev with the release candidate. That is the first real run of the new cleanup paths. 2. Before the prod window, check `PGDATA` and any extra roles on prod. 3. The prod upgrade itself, on the owner's say: `make backup-now`, check out v4.7.0, `docker compose pull`, `make pg-upgrade`, then `docker compose up -d`. Those steps are tracked in the 4.7.0 release issue when it's cut, so this issue stays closed.

claude-bot referenced this issue

2026-11-04 16:21:35 +00:00

chore(release): v4.7.0-rc1 #1332

claude-bot commented

2026-11-04 16:22:09 +00:00

Author

Collaborator

Dev rehearsal, pre-checks (2026-11-04), read-only, on docker-test (quest-board, at v4.6.2, clean tree):

printenv PGDATA gives /var/lib/postgresql/data, the default layout the script handles.
The roles are questboard, questboard_admin and questboard_migrate only, so nothing extra will stop the restore.
The database is 44 MB, and the host has 98 GB free.
Only quest-board_postgres_data exists; there's no postgres18_data yet.

Next, with the owner's go-ahead for the dev deploy: tag v4.7.0-rc1 once #1332 is green, back up, check out the tag, pull, make pg-upgrade, then up -d and verify.

**Dev rehearsal, pre-checks (2026-11-04), read-only, on docker-test (`quest-board`, at v4.6.2, clean tree):** - `printenv PGDATA` gives `/var/lib/postgresql/data`, the default layout the script handles. - The roles are `questboard`, `questboard_admin` and `questboard_migrate` only, so nothing extra will stop the restore. - The database is 44 MB, and the host has 98 GB free. - Only `quest-board_postgres_data` exists; there's no `postgres18_data` yet. Next, with the owner's go-ahead for the dev deploy: tag `v4.7.0-rc1` once #1332 is green, back up, check out the tag, pull, `make pg-upgrade`, then `up -d` and verify.

rbrooks referenced this issue from a commit

2026-11-04 17:11:37 +00:00

fix(repo): pg-upgrade counts tables with a query PostgreSQL accepts (#1245)

rbrooks referenced this issue from a commit

2026-11-04 17:11:37 +00:00

style: ruff format the pg-upgrade test (#1245)

claude-bot referenced this issue

2026-11-04 17:11:43 +00:00

fix(repo): pg-upgrade counts tables with a query PostgreSQL accepts (#1245) #1333

claude-bot commented

2026-11-04 17:11:47 +00:00

Author

Collaborator

Dev rehearsal with 4.7.0-rc1 (2026-11-04): stopped safely at step 4, on a bug in the script, and fixed.

Before the run: backup taken (questboard_manual_20261104T170408Z.dump, 3.2 MB); v4.7.0-rc1 checked out and all three images pulled.
make pg-upgrade: steps 1 to 3 went as expected (volumes checked, a second backup, app and old db stopped). Step 4 failed counting the old tables: ERROR: collations are not supported by type integer from order by 1 collate "C".
The failure behaved as designed: the new volume was removed, the old one untouched, and the app left stopped.
Rolled back: git checkout v4.6.2 && docker compose up -d. Every service was healthy and /api/meta returned 4.6.2, about two minutes down. No data changed.
Fix in #1333: order by table_name::text collate "C", checked read-only on dev's PostgreSQL 16 and on 18, plus a test that runs the query on a real database. That test fails on the old query and passes on the new one.

Next: merge #1333, cut v4.7.0-rc2, and run the rehearsal again. Prod uses the final tag, which will contain the fix.

**Dev rehearsal with 4.7.0-rc1 (2026-11-04): stopped safely at step 4, on a bug in the script, and fixed.** - **Before the run:** backup taken (`questboard_manual_20261104T170408Z.dump`, 3.2 MB); `v4.7.0-rc1` checked out and all three images pulled. - **`make pg-upgrade`:** steps 1 to 3 went as expected (volumes checked, a second backup, app and old db stopped). Step 4 failed counting the old tables: `ERROR: collations are not supported by type integer` from `order by 1 collate "C"`. - **The failure behaved as designed:** the new volume was removed, the old one untouched, and the app left stopped. - **Rolled back:** `git checkout v4.6.2 && docker compose up -d`. Every service was healthy and `/api/meta` returned 4.6.2, about two minutes down. No data changed. - **Fix in #1333:** order by `table_name::text collate "C"`, checked read-only on dev's PostgreSQL 16 and on 18, plus a test that runs the query on a real database. That test fails on the old query and passes on the new one. **Next:** merge #1333, cut `v4.7.0-rc2`, and run the rehearsal again. Prod uses the final tag, which will contain the fix.

rbrooks referenced this issue from a commit

2026-11-04 18:40:23 +00:00

ci: rerun after a network drop fetching the PGDG key (#1245)

claude-bot referenced this issue from a commit

2026-11-04 19:17:27 +00:00

Merge pull request 'fix(repo): pg-upgrade counts tables with a query PostgreSQL accepts (#1245)' (#1333)

claude-bot commented

2026-11-04 20:44:58 +00:00

Author

Collaborator

Dev rehearsal with 4.7.0-rc2 (2026-11-04): passed. rc2 carries #1333's fixed count query.

make backup-now on 4.6.2 wrote questboard_manual_20261104T203748Z.dump. Then v4.7.0-rc2 was checked out and the three images pulled.
make pg-upgrade ran all 7 steps: volumes checked, a second backup, app and old db stopped, the old cluster dumped by postgres:16-alpine (backups/pre-pg18-upgrade_20261104T204100Z.dump), restored into quest-board_postgres18_data, and analysed. All 115 tables matched row for row, at Alembic revision a1269b0c1d2e (e.g. transcript_segments 16,474, session_beats 1,734, sessions 122, users 26). The old volume was not changed and is the rollback.
docker compose up -d: the guard passed and migrate applied the five 4.7.0 migrations (b1242c0d1e2f, d1321c0e5a7b, e1279a0b1c2d, f1280a0b1c2d, a1257b0c1d2e). Every service is healthy.
Verified:
- alembic current gives a1257b0c1d2e (head).
- /api/meta gives {"version":"4.7.0-rc2"}, and /ready is ok.
- select version() gives PostgreSQL 18.6.
- make backup-now wrote the first pg_dump-18 backup (3.2 MB).
- The campaigns are present (11), and the backend, worker and beat logs show no errors.

Dev was down for about four minutes. The prod steps are now rehearsed; prod only on the owner's say.

**Dev rehearsal with 4.7.0-rc2 (2026-11-04): passed.** rc2 carries #1333's fixed count query. 1. `make backup-now` on 4.6.2 wrote `questboard_manual_20261104T203748Z.dump`. Then `v4.7.0-rc2` was checked out and the three images pulled. 2. `make pg-upgrade` ran all 7 steps: volumes checked, a second backup, app and old db stopped, the old cluster dumped by `postgres:16-alpine` (`backups/pre-pg18-upgrade_20261104T204100Z.dump`), restored into `quest-board_postgres18_data`, and analysed. **All 115 tables matched row for row**, at Alembic revision `a1269b0c1d2e` (e.g. `transcript_segments` 16,474, `session_beats` 1,734, `sessions` 122, `users` 26). The old volume was not changed and is the rollback. 3. `docker compose up -d`: the guard passed and `migrate` applied the five 4.7.0 migrations (`b1242c0d1e2f`, `d1321c0e5a7b`, `e1279a0b1c2d`, `f1280a0b1c2d`, `a1257b0c1d2e`). Every service is healthy. 4. **Verified:** - `alembic current` gives `a1257b0c1d2e (head)`. - `/api/meta` gives `{"version":"4.7.0-rc2"}`, and `/ready` is ok. - `select version()` gives PostgreSQL 18.6. - `make backup-now` wrote the first pg_dump-18 backup (3.2 MB). - The campaigns are present (11), and the backend, worker and beat logs show no errors. Dev was down for about four minutes. The prod steps are now rehearsed; prod only on the owner's say.

claude-bot commented

2026-11-06 00:37:21 +00:00

Author

Collaborator

Prod is on 4.7.0 and PostgreSQL 18 (2026-11-06, on the owner's say).

Before the upgrade, prod was healthy at 4.6.2 on PostgreSQL 16.15 (57 MB). I took a backup with make backup-now (questboard_manual_20261106T002909Z.dump, 4.6 MB). A copy is on docker-test in ~/backups-prod/, and another is on prod in ~/qb-pre-4.7.0-20261106T002918/ with .env.
Ran git checkout v4.7.0, then docker compose pull, then make pg-upgrade. All 115 tables matched row for row, and the revision was carried over as a1269b0c1d2e. The script also wrote its own dump, backups/pre-pg18-upgrade_20261106T003324Z.dump.
Ran docker compose up -d. The migrate service applied b1242c0d1e2f → d1321c0e5a7b → e1279a0b1c2d → f1280a0b1c2d → a1257b0c1d2e. Every service is healthy.
Checks:
- /api/meta returns {"version":"4.7.0"};
- alembic current is a1257b0c1d2e (head);
- select version() returns PostgreSQL 18.6;
- /ready returns ok;
- the backend, worker, beat and bot logs show no errors since the restart.
Took a second backup after the upgrade: questboard_manual_20261106T003704Z.dump (5.3 MB).
The app was down for about 4 minutes in total.
Rollback: the old volume quest-board_postgres_data has not been touched, so git checkout v4.6.2 && docker compose up -d still works. Remove that volume only after a few days of normal use.

**Prod is on 4.7.0 and PostgreSQL 18 (2026-11-06, on the owner's say).** - Before the upgrade, prod was healthy at 4.6.2 on PostgreSQL 16.15 (57 MB). I took a backup with `make backup-now` (`questboard_manual_20261106T002909Z.dump`, 4.6 MB). A copy is on docker-test in `~/backups-prod/`, and another is on prod in `~/qb-pre-4.7.0-20261106T002918/` with `.env`. - Ran `git checkout v4.7.0`, then `docker compose pull`, then `make pg-upgrade`. All 115 tables matched row for row, and the revision was carried over as `a1269b0c1d2e`. The script also wrote its own dump, `backups/pre-pg18-upgrade_20261106T003324Z.dump`. - Ran `docker compose up -d`. The migrate service applied b1242c0d1e2f → d1321c0e5a7b → e1279a0b1c2d → f1280a0b1c2d → a1257b0c1d2e. Every service is healthy. - Checks: - `/api/meta` returns `{"version":"4.7.0"}`; - `alembic current` is `a1257b0c1d2e (head)`; - `select version()` returns PostgreSQL 18.6; - `/ready` returns ok; - the backend, worker, beat and bot logs show no errors since the restart. - Took a second backup after the upgrade: `questboard_manual_20261106T003704Z.dump` (5.3 MB). - The app was down for about 4 minutes in total. - Rollback: the old volume `quest-board_postgres_data` has not been touched, so `git checkout v4.6.2 && docker compose up -d` still works. Remove that volume only after a few days of normal use.

Rows
Columns

[Ops] Upgrade PostgreSQL 16 → 18: compose, client, CI, and a dump/restore upgrade for existing data #1245

Why #627 fails

The trap

Plan

Repo changes

Questions for the owner