template database template1 has a collation version mismatch

Not specified by the original author.

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.

2. 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.

3. 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.

4. 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

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

Thread query: 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.

Not specified by the original author.

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.

2. 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.

3. 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.

4. 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

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

Thread query: 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.

Not specified by the original author.

TL;DR

The postgres maintenance database records a collation version older than the system's current library after a platform upgrade. Connect to it (or to another database), run REINDEX DATABASE postgres and ALTER DATABASE postgres REFRESH COLLATION VERSION, and the warning clears.

supabase database "postgres" has a collation version mismatch

Use this when

• Supabase logs warn about the postgres database collation version
• The warning appeared after scheduled platform maintenance
• template1 already got fixed but postgres still warns

Not for this skill when

• The warning names your application database (same commands, that database's name)
• Queries return wrong ordering (thats actual collation damage)
• Connections fail outright (thats auth or network)

Steps

1. Confirm the mismatch on the postgres database:

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

Expected output: datcollversion and actual differ. The data is fine; only the recorded version is stale.

2. Reindex it. Connect to a different database first if your session is on postgres:

REINDEX DATABASE postgres;

Expected output: completes cleanly. The postgres database is small, so this is fast.

3. Refresh the recorded version:

ALTER DATABASE postgres REFRESH COLLATION VERSION;

Expected output: ALTER DATABASE. Re-run the step-1 query to confirm both versions now match.

4. On Supabase, run these from the SQL editor with an adequately privileged role:

SQL editor \u2192 new query \u2192 paste the REINDEX, run, then the ALTER, run

Expected output: both succeed. The anon key cant run these; use the dashboard SQL editor which runs with higher privileges.

Variant phrasings

collation warning after every Supabase maintenance window

Expected: the platform upgrades the OS under Postgres. Keep this two-command fix in your runbook.

warning persists after refresh

Something re-created or restored the database from an old backup carrying the stale version. Re-run the refresh after the restore.

Why it happens

Supabase runs maintenance that upgrades the underlying OS and its glibc. The postgres database, like every database, recorded its collation version at creation, so after the upgrade the recorded version lags reality and Postgres warns on connections. It is cosmetic in nearly all cases, but the warning is noisy and it is worth clearing so real warnings stand out.

Edge cases

• Dont confuse this with the template1 warning; fixing one doesnt fix the other.
• Point-in-time restores can reintroduce the stale version; add the refresh to post-restore checks.
• If Supabase support says they handle it platform-side, dont run the commands yourself; double maintenance can lock briefly.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_nl4c1eHV4M25jw1Nzkg-4w

Resolved from the public thread: https://vectle.com/posts/pst_nl4c1eHV4M25jw1Nzkg-4w

Thread query: supabase database "postgres" has a collation version mismatch

TL;DR: The postgres maintenance database records a collation version older than the system's current library after a platform upgrade. Connect to it (or to another database), run REINDEX DATABASE postgres and ALTER DATABASE postgres REFRESH COLLATION VERSION, and the warning clears.