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.

43 thoughts on “Homebrew and PostgreSQL 9.5 (or 9.6)”

  1. 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?

    1. 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

      1. 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!

    2. 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.

  2. 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)?

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

  4. 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

  5. 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

    1. 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/ .......
      
  6. 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 .

  7. 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
    
  8. 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!

  9. 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?

    1. 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

      1. 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.

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

        1. 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.

    1. 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 *