Plan the Postgres 17 → 18 upgrade (it is a pg_upgrade, not a tag edit) #119
Labels
No labels
No milestone
No project
No assignees
1 participant
Notifications
Due date
No due date set.
Dependencies
No dependencies set.
Reference
Grandiras/Ponente#119
Loading…
Add table
Add a link
Reference in a new issue
No description provided.
Delete branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Both stacks run
docker.io/library/postgres:17-alpine—docker-compose.ymlanddeploy/docker-compose.prod.yml. Postgres 18 has been out for a while and 17 will eventually leave support, but the major cannot be bumped the way the other images can: thedb-datavolume holds a version-specific data directory, so starting an 18 container against a 17 directory refuses to boot.What this needs, and why it is not part of a dependency sweep:
pg_upgrade(or dump/restore) pass overdb-dataon both deployed stacks, prod and dev, with a backup taken first;README.md'spodman run …postgres:17-alpineline and thepostgres:17-alpinesentence indocs/testing.md(the connection-cap paragraph);podman compose down -v) and are therefore the cheap place to rehearse it.Nothing is broken today — 17 is supported until 2029. Filing it so the work is written down rather than rediscovered when it is urgent.
Filed by Claude while working on #117 (run).
🤖 On it — follow along in the action run; the reply will link the full session transcript.
📜 Live transcript — updating every ~5 minutes while the task runs.
Pushed
claude/issue-119. Nothing is bumped — the plan is written down as a runbook, and the reason for not bumping is itself one of the findings.Why no compose edit in this PR
A compose change reaches the server only through
deploy.sh; the auto-update timer touches the app container and nothing else. So a merged17-alpine → 18-alpinebump sits inert until somebody deploys for an unrelated reason, and then takes their stack down for a change they didn't make. The edit has to happen with the data move, anddeploy.shreads the compose file out of the working tree — so both stacks can be migrated from the branch and the PR merged last, which is the order the runbook prescribes.Two things the issue didn't know
The 18 image also moved where data lives.
PGDATAis now/var/lib/postgresql/18/dockerand the volume is declared at/var/lib/postgresql(docker-library/postgres#1259). So the composevolumes:line changes with the tag, not just the image. Bumping only the tag fails loudly — the entrypoint finds the old cluster and exits 1 ("there appears to be PostgreSQL data in: /var/lib/postgresql/data"), the healthcheck never passes,depends_on: service_healthyholds the app back. Nothing is destroyed; the stack is just down.pg_upgradeis the wrong tool at this size. A seeded database is 8 MB. Dump/restore is seconds, rebuilds every index under the new collation (our data is Alpine/musl, and the only ready-made upgrade image,tianon/postgres-upgrade:17-to-18, is Debian/glibc —pg_upgradecopies index files verbatim), lands on a cluster the 18 image made itself in the new layout, and picks up 18's data checksums for free. Thepg_upgradepath is documented anyway, for if the board ever outgrows that.Rehearsed, not asserted
PostgreSQL 17.11 → 18.6, against this repository's own schema applied by the app itself (12 migrations, 12 tables,
enc:v1:values, theIX_AccessRequests_PendingDidpartial unique index):data_checksums = on, app then healthy on itpg_dumpvs 18's\restricttoken — which is what makes the plainpodman exec …-db pg_dumpin the runbook legitimatepg_upgradeinto a default 18 clusterold cluster does not use data checksums but the new one doespg_checksums --enableon the stopped 17 clusterEncryptionMigratorre-encrypted a plaintext rowrelation "AccessRequests" already exists— so the runbook drops the database first;DROP DATABASE … WITH (FORCE)works even with the app connectedmax_connectionsMaximum Pool Size=50and thedocs/testing.mdparagraph only need the image name changedNot rehearsed, and flagged as such in the page: the volume rename on a live host,
deploy.shagainst an 18 compose file, and the e2e suite on 18 — which is exactly why step 1 of the runbook is to wipe the local/e2e stacks (podman compose down -v, before the rename, or the old volume is left undeclared and invisible to compose) and run Playwright against 18 there first. That is the decision the issue asked for: those stacks are never migrated, they are destroyed and rebuilt.Files
deploy/postgres-upgrade.md— new: what changes, the method and why, the order (local → dev → prod → merge), the exact commands per stack, the compose diff, rollback, thepg_upgradealternative with both traps, and what was and wasn't rehearsed.docs/deployment.md— the floating-images bullet corrected (it said "a plannedpg_upgradeagainstdb-data", which is now wrong on both counts) and pointed at the runbook; added to itsCovers:line.deploy/README.md— a row in What still needsdeploy.shand a bullet in Things worth knowing.The volume is renamed to
pg-datarather than reused, with no version in the name: renaming is what makes the rollback a file revert (the 17 volume is untouched and current as of the freeze), and the version is omitted because 18 keeps its data in an18/dockersubdirectory — the next major upgrade happens inside that volume.dotnet test Ponente.sln— 380 passed, includingDocumentationTests. Docs-only and maintainer-facing, so noChangelog.Unreleasedentry.⏱ 12m 16s · 53 turns · tokens: 121.1k in / 47.3k out (+3933.1k cache-read)
📜 Full session transcript
→ Opened PR: #131
→ Filed follow-up issue #132: Neither deployed stack has a database backup, scheduled or otherwise