Mac OS X PostgreSQL Database Setup

The PostgreSQL documentation is quite good.

Section 1.3 Creating a Database suggests first trying

$ createdb mydb
-bash: createdb: command not found

Looking around, I find /Library/PostreSQL/8.4/bin/createdb and

/Library/PostgreSQL/8.4/bin $ ./createdb mydb
Password: 
createdb: could not connect to database postgres: FATAL:  password authentication failed for user "pat"

Pretty much everything I tried ended with that. Whether as user root or postgresl, always the request for a password. On setup, I used the same password as I use for PostgreSQL on the web host. A little Googling and forum surfing led me to instructions for handling this very situation. I needed to edit the configuration file for host-based authentication:

/Library/PostgreSQL/8.4/ $ emacs data/pg_hba.conf

I changed the line

local   all         all                               md5

to

local   all         all                               ident

to allow me to use the utilities as the user postgres without entering a password. Then

/Library/PostgreSQL/8.4/ $ kill -HUP <PID of /Library/PostgreSQL/8.4/bin/postmaster>
/Library/PostgreSQL/8.4/ $ bin/createuser pat
Shall the new role be a superuser? (y/n) y
/Library/PostgreSQL/8.4/ $ bin/psql
psql (8.4.2)
Type "help" for help.

postgres=# alter role pat password 'redacted';
ALTER ROLE
postgres=# \du
            List of roles
 Role name | Attributes  | Member of 
-----------+-------------+-----------
 pat       | Superuser   | {}
           : Create role   
           : Create DB     
 postgres  | Superuser   | {}
           : Create role   
           : Create DB     

postgres=# ^D
/Library/PostgreSQL/8.4/ $ bin/createdb pat

I then reversed the edit of data/pg_hba.conf and sent HUP to the postmaster again. Back in my own account,

pat@stoat:/Library/PostgreSQL/8.4 $ bin/psql -U pat -W
Password for user pat: 
psql (8.4.2)
Type "help" for help.

pat=# \du
            List of roles
 Role name | Attributes  | Member of 
-----------+-------------+-----------
 pat       | Superuser   | {}
           : Create role   
           : Create DB     
 postgres  | Superuser   | {}
           : Create role   
           : Create DB     

pat=# 

Next I'll handle Nuj-specific db items.