19.8. Error Reporting and Logging

PostgreSQL supports several methods for logging server messages, including stderr , csvlog and syslog . On Windows, eventlog is also supported. Set this parameter to a list of desired log destinations separated by commas. The default is to log to stderr only. This parameter can only be set in the postgresql.conf file or on the server command line.

If csvlog is included in log_destination , log entries are output in " comma separated value " ( CSV ) format, which is convenient for loading logs into programs. See Section 19.8.4 for details. logging_collector must be enabled to generate CSV-format log output.

When either stderr or csvlog are included, the file current_logfiles is created to record the location of the log file(s) currently in use by the logging collector and the associated logging destination. This provides a convenient way to find the logs currently in use by the instance. Here is an example of this file's content:

stderr log/postgresql.log
csvlog log/postgresql.csv

current_logfiles is recreated when a new log file is created as an effect of rotation, and when log_destination is reloaded. It is removed when neither stderr nor csvlog are included in log_destination , and when the logging collector is disabled.

local0.*    /var/log/postgresql

This parameter enables the logging collector , which is a background process that captures log messages sent to stderr and redirects them into log files. This approach is often more useful than logging to syslog , since some types of messages might not appear in syslog output. (One common example is dynamic-linker failure messages; another is error messages produced by scripts such as archive_command .) This parameter can only be set at server start.

When logging_collector is enabled, this parameter determines the directory in which log files will be created. It can be specified as an absolute path, or relative to the cluster data directory. This parameter can only be set in the postgresql.conf file or on the server command line. The default is log .

When logging_collector is enabled, this parameter sets the file names of the created log files. The value is treated as a strftime pattern, so % -escapes can be used to specify time-varying file names. (Note that if there are any time-zone-dependent % -escapes, the computation is done in the zone specified by log_timezone .) The supported % -escapes are similar to those listed in the Open Group's strftime specification. Note that the system's strftime is not used directly, so platform-specific (nonstandard) extensions do not work. The default is postgresql-%Y-%m-%d_%H%M%S.log .

If you specify a file name without escapes, you should plan to use a log rotation utility to avoid eventually filling the entire disk. In releases prior to 8.4, if no % escapes were present, PostgreSQL would append the epoch of the new log file's creation time, but this is no longer the case.

If CSV-format output is enabled in log_destination , .csv will be appended to the timestamped log file name to create the file name for CSV-format output. (If log_filename ends in .log , the suffix is replaced instead.)

This parameter can only be set in the postgresql.conf file or on the server command line.

On Unix systems this parameter sets the permissions for log files when logging_collector is enabled. (On Microsoft Windows this parameter is ignored.) The parameter value is expected to be a numeric mode specified in the format accepted by the chmod and umask system calls. (To use the customary octal format the number must start with a 0 (zero).)

The default permissions are 0600 , meaning only the server owner can read or write the log files. The other commonly useful setting is 0640 , allowing members of the owner's group to read the files. Note however that to make use of such a setting, you'll need to alter log_directory to store the files somewhere outside the cluster data directory. In any case, it's unwise to make the log files world-readable, since they might contain sensitive data.

This parameter can only be set in the postgresql.conf file or on the server command line.

When logging_collector is enabled, this parameter determines the maximum lifetime of an individual log file. After this many minutes have elapsed, a new log file will be created. Set to zero to disable time-based creation of new log files. This parameter can only be set in the postgresql.conf file or on the server command line.

When logging_collector is enabled, this parameter determines the maximum size of an individual log file. After this many kilobytes have been emitted into a log file, a new log file will be created. Set to zero to disable size-based creation of new log files. This parameter can only be set in the postgresql.conf file or on the server command line.

When logging_collector is enabled, this parameter will cause PostgreSQL to truncate (overwrite), rather than append to, any existing log file of the same name. However, truncation will occur only when a new file is being opened due to time-based rotation, not during server startup or size-based rotation. When off, pre-existing files will be appended to in all cases. For example, using this setting in combination with a log_filename like postgresql-%H.log would result in generating twenty-four hourly log files and then cyclically overwriting them. This parameter can only be set in the postgresql.conf file or on the server command line.

Example: To keep 7 days of logs, one log file per day named server_log.Mon , server_log.Tue , etc, and automatically overwrite last week's log with this week's log, set log_filename to server_log.%a , log_truncate_on_rotation to on , and log_rotation_age to 1440 .

Example: To keep 24 hours of logs, one log file per hour, but also rotate sooner if the log file size exceeds 1GB, set log_filename to server_log.%H%M , log_truncate_on_rotation to on , log_rotation_age to 60 , and log_rotation_size to 1000000 . Including %M in log_filename allows any size-driven rotations that might occur to select a file name different from the hour's initial file name.

When logging to syslog is enabled, this parameter determines the syslog " facility " to be used. You can choose from LOCAL0 , LOCAL1 , LOCAL2 , LOCAL3 , LOCAL4 , LOCAL5 , LOCAL6 , LOCAL7 ; the default is LOCAL0 . See also the documentation of your system's syslog daemon. This parameter can only be set in the postgresql.conf file or on the server command line.

When logging to syslog is enabled, this parameter determines the program name used to identify PostgreSQL messages in syslog logs. The default is postgres . This parameter can only be set in the postgresql.conf file or on the server command line.

