Heroku recently unveiled support for Postgres 9.2, offering performance improvements and several new features. Before migrating our production database at Jobber, we want to run it in development for a few days.
I’ve been using Postgres.app on my Mac for a while now. Patches are seamless, but migrating from 9.1 to 9.2 takes some command line acrobatics. The documentation sets you on the right track, but I ran into a few gotchas.
These instructions are for upgrading to Version 18.104.22.168 (20). If you have a newer version, things may have improved!
- Keep your old copy of Postgres.app, you’ll need it. After Quitting, move it to the desktop.
- Download the new version of Postgres.app and install it to Applications.
- Launch the new version of Postgres. You need the right-click+open trick on Mountain Lion to get around Gatekeeper. It will move your old database files to a
var-9.1folder and initialize a new database in its place.
- Fiddle in
psqlto prepare the new database (see below).
- Quit out of Postgres.app and perform the upgrade.
Database already exists
My first attempt failed.
The new data files already had a
nathany database (my username), so
pg_upgrade couldn’t create it. I’m sure there are a few ways to solve this problem, depending on whether or not you want to migrate this database or discard it. Let’s keep it.
We’ll need to use
psql with the new Postgres.app running. Here are a few helpful Meta-Commands:
|Command||What it does|
|\l||list databases (press
|\c name||switch databases (connect)|
It won’t DROP a database while connected to it, so I ended up creating a new temporary database. Use a name that won’t cause conflicts:
nathany=# CREATE DATABASE temporary_database; CREATE DATABASE nathany=# \c temporary_database You are now connected to database "temporary_database" as user "nathany". temporary_database=# DROP DATABASE nathany; DROP DATABASE temporary_database=# \q
nathany for your username.
Now we can quit Postgres.app and run the upgrade. It requires absolute paths (no ~/ shortcuts). Substitute
nathany for your username once again.
pg_upgrade --old-datadir "/Users/nathany/Library/Application Support/Postgres/var-9.1" --new-datadir "/Users/nathany/Library/Application Support/Postgres/var" --old-bindir "/Users/nathany/Desktop/Postgres.app/Contents/MacOS/bin" --new-bindir "/Applications/Postgres.app/Contents/MacOS/bin"
This assumes that the new version of Postgres.app is in Applications and the old version is on your Desktop.
You can fire up Postgres.app and
psql to drop that temporary database:
nathany=# DROP DATABASE temporary_database; DROP DATABASE nathany=# \q
When the upgrade completes, it suggests you run:
So do that. And finally, you can delete the old version of Postgres.app. Phew!
That’s A Wrap
You can follow Postgres.app on Twitter to find out when you get to do this all over again. :-)
Now all I have left to do is find a nice database client for Postgres. It looks like the two new candidates are Tuples 2 and Induction (very alpha), though Navicat has been steadily improving as well. Hmm.
Hit me up on Twitter if you have any suggestions for improving this article. Thanks!