Categories
English

Homebrew and PostgreSQL 9.5 (or 9.6)

Edit Sept. 30 2016: PostgreSQL 9.6 was released today, and these instructions should work — just replace 9.4 with 9.5 and 9.5 with 9.6. I also have a guide using pg_upgradecluster on Ubuntu.

PostgreSQL 9.5 was released on Jan. 7, with lots of exciting new features.

I wrote a post about upgrading from 9.3 to 9.4 in the past, and many people found it useful, so I decided to update it a bit for the 9.4 to 9.5 upgrade.

  1. Turn PostgreSQL off first: 
    $ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
# or, if you're running a current version of Homebrew
$ brew services stop postgresql
  2. Update PostgreSQL itself: 
    $ brew update && brew upgrade postgresql
  3. Make a new, pristine 9.5 database: 
    $ initdb /usr/local/var/postgres9.5 -E utf8
  4. Migrate the data to the new 9.5 database. Note that I have 9.4.5_2 in here, it could be that you aren’t on the latest version. Replace 9.4.5_2 with the most current version of postgres in that directory. 
    $ pg_upgrade \
  -d /usr/local/var/postgres \
  -D /usr/local/var/postgres9.5 \
  -b /usr/local/Cellar/postgresql/9.4.5_2/bin/ \
  -B /usr/local/Cellar/postgresql/9.5.0/bin/ \
  -v
  5. Move 9.5 data directory back to where PostgreSQL expects it to be: 
    $ mv /usr/local/var/postgres /usr/local/var/postgres9.4
$ mv /usr/local/var/postgres9.5 /usr/local/var/postgres
  6. Start PostgreSQL back up! 
    $ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
# or, if you're running a current version of Homebrew
$ brew services start postgresql

Note: If you’re using the pg gem for Rails, you should recompile:

$ gem uninstall pg
$ gem install pg

Note 2: If you’ve already uninstalled a previous version of PostgreSQL, there is a good post on StackOverflow with instructions to install previous versions.

49 replies on “Homebrew and PostgreSQL 9.5 (or 9.6)”

Is anyone else not getting the option to install PostgreSQL 9.6 via homebrew? When I do brew upgrade postgresql, I get the following:

“Error: postgresql 9.5.4_1 already installed”

Futhermore, running brew info postgresql shows this:

“postgresql: stable 9.5.4 (bottled), devel 9.6rc1”

Any ideas?

Unfortunately, PostgreSQL 9.6 isn’t available in Homebrew yet. Here’s a link to the relevant PR. It seems like due to the architecture of Homebrew, before releasing a new version of a package, all packages that depend on it need to be compatible with the new version. A couple packages haven’t been updated for PostgreSQL 9.6 compatibility yet, so they’re waiting on those before updating to 9.6. In the meantime, use it with Docker or something? :-S

Okay, that makes sense. Thanks for the response Keita and the tutorial above. I used it last year for 9.5 upgrade & was glad to see it pop-up on Google again this year. Well done!

You can use the Postgres.app instead.
Just remember to uninstall from Homebrew first.
Configure your $PATH to the new app (the Postgres.app documentation has all details on how to proceed)
You can also download for free from the Apple App store PG Commander to access your PostgreSQL tables.

This looks great! I’m currently running 9.3, will I be able to use this and leapfrop 9.4 or do I need to upgrade sequentially (9.3 -> 9.4 -> 9.5)?

Unfortunately you’re going to need to upgrade sequentially. Keep a backup of the database files just in case. 🙂

Hello,

I have upgraded the postgresql-9-5 but when I run the psql command it shows the following error:

/usr/lib/postgresql/9.5/bin/psql: symbol lookup error: /usr/lib/postgresql/9.5/bin/psql: undefined symbol: PQsslInUs

Any idea ?

Thank you for this! Well-written, easy to follow. Just did what you said and worked like a charm!

Worked like a charm running Brew on OSX 10.10.4, going from postgres 9.4.4 to 9.5.

Thanks very much!

