PostgreSQL Reference Manual - Volume 3 - Server Administration Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 204 pages ISBN 0954612043 RRP £13.95 ($24.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL Reference Manual Volume 3 Buy the book here! >>> learn more

4.7.3 What To Log

debug_print_parse (boolean)

debug_print_rewritten (boolean)

debug_print_plan (boolean)

debug_pretty_print (boolean)

These parameters enable various debugging output to be emitted. For each executed query, they print the resulting parse tree, the query rewriter output, or the execution plan. debug_pretty_print indents these displays to produce a more readable but much longer output format. client_min_messages or log_min_messages must be DEBUG1 or lower to actually send this output to the client or the server log, respectively. These parameters are off by default.

log_connections (boolean)

This outputs a line to the server log detailing each successful connection. This is off by default, although it is probably very useful. Some client programs, like psql, attempt to connect twice while determining if a password is required, so duplicate “connection received” messages do not necessarily indicate a problem. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line.

log_disconnections (boolean)

This outputs a line in the server log similar to log_connections but at session termination, and includes the duration of the session. This is off by default. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line.

log_duration (boolean)

Causes the duration of every completed statement to be logged. The default is off. Only superusers can change this setting. For clients using extended query protocol, durations of the Parse, Bind, and Execute steps are logged independently.

Note: The difference between setting this option and setting log_min_duration_statement to zero is that exceeding log_min_duration_statement forces the text of the query to be logged, but this option doesn't. Thus, if log_duration is on and log_min_duration_statement has a positive value, all durations are logged but the query text is included only for statements exceeding the threshold. This behavior can be useful for gathering statistics in high-load installations.

log_line_prefix (string)

This is a printf-style string that is output at the beginning of each log line. The default is an empty string. Each recognized escape is replaced as outlined below - anything else that looks like an escape is ignored. Other characters are copied straight to the log line. Some escapes are only recognized by session processes, and do not apply to background processes such as the main server process. Syslog produces its own time stamp and process ID information, so you probably do not want to use those escapes if you are using syslog. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line.

Escape	Effect	Session only
%u	User name	yes
%d	Database name	yes
%r	Remote host name or IP address, and remote port	yes
%h	Remote host name or IP address	yes
%p	Process ID	no
%t	Time stamp (no milliseconds, no timezone on Windows)	no
%m	Time stamp with milliseconds	no
%i	Command tag: This is the command that generated the log line.	yes
%c	Session ID: A unique identifier for each session. It is 2 4-byte hexadecimal numbers (without leading zeros) separated by a dot. The numbers are the session start time and the process ID, so this can also be used as a space saving way of printing these items.	yes
%l	Number of the log line for each process, starting at 1	no
%s	Session start time stamp	yes
%x	Transaction ID	yes
%q	Does not produce any output, but tells non-session processes to stop at this point in the string. Ignored by session processes.	no
%%	Literal %	no

log_statement (string)

Controls which SQL statements are logged. Valid values are none, ddl, mod, and all. ddl logs all data definition statements, such as CREATE, ALTER, and DROP statements. mod logs all ddl statements, plus data-modifying statements such as INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE, EXECUTE, and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type. For clients using extended query protocol, logging occurs when an Execute message is received, and values of the Bind parameters are included (with any embedded single-quote marks doubled). The default is none. Only superusers can change this setting.

Note: Statements that contain simple syntax errors are not logged even by the log_statement = all setting, because the log message is emitted only after basic parsing has been done to determine the statement type. In the case of extended query protocol, this setting likewise does not log statements that fail before the Execute phase (i.e., during parse analysis or planning). Set log_min_error_statement to ERROR (or lower) to log such statements.

log_hostname (boolean)

By default, connection log messages only show the IP address of the connecting host. Turning on this parameter causes logging of the host name as well. Note that depending on your host name resolution setup this might impose a non-negligible performance penalty. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line.