Set the following parameters to enable Postgresql to log every statement:
log_destination='stderr'
logging_collection=on
log_directory='pg_log'
log_filename = 'postgresql-%a.log'
log_error_verbosity=verbose
log_min_duration_statement = 0
log_line_prefix = '%t %r [%p]: [%l-1] user=%u,db=%d '
Here,
> 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;
...