Upgrading to PostgreSQL 8.4.1 from KyngChaos
Today I wanted to upgrade my PostgreSQL install to 8.4.1 (from 8.3.x) based on the KyngChaos package I'd just started using. It's as much a test of the upgrade process as it is about any 8.4.x feature in PostgreSQL. It's really not all that hard, just a few steps in the right order.
First, open up a Terminal.app window and become the user postgres:
su - postgres
then, dump the database. Assuming you're in the postgres user's home directory - /Users/postgres, you can do something like:
/usr/local/pgsql/bin/pg_dumpall > postgresql_20090930.dump
and the dump will be tagged with the date. Easy.
Next, turn off the existing PostgreSQL engine, and set set the old install aside:
sudo launchctl unload /Library/LaunchDaemons/org.postgresql.postgres.plist cd /usr/local sudo mv pgsql pgsql_old
Next, install the new package. If things are going well, the new engine will be started by the installer. As long as the launchd file exists, you should be OK.
Next, load the databases. Back in the postgres user's Terminal window:
/usr/local/pgsql/bin/psql -d template1 -f postgresql_20090930.dump
Check that the databases are back and functioning. Then it's just a matter of a little cleanup:
sudo rm -rf /usr/local/pgsql_old