log_destination (string)

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

Ifcsvlog is included inlog_destination, log entries are output in"comma separated value" (CSV) format, which is convenient for loading logs into programs. SeeSection 18.8.4 for details.logging_collector must be enabled to generate CSV-format log output.

Note: On most Unix systems, you will need to alter the configuration of your system'ssyslog daemon in order to make use of thesyslog option forlog_destination.PostgreSQL can log tosyslog facilitiesLOCAL0 throughLOCAL7 (seesyslog_facility), but the defaultsyslog configuration on most platforms will discard all such messages. You will need to add something like:

local0.*    /var/log/postgresql

to thesyslog daemon's configuration file to make it work.

On Windows, when you use theeventlog option forlog_destination, you should register an event source and its library with the operating system so that the Windows Event Viewer can display event log messages cleanly. SeeSection 17.11 for details.

logging_collector (boolean)

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

Note: It is possible to log tostderr without using the logging collector; the log messages will just go to wherever the server'sstderr is directed. However, that method is only suitable for low log volumes, since it provides no convenient way to rotate log files. Also, on some platforms not using the logging collector can result in lost or garbled log output, because multiple processes writing concurrently to the same log file can overwrite each other's output.

Note: The logging collector is designed to never lose messages. This means that in case of extremely high load, server processes could be blocked while trying to send additional log messages when the collector has fallen behind. In contrast,syslog prefers to drop messages if it cannot write them, which means it may fail to log some messages in such cases but it will not block the rest of the system.

log_directory (string)

Whenlogging_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 thepostgresql.conf file or on the server command line. The default ispg_log.

log_filename (string)

Whenlogging_collector is enabled, this parameter sets the file names of the created log files. The value is treated as astrftime 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 bylog_timezone.) The supported%-escapes are similar to those listed in the Open Group'sstrftime specification. Note that the system'sstrftime is not used directly, so platform-specific (nonstandard) extensions do not work. The default ispostgresql-%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 inlog_destination,.csv will be appended to the timestamped log file name to create the file name for CSV-format output. (Iflog_filename ends in.log, the suffix is replaced instead.)

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

log_file_mode (integer)

On Unix systems this parameter sets the permissions for log files whenlogging_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 thechmod andumask system calls. (To use the customary octal format the number must start with a0 (zero).)

The default permissions are0600, meaning only the server owner can read or write the log files. The other commonly useful setting is0640, 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 alterlog_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 thepostgresql.conf file or on the server command line.

log_rotation_age (integer)

Whenlogging_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 thepostgresql.conf file or on the server command line.

log_rotation_size (integer)

Whenlogging_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 thepostgresql.conf file or on the server command line.

log_truncate_on_rotation (boolean)

Whenlogging_collector is enabled, this parameter will causePostgreSQL 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 alog_filename likepostgresql-%H.log would result in generating twenty-four hourly log files and then cyclically overwriting them. This parameter can only be set in thepostgresql.conf file or on the server command line.

Example: To keep 7 days of logs, one log file per day namedserver_log.Mon,server_log.Tue, etc, and automatically overwrite last week's log with this week's log, setlog_filename toserver_log.%a,log_truncate_on_rotation toon, andlog_rotation_age to1440.

Example: To keep 24 hours of logs, one log file per hour, but also rotate sooner if the log file size exceeds 1GB, setlog_filename toserver_log.%H%M,log_truncate_on_rotation toon,log_rotation_age to60, andlog_rotation_size to1000000. Including%M inlog_filename allows any size-driven rotations that might occur to select a file name different from the hour's initial file name.

syslog_facility (enum)

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

syslog_ident (string)

When logging tosyslog is enabled, this parameter determines the program name used to identifyPostgreSQL messages insyslog logs. The default ispostgres. This parameter can only be set in thepostgresql.conf file or on the server command line.

event_source (string)

When logging toevent log is enabled, this parameter determines the program name used to identifyPostgreSQL messages in the log. The default isPostgreSQL. This parameter can only be set in thepostgresql.conf file or on the server command line.

client_min_messages (enum)

Controls which message levels are sent to the client. Valid values areDEBUG5,DEBUG4,DEBUG3,DEBUG2,DEBUG1,LOG,NOTICE,WARNING,ERROR,FATAL, andPANIC. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. The default isNOTICE. Note thatLOG has a different rank here than inlog_min_messages.

log_min_messages (enum)

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

log_min_error_statement (enum)

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 areDEBUG5,DEBUG4,DEBUG3,DEBUG2,DEBUG1,INFO,NOTICE,WARNING,ERROR,LOG,FATAL, andPANIC. The default isERROR, 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 toPANIC. Only superusers can change this setting.

log_min_duration_statement (integer)

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 to250ms 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.

Note: When using this option together withlog_statement, the text of statements that are logged because oflog_statement will not be repeated in the duration log message. If you are not usingsyslog, it is recommended that you log the PID or session ID usinglog_line_prefix so that you can link the statement message to the later duration message using the process ID or session ID.

Table 18-1. Message Severity Levels

application_name (string)

Theapplication_name can be any string of less thanNAMEDATALEN 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 thepg_stat_activity view and included in CSV log entries. It can also be included in regular log entries via thelog_line_prefix parameter. Only printable ASCII characters may be used in theapplication_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 atLOG 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 adjustingclient_min_messages and/orlog_min_messages. These parameters are off by default.

debug_pretty_print (boolean)

When set,debug_pretty_print indents the messages produced bydebug_print_parse,debug_print_rewritten, ordebug_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 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 thepostgresql.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 cannot be changed after session start. The default is off.

Note: Some client programs, likepsql, 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 tolog_connections but at session termination, and includes the duration of the session. This is off by default. This parameter cannot be changed after session start.

log_duration (boolean)

Causes the duration of every completed statement to be logged. The default isoff. 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 settinglog_min_duration_statement to zero is that exceedinglog_min_duration_statement forces the text of the query to be logged, but this option doesn't. Thus, iflog_duration ison andlog_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 areTERSE,DEFAULT, andVERBOSE, each adding more fields to displayed messages.TERSE excludes the logging ofDETAIL,HINT,QUERY, andCONTEXT error information.VERBOSE output includes theSQLSTATE error code (see alsoAppendix A) 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 thepostgresql.conf file or on the server command line.

log_line_prefix (string)

This is aprintf-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 thepostgresql.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 frompg_stat_activity, use this query:

SELECT to_hex(EXTRACT(EPOCH FROM backend_start)::integer) || '.' ||       to_hex(pid)FROM pg_stat_activity;

Tip: If you set a nonempty value forlog_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 tosyslog.

log_lock_waits (boolean)

Controls whether a log message is produced when a session waits longer thandeadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance. The default isoff.

log_statement (enum)

Controls which SQL statements are logged. Valid values arenone (off),ddl,mod, andall (all statements).ddl logs all data definition statements, such asCREATE,ALTER, andDROP statements.mod logs allddl statements, plus data-modifying statements such asINSERT,UPDATE,DELETE,TRUNCATE, andCOPY FROM.PREPARE,EXECUTE, andEXPLAIN 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 isnone. Only superusers can change this setting.

Note: Statements that contain simple syntax errors are not logged even by thelog_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). Setlog_min_error_statement toERROR (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 server log. UnlikeTimeZone, this value is cluster-wide, so that all sessions will report timestamps consistently. The built-in default isGMT, but that is typically overridden inpostgresql.conf;initdb will install a setting there corresponding to its system environment. SeeSection 8.5.3 for more information. This parameter can only be set in thepostgresql.conf file or on the server command line.