Enabling SQL Statement Logging in Postgresql

It is often nice to see what SQL statements an application will run.  Maybe there is a gui app to add a user and you want to script adding one thousand users and you want to make sure you know how users are add.

So in Microsoft SQL Server there is a SQL Profiler tool that will show you all the SQL statements.  However, when using Postgresql on FreeBSD, there is often nothing but the command line.

You can easily have all SQL statements logged to a file in Postgresql on FreeBSD.

Note: I already have a post on Installing and Using Postgresql on FreeBSD.

Logging all SQL statements

Here is how to do it:

Step 1 – Configure the postgresql.conf

1. Open the postgresql.conf file.

   ee /usr/local/pgsql/data/postgresql.conf
   

2. Find the log_statement value in the log. Uncomment it and set it to all.

   log_statement = 'all'                   # none, ddl, mod, all
   

3. Close and save the postgresql.conf file.

Step 2 – Configure the syslog.conf

1. Open the syslog.conf file.

   ee /etc/syslog.conf
   

2. Add the following line to the end of the log.

   local0.*                                        /var/log/pgsql.log
   

3. Close and save the syslog.conf file.

Step 3 – Create the log file

1. Create the log file.

   touch /var/log/pgsql.log
   

2. Make the file a little more secure.

   chmod 600 /var/log/pgsql.log
   

Step 4 – Restart Posgresql and Syslog services

1. Restart the postgresql service.

   /usr/local/etc/rc.d/postgresql restart
   

Or in FreeBSD 8.1 and later, you can use this syntax.

service postgresql restart

2. Restart the syslogd service.

   /etc/rc.d/syslogd restart
   

   Or in FreeBSD 8.1 and later, you can use this syntax.

   service syslogd restart
   

Ok, now you should be able to capture the sql statements.  Here is a log entry from my /var/log/pgsql.log.

Sep  9 22:10:26 FBSD81 postgres[18507]: [5-1] LOG:  statement: select * from "Member";

Logging the time it takes to execute the SQL statement

Ok, this assumes you have already done the steps above.

Step 1 – Configure the postgresql.conf

1. Open the postgresql.conf file.

   ee /usr/local/pgsql/data/postgresql.conf
   

2. Find the log_min_duration_statement value in the log. Uncomment this line and set it to zero.

   log_min_duration_statement = 0          # -1 is disabled, 0 logs all statements
                                           # and their durations, > 0 logs only
                                           # statements running at least this number
                                           # of milliseconds
   

   NOTE: It is interesting to note that you can have the log_statement value on or off.  So technically, enabling the log_min_duration_statement will also log all SQL statements if it is set to 0.

3. Close and save the postgresql.conf file.

Step 2 – Restart Posgresql

1. Restart the postgresql service.

   /usr/local/etc/rc.d/postgresql restart
   

Or in FreeBSD 8.1 and later, you can use this syntax.

service postgresql restart

Ok, now you should be able to capture the sql statements.  Here is a log entry from my /var/log/pgsql.log.

Sep  9 22:26:06 FBSD81 postgres[18759]: [4-1] LOG:  statement: select * from "Member";
Sep  9 22:26:06 FBSD81 postgres[18759]: [5-1] LOG:  duration: 0.957 ms

Logging SQL queries that take too long to execute

If the log_statement value is on.  All SQL

Step 1 – Configure the postgresql.conf

1. Open the postgresql.conf file.

   ee /usr/local/pgsql/data/postgresql.conf
   

2. Find the log_min_duration_statement value in the log. Uncomment this line and set it to a value in milisecond.  For example, to log any statement that takes longer than 3 seconds, use 3000.

   log_min_duration_statement = 3000     # -1 is disabled, 0 logs all statements
                                         # and their durations, > 0 logs only
                                         # statements running at least this number
                                         # of milliseconds
   

3. Find the log_statement value in the log. Comment out the line or set the value to none (or both).

   #log_statement = 'none'                   # none, ddl, mod, all
   

4. Close and save the postgresql.conf file.

Step 2 – Restart Posgresql

1. Restart the postgresql service.

   /usr/local/etc/rc.d/postgresql restart
   

Or in FreeBSD 8.1 and later, you can use this syntax.

service postgresql restart

Ok, I don’t really have a SQL statement that will take 3 seconds, so I configured it for 10 milliseconds and I could see that statements that took less than 10 milliseconds were not logged while those that took more were logged.

IMPORTANT! Turn logging off when you are done

Don’t forget to turn this logging off when you are finished as this is a huge performance hit.  Leaving this on is not efficient and can result in slowness.