Collation version mismatch

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?

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.

StackExchange Database Administrators Q#324649, answer score: 24