pg_upgrade: "lc_collate values for database "postgres" do not match"

Question

I've just upgraded (using Homebrew) my postgreSql and postGis installations. I can no longer 'brew postgresql-upgrade-database' (as it's telling me that my data is 'error: already upgraded' from a previous attempt; it's not, since the attempt failed), and going the manual 'pg_upgrade' route is fine, until treating 'template1' database throws a

    lc_collate values for database "postgres" do not match:  old "C", new "en_US.UTF-8"

error. I did not create that 'template1' database, so it is expendable (if not somehow needed) if it somehow possible to override the string-match requirement. Thanks in advance for any advice.

Answer 1

Simply update the collation and ctype encodings :

UPDATE pg_database SET datcollate='en_US.UTF-8', datctype='en_US.UTF-8' WHERE datname='postgres';

Repeat this query for template1 & template0 as well — or if you only have those three databases you can just drop the WHERE clause.

Check with \l.

Answer 2

You are unclear about which database is affected: postgres or template1.

To use pg_upgrade, the new and old cluster must be installed with the same locale. So try

initdb --locale=C ...

to create the new cluster.

template1 is an essential database – without it, CREATE DATABASE will have a problem. You should also retain the postgres administrative database.

If both databases have different locale, you need to create the new cluster in the same way, for example:

DROP DATABASE postgres;
CREATE DATABASE postgres
   LC_COLLATE 'C' LC_CTYPE 'C'
   TEMPLATE template0;