Monday, June 6, 2011

Postgresql logging parameters to log every statement


 Set the following parameters to enable Postgresql to log every statement:
 log_filename = 'postgresql-%a.log'
 log_min_duration_statement = 0
 log_line_prefix = '%t %r [%p]: [%l-1] user=%u,db=%d '

>  log_destination is set to 'stderr' which is default.
> logging_collection to capture plain and CSV-format log messages sent to stderr and redirects them into log files
> log_directory is set to 'pg_log' which is default
> log_filename is set to 'postgresql-%a.log' which will create log file on each day and truncate log file in every   7 days, for example, postgresql-Mon.log, postgresql-Tue.log
> log_error_verbosity is set to verbose to include the SQLSTATE error code and the source code file name, function name, and line number that generated the error
> log_min_duration_statement is set to 0. This ensures that every statement will be logged in the log file
> log_line_prefix is set to '%t %r [%p]: [%l-1] user=%u,db=%d ' where
  %t is used for time stamp,
  %r is used for remote host and port,
  %p is used for process id,
  %l is used for number of log line for each session or process starting from 1
  %u is used for user name
  %d is used for database name
Following is an example of the log information after setting the above parameters:

less postgresql-Mon.log
2011-06-06 14:56:11 BDT [local] [2051]: [3-1] user=postgres,db=postgres LOG:  00000: duration: 0.158 ms  statement: select 1;

Leave a Reply