Logging SQL queries

A note to myself about how to log SQL queries when using PostgreSQL. In postgresql.conf set the following parameters:

log_statement = true
syslog = 2
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

The database will need to be restarted in order for the changes to have any effect. To view the SQL queries that are being executed, log in as root and issue the following command:

[root@localhost ] tail -f /var/log/messages

Once you have finished the analysis don’t forget to turn off the SQL logging.

The reason I needed to do this was that Postfix was rejecting email for a virtual domain set up on my server. The mapping tables were in the database so I needed to see what SQL query Postfix was sending to PostgreSQL to try and figure out why the lookup was failing. As it happens I figured out the problem, but that’s a post for another day.

1 thought on “Logging SQL queries

  1. Pingback: Simon Buckle’s Weblog » Blog Archive » How to log SQL queries

Leave a Reply