In general the move to Leopard has been very smooth for me but there was one notable exception, getting a working PostgreSQL 8 sever up and running on my MacBookPro. A few weeks ago I’d expended a lot of time and effort on this and gotten nowhere so at that stage I’d decided to work around the problem by using a remote PostgreSQL server rather than running one locally. This works fine as long as you have broadband internet access. However, I’m off to Belgium for a week on Saturday where I’ll have no broadband but where I will need to get some development work done that requires access to a PostgerSQL server.

With the end of the week getting closer I had another go at getting PosgreSQL to behave on OS X and I did eventually succeed. I got 90% of the way there by following these instructions but then I ran into a few strange problem that took a little more time and effort to work around. However, the good news is that I got it all figured out in the end!

[tags]PostgreSQL, OS X, OS X 10.5 Leopard[/tags]

Firstly, PostgreSQL’s security model requires that some commands be issued as the user the server runs under. This is a good idea and generally not a problem. In Leopard I used to get a shell as the postgres user by issuing the following terminal command:

sudo su - postgres

However, this doesn’t work for me when I install PosgreSQL using the instructions linked above. When I issue the above command I don’t get an error but I get dropped back into my own account rather than the postgres account. No idea why. It’s just odd. I found my way round that quick enough by using the following instead:

sudo -u postgres bash

That gives me a shell as the user postgres. However, within that shell odd things were happening. For example, when ever I tried to create a user or a database I got the following bizarre result:

bash-3.2$ createuser bart
Shall the new role be a superuser? (y/n) y
createuser: could not connect to database postgres: FATAL:  role "252F6238093003CC96A19B829010327D1B3019B8" does not exist
bash-3.2$

For some reason that I can’t figure out PostgreSQL seems to be reading the username of the currently logged in person as 252F6238093003CC96A19B829010327D1B3019B8. Madness. However, I did find an easy workaround, always explicitly tell PostgeSQL which user you are using the --username flag like so:

bash-3.2$ createdb --username postgres vtie_portal_live
CREATE DATABASE
bash-3.2$