tickets #170143
closedPostgreSQL on mirrordb{1,2} collation version mismatch
0%
Description
crameleon@mirrordb2:/home/crameleon> sudo -u postgres psql
[sudo] password for crameleon:
WARNING: database "postgres" has a collation version mismatch
DETAIL: The database was created using collation version 2.31, but the operating system provides version 2.38.
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.
psql (15.8)
Type "help" for help.
postgres=#
I already encountered this on the template1 database the other day, there I solved it using:
REINDEX DATABASE template1;
ALTER DATABASE template1 REFRESH COLLATION VERSION;
I could not quite find any official documentation about whether that's the right procedure (regarding the hint "Rebuild all objects in this database that use the default collation") and am hence a bit reluctant to run it on the postgres database.
Updated by crameleon 21 days ago · Edited
- Status changed from New to In Progress
- Assignee set to crameleon
The query
SELECT datname, datcollate, datcollversion FROM pg_database ;
will help identify the affected databases.
It seems only ones using en_US.UTF-8 as a collation have a collation version at all.
To resolve the issue at hand, we identify all databases using en_US.UTF-8 and a version not matching the latest, which is currently 2.38 as per the warning:
SELECT datname FROM pg_database WHERE datcollate = 'en_US.UTF-8' AND datcollversion != '2.38' ;
We then refresh every one of these databases:
REINDEX DATABASE foo ;
ALTER DATABASE foo REFRESH COLLATION VERSION ;
This is somewhat documented in https://www.postgresql.org/docs/current/sql-altercollation.html#SQL-ALTERCOLLATION-NOTES, even though the example there shows a mismatch directly on a collation as opposed to on a database.
For completeness, here is a full example using the database "netbox":
postgres=# SELECT datname, datcollate, datcollversion FROM pg_database WHERE datname = 'netbox';
datname | datcollate | datcollversion
---------+-------------+----------------
netbox | en_US.UTF-8 | 2.31
(1 row)
postgres=# REINDEX DATABASE netbox;
ERROR: can only reindex the currently open database
postgres=# \c netbox
WARNING: database "netbox" has a collation version mismatch
DETAIL: The database was created using collation version 2.31, but the operating system provides version 2.38.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE netbox REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
You are now connected to database "netbox" as user "postgres".
netbox=# REINDEX DATABASE netbox;
REINDEX
netbox=# ALTER DATABASE netbox REFRESH COLLATION VERSION;
NOTICE: changing version from 2.31 to 2.38
ALTER DATABASE
netbox=# SELECT datname, datcollate, datcollversion FROM pg_database WHERE datname = 'netbox';
datname | datcollate | datcollversion
---------+-------------+----------------
netbox | en_US.UTF-8 | 2.38
(1 row)