Working with Postgres on OS X Mountain Lion

Installation

Macs come with Postgres installed (9.1.4 for mine purchased in November 2012) (/usr/bin/psql), but I also installed the Postgres app, which appears to have installed 9.1.4 (downloaded in early November 2012).

1. Install the Postgres app
2. Start the Postgres app.  When I start the app, I get the Postgres elephant in my menu bar -- clicking it shows the port Postgres is running on.
3. Use ps -ef | grep postgres to figure out where the path to the Postgres bin directory and the path to the Postgres data directory are.  The output will look something like this: 501 22332     1   0  9:28PM ??         0:00.25 /Applications/Postgres.app/Contents/MacOS/bin/postgres -D /Users/hm1/Library/Application Support/Postgres/var -p5432 where /Applications/Postgres.app/Contents/MacOS/bin/postgres shows the bin directory plus the executable, -D /Users/hm1/Library/Application Support/Postgres/var shows the data directory and -p5432 shows the port Postgres is running on.  Sadly, you can see that there are spaces in the path which means you'll have to enclose this in quotes where you use it.
4. Add the Postgres bin to your path in the current session or to your .bash_profile.  e.g., export PATH=/Applications/Postgres.app/Contents/MacOS/bin:$PATH

Troubleshooting

Even with the Postgres bin directory appearing first in my PATH, unless I run the Postgres commands (psql, postgres, etc.) from , I need to specify the data files or host location.  If this doesn't work for you, you can alias the commands, uninstall the base installation or use the base installation,  Sarting a new terminal session seems to get around this issue where just sourcing the updated .bash_profile doesn't.

If you get the following error when issuing postgres, your path is not correct and/or Postgres is not running:

postgres does not know where to find the server configuration file.
You must specify the --config-file or -D invocation option or set the PGDATA environment variable.

If you modified the PATH variable via .bash_profile don't forget to source it or start a new terminal session

Using ps above you can see what the -D argument needs to be, or or add the Postgres path section prior to the existing PATH elements.

If you get the following error when issuing psql

psql: could not connect to server: No such file or directory

Is the server running locally and accepting

connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

then you need to start with psql -h localhost, or add the Postgres path section prior to the existing PATH elements.

Resources

http://postgresapp.com/documentation
http://www.postgresql.org/docs/9.1/interactive/tutorial.html