Switching to PostgreSQL, Part 1: Installing Locally on OS X

Here's an interesting tidbit: Postgres evolved from UC Berkeley's Ingres database, hence the name - "Post Ingres", or Postgres

If you've recently picked up a Mac with the Lion operating system already installed, this article probably won't be of much use to you. Apparently, Postgres is the new default for Mac desktops and servers:

Apple Ditches MySQL In Favor Of PostgreSQL [Network World - August 4, 2011]

I've been a MySQL user for almost a decade, and sometimes it feels like I've had my MBP for nearly that long. So I'm coming from the perspective of someone who switched on an older version of the OS.

My reason for switching? 50% peer pressure (all the open source kids are using Postgres) and 50% frustration with the way OS X handles (or handled) MySQL.

In January of this year, I finally upgraded to Snow Leopard. As OS X upgrades are wont to do, it borked my MySQL install. How crazy is it that I didn't even notice until a few months later? (My day job kept me so busy through the spring that I just didn't have occasion to work with MySQL locally that often.)

So this set of instructions obviously no longer works: Installing Django with MySQL on Mac OS X (from June of 2008)

I even tried using homebrew, that failed on make (probably an out of date compiler). (Incidentally, if you're still bent on trying, here's a great set of instructions: Installing MySQL on Snow Leopard using Homebrew [Trey Piepmeier - February 28, 2010])

Had I continued down that rabbit hole, I would have gotten MySQL installed and running eventually. But what a headache. So I finally listened to the advice of trusted friends, friends who care about my welfare and mental health, and went with a homebrew install of Postgres.

How to get homebrew:

	http://mxcl.github.com/homebrew/

How to use homebrew:

	https://github.com/mxcl/homebrew/wiki

Installing postgres with homebrew was as simple as any other homebrew install:

	brew install postgresql

I wish that I had saved the stdout from the install so that you could see what to expect, but the only thing I kept was the last bit:

	Success. You can now start the database server using:
	    /usr/local/Cellar/postgresql/9.0.3/bin/postgres -D /usr/local/Cellar/postgresql/9.0.3/data
	or
    	    /usr/local/Cellar/postgresql/9.0.3/bin/pg_ctl -D /usr/local/Cellar/postgresql/9.0.3/data -l logfile start

How helpful is that? And in case you're curious, you can confirm you have it installed thusly:

	brew search postgresql

Anyway, my installation was error-free, but afterwards I did have to make a few modifications to get postgres running. For some reason the install did not include a folder to store the data (that folder also needs to have ownership set):

	sudo mkdir /usr/local/Cellar/postgresql/9.0.3/data
	sudo chown postgres /usr/local/Cellar/postgresql/9.0.3/data

Then there was the pg_hba.conf to update. You should find it here:

	/usr/local/Cellar/postgresql/9.0.3/data/pg_hba.conf

And there's a sample here, showing how the entries should be formatted:

	/usr/local/Cellar/postgresql/9.0.3/share/postgresql/pg_hba.conf.sample

Here's an idea of what my entries look like:

	# TYPE  DATABASE                USER            CIDR-ADDRESS            METHOD
	# IPv4 local connections:
	host    all                     postgres        127.0.0.1/32            md5
	host    my_first_database       all             127.0.0.1/32            md5
	# "local" is for Unix domain socket connections only
	local   all                     postgres                                md5
	local   my_first_database       all                                     md5

I'm getting a little ahead of myself - the database-specific entries (e.g., 'my_first_database') don't need to be there until after you've created your first database, but I don't think there's any harm in sticking them in there ahead of time. At least, I didn't have any trouble starting postgres with them there.

NOTE: After upgrading to Lion, I did get this error when I tried to start Postgres:

	FATAL:  no pg_hba.conf entry for host "::1", user "postgres", database "bshaurette_belle", SSL off

Just add this to your pg_hba.conf and you should be good to go:

	# IPv6 local connections:
	host    all             all             ::1/128                 md5

Here's a message from the postgres archives with a sort-of explanation: Re: [GENERAL] FATAL: no pg_hba.conf entry for host "::1***" [February 22, 2011]

Now, to start postgres locally, I have to switch users:

	su -l postgres
	/usr/local/Cellar/postgresql/9.0.3/bin/postgres -D /usr/local/Cellar/postgresql/9.0.3/data

Which returns something like this:

	[postgres]$ /usr/local/Cellar/postgresql/9.0.3/bin/postgres -D /usr/local/Cellar/postgresql/9.0.3/data
	LOG:  database system was shut down at 2011-08-15 21:31:01 PDT
	LOG:  database system is ready to accept connections
	LOG:  autovacuum launcher started

To stop it manually, just :

	LOG:  received fast shutdown request
	LOG:  aborting any active transactions
	LOG:  autovacuum launcher shutting down
	LOG:  shutting down
	LOG:  database system is shut down

For more information about your install, run the 'brew info postgresql' command.

To get simple information about the status of your local postgres server (simple as in "is it running"), you'll first need to make sure you have this env variable set:

	export PGDATA=/usr/local/Cellar/postgresql/9.0.3/data

Then you can run this command (which returns status and a process id when the server is running):

	pg_ctl status

	[postgres]$ pg_ctl status
	pg_ctl: server is running (PID: 12183)
	/usr/local/Cellar/postgresql/9.0.3/bin/postgres "-D" "/usr/local/Cellar/postgresql/9.0.3/data"

For more information about how to use pg_ctl (and its modifiers that initialize, start, stop, or control a PostgreSQL server), check out this doc page: http://developer.postgresql.org/pgdocs/postgres/app-pg-ctl.html