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

Merged

claude-bot merged 3 commits from feat/1245-postgres-18 into main 2026-10-31 18:38:32 +00:00

Conversation 1 Commits 3 Files changed 14 +760 -21

claude-bot commented 2026-10-31 17:51:17 +00:00

Collaborator

Copy link

Closes #1245. Supersedes Renovate's #627.

What changes

• db runs postgres:18-alpine on a new volume, postgres18_data, mounted at /var/lib/postgresql because the 18 image keeps its data in 18/docker. The old postgres_data volume is kept untouched, as the rollback copy and archive.
• pg-upgrade-guard, a one-shot service that db depends on: it exits 1 when the old volume holds a cluster and the new one has none. Without it, starting the new stack would create an empty database and migrate an empty schema. Fresh installs and upgraded installs both pass.
• make pg-upgrade (scripts/pg-upgrade.sh), which:
  • takes a backup, then dumps with a throwaway 16 container;
  • restores into 18, then analyses;
  • compares per-table counts and the alembic revision;
  • refuses a populated target unless FORCE=1;
  • on failure, removes the new volume so the guard stays armed.
• CI, the e2e image (Dockerfile PG_MAJOR=18) and the e2e compose file all move to 18. Renovate reads PG_MAJOR.
• docs/OPERATIONS.md has a new section, "Upgrading PostgreSQL 16 → 18". It covers the guard's states, the steps, the prod order and rollback.

Verified on docker-test

• Lint and checks are clean.
• The full backend suite passed on postgres:18: 10417 passed, 14 skipped, 0 failed.
• Rehearsal on a copy of a dev backup, in a throwaway compose project:
  • the guard stopped the new stack before the upgrade;
  • make pg-upgrade took 22 s, with all 115 tables and the alembic revision matching;
  • roles and privileges were intact afterwards;
  • a re-run was refused;
  • an injected failure left the old volume intact, and a clean re-run then passed;
  • a fresh install passed.
• Not run: the e2e journeys on 18. CI runs them first.

