Upgrading four PostgreSQL instances to 18.2 in Docker

I run a handful of self-hosted services on my server. Miniflux for RSS, Invidious for YouTube, a Django app, and Mastodon. They all use PostgreSQL, and they were all stuck on older versions — 15 or 17, depending on when I last felt brave.

PostgreSQL 18.2 came out and I figured it was time. Four databases, four upgrades, one evening. How hard could it be.

It took two evenings.

The general pattern

Every upgrade followed the same rough steps:

1. Stop the containers
2. Copy the data directory as a backup
3. Run tianon/postgres-upgrade to do the actual pg_upgrade
4. Fix the directory layout (more on this later)
5. Fix pg_hba.conf (more on this too)
6. Update docker-compose.yml to use postgres:18
7. Start everything up and hold your breath

Here’s what the backup looked like for every service:

docker compose stop
cp -r ./pgdata ./pgdata-bak

And the upgrade command, using Miniflux as an example:

docker run --rm \
  -v /root/Docker/miniflux/pg17:/var/lib/postgresql/17/docker \
  -v /root/Docker/miniflux/pg18:/var/lib/postgresql/18/docker \
  -e PGDATAOLD=/var/lib/postgresql/17/docker \
  -e PGDATANEW=/var/lib/postgresql/18/docker \
  -e POSTGRES_INITDB_ARGS="--no-data-checksums" \
  tianon/postgres-upgrade:17-to-18

Simple enough. Except every instance found a new way to fail.

The data checksums problem

The first thing that blew up was Miniflux. The upgrade container initialized the new cluster with checksums enabled by default, but my old cluster had them off. The error:

old cluster does not use data checksums but the new one does

The fix is passing --no-data-checksums in POSTGRES_INITDB_ARGS. I hit this on every single upgrade because none of my old clusters used checksums. You would think I’d remember after the first time.

PostgreSQL 18 changed its directory layout

This was the one that ate the most time. PostgreSQL 18’s Docker image expects data in a version-specific subdirectory: /var/lib/postgresql/18/docker. Previous versions just used /var/lib/postgresql/data.

The tianon/postgres-upgrade image does the upgrade, but the output directory doesn’t always match what the official postgres:18 image expects. So after the upgrade, you need to shuffle directories around:

mkdir -p ./pgdata/18
mv ./pgdata/docker ./pgdata/18/docker

Then update your volume mount from ./pgdata:/var/lib/postgresql/data to ./pgdata:/var/lib/postgresql. Mount the parent, let the image find the subdirectory itself.

I got this wrong at least twice. The container starts, PostgreSQL sees an empty data directory, initializes a fresh cluster, and your data is just sitting in the wrong folder while a brand new empty database happily accepts connections. Terrifying if you don’t realize what happened.

pg_hba.conf resets every time

The upgrade process runs initdb, which generates a fresh pg_hba.conf. The fresh config only allows connections from 127.0.0.1/32. In Docker, your app container is on a different IP. So the app can’t connect.

After every upgrade I had to edit pg_hba.conf to allow connections from the Docker network. The default Docker bridge subnet is 172.16.0.0/12, so scope it to that instead of opening it to the world:

host    all    all    172.16.0.0/12    scram-sha-256

If you’re using a custom Docker network with a different subnet, check it with docker network inspect <network_name> and use that CIDR instead. Don’t use 0.0.0.0/0 with trust — even on an internal network, there’s no reason to skip authentication entirely.

Invidious and the custom superuser

Invidious uses a custom PostgreSQL superuser instead of the default postgres. The upgrade tool assumes postgres unless you tell it otherwise, so the first attempt died with:

FATAL: role "postgres" does not exist

The fix was passing the username everywhere:

docker run --rm \
  -v /root/Docker/invidious/postgresdata:/var/lib/postgresql/17/data \
  -v /root/Docker/invidious/pgdata:/var/lib/postgresql/18 \
  -e POSTGRES_INITDB_ARGS="--no-data-checksums --username=myuser" \
  -e PGUSER=myuser \
  tianon/postgres-upgrade:17-to-18 \
  --username=myuser

Three separate places to specify the username. Miss any one of them and it fails with a different error each time.

The Django app: jumping from 15 to 18

The Django app was still on PostgreSQL 15. I was worried about skipping versions, but tianon provides a 15-to-18 upgrade image and pg_upgrade handles major version jumps fine. Django 4.2 supports PostgreSQL 12 and above, so 18 was within the compatibility window.

docker run --rm \
  -v /root/Docker/DutaMasIndo/postgres:/var/lib/postgresql/15/data \
  -v /root/Docker/DutaMasIndo/pgdata:/var/lib/postgresql/18 \
  -e POSTGRES_INITDB_ARGS="--no-data-checksums" \
  -e PGUSER=postgres \
  tianon/postgres-upgrade:15-to-18 \
  --username=postgres

Same directory restructuring, same pg_hba.conf dance. At least by this point I had a routine.

Mastodon

Mastodon v4.5.x requires PostgreSQL 14 or newer, so 18 is well within range. The upgrade itself was identical to Miniflux — same version jump (17 to 18), same checksums issue, same directory fix.

The only thing that made me nervous was the database size. Mastodon accumulates a lot of data. But pg_upgrade uses hard links by default, so even large databases upgrade quickly without doubling disk usage.

Post-upgrade cleanup

After every upgrade, PostgreSQL complained about collation version mismatches:

WARNING: database "invidious" has a collation version mismatch

The operating system in the new container ships a newer glibc with updated collation definitions. The fix:

ALTER DATABASE invidious REFRESH COLLATION VERSION;
ALTER DATABASE postgres REFRESH COLLATION VERSION;
ALTER DATABASE template1 REFRESH COLLATION VERSION;

Then rebuild the statistics so the query planner isn’t working with stale data:

docker compose exec db vacuumdb -U postgres --all --analyze-in-stages --missing-stats-only

Updated docker-compose.yml

The final compose config for each service looked roughly like this:

services:
  db:
    image: postgres:18
    volumes:
      - ./pgdata:/var/lib/postgresql
      - /etc/localtime:/etc/localtime:ro

The key change is the volume mount. Mount the parent directory, not the data directory directly.

What I’d do differently

Write a script. I did four upgrades manually and made the same mistakes repeatedly. The steps are mechanical: stop, backup, run the upgrade image, restructure directories, fix pg_hba.conf, update compose, start. A bash script with the service name and source version as arguments would’ve saved me an evening.

I’d also check the checksum setting beforehand. You can see it with SHOW data_checksums; inside the running database, or pg_controldata on the data directory. Knowing upfront avoids the “oh right, checksums” moment on every single upgrade.

All four services are running on 18.2 now. The databases are fine. The backups are still sitting there, in directories named pg17-backup and postgres-bak, and I will definitely forget to clean them up.