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

postgresqlupgrade

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.

Best Answer

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"

Related Topic