I am working on testing a method for postgres 9.3 to 10 upgrade. I currently am at the dry run phase and ran into a few issues. The current blocking one is lc_collate values for database "postgres" do not match: old "C.UTF-8", new "en_US.UTF-8", I get this when I run the below command:

time ${PGBINNEW}/pg_upgrade --old-bindir=${PGBINOLD} --new-bindir=${PGBINNEW} --old-datadir=${PGDATAOLD} --new-datadir=${PGDATANEW} --old-port=${PGPORT} --new-port=$5433 --jobs=$(nproc | awk '{ {print ($1 > 4) ? 4 : ($1 == 1 ? 1 : $1 - 1)} }') --link --username=postgres --check

Any ideas on what might be happening here?

  • collation on the databases are different, update the collation on old (9.3) before retrying to upgrade – Tensibai Sep 5 '18 at 14:21
  • @Tensibai I am trying to upgrade the database yes. When I am going through the upgrade process I get this error. It is part way through the upgrade process. – user8517 Sep 5 '18 at 14:23
  • And the error is quite clear, the new system doesn't use the same collation and the tool can't convert by itself, so you have to convert the collation of the postgres database on the origin first. – Tensibai Sep 5 '18 at 14:27
  • @Tensibai is there a way to see what the current one is and set the new DB to have the same one then? – user8517 Sep 5 '18 at 14:32
  • Did you read the error message ? all is there – Tensibai Sep 5 '18 at 15:01

I solved this thanks to Reddit post. It was fairly simple when combined with another part of my testing.

I used the code {PGBINNEW}/initdb --pgdata="$PGDATANEW" --encoding=UTF8 --no-locale to reinit the DB AFTER I did the below command:

sudo rm -rf ${PGBASEDIR}/data10

What happened was I cleared the old DB init config and then put in the config I needed without the localization of the database


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy