|The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 274 pages
RRP £9.95 ($14.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
4.7.3 What To Log
application_namecan be any string of less than
NAMEDATALENcharacters (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_activityview and included in CSV log entries. It can also be included in regular log entries via the
log_line_prefixparameter. Only printable ASCII characters may be used in the
application_namevalue. Other characters will be replaced with question marks (
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
LOGmessage 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
log_min_messages. These parameters are off by default.
debug_pretty_printindents the messages produced by
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.
- 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.
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.
This outputs a line in the server log similar to
log_connectionsbut 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.
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_statementto zero is that exceeding
log_min_duration_statementforces the text of the query to be logged, but this option doesn't. Thus, if
log_min_duration_statementhas 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.
Controls the amount of detail written in the server log for each
message that is logged. Valid values are
VERBOSE, each adding more fields to displayed messages.
TERSEexcludes the logging of
VERBOSEoutput includes the
SQLSTATEerror 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.
- 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.
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
Application name yes
User name yes
Database name yes
Remote host name or IP address, and remote port yes
Remote host name or IP address yes
Process ID no
Time stamp without milliseconds no
Time stamp with milliseconds no
Command tag: type of session's current command yes
SQLSTATE error code no
Session ID: see below no
Number of the log line for each session or process, starting at 1 no
Process start time stamp no
Virtual transaction ID (backendID/localXID) no
Transaction ID (0 if none is assigned) no
Produces no output, but tells non-session processes to stop at this point in the string; ignored by session processes no
%cescape 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
%ccan 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) || '.' || to_hex(procpid) 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.
Controls whether a log message is produced when a session waits
deadlock_timeoutto acquire a lock. This is useful in determining if lock waits are causing poor performance. The default is
Controls which SQL statements are logged. Valid values are
ddllogs all data definition statements, such as
ddlstatements, plus data-modifying statements such as
EXPLAIN ANALYZEstatements 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
allsetting, 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
ERROR(or lower) to log such statements.
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.
Sets the time zone used for timestamps written in the log.
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 9781906966072||The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide||See the print edition|