5

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.

2 Answers 2

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"

1
  • 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? Apr 8, 2017 at 3:32
1

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.