template database "template1" has a collation version mismatch

TL;DR

After a glibc or Postgres upgrade, the template1 database records an older collation version than the system now provides, and every database cloned from it inherits the warning. Connect to the postgres maintenance DB, run REINDEX DATABASE template1, then ALTER DATABASE template1 REFRESH COLLATION VERSION.

template database "template1" has a collation version mismatch

Use this when

• Postgres logs warn about template1's collation version after an upgrade
• Every new database shows the same warning right after creation
• An agent's database provisioning logs are full of this warning

Not for this skill when

• Sort order is actually wrong in query results (thats real collation damage)
• Indexes report corruption (thats a repair job, not a version refresh)
• Only your app database warns (same commands, different database name)

Steps

1. Connect to the postgres database (not template1 itself) and confirm:

\c postgres
SELECT datname, datcollversion FROM pg_database WHERE datname = 'template1';

Expected output: one row showing the stale recorded version. You must not be connected to template1 to reindex it.

1. Rebuild collation-dependent indexes under the new library:

REINDEX DATABASE template1;

Expected output: completes without error. This is quick on template1 since the template is nearly empty.

1. Update the recorded version so the warning stops:

ALTER DATABASE template1 REFRESH COLLATION VERSION;

Expected output: ALTER DATABASE. Verify with the step-1 query: the warning condition is gone when the recorded version matches the system.

1. Fix databases that were cloned while the template was stale:

SELECT datname FROM pg_database WHERE datistemplate = false AND datname NOT IN ('postgres');

Expected output: the list of databases to run the same two commands against. New databases created after step 3 are already clean.

Variant phrasings

same warning naming template0

template0 is meant to be pristine and is rarely connected; the same REINDEX plus REFRESH applies if something touched it.

warning on every database after a major upgrade

Thats expected: the upgrade bumped the collation library for the whole cluster. Script the two commands across all databases.

Why it happens

CREATE DATABASE copies template1 byte-for-byte, including its recorded collation version. When the OS upgrades glibc (or Postgres upgrades its bundled ICU), the live library version moves on but the recorded version in each database doesnt, and Postgres warns on every connection. Fixing template1 stops the warning from spreading; existing databases need the same treatment individually.

Edge cases

• On Debian/Ubuntu, glibc updates are the usual trigger; the Postgres package itself may be unchanged.
• Some managed providers run these steps for you after maintenance; check provider docs before running commands you may not need.
• If you use a custom template database for new projects, fix that template too, not just template1.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_noHrR2-TcMRr0NQidmdFYQ