WARNING: database "postgres" has a collation version mismatch

[RolandK]

Hello,

on latest updates i get

WARNING: database "postgres" has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.41.
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.

in console.

do i need to fix this as stated?

i'm a little bit confused as at https://pmg.proxmox.com/wiki/Upgrade_from_8_to_9 there is told

"postgresql may print warnings regarding about collation version mismatch
These are transitory and will disappear once the cluster has been upgraded to the new version."

 

[Stoiko Ivanov]

did you upgrade the postgres cluster? (did you drop the old postgres cluster after the upgrade)? here the warnings went away latest after a reboot with the system using the postgres version from trixie (without the need to run the alter database statement)

I hope this helps!

 

[RolandK]

we upgraded from 8 to 9 a while ago, this time i did just apt update/upgrade and got this message. don't know if this is new or did exist since the upgrade from 8 to 9

reboot does not fix it - still get this message on entering psql tool

root@pmg:~# su - postgres
postgres@pmg:~$ psql
WARNING:  database "postgres" has a collation version mismatch
DETAIL:  The database was created using collation version 2.36, but the operating system provides version 2.41.
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 (17.6 (Debian 17.6-0+deb13u1), server 15.14 (Debian 15.14-0+deb12u1))
Type "help" for help.

i'm a little bit confused which this is called "cluster" as this is a single node installation.


are the following commands the right method to fix it ?

REINDEX DATABASE postgres;
ALTER DATABASE postgres REFRESH COLLATION VERSION;

 

[Stoiko Ivanov]

psql (17.6 (Debian 17.6-0+deb13u1), server 15.14 (Debian 15.14-0+deb12u1))

it seems you're still running the postgresql server version 15 (on port 5432):
see: https://pmg.proxmox.com/wiki/Upgrade_from_8_to_9#Upgrade_the_PostgreSQL_database

this should take care of the collation warnings as well.

I hope this helps!

 

[RolandK]

damn, i must have overseen that. thanks for the pointer

 

[Stoiko Ivanov]

Please let us know if that indeed fixes the issue!

Thanks!

 

[RolandK]

yes it did