Home / server / Questions / 342626
Accepted
Daniel Fischer
Asked: 2011-12-20 22:51:00 +0800 CST

How do I upgrade postgresl database? Incompatibility error

  • 772

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.

postgresql

5 Answers

  1. Best Answer

    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
    • 42

  2. If you’re on macOS and installed Postgres via Homebrew you can simply run:

    brew postgresql-upgrade-database

    This will ensure that the old Postgres version is installed, create a new database, and then migrate it via pg_upgrade. The old data will remain in /usr/local/var/postgres.old.

    • 14

  3. 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
    • 7

  4. I missed/forgot the "initdb" line

    initdb -D /usr/local/var/postgres

    After the DB was created, the pg_upgrade worked on my windows system.

    • 1

  5. For Arch Linux, there is a solution in the wiki that worked fine for me:

    pacman -S --needed postgresql-old-upgrade
su -
su - postgres -c 'mv /var/lib/postgres/data /var/lib/postgres/data-9.2'
su - postgres -c 'mkdir /var/lib/postgres/data'
su - postgres -c 'initdb --locale en_US.UTF-8 -E UTF8 -D /var/lib/postgres/data'
su - postgres -c 'pg_upgrade -b /opt/pgsql-9.2/bin/ -B /usr/bin/ -d /var/lib/postgres/data-9.2 -D /var/lib/postgres/data'
    • 0