This warning is described in PostgreSQL's manual, under
ALTER COLLATION; here's the relevant excerpt (emphasis mine):
When a collation object is created, the provider-specific version of the collation is recorded in the system catalog. When the collation is used, the current version is checked against the recorded version, and a warning is issued when there is a mismatch, for example:
WARNING: collation "xx-x-icu" has version mismatch
DETAIL: The collation in the database was created using version 22.214.171.124, but the operating system provides version 126.96.36.199.
HINT: Rebuild all objects affected by this collation and run ALTER COLLATION pg_catalog."xx-x-icu" REFRESH VERSION, or build PostgreSQL with the right library version.
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.
On my ArchLinux machine, when I did regular package updates (
pacman -Syu), PostgreSQL was bumped from 15.3-4 to 15.3-5. After a restart, when I entered the psql prompt I got a warning similar to above (with old and new versions
2.38). Let's say
dummy was my database, doing the following fixed it:
REINDEX DATABASE dummy;
ALTER DATABASE dummy REFRESH COLLATION VERSION;
The output to the last command was
NOTICE: 00000: changing version from 2.37 to 2.38
LOCATION: AlterDatabaseRefreshColl, dbcommands.c:2397
No, I didn't take any backups before doing this, but it might be better if it's some production DB. This fixed the issue cleanly.