Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

PostgreSQL warns about a collation version mismatch after an OS upgrade

ErrorWARNING: database "mydb" has a collation version mismatch

Short answer

The operating system's collation library changed since the database recorded its version, so sort order may differ from what existing indexes contain. Rebuild the objects that depend on the collation with REINDEX, then run ALTER DATABASE ... REFRESH COLLATION VERSION. Refreshing first only hides the warning.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    List which collations differ and which objects depend on them. This is the query the PostgreSQL manual gives for finding the affected objects.

    SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
           pg_describe_object(classid, objid, objsubid) AS "Object"
      FROM pg_depend d JOIN pg_collation c
           ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
      WHERE c.collversion <> pg_collation_actual_version(c.oid)
      ORDER BY 1, 2;
  2. 2Match what you see, then jump to its fix

    1. 1The warning appears on connect after a distribution upgrade or after restoring a data directory onto a newer OSThe OS upgrade changed the collation library under existing indexesFix
    2. 2The warning returns on every connection even after REINDEXThe rebuild is done but the recorded version was never refreshedFix
    3. 3The first-step query still lists objects after the rebuild, such as system catalog indexes or exclusion-constraint indexesSome dependent objects were skipped by REINDEX DATABASE CONCURRENTLYFix

What does the error look like?

  • WARNING: database "mydb" has a collation 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 DATABASE ... REFRESH COLLATION VERSION

Cause 1 of 3

The OS upgrade changed the collation library under existing indexes

1Check

The manual names upgrading the OS to a new major version as the usual reason. With glibc, the C library version stands in for the collation version, so a new glibc triggers the warning.

SELECT datname, datcollversion, pg_database_collation_actual_version(oid) FROM pg_database;

2Fix

Rebuild every index that uses the changed collation (system catalog indexes come in the next cause). REINDEX CONCURRENTLY does not lock out writes on the table, but it takes significantly longer than a normal rebuild.

REINDEX DATABASE CONCURRENTLY mydb;

3Verify

Run the first-step query again; the objects stay listed until the version is refreshed in the next cause.

SELECT count(*) FROM pg_collation WHERE collversion <> pg_collation_actual_version(oid);

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The rebuild is done but the recorded version was never refreshed

1Check

The warning stays until the catalog records the new version. The manual says ALTER ... REFRESH VERSION does not check that the objects were rebuilt, so run it only after the rebuild.

SELECT datname, datcollversion FROM pg_database WHERE datname = current_database();

2Fix

Refresh the version for the database default collation. For another collation, use ALTER COLLATION with its name.

ALTER DATABASE mydb REFRESH COLLATION VERSION;

3Verify

Reconnect; the warning is gone.

SELECT datname, datcollversion FROM pg_database WHERE datname = current_database();

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

Some dependent objects were skipped by REINDEX DATABASE CONCURRENTLY

1Check

REINDEX DATABASE leaves out indexes on system catalogs, and a concurrent run skips indexes that back exclusion constraints. They stay on the list, so the warning would only be hidden, not fixed, by refreshing.

-- re-run the first-step query and read the Object column

2Fix

Rebuild system catalog indexes with REINDEX SYSTEM, which cannot run concurrently. Rebuild exclusion-constraint indexes with a normal REINDEX on their table, which locks out writes while it runs.

REINDEX SYSTEM;
REINDEX TABLE my_table_with_exclusion_constraint;

3Verify

The first-step query returns no rows for the objects you rebuilt.

-- re-run the first-step query

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Before a major OS upgrade, plan a REINDEX window; the manual warns that collation changes can corrupt indexes.
  • Treat the warning as a task, not noise: do not refresh the version until the rebuild has finished.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.