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.

Daniel Fischer
  • 669
  • 1
  • 8
  • 11

5 Answers5

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
Max Chernyak
  • 650
  • 8
  • 21
  • 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 03 '12 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 '15 at 16:13
  • I get permission denied when trying to mv Postgres to Postgres.old, how should I tackle it? – Joseph K. Sep 01 '20 at 23:22
  • 1
    This answer is outdated, you should try [Frederik](https://serverfault.com/a/1051073/560395)’s solution first, and run `brew postgresql-upgrade-database. It is what the brew upgrade suggests. – kotchwane Oct 11 '21 at 09: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.

Frederik
  • 240
  • 2
  • 3
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
Khaled
  • 36,533
  • 8
  • 72
  • 99
imel96
  • 406
  • 4
  • 9
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.

J. Scott Elblein
  • 179
  • 1
  • 1
  • 11
user321204
  • 11
  • 1
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'
Rudy Matela
  • 111
  • 4