{"id":533,"date":"2014-03-19T21:07:53","date_gmt":"2014-03-20T03:07:53","guid":{"rendered":"https:\/\/www.galador.org\/blog\/?p=533"},"modified":"2014-03-19T21:15:49","modified_gmt":"2014-03-20T03:15:49","slug":"upgrading-brew-installed-postgresql-on-osx","status":"publish","type":"post","link":"https:\/\/www.galador.org\/blog\/upgrading-brew-installed-postgresql-on-osx\/","title":{"rendered":"Upgrading Brew-Installed PostgreSQL on OSX"},"content":{"rendered":"

Here’s the tl;dr: If you want to see the full instructions, look further down:<\/p>\n

\r\nnew_pg=9.3.3 #CHANGE THIS!!\r\npsql postgres -c \"select version()\" #make sure you've still got the old version running\r\npg_dumpall > pg_backup.sql\r\nlaunchctl unload ~\/Library\/LaunchAgents\/homebrew.mxcl.postgresql.plist\r\nsudo mv \/usr\/local\/var\/postgres \/usr\/local\/var\/postgres.old\r\nsudo brew switch postgresql $new_pg\r\ninitdb \/usr\/local\/var\/postgres -E utf8\r\nlaunchctl load -w ~\/Library\/LaunchAgents\/homebrew.mxcl.postgresql.plist\r\ncreatedb #run this if you get an error when trying to import the dump below\r\npsql < pg_backup.sql\r\npsql postgres -c \"select version()\" #make sure you've got the new version running\r\npsql -l #see if all of your databases are still there\r\nvacuumdb --all --analyze-only #optional\r\nsudo brew cleanup postgresql #this will remove the older versions brew installed\r\nsudo rm -rf \/usr\/local\/var\/postgres.old #if you're feeling really confident \/ don't care\r\n<\/pre>\n
Somehow, whenever I sit down to do some tinkering on my computer, it seems like a new version of PostgreSQL<\/a> is ready to be installed via Brew<\/a>. Since I always forget what the steps are to upgrade between major versions of Postgres, I figured I'd throw it into a blog post. Mostly for my own pleasure, but others might find it useful, as well.<\/p>\n
First, you realize that Brew is installing the new version, and you panic! \"What about my data!?\" you might say. Don't worry, the old version is still running. You can verify this by opening psql<\/code>, and running select version()<\/code>. You should see your old version. We need to dump your databases so that you can restore them to your new version:<\/p>\n
pg_dumpall > pg_backup.sql<\/pre>\n
Now, we need to stop the old version, move the old cluster to a safe place (in case we need to revert for some reason), tell brew we want to use the new version as the default, and start the new one. It sounds complicated, but it's not:<\/p>\n
launchctl unload ~\/Library\/LaunchAgents\/homebrew.mxcl.postgresql.plist\r\nsudo mv \/usr\/local\/var\/postgres \/usr\/local\/var\/postgres.old\r\nsudo brew switch postgresql 9.3.3\r\ninitdb \/usr\/local\/var\/postgres -E utf8\r\nlaunchctl load -w ~\/Library\/LaunchAgents\/homebrew.mxcl.postgresql.plist<\/pre>\n
Now, restore the dump into the new version:<\/p>\n
psql < pg_backup.sql<\/pre>\n
If you get an error saying that the database (usually named the same as your login) could not be found, just crate it, using the following command:<\/p>\n
createdb<\/pre>\n
You should now be good to go! As a bonus, if you get this error (which I did)<\/p>\n
\u00e2\u017e\u0153  ~  psql
\ndyld: Library not loaded: \/usr\/local\/opt\/readline\/lib\/libreadline.6.2.dylib
\n  Referenced from: \/usr\/local\/bin\/psql
\n  Reason: image not found
\n[1]    39772 trace trap  psql<\/code><\/p>\n
You either need to install readline<\/code> from brew, or it needs to be re-linked:<\/p>\n
sudo brew install readline\r\nsudo brew link --force readline<\/pre>\n
If you had to install readline, then you will need to re-install postgres:<\/p>\n
sudo brew reinstall postgresql<\/pre>\n","protected":false},"excerpt":{"rendered":"
Here’s the tl;dr: If you want to see the full instructions, look further down: new_pg=9.3.3 #CHANGE THIS!! psql postgres -c “select version()” #make sure you’ve still got the old version running pg_dumpall > pg_backup.sql launchctl unload ~\/Library\/LaunchAgents\/homebrew.mxcl.postgresql.plist sudo mv \/usr\/local\/var\/postgres \/usr\/local\/var\/postgres.old sudo brew switch postgresql $new_pg initdb \/usr\/local\/var\/postgres -E utf8 launchctl load -w ~\/Library\/LaunchAgents\/homebrew.mxcl.postgresql.plist createdb … Continue reading Upgrading Brew-Installed PostgreSQL on OSX<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[61,62],"tags":[],"class_list":["post-533","post","type-post","status-publish","format-standard","hentry","category-database","category-postgres"],"_links":{"self":[{"href":"https:\/\/www.galador.org\/blog\/wp-json\/wp\/v2\/posts\/533","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.galador.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.galador.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.galador.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.galador.org\/blog\/wp-json\/wp\/v2\/comments?post=533"}],"version-history":[{"count":14,"href":"https:\/\/www.galador.org\/blog\/wp-json\/wp\/v2\/posts\/533\/revisions"}],"predecessor-version":[{"id":547,"href":"https:\/\/www.galador.org\/blog\/wp-json\/wp\/v2\/posts\/533\/revisions\/547"}],"wp:attachment":[{"href":"https:\/\/www.galador.org\/blog\/wp-json\/wp\/v2\/media?parent=533"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.galador.org\/blog\/wp-json\/wp\/v2\/categories?post=533"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.galador.org\/blog\/wp-json\/wp\/v2\/tags?post=533"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}