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.

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.

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

1. Refresh the recorded collation version:

ALTER DATABASE "postgres" REFRESH COLLATION VERSION;

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

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

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

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