19

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?

0

3 Answers 3

26

This warning is described in PostgreSQL's manual, under ALTER COLLATION; here's 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. 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:

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.

0
16

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 have 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 <you_database_name>;

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

Update by @laurenz-albe

0
-1

I got the same problem

porblem

so I list the database list database

REINDEX all the database so REINDEX

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

2
  • 1
    This doesn't really add anything not already covered in the other answers.
    – Hannah Vernon
    Oct 16, 2023 at 16:23
  • Also, you only really indexed the postgres database. You should have read the error messages.
    – sur5r
    May 6 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.