Moving the compose fixture from PostgreSQL 16 to 18

Source of truth: warden/docs/postgres-18-upgrade.md (synced at build time — edit the original, not this copy). docker-compose.yml now runs ghcr.io/veltrosecurity/base-postgres:18. A PostgreSQL 18 server can’t open a 16 data directory, and the 18 image keeps its data under /var/lib/postgresql/18/docker. So the fixture uses a new volume, warden-pg18data, mounted at /var/lib/postgresql. The old warden-pgdata volume is never mounted by 18.

A fresh install needs nothing. If you have data on 16 that you want to keep, copy it across with a dump and restore. The old volume stays as it is, so you can roll back.

The Helm chart needs none of this; external databases are yours to upgrade. Its bundled PostgreSQL already runs 18: the official postgres:18.6 image through the Bitnami postgresql subchart. If your install ran an earlier chart, which used Bitnami’s image, the data carries over, but Postgres warns that each database’s collation version (glibc 2.43 in Bitnami’s image, 2.41 in the official one) doesn’t match. Once, as the postgres user, run REINDEX DATABASE <db> and then ALTER DATABASE <db> REFRESH COLLATION VERSION for warden, postgres and template1.

Before you start

Run these from the directory with your current (PostgreSQL 16) docker-compose.yml and .env, with the stack up. The commands read the user and database from the Postgres container, so custom POSTGRES_* values work as is. Volume names below start with <project>, the compose project name: the directory name unless you set COMPOSE_PROJECT_NAME. docker volume ls shows them.

If you already started the new compose, warden-pg18data holds an initialised 18 database with Warden’s tables, and the restore below would fail with “relation already exists”. Remove that volume first. This drops anything written on 18:

docker compose down
docker volume rm <project>_warden-pg18data

Then put the previous (PostgreSQL 16) docker-compose.yml back, run docker compose up -d and start at step 1.

Steps

Stop everything that writes, then dump the database and record row counts:

docker compose stop warden-frontend warden-backend warden-worker
docker compose exec -T warden-postgres sh -c \
  'pg_dump -U "$POSTGRES_USER" -d "$POSTGRES_DB"' > warden-pg16.sql
cat > count-rows.sql <<'SQL'
SELECT table_name, (xpath('/row/n/text()', query_to_xml(
         format('SELECT count(*) AS n FROM %I.%I', table_schema, table_name),
         false, true, '')))[1]::text AS n
FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
ORDER BY 1;
SQL
docker compose exec -T warden-postgres sh -c \
  'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -At' < count-rows.sql > counts-16.txt

Take the stack down. Don’t pass -v, that deletes the old volume:
Terminal window
```
docker compose down
```
Switch to the new docker-compose.yml (pull the release or check out the new version) and start only Postgres. It initialises an empty 18 database on warden-pg18data:
Terminal window
```
docker compose up -d --wait warden-postgres
```
Don’t start the backend yet. Its migrations would create the tables and the restore would then fail.

Restore the dump, refresh planner statistics and compare the counts:

docker compose exec -T warden-postgres sh -c \
  'psql -v ON_ERROR_STOP=1 -q -U "$POSTGRES_USER" -d "$POSTGRES_DB"' < warden-pg16.sql
docker compose exec -T warden-postgres sh -c \
  'vacuumdb -U "$POSTGRES_USER" -d "$POSTGRES_DB" --analyze-only'
docker compose exec -T warden-postgres sh -c \
  'psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -At' < count-rows.sql > counts-18.txt
diff counts-16.txt counts-18.txt && echo "row counts match"

Stop here if diff prints anything.

Start the rest of the stack:
Terminal window
```
docker compose up -d
```

Rolling back

The 16 data is still in <project>_warden-pgdata. Anything written on 18 since the move is lost.

docker compose down
# put the previous docker-compose.yml back, then:
docker compose up -d

Cleaning up

Once you’re happy with 18, remove the old volume and the dump:

docker volume rm <project>_warden-pgdata
rm warden-pg16.sql counts-16.txt counts-18.txt count-rows.sql