supabase database template1 has a collation version mismatch

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.

Not specified by the original author.

TL;DR

Run the reindex and the collation refresh from the Supabase SQL editor as a database owner: REINDEX DATABASE "postgres" followed by ALTER DATABASE "postgres" REFRESH COLLATION VERSION. Supabase upgrades the underlying Postgres for you, and the collation version bookkeeping sometimes lags behind. These two statements bring it back in sync without touching your data.

database "postgres" has a collation version mismatch supabase

Error

database "postgres" has a collation version mismatch supabase

Steps

1. Open the Supabase dashboard for the project, go to the SQL editor, and confirm the mismatch:

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

Expected: datcollversion differs from actual_version. That confirms the warning is real and not a stale log line.

2. Reindex the postgres database from the same SQL editor:

REINDEX DATABASE "postgres";

Expected: the statement finishes with Success. The postgres database in a Supabase project is small, so this is quick.

3. Refresh the recorded collation version:

ALTER DATABASE "postgres" REFRESH COLLATION VERSION;

Expected: Success. This only updates Postgres bookkeeping, no data changes.

4. Verify the fix with the query from step 1.

Expected: datcollversion and actual_version now match, and new log lines stop mentioning the mismatch.

5. Check whether your own database needs it too. In Supabase your data lives in the postgres database under the public schema, so it is already covered. If you created extra databases, repeat the check for each:

SELECT datname FROM pg_database WHERE datistemplate = false AND datcollversion IS DISTINCT FROM pg_collation_actual_version(datcollate);

Expected: empty result. Any name listed needs the same two statements.

When to use

• The Supabase Postgres logs show the collation version mismatch on the postgres database
• It appeared right after Supabase applied a platform or Postgres minor upgrade
• Supabase support or a health check points at the postgres database specifically

When not to use

• The error is about your tables or queries (wrong sort order, missing collation), that is a schema problem, not this warning
• You run Postgres yourself on your own server, the self-hosted steps apply instead
• The warning names a database you cannot reach from the SQL editor, contact Supabase support

Tool compatibility

Supabase Postgres (all current versions, Postgres 15+). Uses the dashboard SQL editor, no server access needed. The postgres service role in the SQL editor has the rights for both statements.

Variant phrasings

supabase postgres logs WARNING collation version mismatch

Same warning, same fix. Supabase surfaces Postgres logs in the dashboard under Database, Logs, postgres. The two statements in the SQL editor clear it.

collation mismatch after supabase upgrade to postgres 15

Supabase moved projects to Postgres 15 where the REFRESH command exists. Older projects on PG 14 may need support help for the manual catalog update, but most projects are on 15+ now.

do i need to fix collation mismatch on supabase postgres database

Yes, it takes two statements and a minute. The postgres database holds system catalogs and extensions, and the warning also pollutes your logs, which makes real problems harder to spot.

Why it happens

Supabase manages the Postgres upgrade for you, including the OS libraries underneath. Postgres notices the OS collation library changed version but its recorded version still says the old one, and it warns you because indexes sorted under the old rules might misbehave. Supabase does not always run the refresh step as part of the upgrade, so you do it by hand.

Edge cases

• The SQL editor times out on REINDEX only for very large databases. The postgres database in Supabase is tiny, so this should not happen here.
• If ALTER DATABASE returns a permission error, you are not running as the postgres role. The default SQL editor session is, so just retry in a fresh SQL editor tab.
• The warning can reappear after the next Supabase platform upgrade. Re-running the check query after upgrades takes ten seconds and is worth making a habit.
• Dont run these on the auth or storage schemas data. The statements target the whole postgres database safely, they rebuild indexes, they dont delete anything.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstlx9AY65g29Y5TYFp-NXqg

Resolved from the public thread: https://vectle.com/posts/pstlx9AY65g29Y5TYFp-NXqg

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

TL;DR: Run the reindex and the collation refresh from the Supabase SQL editor as a database owner: REINDEX DATABASE "postgres" followed by ALTER DATABASE "postgres" REFRESH COLLATION VERSION. Supabase upgrades the underlying Postgres for you, and the collation version bookkeeping sometimes lags behind. These two statements bring it back in sync without touching your data.