Upgrading to PostgreSQL 9.1.0 from KyngChaos
I have been a big fan of the KyngChaos builds of PostgreSQL for quite a while. After Marc L. stopped posting his, it made sense to find another source, and while I've heard that Apple's Mac OS X Lion Server has PostgreSQL as opposed to MySQL which it had in Snow Leopard, I haven't seen a lot about this part of Lion Server, and I'm consequently a little nervous about getting Server on my laptop and not being happy with the burden it places on my box. If I had a nice Mac Pro, I'd get it - but that's not (yet) the case.
So here's what I noticed in the update from PostgreSQL 8 to 9.0.4, and on Mac OS X Lion. There's a lot of little things, and it made for a somewhat hybrid scheme that I'm going to try and get rid of today with the upgrade to PostgreSQL 9.1.
First off, Mac OS X 10.7 has psql on it already. It's got a complete PostgreSQL client in it. It happens to be 9.0.4, which is nice, as that matched the version I was targeting. Given that, it's only the server I need to install. Not bad, but it's all a package anyway, so no big deal.
Secondly, in 10.7, the PostgreSQL user is _postgres and it's not a user that can login. This means that a lot of the update schemes that I used to use don't work because you can't log in as the postgres user any more. However, there seems to be a better scheme - assuming you're migrating from a recent version. The update procedures are detailed in a file in the disk image you download from the site, but I haven't been able to get it to work. I'll try on the next update again, but this dump-and-load works really well for me and does not require that we have a postgres user to log in as.
Step 1 - we need to get a complete dump of the database. It's pretty easy, just get to anyplace you can write a file - I used my home directory, and issue the following command as yourself:
/usr/local/pgsql/bin/pg_dumpall -U _postgres -o > pgbackup
Step 2 - stop (unload) the existing version of PostgreSQL. This is going to shut it down, but it's possible to pull it back if you need to:
sudo launchctl unload \ /Library/LaunchDaemons/org.postgresql.postgres.plist
Step 3 - install the new PostgreSQL package from the site. I noticed that I had to install it twice in order to get the contents of the /usr/local/pgsql-9.1/data directory, so if you don't get that, then install it again, and it should get installed properly.
Step 4 - start the new version. The launch daemon file should be installed properly and you should be good to go, but it never hurts to check:
sudo launchctl load \ /Library/LaunchDaemons/org.postgresql.postgres.plist ps -ef | grep post …lots of postmaster processes listed...
Step 5 - reload your database. Be in the same place as Step 1 and you can simply issue:
/usr/local/pgsql/bin/psql -U _postgres -d template1 \ -f /full/path/to/pgbackup
Check with psql and maybe a simple PHP script, and you should be good to go. Once it's all OK and running, you can remove the old install:
sudo rm -rf /usr/local/pgsql-9.0
At the same time, if you have the old postgres user still installed, you can delete him as well - there's nothing you need from him any longer. Lion's _postgres user can handle all the chores from here quite nicely.
If you run into problems, check the permissions on the directories. Because I was switching from the postgres user to the _postgres user, I needed to make the old install usable by the new user:
sudo chown -R _postgres:_postgres /usr/local/pgsql-9.0/data sudo chown -R _postgres:admin /usr/local/pgsql-9.0/var
PostgreSQL 9.1 has a ton of interesting features. I'm using the replication at The Shop, and it's working like a charm. I'm a big fan, and these updates are really exciting to see.