Resolving "collation version mismatch" warnings after a PostgreSQL upgrade
Introduction
This article discusses the appearance and resolution of "collation version mismatch" warning messages appearing in the application logs after upgrading a PostgreSQL database.
Here is an abridged example of the warning messages seen in the logs:
WARNING: database "postgres" 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 postgres REFRESH COLLATION VERSION,
or build PostgreSQL with the right library version.
This is a great example of application developers providing clear and concise log messages for the end user.
According to the PostgreSQL documentation:
A change in collation definitions can lead to corrupt indexes and other problems because the database system relies on stored objects having a certain sort order. Generally, this should be avoided, but it can happen in legitimate circumstances, such as when upgrading the operating system to a new major version or when using pg_upgrade to upgrade to server binaries linked with a newer version of ICU. When this happens, all objects depending on the collation should be rebuilt, for example, using REINDEX. When that is done, the collation version can be refreshed using the command ALTER COLLATION ... REFRESH VERSION. This will update the system catalog to record the current collation version and will make the warning go away. Note that this does not actually check whether all affected objects have been rebuilt correctly.
Note: For this particular use case, I am running Django(5.2.x) web applications with a PostgreSQL(15.x) database back end within Docker(29.2.1) containers on a Debian testing(trixie) Linux distribution.
Instructions
- Update
docker-compose.ymlwith new version of the PostgreSQL database.- For example, replace the statement
image: postgres:15.11withimage: postgres:15.17. - See the official PostgreSQL Docker image page for additional information.
- For example, replace the statement
- Rebuild the Docker environment:
docker compose up --build Once the build is finished, the warning messages mentioned above will start appearing in the logs.
Make a note of all the databases listed in the log messages that need to be rebuilt. In this case, we have four databases to update:
- test_django-start
- template1
- postgres
- django-start
Log into the database container:
sh docker exec -it django_start-db bashConnect to the first database.
psql -d test_django-start -U django_adminExecute the commands appropriate to the connected database.
ALTER DATABASE "test_django-start" REFRESH COLLATION VERSION;
REINDEX DATABASE "test_django-start";
\c template1
ALTER DATABASE template1 REFRESH COLLATION VERSION;
REINDEX DATABASE template1;
\c postgres
ALTER DATABASE postgres REFRESH COLLATION VERSION;
REINDEX DATABASE postgres;
\c django-start
ALTER DATABASE "django-start" REFRESH COLLATION VERSION;
REINDEX DATABASE "django-start";
\q # quit psql
exit # exit container
Restart the database container.
docker stop django_start-db docker start django_start-db
