[SOLVED] Phoenix ecto mismatch collation

hi my latest phoenix and elixir, psql (PostgreSQL) 15.3 (Debian 15.3-1.pgdg110+1)
had an issue

it always ** (Mix) The database for MyApp.Repo couldn’t be created: ERROR XX000 (internal_error) template database “template1” has a collation version mismatch
but there’s no “template1” database in my psql, and i dont even create that database before

hint: Rebuild all objects in the template database that use the default collation and run ALTER DATABASE template1 REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.                                 

The template database was created using collation version 2.36, but the operating system provides version 2.37.

when i try to run mix ecto.create and also all my previous database project had and warning about the collation version mismatch, because of that,i cant run mix ecto , so i cant add or create or update new database for my project

Hey @Gelato_Candy this isn’t really specific to Phoenix / Ecto, this is a postgres issue. The message indicates what to do. Connect to your database via psql and run: ALTER DATABASE template1 REFRESH COLLATION VERSION. You may need to do this to other databases beyond template1.

but on my ecto there’s no “template1” database available , also i cant even run the mix ecto.create in my phoenix project so i cant connected to my desired database if i cant even create that database

Again this isn’t about ecto. Your postgres is broken and you need to fix it before ecto can connect to it. You need to use psql. Go to your terminal and type psql template1.

See: postgresql - Collation version mismatch - Database Administrators Stack Exchange

1 Like

oh my god, its so simple than i thought

ALTER DATABASE template1 REFRESH COLLATION VERSION;
NOTICE: changing version from 2.36 to 2.37

thankyou so much, really appreciate

1 Like