Upgrading Postgres.app 9.1 to 9.2, getting lc_collate values do not match error

Question

I'm upgrading a Postgres.app on my Macbook Pro (my development machine) and I'm getting an error when I run:

pg_upgrade -b /Users/foo/Library/PostgreSQL/9.1/bin/ 
           -B /Applications/Postgres.app/Contents/MacOS/bin/ 
           -d /Users/foo/Library/Application\ Support/Postgres/var-9.1/ 
           -D /Users/foo/Library/Application\ Support/Postgres/var

The error I'm getting:

lc_collate cluster values do not match:  old "en_US", new "en_US.UTF-8"

How do I correct this?

Note: I used to have Postgres.app running 9.1, but I overwrote it. The directions I should have read/followed is: Upgrading Postgres.app. So the old bin is gone, but I still have the data directory. Instead I downloaded Postgres 9.1 and installed it elsewhere to use the bin directory to upgrade and move my data to the new Postgres.

score 3 · Answer 1 · answered Aug 01 '13 at 11:30

3

from http://www.postgresql.org/docs/9.0/static/pgupgrade.html

Initialize the new PostgreSQL cluster

Initialize the new cluster using initdb. Again, use compatible initdb flags that match the old cluster. Many prebuilt installers do this step automatically. There is no need to start the new cluster.

When you run initdb make sure --lc-collate=locale is the same as the old db e.g "en_us"

answered Aug 01 '13 at 11:30

user993553

429
3
8

The DB I'm migrating from is set to a locale that apparently doesn't exist on my system, so using the --locale flag with initdb is impossible. Am I missing something or was this overlooked with initdb? – Alkanshel Apr 08 '17 at 03:32

score 1 · Accepted Answer · edited Sep 01 '13 at 06:57

Since I'm migrating data and don't care about anything new in the database, I just deleted whatever was there (of course you can back it up to be safe):

rm -rf ~/Library/Application\ Support/Postgres/var

and created a new one with locale en_US, with the following:

initdb --locale=en_US -D ~/Library/Application\ Support/Postgres/var

Then I just ran the upgrade:

pg_upgrade -b ~/Library/PostgreSQL/9.1/bin/ 
           -B /Applications/Postgres.app/Contents/MacOS/bin/ 
           -d ~/Library/Application\ Support/Postgres/var-9.1/ 
           -D ~/Library/Application\ Support/Postgres/var

Then just run the analyzer and all was good.

./analyze_new_cluster.sh

I connected with pgAdmin3 and all the data was as it should be.

Upgrading Postgres.app 9.1 to 9.2, getting lc_collate values do not match error

2 Answers2