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

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.

Solution 1:

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"

Solution 2:

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.