Turn on Postgres Query Logging in OS X

Sometimes I like leaving an sql log terminal open so I can see exactly how a contrib application interacts with the database. When I searched for how to turn on Postgresql query logging in Mac OS X, I couldn’t find anything straightforward, so I figured I’d post what I did to turn this on.

I put this in /Library/PostgreSQL/8.3/data/postgresql.conf

syslog_facility = ‘LOCAL0’ syslog_ident = ‘postgres’ logging_collector = on # Enable capturing of stderr and csvlog

                   # into log files. Required to be on for csvlogs.

log_directory = ‘/var/log/pg_log’ # directory where log files are written, log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log’ # log file name pattern, log_destination = ‘stderr’ log_statement = ‘all’

Then, i did mkdir -p /var/log/pg_log && sudo chown postgres /var/log/pg_log

sudo su su postgres /Library/PostgreSQL/8.3/bin/pg_ctl stop -D /Library/PostgreSQL/8.3/data /Library/PostgreSQL/8.3/bin/pg_ctl start -D /Library/PostgreSQL/8.3/data

To watch the log, sudo tail -f /var/log/pg_log/postgresql-*