Great post, very helpful. Wondering if someone here can help me, I somehow borked my postgres db last night. I was running 9.4.*, and then accidentally re-installed Homebrew (as a result of working on a another project and not thinking), which auto-installed postgres 9.5.1. I went ahead and re-installed 9.4.5 binary, then switched back to 9.5.1 and am now trying to run pg_upgrade with this command:

pg_upgrade -d /usr/local/var/postgres \
-D /usr/local/var/postgres9.5 \
-b /usr/local/Cellar/postgresql94/9.4.5/bin/ \
-B /usr/local/Cellar/postgresql/9.5.1/bin/ -v

but running into the following error in Terminal:

failure
There were problems executing “”/usr/local/Cellar/postgresql94/9.4.5/bin/pg_ctl” -w -l “pg_upgrade_server.log” -D “/usr/local/var/postgres” -o “-p 50432 -b -c listen_addresses=” -c unix_socket_permissions=0700 -c unix_socket_directories=’/Users/username/Projects/ProjectName'” start >> “pg_upgrade_server.log” 2>&1″
Consult the last few lines of “pg_upgrade_server.log” for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket “/Users/username/Projects/ProjectName/.s.PGSQL.50432”?

And here’s the log from pg_upgrade_server.log:

pg_upgrade run on Thu Mar 10 19:51:23 2016

command: “/usr/local/Cellar/postgresql94/9.4.5/bin/pg_ctl” -w -l “pg_upgrade_server.log” -D “/usr/local/var/postgres” -o “-p 50432 -b -c listen_addresses=” -c unix_socket_permissions=0700 -c unix_socket_directories=’/Users/username/Projects/ProjectName'” start >> “pg_upgrade_server.log” 2>&1
waiting for server to start….LOG: database system shutdown was interrupted; last known up at 2016-03-10 17:50:44 PST
LOG: database system was not properly shut down; automatic recovery in progress
LOG: record with zero length at 0/2BE6970
LOG: redo is not required
FATAL: could not open directory “pg_logical/snapshots”: No such file or directory
LOG: startup process (PID 11160) exited with exit code 1
LOG: aborting startup due to startup process failure
…. stopped waiting
pg_ctl: could not start server
Examine the log output.

Not quite sure what I’m doing wrong. Been trying for 2 days to figure it out. Any ideas? I feel like I messed something up in the original accidental update, but don’t know where to look next. Thanks in advance for any help

Thanks for the instructions. I received this error message:

pg_upgrade -d /usr/local/var/postgres/ -D /usr/local/var/postgres9.5/ -b /usr/local/Cellar/postgresql94/9.4.5/bin/ -B /usr/local/Cellar/postgresql/9.5.1/bin/ -v
Running in verbose mode

Performing Consistency Checks

Checking cluster versions
New cluster data and binary directories are from different major versions.
Failure, exiting

I think it might be because the pg_upgrade you’re referring to is the 9.5.1 version? Try checking the version using the following command: pg_upgrade -V.

If it is 9.5.1, try running 9.4’s pg_upgrade using an absolute path:

/usr/local/Cellar/postgresql94/9.4.5/bin/pg_upgrade -d /usr/local/var/postgres/ -D /usr/local/var/postgres9.5/ .......

Very useful, thank you! If someone is using pgAdmin, you should upgrade it either to pgAdmin v1.22.0.

Thank you for posting these instructions. Worked like a charm.

A fantastic time saver that I greatly appreciate.

Cheers, S t u a r t .

I just wanted to thank you for this. It was exactly what I was looking for. Clear, helpful, perfect.

I had a similar problem to Michael Johnston, only in my case…

FATAL:  incompatible library "/usr/local/lib/postgresql/pg_stat_statements.so": version mismatch
DETAIL:  Server is version 9.4, library is version 9.5.
.... stopped waiting
pg_ctl: could not start server

I’ve posted a reply to another comment, that describes how to work around this issue.

