I recently performed an upgrade on my development PostgreSQL server and found the information harder to get together than the actual process. There were a number of steps that were not immediately obvious to me from a MySQL background. The following is a quick overview from my notes, which I will expand and attempt to keep up to date as caveats and changes are discovered from future updates.
My current development environment is Mac OS X Mountain Lion using the fantastic Homebrew as my package manager.
$ brew update
$ psql --version # «OLD_VERS»
$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ mv /usr/local/var/postgres /usr/local/var/postgres.old
$ brew upgrade postgresql
$ psql --version # «NEW_VERS»
$ initdb /usr/local/var/postgres -E utf8
$ pg-upgrade --old-datadir /usr/local/var/postgres.old \
--new-datadir /usr/local/var/postgres \
--old-bindir /usr/local/Cellar/postgresql/«OLD_VERS»/bin \
--new-bindir /usr/local/Cellar/postgresql/«NEW_VERS»/bin
$ ./delete_old_cluster.sh
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ ./analyze_new_cluster.sh
$ brew cleanup postgresql
Also worth mentioning these particular notes about PostgreSQL as they're mildly related:
- When in doubt, check the logs:
/usr/local/var/postgres/server.log
- If you ever forget where the logs or the data, lib or bin directories are, check out the
LaunchAgent plist file as it has those as parameters:
~/Library/LaunchAgent/homebrew.mxcl.postgresql.plist
- If you ever think PostgreSQL should be running but it's not, the logs will likely mention
something about it thinking there is already an instance running. This probably means there is a
leftover PID file in the data directory:
/usr/local/var/postgres/postmaster.pid