Skip to main content

​
Postgres

​
Collation version mismatch in Postgres

You may encounter Postgres warning logs about a collation version mismatch, for example: 
WARNING:  database "xyz" has a collation version mismatch
DETAIL:  The database was created using collation version 2.31, but the operating system provides version 2.42.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE xyz REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
The warning indicates that the OS collation library in the image is newer than the one used when the database was created. The problem does not indicate a data corruption issue. Instead, it is a warning asking to reindex the data so it can be preserved. To fix the problem, update the collation metadata and rebuild the affected indexes: 
ALTER DATABASE xyz REFRESH COLLATION VERSION;
REINDEX DATABASE
The above Postgres (PostgreSQL) utility command updates the recorded version and reindexes the affected objects.
Reindexing locks the database, so it’s best to run this command during a short maintenance window.