pg_upgrade: "lc_collate values for database "postgres" do not match"
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
.
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;