Fix PostgreSQL Collation Version Mismatch After an OS Upgrade

Problem statement

After upgrading the server from Ubuntu 22.04 to 24.04, switching the Docker image from postgres:16-bullseye to postgres:16-bookworm, or restoring a data directory on a different OS, every connection logs:

WARNING:  database "appdb" has a collation version mismatch
DETAIL:  The database was created using collation version 2.31, but the operating
system provides version 2.39.
HINT:  Rebuild all objects in this database that use the default collation and run
ALTER DATABASE appdb REFRESH COLLATION VERSION, or build PostgreSQL with the right
library version.

Django keeps working, and the warning seems cosmetic. It is not. PostgreSQL sorts text using the operating system's C library (glibc). glibc 2.28 changed sort order for many strings, and other releases changed it slightly. B-tree indexes on text columns are stored in the old order, so after the upgrade lookups can miss rows and unique constraints can let duplicates in.

1. Take a backup before changing anything.
2. Find indexes that depend on the default collation (text, varchar, citext columns).
3. Rebuild them: REINDEX DATABASE CONCURRENTLY appdb; on PostgreSQL 12+, or per index during low traffic.
4. Then record the new version: ALTER DATABASE appdb REFRESH COLLATION VERSION;
5. For future moves, prefer logical dumps (pg_dump/pg_restore) or the ICU provider, which avoid silent changes.

Step-by-step solution

1. Assess the impact

List indexes on collatable columns:

SELECT DISTINCT indrelid::regclass AS table_name, indexrelid::regclass AS index_name
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
WHERE a.attcollation <> 0
  AND i.indrelid::regclass::text NOT LIKE 'pg_%';

In a typical Django project this includes auth_user.username, slugs, email columns, django_session.session_key, and any unique=True or db_index=True CharField. Integer and UUID indexes are unaffected.

The amcheck extension can verify whether specific indexes are actually corrupt:

CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check('auth_user_username_key'::regclass, true);

2. Rebuild the indexes

On PostgreSQL 12 and later, rebuild without blocking writes:

REINDEX DATABASE CONCURRENTLY appdb;

This rebuilds every index, which takes a while on big databases but keeps the app online. To limit work, reindex only the indexes from step 1:

REINDEX INDEX CONCURRENTLY auth_user_username_key;

3. Check unique constraints for duplicates

If the app wrote data between the upgrade and the reindex, a corrupted unique index may have let duplicates in. A unique reindex then fails with could not create unique index. Find them:

SELECT lower(email), count(*) FROM accounts_user GROUP BY 1 HAVING count(*) > 1;

Resolve duplicates in Django (merge or rename accounts), then rerun the reindex.

4. Record the new collation version

Only after rebuilding:

ALTER DATABASE appdb REFRESH COLLATION VERSION;
ALTER DATABASE template1 REFRESH COLLATION VERSION;

Refresh template1 too, so new databases, including Django's test database, do not inherit the warning. Individual collations used in columns can be refreshed with ALTER COLLATION ... REFRESH VERSION.

5. Avoid it next time

The warning appears when a data directory is used with a different glibc. Two approaches avoid it:

• Logical moves. Moving data with pg_dump and pg_restore rebuilds indexes on the new system automatically.
• Pin the image. In Docker, pin the full tag including the Debian release (postgres:17-bookworm) so a pull does not silently change glibc under an existing volume.

Explanation

PostgreSQL's default libc collation provider asks the operating system to compare strings. A B-tree index is a sorted structure, and searches assume the same comparison order as when the index was built. If the order changes, a search can walk past the place where a value is stored and conclude it does not exist. PostgreSQL 15 began recording the collation version per database and warning on mismatch, which is why the message appears after upgrades that were silent before.

Edge cases / notes

• ICU collations. Databases created with --locale-provider=icu depend on the ICU library version instead of glibc, with the same need to reindex when it changes.
• C collation. Databases using C or POSIX collation compare bytes and are unaffected.
• Managed services. RDS and Cloud SQL manage OS libraries for you and handle collation changes during their upgrades; check their release notes.

Next steps

• Plan major version moves with Upgrade PostgreSQL Major Version for Django.
• Keep a restorable copy with Back Up and Restore PostgreSQL for Django.
• Moving servers? Follow Move Django to a New Server.

FAQ

Can I just run REFRESH COLLATION VERSION?

It removes the warning but leaves any misordered indexes in place. Reindex first.

Will Django show errors if indexes are corrupt?

Usually not directly. You see missing search results, logins that fail for some usernames, or duplicate values in unique columns.

How long does REINDEX DATABASE CONCURRENTLY take?

About as long as building every index from scratch, often minutes to hours depending on size. It does not block writes.

Does this affect SQLite or MySQL?

No. SQLite uses its own comparison functions, and MySQL ships its own collations.