How do I upgrade postgresl database? Incompatibility error

Question

I installed postgresql via Homebrew.

I have the following issue after upgrading:

FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 9.0, which is not compatible with this version 9.1.2.

Any tips on how to upgrade? I tried the following:

$ pg_upgrade -d /usr/local/var/postgres/ -D /usr/local/var/postgres -b 
/usr/local/Cellar/postgresql/9.0.4/bin -B /usr/local/Cellar/postgresql/9.1.2/bin

It didn't work. Here's the output.

Performing Consistency Checks
Checking current, bin, and data directories                 ok
Checking cluster versions                                   
This utility can only upgrade to PostgreSQL version 9.1.
Failure, exiting

error.

Answer 1

For me on OS X with Homebrew it was like this.

1. Installed new postgres with Homebrew (started getting the error)
2. mv /usr/local/var/postgres /usr/local/var/postgres.old
3. initdb -D /usr/local/var/postgres
4. pg_upgrade -b /usr/local/Cellar/postgresql/9.0.4/bin -B /usr/local/Cellar/postgresql/9.1.2/bin -d /usr/local/var/postgres.old -D /usr/local/var/postgres
5. ./delete_old_cluster.sh (this script is created for you automatically in current dir when you go through above steps)
6. rm delete_old_cluster.sh

Answer 2

Here's how I did it on fedora:

• rename your old data directory to something like data.old
• run postgresql-setup initdb this will create a new data directory
• then run pg_upgrade -b /usr/lib64/pgsql/postgresql-9.0/bin/ -B /usr/bin/ -d data.old/ -D data

I think for you that would be:

pg_upgrade -b /usr/local/Cellar/postgresql/9.0.4/bin -B /usr/bin/ -d /usr/local/var/postgres.old/ -D /usr/local/var/postgres/

• you also want to copy pg_hba.conf and postgresql.conf from data.old to the new data directory.
• restart postgresql