My replicated database warns about a collation version mismatch, how seriously should I take it?

The remedy is to rebuild the affected indexes and then tell the database that the version is now current.

In practice:

1. Take a backup first. Not optional.
2. Reindex: the whole database is simplest and safest. On a large database this takes time and locks, so plan it; there is a concurrent variant that avoids most of the locking at the cost of being slower and occasionally leaving invalid indexes to clean up.
3. Refresh the recorded collation version so the warning clears. There is an ALTER statement for this at both database and collation level.

Step 3 alone makes the message go away and fixes nothing. That is the shortcut people find and it converts a visible warning into an invisible problem. If you do only one of the steps, do step 2.

Also worth checking constraints and partitions, since unique constraints and range partitioning on text both depend on the same ordering.

How you got here is worth understanding so it does not repeat: the replica is running on a host with a different operating system version from the source.

That is extremely common when building a new replica on newer hardware or a newer base image, and it is one of the standard traps in database migration. The data files came from a machine with one locale library and are now being read by a machine with another.

Two ways to avoid it in future:

Match the operating system version between primary and replicas, and upgrade them together deliberately rather than incidentally.

Use a collation provider that versions independently of the operating system, which recent PostgreSQL versions support. That decouples sort order from whatever the host happens to ship and is the more durable answer for anything long-lived.

Take it seriously. The risk is silently incorrect indexes, which is one of the worse failure modes a database has.

The mechanism: collation determines sort order for text. The database does not implement that itself: it delegates to the operating system's locale library. When you build an index on a text column, the entries are ordered according to the collation rules in force at the time.

If the library is later upgraded and its rules change, even for a small number of character sequences, then the stored index order no longer matches what the database now believes the order to be. The index is not corrupted in any way a check would notice. It is simply sorted by rules nobody uses any more.

The consequence is queries that miss rows. A lookup descends the index using the new rules, takes a branch that would have been right today, and does not find an entry filed under the old rules. No error, no warning, just an absent row.

That is why the warning exists and why it recommends rebuilding rather than suggesting it.

Also worth checking constraints that depend on text ordering, not only the obvious indexes.

Worth knowing this class of problem is not specific to one database. Anything that stores an ordering derived from an external library has the same exposure when that library changes.

The general lesson: an index is a cache of an ordering, and if the function that produced the ordering changes, the cache is stale. It applies to collations, to some full-text configurations, and to any functional index over a function whose definition you later alter.

That last one catches people regularly and produces exactly the same silent wrong-results symptom.

Turned up here from a completely different problem and this explained something that had confused me for a year.