database "postgres" has a collation version mismatch

TL;DR

Confirm the mismatch with a catalog query, then run REINDEX DATABASE followed by ALTER DATABASE ... REFRESH COLLATION VERSION on the postgres database. The warning appears after the OS C library (glibc) is upgraded, because the collations stored in the database were built with the old library. Reindexing rebuilds the indexes against the new collations and the refresh tells Postgres the database is now consistent.

database "postgres" has a collation version mismatch

Error

database "postgres" has a collation version mismatch

Steps

1. Confirm which database reports the mismatch and compare the stored version to the actual one:

SELECT datname, datcollversion, pg_collation_actual_version(datcollate) AS actual_version
FROM pg_database;

Expected: the row for postgres shows a datcollversion that differs from actual_version, or datcollversion is null while the actual version is set. That gap is the mismatch.

1. Reindex the database to rebuild every index against the new collations:

REINDEX DATABASE "postgres";

Expected: REINDEX completes with no error. On a busy server this can take a while and briefly locks each index, so run it during a quiet window.

1. Refresh the recorded collation version so Postgres stops warning:

ALTER DATABASE "postgres" REFRESH COLLATION VERSION;

Expected: ALTER DATABASE returns success. This command just updates the bookkeeping, it does not change any data.

1. Re-run the catalog query from step 1.

Expected: datcollversion now equals actual_version for the postgres row, and the mismatch warning stops appearing in the logs.

1. Check the other databases too, the same upgrade usually affects all of them:

SELECT datname FROM pg_database WHERE datistemplate = false AND datcollversion IS DISTINCT FROM pg_collation_actual_version(datcollate);

Expected: an empty result. Any name that shows up needs the same reindex plus refresh treatment.

When to use

• Postgres logs the collation version mismatch warning after an OS or glibc upgrade
• A monitoring check flags the postgres database specifically
• You restored or cloned a cluster and the template database carried an old collation version

When not to use

• The error names a query collation problem like "collation ... does not exist", that is a different error
• The mismatch is on an application database, run the same steps but on that database instead of postgres
• Indexes still fail validation after the reindex, that points at corruption, not a version mismatch

Tool compatibility

PostgreSQL 15 and newer (REFRESH COLLATION VERSION exists since PG 15). On PG 14 and older you must reindex and then manually update pg_database, or dump and restore. Applies to self-hosted Postgres on Linux and to container images that got rebuilt on a newer base image.

Variant phrasings

WARNING: database "postgres" has a collation version mismatch, but the operating system provides version "2.36"

The same problem. The quoted version number is the glibc version your OS now reports. Follow the same reindex plus refresh steps.

collation version mismatch on template1 after postgres upgrade

template1 is the model for new databases, so fix it the same way (REINDEX DATABASE template1, then refresh). Do it before creating new databases or they inherit the stale version.

reindexdb fails with collation errors

Use the SQL form above instead of the reindexdb wrapper while diagnosing. If plain REINDEX fails on a specific index, drop and recreate just that index, then continue.

Why it happens

Postgres stores sort-order rules (collations) provided by the OS C library. When the OS upgrades glibc, those rules can change, but the database still records the version it was built with. Postgres notices the recorded version no longer matches what the OS reports and warns you, because existing indexes were sorted under the old rules and could now return rows in the wrong order.

Edge cases

• On Postgres 14 and older there is no REFRESH COLLATION VERSION. Reindex, then update the catalog row, or take the dump and restore route.
• REINDEX DATABASE takes locks. On a production database run it in a maintenance window or reindex indexes one at a time.
• If the postgres database is only used for admin tooling and you never query it, the fix is still worth doing. Monitoring noise from it hides real warnings.
• Docker images rebuilt on a newer base layer trigger this silently. Pin the base image or add the check to your deploy pipeline.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_qFfoRKV-MvV5MK-bsIyc5Q