When I was poking around to see what was installed, I found psql in /usr/bin/psql and I had taken that to mean that I already had PostgreSQL installed. However, looking at the listening ports and in the socket directory, I don’t see any evidence of a running server. Hmmm……

  $ which psql
  /usr/bin/psql

  $ psql --version
  psql (PostgreSQL) 9.0.5
  contains support for command-line editing

  $ ps -ef | grep -i sql
  # .... only see stuff for mysql

  $ lsof -i -P | grep LISTEN
  ruby      16322  cnk   11u  IPv4 0xffffff801910cfa0      0t0  TCP    *:3000 (LISTEN)
  mysqld    29721  cnk   10u  IPv4 0xffffff8018365500      0t0  TCP    *:3306 (LISTEN)

  $ find /usr -name pg_ctl
  # Got nothing

  $ psql
  psql: could not connect to server: Permission denied
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

   $ sudo ls -al /var/pgsql_socket
  total 0
  drwxr-x---   2 _postgres  _postgres   68 Apr 26 16:40 .
  drwxr-xr-x  28 root       wheel      952 Jul  4 12:16 ..

Googling around it appears that the Lion Server may have the full PostgreSQL server running but my laptop seems to only have the psql client installed. OK let’s whip out homebrew. There appear to be formulas for versions 8 and 9 - but the default is now 9 so:

    $ brew install postgresql

That compiled and installed PostgreSQL 9.1.4. Then I followed the instructions given at the end of the install:

    If this is your first install, create a database with:
      initdb /usr/local/var/postgres

The initdb had a couple of interesting messages. First, The files belonging to this database system will be owned by user "cnk". This user must also own the server process. Ok that isn’t a problem - on a laptop anyway. And it also warned me that it was enabling "trust" authentication for local connections. The notes in /usr/local/var/postgres/pg_hba.conf warn me that local “trust” authentication means that any local user can connect as any databases user - including the database superuser. Again more permissive than I generally am, but probably OK - and certainly convenient - as long as I am only accepting connections from localhost.

The next useful piece of information from the brew install script was how to have PostgreSQL start up when I log in:

    If this is your first install, automatically load on login with:
      mkdir -p ~/Library/LaunchAgents
      cp /usr/local/Cellar/postgresql/9.1.4/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
      launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Homebrew also shows it’s Ruby leanings by helpfully suggesting the compile flags I may want when installing the pg ruby gem. Since I may want this available to me in a variety of gemsets, let’s see if I can just install it in the global gemset for Ruby 1.9.3 (the only Ruby I have installed at the moment).

    $ rvm gemset use global
    $ env ARCHFLAGS="-arch x86_64" gem install pg
    Fetching: pg-0.14.0.gem (100%)
    Building native extensions.  This could take a while...
    Successfully installed pg-0.14.0

OK so now that we are installed and running, let’s log in:

  $ psql
  psql: FATAL:  database "cnk" does not exist
  $ createdb cnk
  createdb cnk
  [10:46 PM] (brazen:~) $ psql
  psql (9.1.4)

  cnk=#