Upgrading from PG 9.1 to PG 9.2 with PostgreSQL.app

pg91_to_92.md

Upgrading from PG 9.1 to 9.2

WARNING

Don't just try and install it. You need both the new and old versions of Postgres.app to migrate the database.

Increase PG Memory

http://benscheirman.com/2011/04/increasing-shared-memory-for-postgres-on-os-x

sudo sysctl -w kern.sysv.shmmax=1610612736
sudo sysctl -w kern.sysv.shmall=393216
sudo sysctl -w kern.sysv.shmmin=1
sudo sysctl -w kern.sysv.shmmni=32
sudo sysctl -w kern.sysv.shmseg=8
sudo sysctl -w kern.maxprocperuid=512
sudo sysctl -w kern.maxproc=2048

Follow these steps

1. Shut down your old Postgres.app
2. Move ~/Library/Application\ Support/Postgres/var to ~/Library/Application\ Support/Postgres/var-9.1
3. Move /Applications/Postgres.app to ~/Desktop/pg91/Postgres.app (any directory works, I picked pg91).
4. Install the new Postgres.app in Applications and run it to create the var directory.
5. Shut down the new Postgres.app
6. Run the pg_upgrade command:

/Applications/Postgres.app/Contents/MacOS/bin/pg_upgrade -d var-9.1 -D var -b ~/Desktop/pg91/Postgres.app/Contents/MacOS/bin -B /Applications/Postgres.app/Contents/MacOS/bin

If you're like me and you deleted Postgres before reading this use Time Machine to recover ~/Library/Application\ Support/Postgres/var and /Applications/Postgres.app and then reboot.

I had a lot of trouble with Postgres.app pointing to a var folder in the Trash, so watch out for that too.