Fixing PostgreSQL collation version mismatch

After pulling a new version of the Docker PostgreSQL container, I started getting this warning:

WARNING:  database "dev" has a collation version mismatch
DETAIL:  The database was created using collation version 2.36, but the operating system provides version 2.41.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE dev REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

This seems to have occurred because the underlying OS was changed to Trixie from Bullseye for the default image. i.e. I used:

image: postgres:17

Rather than:

image: postgres:17-bookworm

This is on my dev machine, so it’s not a major problem as usually I can just blow away the database and recreate it. However, I have some test data that I don’t want to lose quite yet, so I took a backup using pg_dump and then did this instead:

REINDEX DATABASE dev;
ALTER DATABASE dev REFRESH COLLATION VERSION;

The warning’s now gone.

Of course, in production, you’ll want to be a bit more careful and a pg_dump and load is more likely to avoid any risks of corruption. As ever, YMMV.