postgres collation mismatch

Question

I have pgserver running postgres 17, OS untuntu 24. The collation version is 2.40. My client side is Rocky linux 8:

hlsb_release -a
LSB Version: :core-4.1-amd64:core-4.1-noarch
Distributor ID: Rocky
Description: Rocky Linux release 8.10 (Green Obsidian)
Release: 8.10
Codename: GreenObsidian
hepsql --version
psql (PostgreSQL) 17.0

After I login to the server from my client machine (pgclient) I got warning.

WARNING: database "aurum" has a collation version mismatch
DETAIL: The database was created using collation version 2.40, but the operating system provides version 2.39.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE aurum REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
psql (17.0)

I don't want to rebuild my server to 2.39. To upgrade my collation to 2.40, should I recompile a new client program using the latest GCC library?

Answer 1

This is not about your software, but about the indexes in your database. You must have upgraded the C library of your operating system. Now you have to rebuild all the indexes on strings; then execute the ALTER DATABASE, just as the hint tells you.