On my macbook it worked like a charm.
On my iMac, life was not so easy.
First it didn’t stop postgres with the unload.
So: pg_ctl -D /usr/local/var/postgres stop
during the upgrade it stopped on a library not found. $lib/mysql_fdw.
I experimented with this some time ago. Trying to start postgres failed. pg_ctl to the rescue:
pg_ctl -D /usr/local/var/postgres -p /usr/local/Cellar/postgres/9.4.5_2/bin/postgres start
Using grep mysql_fdw pg_* in the local directory gave me the dump file that contains the extension. Scrolling through the output above, I found the database, I dropped the database, stopped postgres using pg_ctl again and reran the upgrade. Success!

When running the pg_upgrade command, on two different machines, I get the following error:

pg_dump: [archiver (db)] query failed: ERROR:  incompatible library "/usr/local/lib/postgresql/hstore.so": version mismatch
DETAIL:  Server is version 9.4, library is version 9.5.

on databases that use hstore. I was unable to figure out why, and so am temporarily downgrading to 9.4. Anyone have any ideas, or successfully upgrade to 9.5 with databases that use hstore?

It’s possible, but it won’t be fun:

1) While still on 9.4 make sure your hstore databases no longer use the deprecated => operator:

for db in `echo 'SELECT datname FROM pg_database;' |/usr/local/Cellar/postgresql/9.4.5_2/bin/psql -t`;do echo $db; echo 'ALTER EXTENSION "hstore" UPDATE;' | /usr/local/Cellar/postgresql/9.4.5_2/bin/psql $db;done

2) stop 9.4 server, brew upgrade postgresql and initdb the new data directory as in the guide

3) Rename 9.5 pg_ctl to pg_ctl.bak and create a shell wrapper as pg_ctl:

#!/bin/sh
ln -sf /usr/local/Cellar/postgresql/9.5.0/lib/postgresql /usr/local/lib/
exec /usr/local/Cellar/postgresql/9.5.0/bin/pg_ctl.bak "$@"

4) Rename 9.5 pg_dump to pg_dump.bak and create a shell wrapper pg_dump:

#!/bin/sh
ln -sf /usr/local/Cellar/postgresql/9.4.5_2/lib/postgresql /usr/local/lib/
exec /usr/local/Cellar/postgresql/9.5.0/bin/pg_dump.bak "$@"

5) Rename 9.5 pg_dumpall to pg_dumpall.bak and create a shell wrapper pg_dumpall:

#!/bin/sh
ln -sf /usr/local/Cellar/postgresql/9.4.5_2/lib/postgresql /usr/local/lib/
exec /usr/local/Cellar/postgresql/9.5.0/bin/pg_dumpall.bak "$@"

6) Make the wrappers executable with chmod +x

7) Start pg_upgrade as in the guide (this also works with the -k switch, if your low on space, like me)

8) Remove the shell wrappers and rename the .bak files to their original name

9) Swap your data directories as in the guide

10) Start the 9.5 server and analyze it using the generated analyze_all.sh script

11) Enjoy postgres 9.5 and grab yourself a coffee for successfully working around the buggy pg_upgrade

Essentially what the shell wrappers do is swap around between the 9.5.0 and 9.4.5 lib directories at the right points in the migration process. Because pg_dump needs the 9.4 versions and pg_ctl starts the server which needs the 9.5 ones.

Be sure to replace the fancy typographic quotes in the above post with normal single/double quotes or things will fail.

It’s possible that despite the ALTER EXTENSION hstore UPDATE you still have the deprecated => operator in your database and the upgrade will fail, but you can now safely drop it on postgres 9.4 by running DROP OPERATOR => (text, text);. If you try this before updating hstore it might fail, because the operator is still referenced.

Thank you so much – your fix for the library mismatch problem has just saved me in an upgrade from 9.5 to 9.6. The instructions were really clear and you’ve saved me loads of time!

If you haven’t done brew cleanup, the old version should still be in there. Check /usr/local/Cellar/postgresql/ and see if it’s there. If not, you’ll have to jump through some hoops to reinstall it…

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.