openSUSE admin

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)

Done for all affected databases.