PostgreSQL warns about a collation version mismatch after an OS upgrade
WARNING: database "mydb" has a collation version mismatchShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
The 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
The warning returns on every connection even after REINDEXThe rebuild is done but the recorded version was never refreshedFix - 3
The 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
- 1
What does the error look like?
WARNING: database "mydb" has a collation version mismatchDETAIL: 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
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
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 column2Fix
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 queryConfirmed by postgresql.org
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix