Homebrew install of Postgres on Mountain Lion

gistfile1.sh

### Install postgres using Homebrew

nauru:~ kaiju$ brew install postgres
==> Downloading http://ftp.postgresql.org/pub/source/v9.2.4/postgresql-9.2.4.tar.bz2
Already downloaded: /Library/Caches/Homebrew/postgresql-9.2.4.tar.bz2
==> Patching
patching file src/pl/plpython/Makefile
patching file contrib/uuid-ossp/uuid-ossp.c
==> ./configure --prefix=/usr/local/Cellar/postgresql/9.2.4 --datadir=/usr/local/Cellar/postgresql/9.2.4/share/postgresql --docdir=/usr/local/Cellar/postgresql/9.2.4/share/doc/postgresql --enable-thread-safety
==> make install-world
==> Caveats
If builds of PostgreSQL 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:
  https://github.com/mxcl/homebrew/issues/issue/2510

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

To migrate existing data from a previous major version (pre-9.2) of PostgreSQL, see:
  http://www.postgresql.org/docs/9.2/static/upgrading.html

Some machines may require provisioning of shared memory:
  http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC
When installing the postgres gem, including ARCHFLAGS is recommended:
  ARCHFLAGS="-arch x86_64" gem install pg

To install gems without sudo, see the Homebrew wiki.

To reload postgresql after an upgrade:
    launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
    launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
==> Summary
🍺  /usr/local/Cellar/postgresql/9.2.4: 2842 files, 39M, built in 3.6 minutes
nauru:~ kaiju$ 

### Edit .bash_profile to include postgres bin path: 
export PATH=$PATH:/usr/local/Cellar/postgres/9.2.4/bin

### Create postgres group if does not exist
nauru:~ kaiju$ sudo dscl . -create /Groups/postgres 
nauru:~ kaiju$ sudo dscl . -create /Groups/postgres RealName 'PostgreSQL Server'
nauru:~ kaiju$ sudo dscl . -create /Groups/postgres PrimaryGroupID 183          
nauru:~ kaiju$ sudo dscl . -create /Groups/postgres UserShell /usr/bin/false
nauru:~ kaiju$ sudo dscl . -create /Groups/postgres Password 'postgres'
nauru:~ kaiju$ sudo dscl . -create /Groups/postgres NFSHomeDirectory /var/empty

### Create postgres user if does not exist. This will be used as default owner of production server
nauru:~ kaiju$ sudo dscl . -create /Users/postgres Password 'postgres'
nauru:~ kaiju$ sudo dscl . -create /Users/postgres RealName 'PostgreSQL Server'
nauru:~ kaiju$ sudo dscl . -create /Users/postgres PrimaryGroupID 183
nauru:~ kaiju$ sudo dscl . -create /Users/postgres UniqueID 183
nauru:~ kaiju$ sudo dscl . -create /Users/postgres UserShell /bin/sh
nauru:~ kaiju$ sudo dscl . -create /Users/postgres NFSHomeDirectory /usr/local/var/postgres

### Set up directories for Postgres to use
nauru:local kaiju$ sudo -p mkdir /usr/localvar/postgres
nauru:local kaiju$ sudo chown postgres:postgres /usr/local/var/postgres
nauru:local kaiju$ sudo mkdir -p var/postgres/data
nauru:local kaiju$ sudo chown postgres:postgres var/postgres/data
nauru:local kaiju$ sudo mkdir -p var/postgres/logs
nauru:local kaiju$ sudo chown postgres:postgres var/postgres/logs

### Initialize first database
nauru:local kaiju$ sudo su postgres -c '/usr/local/Cellar/postgresql/9.2.4/bin/initdb -D /usr/local/var/postgres/data'
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

fixing permissions on existing directory /usr/local/var/postgres/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 20
selecting default shared_buffers ... 1600kB
creating configuration files ... ok
creating template1 database in /usr/local/var/postgres/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/Cellar/postgresql/9.2.4/bin/postgres -D /usr/local/var/postgres/data
or
    /usr/local/Cellar/postgresql/9.2.4/bin/pg_ctl -D /usr/local/var/postgres/data -l logfile start

### Add startup items
nauru:local kaiju$ cp /usr/local/Cellar/postgresql/9.2.4/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
nauru:local kaiju$ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
nauru:local kaiju$ sudo su postgres -c 'pg_ctl -D /usr/local/var/postgres/data -l /usr/local/var/postgres/logs/server.log start'
server starting

### Edit postgres.conf to turn off file syncing, speeds up restoring from backup
nauru:local kaiju$ sudo su postgres
sh-3.2$ cd var/postgres/data
nano postgresql.conf  # Find the line starting with "fsync", uncomment it, and turn it off.
exit

### Create superuser role for database
nauru:local kaiju$ psql -U postgres -c "CREATE ROLE bfxadmin LOGIN SUPERUSER;"
CREATE ROLE

### Test database
nauru:local kaiju$ psql postgres bfxadmin
psql (9.2.4)
Type "help" for help.

postgres-# \q
nauru:local kaiju$