When logging to syslog and this is on (the default), then each message will be prefixed by an increasing sequence number (such as [2] ). This circumvents the " --- last message repeated N times --- " suppression that many syslog implementations perform by default. In more modern syslog implementations, repeated message suppression can be configured (for example, $RepeatedMsgReduction in rsyslog ), so this might not be necessary. Also, you could turn this off if you actually want to suppress repeated messages.

This parameter can only be set in the postgresql.conf file or on the server command line.

When logging to syslog is enabled, this parameter determines how messages are delivered to syslog. When on (the default), messages are split by lines, and long lines are split so that they will fit into 1024 bytes, which is a typical size limit for traditional syslog implementations. When off, PostgreSQL server log messages are delivered to the syslog service as is, and it is up to the syslog service to cope with the potentially bulky messages.

If syslog is ultimately logging to a text file, then the effect will be the same either way, and it is best to leave the setting on, since most syslog implementations either cannot handle large messages or would need to be specially configured to handle them. But if syslog is ultimately writing into some other medium, it might be necessary or more useful to keep messages logically together.

This parameter can only be set in the postgresql.conf file or on the server command line.

When logging to event log is enabled, this parameter determines the program name used to identify PostgreSQL messages in the log. The default is PostgreSQL . This parameter can only be set in the postgresql.conf file or on the server command line.

Controls which message levels are written to the server log. Valid values are DEBUG5 , DEBUG4 , DEBUG3 , DEBUG2 , DEBUG1 , INFO , NOTICE , WARNING , ERROR , LOG , FATAL , and PANIC . Each level includes all the levels that follow it. The later the level, the fewer messages are sent to the log. The default is WARNING . Note that LOG has a different rank here than in client_min_messages . Only superusers can change this setting.

Controls which SQL statements that cause an error condition are recorded in the server log. The current SQL statement is included in the log entry for any message of the specified severity or higher. Valid values are DEBUG5 , DEBUG4 , DEBUG3 , DEBUG2 , DEBUG1 , INFO , NOTICE , WARNING , ERROR , LOG , FATAL , and PANIC . The default is ERROR , which means statements causing errors, log messages, fatal errors, or panics will be logged. To effectively turn off logging of failing statements, set this parameter to PANIC . Only superusers can change this setting.

Causes the duration of each completed statement to be logged if the statement ran for at least the specified number of milliseconds. Setting this to zero prints all statement durations. Minus-one (the default) disables logging statement durations. For example, if you set it to 250ms then all SQL statements that run 250ms or longer will be logged. Enabling this parameter can be helpful in tracking down unoptimized queries in your applications. Only superusers can change this setting.

For clients using extended query protocol, durations of the Parse, Bind, and Execute steps are logged independently.

Set the fraction of transactions whose statements are all logged, in addition to statements logged for other reasons. It applies to each new transaction regardless of its statements' durations. The default is 0 , meaning not to log statements from any additional transaction. Setting this to 1 logs all statements for all transactions. log_transaction_sample_rate is helpful to track a sample of transaction.

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 ( ? ).

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.

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.

Causes checkpoints and restartpoints to be logged in the server log. Some statistics 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. Only superusers can change this parameter at session start, and it cannot be changed at all within a session. The default is off .

Causes session terminations to be logged. The log output provides information similar to log_connections , plus the duration of the session. Only superusers can change this parameter at session start, and it cannot be changed at all within a session. The default is off .

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.

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 Appendix A ) 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 will be treated as empty by background processes such as the main server process. Status information may be aligned either left or right by specifying a numeric literal after the % and before the option. A negative value will cause the status information to be padded on the right with spaces to give it a minimum width, whereas a positive value will pad on the left. Padding can be useful to aid human readability in log files. This parameter can only be set in the postgresql.conf file or on the server command line. The default is '%m [%p] ' which logs a time stamp and the process ID.

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(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' ||
       to_hex(pid)
FROM pg_stat_activity;

log_line_prefix = '%m [%p] %q%u@%d/%a '

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 . Only superusers can change this setting.

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.

Causes each replication command to be logged in the server log. See Section 52.4 for more information about replication command. The default value is off . Only superusers can change this setting.

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 server log. Unlike TimeZone , this value is cluster-wide, so that all sessions will report timestamps consistently. The built-in default is GMT , but that is typically overridden in postgresql.conf ; initdb will install a setting there corresponding to its system environment. See Section 8.5.3 for more information. This parameter can only be set in the postgresql.conf file or on the server command line.

Sets a name that identifies this database cluster (instance) for various purposes. The cluster name appears in the process title for all server processes in this cluster. Moreover, it is the default application name for a standby connection (see synchronous_standby_names .)

The name can be any string of less than NAMEDATALEN characters (64 characters in a standard build). Only printable ASCII characters may be used in the cluster_name value. Other characters will be replaced with question marks ( ? ). No name is shown if this parameter is set to the empty string '' (which is the default). This parameter can only be set at server start.

Enables updating of the process title every time a new SQL command is received by the server. This setting defaults to on on most platforms, but it defaults to off on Windows due to that platform's larger overhead for updating the process title. Only superusers can change this setting.