32

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.

Improve this question

5 Answers 5

Reset to default
42

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
Improve this answer
4
  • Fantastic and just what i needed friend! thanks. Espeically after a Lion upgrade coming from Snow Leopard. Check the server.log google people!!
    – pjammer
    Mar 3, 2012 at 21:45
  • This worked perfectly for me, thanks! Note that postgres must not be running before doing these steps, so if you have installed it through Homebrew make sure to unload the launch agent that makes it start automatically: launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist. After all the above steps are done, just load it again: launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
    – Markus Amalthea Magnuson
    May 18, 2015 at 16:13
  • I get permission denied when trying to mv Postgres to Postgres.old, how should I tackle it?
    – Joseph K.
    Sep 1, 2020 at 23:22
  • 1
    This answer is outdated, you should try Frederik’s solution first, and run `brew postgresql-upgrade-database. It is what the brew upgrade suggests.
    – kotchwane
    Oct 11, 2021 at 9:14
14

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.

Improve this answer
1
  • This worked for me! Thanks!
    – Davide C
    Oct 13, 2021 at 21:25
7

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
Improve this answer
1

I missed/forgot the initdb line:

initdb -D /usr/local/var/postgres

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

Improve this answer
0

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'
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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