supabase database "template1" has a collation version mismatch

TL;DR

Postgres upgraded its collation library (usually glibc) and the template1 database still records the old version, so every new database cloned from it inherits the warning. Fix it by running REINDEX DATABASE template1 and then refreshing the collation version, which clears the warning for template1 and everything created from it afterward.

database "template1" has a collation version mismatch

Use this when

• Supabase logs or the dashboard warn about template1 collation version
• The warning appeared right after a Postgres upgrade
• New databases show the same warning (they inherit it from template1)

Not for this skill when

• Queries return wrong sort order or corrupted text (thats actual collation damage, not the version warning)
• The warning names your app database only (fix that database the same way, but template1 is a separate step)
• You are seeing index corruption errors (thats REINDEX for data repair, a different job)

Steps

1. Connect to the postgres maintenance database and confirm the mismatch:

SELECT datname, datcollversion, pg_collation_actual_version(datcollate) AS actual
FROM pg_database WHERE datname = 'template1';

Expected output: datcollversion differs from actual. That gap is the entire problem; the data is fine, the recorded version is stale.

1. Reindex template1 to rebuild any collation-dependent indexes under the new library version:

REINDEX DATABASE template1;

Expected output: the command completes without error. On Supabase you may need to run this via the SQL editor with sufficient privileges.

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

ALTER DATABASE template1 REFRESH COLLATION VERSION;

Expected output: ALTER DATABASE confirmation. Re-run the step-1 query and both version columns should now match.

1. Repeat for your app databases if they show the same warning, since they were cloned before the fix:

SELECT 'REINDEX DATABASE ' || quote_ident(datname) || ';'
FROM pg_database WHERE datname NOT IN ('template0', 'template1', 'postgres');

Expected output: a list of REINDEX statements to run per database, followed by ALTER DATABASE ... REFRESH COLLATION VERSION for each.

Variant phrasings

collation version mismatch on the postgres database itself

Same fix, run against that database name. template1 just matters more because it is the clone source.

warning keeps coming back after REINDEX

You reindexed but skipped the REFRESH step. The warning keys off the recorded version, so both steps are required.

Why it happens

Postgres records the collation library version at database creation time. OS or Postgres upgrades bump the library (glibc is the usual one), and the recorded version no longer matches reality. Postgres warns rather than failing because the data is almost always fine, but it flags the risk that sort order could differ. template1 is special because every CREATE DATABASE copies it, so one stale template spreads the warning to all future databases.

Edge cases

• You cannot REINDEX template1 while connected to it; connect to the postgres database instead.
• On managed Supabase, some of these commands need the service role or SQL editor; the restricted anon key wont cut it.
• If you actually had wrong sort results (not just the warning), REINDEX alone may not fix existing indexes built under the old collation; check the Postgres release notes for your version.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_UItemX7QY7FmH7QHIH3z8Q