Collation version mismatch

Question

I replicated a database, it's working fine but when I enter the replication database it displays the following message:

The database was created using collation version 2.31, but the operating system provides version 2.35.

Rebuild all objects in this database that use the default collation and run ALTER DATABASE template1 REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

PgPool is also not working with this replication database, it only works when I remove it.

Pgpool gives the following error when trying to login:

child pid 5286: ERROR: unable to read message kind
child pid 5286: DETAIL: kind does not match between main(53) slot[1] (4e)

The same message also appears in the postgresql logs.

How do I rebuild my database?

Answer 1

This warning is described in PostgreSQL's manual under ALTER COLLATION. Here is the relevant excerpt (emphasis mine):

When a collation object is created, the provider-specific version of the collation is recorded in the system catalog. When the collation is used, the current version is checked against the recorded version, and a warning is issued when there is a mismatch, for example:

WARNING:  collation "xx-x-icu" has version mismatch
DETAIL:  The collation in the database was created using version 1.2.3.4, but the operating system provides version 2.3.4.5.
HINT:  Rebuild all objects affected by this collation and run ALTER COLLATION pg_catalog."xx-x-icu" REFRESH VERSION, or build PostgreSQL with the right library version.

A change in collation definitions can lead to corrupt indexes and other problems because the database system relies on stored objects having a certain sort order. Generally, this should be avoided, but it can happen in legitimate circumstances, such as when upgrading the operating system to a new major version or when using pg_upgrade to upgrade to server binaries linked with a newer version of ICU [link added, ed.]. When this happens, all objects depending on the collation should be rebuilt, for example, using REINDEX. When that is done, the collation version can be refreshed using the command ALTER COLLATION ... REFRESH VERSION. This will update the system catalog to record the current collation version and will make the warning go away. Note that this does not actually check whether all affected objects have been rebuilt correctly.

On my ArchLinux machine, when I did regular package updates (pacman -Syu), PostgreSQL was bumped from 15.3-4 to 15.3-5. After a restart, when I entered the psql prompt I got a warning similar to above (with old and new versions 2.37 and 2.38). Let's say dummy was my database, doing the following fixed it, using REINDEX and then ALTER DATABASE

REINDEX DATABASE dummy;

ALTER DATABASE dummy REFRESH COLLATION VERSION;

The output to the last command was:

NOTICE:  00000: changing version from 2.37 to 2.38
LOCATION:  AlterDatabaseRefreshColl, dbcommands.c:2397
ALTER DATABASE

No, I didn't take any backups before doing this, but it might be better if it's some production DB. This fixed the issue cleanly.

Answer 2

I solved the problem as follows:

I didn't need to rebuild my database, I don't know if this could cause a problem in the future.

Before performing the steps here, make a backup of your database.

My master database was created about 3 months ago, my replication database was created recently, that means even the two servers being Ubuntu 22.04 their LTS version are different, and it is not solved by a simple apt upgrade

The collation versions of my systems are different and to update the master collation I had to update the Ubuntu LTS version.
I used the following command to update Ubuntu master:

sudo do-release-upgrade

After that I used the following command that I got from the error log itself:

REINDEX DATABASE <your_database_name>;

ALTER DATABASE <your-database-name> REFRESH COLLATION VERSION;

Update by Laurenz Albe.

Answer 3

I got the same problem

so I list the database

REINDEX all the database

And after that I successfully able to start msfconsole without any problem