Prod order (at the 4.7.0 deploy, owner's say only): make backup-now → check out the tag → docker compose pull → make pg-upgrade → docker compose up -d. Do not run make migrate-safe or up before pg-upgrade: the 18 client refuses to back up a 16 server, and up would stop the old db at the guard.

🤖 Generated with Claude Code

Closes #1245. Supersedes Renovate's #627. **What changes** - `db` runs `postgres:18-alpine` on a **new** volume, `postgres18_data`, mounted at `/var/lib/postgresql` because the 18 image keeps its data in `18/docker`. The old `postgres_data` volume is kept untouched, as the rollback copy and archive. - **`pg-upgrade-guard`**, a one-shot service that `db` depends on: it exits 1 when the old volume holds a cluster and the new one has none. Without it, starting the new stack would create an empty database and migrate an empty schema. Fresh installs and upgraded installs both pass. - **`make pg-upgrade`** (`scripts/pg-upgrade.sh`), which: - takes a backup, then dumps with a throwaway 16 container; - restores into 18, then analyses; - compares per-table counts and the alembic revision; - refuses a populated target unless `FORCE=1`; - on failure, removes the new volume so the guard stays armed. - CI, the e2e image (`Dockerfile` `PG_MAJOR=18`) and the e2e compose file all move to 18. Renovate reads `PG_MAJOR`. - `docs/OPERATIONS.md` has a new section, "Upgrading PostgreSQL 16 → 18". It covers the guard's states, the steps, the prod order and rollback. **Verified on docker-test** - Lint and checks are clean. - The full backend suite passed on postgres:18: 10417 passed, 14 skipped, 0 failed. - Rehearsal on a copy of a dev backup, in a throwaway compose project: - the guard stopped the new stack before the upgrade; - `make pg-upgrade` took 22 s, with all 115 tables and the alembic revision matching; - roles and privileges were intact afterwards; - a re-run was refused; - an injected failure left the old volume intact, and a clean re-run then passed; - a fresh install passed. - **Not run:** the e2e journeys on 18. CI runs them first. **Prod order (at the 4.7.0 deploy, owner's say only):** `make backup-now` → check out the tag → `docker compose pull` → **`make pg-upgrade`** → `docker compose up -d`. Do not run `make migrate-safe` or `up` before `pg-upgrade`: the 18 client refuses to back up a 16 server, and `up` would stop the old db at the guard. 🤖 Generated with [Claude Code](https://claude.com/claude-code)

claude-bot added 1 commit 2026-10-31 17:51:18 +00:00

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

Moves the db image, the backend's pg_dump client (ARG PG_MAJOR), the e2e-db
stage, the CI service and the test recipes to PostgreSQL 18. The 18 image keeps
its cluster in /var/lib/postgresql/18/docker, so the db mounts a NEW volume,
postgres18_data, at /var/lib/postgresql; reusing the old volume would start an
empty database.

- scripts/pg-upgrade.sh (make pg-upgrade): dump with the old 16 container's own
  pg_dump into ./backups, restore into the new volume with pg_restore 18,
  ANALYZE, compare every table's row count and the alembic revision. Refuses
  unless the old volume holds a 16 cluster and the new one none (FORCE=1
  overrides); undoes the new volume on failure; never removes the old volume.
- pg-upgrade-guard: one-shot service that runs before db and migrate and exits 1,
  naming make pg-upgrade, when the old volume holds a cluster and the new one
  none. A fresh install and an upgraded install pass.
- tests: compose agreement asserts the mount path; the guard's states are run
  from the shipped compose text.
- Renovate: group postgres across managers, and read ARG PG_MAJOR with a regex
  manager, so the next major is one complete PR. Majors are not held.
- docs/OPERATIONS.md: "Upgrading PostgreSQL 16 -> 18" (prod steps, rollback,
  archive and removal).

Closes #627 (superseded).

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

claude-bot referenced this pull request 2026-10-31 17:51:35 +00:00

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

claude-bot referenced this pull request 2026-10-31 17:55:38 +00:00

Browse the rules, stage 3: filters for each game system (spell tradition and rank, creature traits and size, item price and bulk…) #1242

rbrooks added 2 commits 2026-10-31 18:02:53 +00:00

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

- A failure before step 5 no longer removes the old release's 16 db; only
  the 18 db this run started is removed, and the script says how to get
  the old release running again once it has stopped the app.
- The guard stops on anything in the old volume, not just a top-level
  PG_VERSION, so a PGDATA subdirectory cannot let 18 start empty.
- OLD_VOLUME and NEW_VOLUME must differ, and a new volume laid out like
  the old one is refused, so a failure can never remove the 16 data.
- Table counts sort by the C collation.
- OPERATIONS.md: check PGDATA and extra roles before the window; re-run
  after a failure by removing the new volume, not with FORCE=1.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

style: ruff format (#1245) ... 9ddf8904e1

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

claude-bot commented 2026-10-31 18:03:02 +00:00

Author

Collaborator

Copy link

Independent review, and fixes

The review found no path that loses or corrupts data, but it found three defects. I checked each against the code and fixed them in 2 commits:

1. A failure at step 2 or 3 took prod down. If the backup or the stop step failed, cleanup ran compose rm -sf db. At that point db is still the old release's running 16 container, and the message then said the stack was untouched. Fixed:
   • cleanup removes db only after step 5 has started the 18 one;
   • once the app is stopped, the script says how to start the old release again.
2. The guard could pass on a PGDATA subdirectory. It read only $old/PG_VERSION, so with PGDATA=.../data/pgdata it would pass, 18 would start empty, and migrate would build an empty schema. Fixed: anything in the old volume now counts as data. There is a new test for that layout. The script's step 1 now names the layout instead of saying "nothing to upgrade".
3. NEW_VOLUME=<old volume>, followed by any failure, would remove the 16 data. Fixed: the script refuses the same name for both, and refuses a "new" volume laid out like the old one.

Also:

• the count comparison sorts by COLLATE "C";
• OPERATIONS.md now says:
  • check PGDATA and any extra roles before the window;
  • after a failed or rolled-back upgrade, re-run by removing the new volume, not with FORCE=1, which would mix 4.7.0 tables with an older revision.

Checked on docker-test:

• lint is clean (982 files formatted, all checks pass);
• test_pg_upgrade, test_backup_client_version and test_migrate_safe_script pass on postgres:18 (33 passed).

Not rehearsed: the new cleanup paths on a real stack; their tests only read the script. The dev rehearsal with the 4.7.0 release candidate is where they get a real run, before prod.

Before the prod window, check on prod: that docker compose exec db printenv PGDATA prints nothing or /var/lib/postgresql/data.

## Independent review, and fixes The review found **no path that loses or corrupts data**, but it found three defects. I checked each against the code and fixed them in 2 commits: 1. **A failure at step 2 or 3 took prod down.** If the backup or the stop step failed, cleanup ran `compose rm -sf db`. At that point `db` is still the old release's running 16 container, and the message then said the stack was untouched. Fixed: - cleanup removes `db` only after step 5 has started the 18 one; - once the app is stopped, the script says how to start the old release again. 2. **The guard could pass on a `PGDATA` subdirectory.** It read only `$old/PG_VERSION`, so with `PGDATA=.../data/pgdata` it would pass, 18 would start empty, and `migrate` would build an empty schema. Fixed: anything in the old volume now counts as data. There is a new test for that layout. The script's step 1 now names the layout instead of saying "nothing to upgrade". 3. **`NEW_VOLUME=<old volume>`, followed by any failure, would remove the 16 data.** Fixed: the script refuses the same name for both, and refuses a "new" volume laid out like the old one. Also: - the count comparison sorts by `COLLATE "C"`; - OPERATIONS.md now says: - check `PGDATA` and any extra roles before the window; - after a failed or rolled-back upgrade, re-run by removing the new volume, **not** with `FORCE=1`, which would mix 4.7.0 tables with an older revision. **Checked on docker-test:** - lint is clean (982 files formatted, all checks pass); - `test_pg_upgrade`, `test_backup_client_version` and `test_migrate_safe_script` pass on postgres:18 (33 passed). **Not rehearsed:** the new cleanup paths on a real stack; their tests only read the script. The dev rehearsal with the 4.7.0 release candidate is where they get a real run, before prod. **Before the prod window, check on prod:** that `docker compose exec db printenv PGDATA` prints nothing or `/var/lib/postgresql/data`.

claude-bot scheduled this pull request to auto merge when all checks succeed 2026-10-31 18:03:56 +00:00

claude-bot merged commit 702366af0f into main 2026-10-31 18:38:32 +00:00

claude-bot referenced this pull request 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 referenced this pull request 2026-10-31 19:27:51 +00:00

chore(deps): update postgres docker tag to v18 #627

claude-bot referenced this pull request 2026-10-31 19:28:03 +00:00

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

Sign in to join this conversation.

Reviewers

No reviewers

Labels

Clear labels   

backend

Issue related to the backend

  

bot

Issue related to the bot

  

bug

Something is not working

  

duplicate

This issue or pull request already exists

  

enhancement

New feature

  

frontend

Issue related to the frontend

  

help wanted

Need some help

  

invalid

Something is wrong

  

question

More information is needed

  

recording

Issue related to the recording system

  

security

Security hardening / vulnerability work

  

wontfix

This won't be fixed

No labels

backend

bot

bug

duplicate

enhancement

frontend

help wanted

invalid

question

recording

security

wontfix

Milestone

Clear milestone

No items

No milestone

Projects

Clear projects

No items

No project

Assignees

Clear assignees

No assignees

2 participants

Notifications

Subscribe

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference

rbrooks/Quest-Board!1299

No description provided.