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.
- 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
- Update PostgreSQL itself:
$ brew update && brew upgrade postgresql
- Make a new, pristine 9.5 database:
$ initdb /usr/local/var/postgres9.5 -E utf8
- 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. Replace9.4.5_2
with the most current version ofpostgres
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
- 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
- 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)”
[…] tried to follow this tutorial (from 9.4 to 9.5) for upgrading 9.6.1, but keep getting this error […]
Thanks, this worked for 9.6 to 10.1 as well.
[…] along with Homebrew in this example. I have also seen a good post from Christopher Brown and Keita on manually backing up and copying data files using pg_upgrade. These involve a few more steps that […]
[…] Eventually, I ran into what looks like the answer in two related locations: a blog post describing upgrades to PostgreSQL and another one describing the upgrade process using brew. Apparently, I had two versions of […]
Massively useful – thanks!
Thank you very much for this. Just exactly what I’m looking for. Bookmarked!
This was great going from 9.5 to 9.6 as well!
It worked for me too! Many thanks! 🙂
9.3.4 to 9.6.1 !!!
Kudos to you!
Worked perfectly going from 9.3.4 to 9.6.1
Thank you! Worked.
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.
Thanks for sharing!
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 ?
I’m not certain, but you can try following some of the suggestions here: http://stackoverflow.com/questions/34886981/postgresql-errors-install-and-run-postgresql-9-5
Thank you for this! Well-written, easy to follow. Just did what you said and worked like a charm!
Thanks for documenting this. Worked like a charm.
Worked like a magic, awesome post
[…] After this, finished with setup. Use this excellent blog post about upgrading, starting with item 3. […]
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:This saved me so much time. Thanks a million times over.
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 .
This was super helpful, thank you :+1:
This blog post was super helpful! Thanks so much, Keita!
Thanks, this fixed my problem on OSX and Homebrew:
postgres 9.4 database files incompatible with 9.5
🙂
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…
I’ve posted a reply to another comment, that describes how to work around this issue.
I found the reason why postgres wasn’t stopped.
Don’t run launchctl from within a tmux session.
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!
The instructions worked perfectly for me, thanks for the guide.
When running the pg_upgrade command, on two different machines, I get the following error:
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: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:
4) Rename 9.5 pg_dump to pg_dump.bak and create a shell wrapper pg_dump:
5) Rename 9.5 pg_dumpall to pg_dumpall.bak and create a shell wrapper pg_dumpall:
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 runningDROP 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!
Very useful writeup, thanks!
Yeah thanks a lot, this save my morning! I shared.
If was removed the old version 9.4.x? it’s a problem? ????
tks
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…