Postgres 16 vs 18

[sttrebo]

Just curious if there's plan to update the docker info to point to Postgres 18? Any reasons why not? Just curious...

Thanks

[MBombeck]

Good question, and the short answer is: HealthLog runs fine on 18, the compose file just doesn't switch you over by itself.

I checked it properly before answering. All migrations apply cleanly on a fresh Postgres 18.6, and the whole integration suite now runs against 18 as well as 16 (the next release lets anyone repeat that with INTEGRATION_PG_IMAGE=postgres:18-alpine). The TrueNAS app already starts new installs on 18.

The reason the compose file stays on 16 is the existing installs. A major version can't be upgraded by changing the image line: the 18 image refuses a data directory written by 16, and it also expects the volume mounted one level up (/var/lib/postgresql instead of /var/lib/postgresql/data). If the default flipped to 18, everyone who pulls the new compose file would find their database container stopping at start. No data lost, but a stack that's down until they notice and roll back. That's not something I want to hand people on a routine update.

So the move is a dump and restore into a fresh volume. I tried it end to end (dump from 16, restore into 18, migration history intact, nothing pending) and wrote it up for the next release as docs/ops/postgres-major-upgrade.md. The gist:

1. docker compose stop app
2. docker compose exec -T db pg_dump -U healthlog -Fc healthlog > healthlog-pg16.dump
3. docker compose stop db
4. In the db service set image: postgres:18.6-alpine and volumes: - pgdata18:/var/lib/postgresql, and add pgdata18: to the top-level volumes:
5. docker compose up -d db, then docker compose exec -T db pg_restore -U healthlog -d healthlog --no-owner < healthlog-pg16.dump
6. docker compose exec -T db vacuumdb -U healthlog -d healthlog --analyze-in-stages, because a restore brings the rows but not the planner statistics, and without them some pages can take seconds until autovacuum catches up
7. docker compose up -d app

The old pgdata volume stays untouched, so going back is just restoring the two old lines. Keep it (and your encryption key next to the dump) until 18 has run for a few days.

Moving the default for new installs to 18 is on the list. I'd rather do that together with a start-up check that tells an existing 16 install what to do than flip it quietly.

[sttrebo]

sounds good, thanks. since i'm a new setup, I can just make my install for 18 and go from there.

[MBombeck]

@sttrebo quick follow-up, because your question kept nagging at me. My first answer boiled down to "it works on 18, but the compose file stays on 16 so nobody's stack breaks on an update". Thinking about it again, that's a reason to make the switch safe, not a reason to skip it.

So 1.46 runs Postgres 18, and since you're starting fresh, the new compose file gives you 18 right away. For everyone already on 16: with the new compose file, the database moves over by itself on the first start. A small one-off container copies it into a fresh volume, checks that every table has the same number of rows on both sides, and only then lets the app start. If anything goes wrong, the app just doesn't start and the log tells you why. Your old volume isn't touched, so going back is two lines in the compose file.

On my own instance (about 600 MB) the whole move took 29 seconds, with every one of its 180 tables checked.

If you run your own Postgres 16, nothing changes for you. HealthLog keeps working on it, and the tests run on both versions now.

Thanks for poking at this!