I love Postgres.app it really is the quickest and easiest way to get a postgres database running on your mac.
But I just had some bad time upgrading from Postgres.app 9.2 to 9.3.
So I wrote this short step by step guide to help you upgrade your database without losing all the data you already have there.
- Step 1 - Make sure your PG binaries are linked correctly into your PATH and that you’ll be using the correct
pg_upgradecommands by checking that the following line appears in your
.zshrc(which ever you use)
- Step 2 - Open a new terminal window and run
which psqlit should point to
/Applications/Postgres.app/Contents/MacOS/bin/psql- If it doesn’t check Step 0 again.
- Step 3 - Download the new Postgres.app.
- Step 4 - Close the Postgres.app if it’s currently running.
- Step 5 - Copy your old 9.2 Postgres.app from /Applications to your desktop.
- Step 6 - Move the newly downloaded Postgres.app to /Applications and launch it.
- Step 7 - Run
psqland connect to the DB.
- Step 8 - In the psql run
\lto get a list of databases. You should have 4 databases: postgres, 2 templates and one database for your username.
- Step 9 - Run
\c postgresto move to the postgres database from your user’s database. This is necessarily since you can’t drop the database you are currently in (see next step).
- Step 10 - Run
DROP DATABASE username;(replace username with your username) to remove your user’s database.
- Step 11 - Run
\qto close psql.
- Step 12 - Close Postgres.app
- Step 13 - In the terminal run the following command making sure that all the locations are correct.
pg_upgrade --old-datadir ~/Library/Application\ Support/Postgres/var \ --new-datadir ~/Library/Containers/com.heroku.postgres/Data/Library/Application\ Support/Postgres/var \ --old-bindir ~/Desktop/Postgres.app/Contents/MacOS/bin \ --new-bindir /Applications/Postgres.app/Contents/MacOS/bin
- Step 14 - Let the upgrade command run, once it’s finished successfully it will print out
- Step 15 - Start Up Postgres.app again
- Step 16 - Run
./analyze_new_cluster.shto get your DB ready for business
(Optional Last Step) - Run
./delete_old_cluster.sh to remove the old 9.2 data and delete the old Postgres.app from your desktop.
I’m still having problems - what to do?
This is what I did whenever I had issues during the upgrade process and needed a hard-reset.
- Step -1 - Close the Postgres.app
- Step -2 - Delete the
- Step -3 - Reopen the Postgres.app let it rebuild the directory and start again from Step 7 in the guide.
Pro Tip #3 - Postgres Client for Mac
I’ve been looking a long time for a Postgres client for Mac that will be even a fraction of what Sequel Pro is for MySQL.
Well, I just found out about a pretty new client that shows a lot of potential and is really good (at least for my uses)
PG Commander you should definitely check it out.
Hope this helps a few people.
You can always find me on twitter @yonbergman.