How to log all sql statements in Postgresql running on FreeBSD?

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
    
  2. Or in FreeBSD 8.1 and later, you can use this syntax.

    service postgresql restart
    
  3. 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
    
  2. 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
    
  2. 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.

5 Comments

  1. Pawel says:

    When i enable full log i see columns name but i don't see insert values, like this:

    EXECUTE [PREPARE: INSERTNTO table_name (rejestr, rok) VALUES ( $1, $2)

    It is possible to log and see what is in $1 and $2 ?

  2. Francisco Reyes says:

    Instead of doing a restart it is better to do a reload. Don't know if "Service" supports it, but you can su - to the pgsql user and do:
    pg_ctl reload

    A restart would conflict with existing connections. Reload does the work without affecting operation.

Leave a Reply

Powered by sweet Captcha