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.
These are only used if logging_collector is on:
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
now, restart postgres
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-*