Show you the steps of upgrading postgres to 9.2 on Mac OS X using homebrew

brew_install_postgres.sh

# Update brew & install the latest postgres first
brew update
brew install postgres

# Shut down the old running postgres
# Assume you previously installed postgres using homebrew
pg_ctl -D /usr/local/Cellar/posgres/9.1.1 stop

# If postgres won't stop, you have to do the following step to unload it from LaunchAgents
launchctl unload -w ~/Library/LaunchAgents/org.postgresql.postgres.plist
rm ~/Library/LaunchAgents/org.postgresql.postgres.plist

# Use pg_upgrade to migrate your old data to the new database version
# If you encounter the problem that "can't create share memory in mountain lion"
# Run these 2 commands
sudo sysctl -w kern.sysv.shmall=65536
sudo sysctl -w kern.sysv.shmmax=16777216

# Otherwise
mkdir /usr/local/var/postgres9.2
initdb /usr/local/var/postgres9.2
pg_upgrade -d /usr/local/var/postgres/ -D /usr/local/var/postgres9.2/ \
-b /usr/local/Cellar/postgresql/9.1.2/bin/ \
-B /usr/local/Cellar/postgresql/9.2.2/bin/ -v
cd /usr/local/var/
mv postgres postgres8
mv postgres9 postgres

# And now your postgres should be automatically started, though you could might problems 
# when you test the connection to the db server, such as
# 'connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?'
# In this case, we have to modify the postgresql.conf and restart the database server to fix it.
cd /usr/var/postgres
vi postgresql.conf

# Find out the following in your .conf file and change them to:
# unix_socket_directory = '/var/pgsql_socket'    # dont worry if yours is different
# #unix_socket_group = ''                        # default is fine here
# unix_socket_permissions = 0777                 # check this one
#
# Then restart the server
launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
pg_ctl -D /usr/local/var/postgres restart

# And create a symbolic link in order to let postgres find out where the socket is.
ln -s /var/pgsql_socket/ /tmp/.s.PGSQL.5432

# p.s. please make sure you have the right to write to /var/pg_sql_socket

# And now you can test your database connection, it should work right now
psql -U postgres

# Finally, we can load the homebrew installed postgres to our LaunchAgents
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

# And that's it!