- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 274 pages
ISBN 9781906966072
RRP £9.95 ($14.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

4.7.3 What To Log

application_name (string)
The application_name can be any string of less than NAMEDATALEN characters (64 characters in a standard build). It is typically set by an application upon connection to the server. The name will be displayed in the pg_stat_activity view and included in CSV log entries. It can also be included in regular log entries via the log_line_prefix parameter. Only printable ASCII characters may be used in the application_name value. Other characters will be replaced with question marks (?).
debug_print_parse (boolean)
debug_print_rewritten (boolean)
debug_print_plan (boolean)
These parameters enable various debugging output to be emitted. When set, they print the resulting parse tree, the query rewriter output, or the execution plan for each executed query. These messages are emitted at LOG message level, so by default they will appear in the server log but will not be sent to the client. You can change that by adjusting client_min_messages and/or log_min_messages. These parameters are off by default.
debug_pretty_print (boolean)
When set, debug_pretty_print indents the messages produced by debug_print_parse, debug_print_rewritten, or debug_print_plan. This results in more readable but much longer output than the “compact” format used when it is off. It is on by default.
log_checkpoints (boolean)
Causes checkpoints to be logged in the server log. Some statistics about each checkpoint are included in the log messages, including the number of buffers written and the time spent writing them. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line. The default is off.
log_connections (boolean)
Causes each attempted connection to the server to be logged, as well as successful completion of client authentication. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line. The default is off.

Note: 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.

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_error_verbosity (enum)
Controls the amount of detail written in the server log for each message that is logged. Valid values are TERSE, DEFAULT, and VERBOSE, each adding more fields to displayed messages. TERSE excludes the logging of DETAIL, HINT, QUERY, and CONTEXT error information. VERBOSE output includes the SQLSTATE error code (see also Volume 1A: A PostgreSQL Error Codes) and the source code file name, function name, and line number that generated the error. Only superusers can change this setting.
log_hostname (boolean)
By default, connection log messages only show the IP address of the connecting host. Turning this parameter on 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.
log_line_prefix (string)
This is a printf-style string that is output at the beginning of each log line. % characters begin “escape sequences” that are replaced with status information as outlined below. Unrecognized escapes are ignored. Other characters are copied straight to the log line. Some escapes are only recognized by session processes, and are ignored by background processes such as the main server process. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line. The default is an empty string.
Escape Effect Session only
%a Application name yes
%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 without milliseconds no
%m Time stamp with milliseconds no
%i Command tag: type of session's current command yes
%e SQLSTATE error code no
%c Session ID: see below no
%l Number of the log line for each session or process, starting at 1 no
%s Process start time stamp no
%v Virtual transaction ID (backendID/localXID) no
%x Transaction ID (0 if none is assigned) no
%q Produces no output, but tells non-session processes to stop at this point in the string; ignored by session processes no
%% Literal % no
The %c escape prints a quasi-unique session identifier, consisting of two 4-byte hexadecimal numbers (without leading zeros) separated by a dot. The numbers are the process start time and the process ID, so %c can also be used as a space saving way of printing those items. For example, to generate the session identifier from pg_stat_activity, use this query:
SELECT to_hex(EXTRACT(EPOCH FROM backend_start)::integer) 
  || '.' ||
FROM pg_stat_activity;

Tip: If you set a nonempty value for log_line_prefix, you should usually make its last character be a space, to provide visual separation from the rest of the log line. A punctuation character can be used too.

Tip: Syslog produces its own time stamp and process ID information, so you probably do not want to include those escapes if you are logging to syslog.

log_lock_waits (boolean)
Controls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance. The default is off.
log_statement (enum)
Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements). 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_temp_files (integer)
Controls logging of temporary file names and sizes. Temporary files can be created for sorts, hashes, and temporary query results. A log entry is made for each temporary file when it is deleted. A value of zero logs all temporary file information, while positive values log only files whose size is greater than or equal to the specified number of kilobytes. The default setting is -1, which disables such logging. Only superusers can change this setting.
log_timezone (string)
Sets the time zone used for timestamps written in the log. Unlike timezone, this value is cluster-wide, so that all sessions will report timestamps consistently. The default is unknown, which means use whatever the system environment specifies as the time zone. See Volume 1A: 6.5.3 Time Zones for more information. This parameter can only be set in the ‘postgresql.conf’ file or on the server command line.
ISBN 9781906966072The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration GuideSee the print edition