<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>19.8. Error Reporting and Logging</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="runtime-config-query.html" title="19.7. Query Planning" /><link rel="next" href="runtime-config-statistics.html" title="19.9. Run-time Statistics" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">19.8. Error Reporting and Logging</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-query.html" title="19.7. Query Planning">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 19. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.12 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-statistics.html" title="19.9. Run-time Statistics">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="RUNTIME-CONFIG-LOGGING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">19.8. Error Reporting and Logging</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE">19.8.1. Where To Log</a></span></dt><dt><span class="sect2"><a href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN">19.8.2. When To Log</a></span></dt><dt><span class="sect2"><a href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT">19.8.3. What To Log</a></span></dt><dt><span class="sect2"><a href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG">19.8.4. Using CSV-Format Log Output</a></span></dt><dt><span class="sect2"><a href="runtime-config-logging.html#id-1.6.6.11.7">19.8.5. Process Title</a></span></dt></dl></div><a id="id-1.6.6.11.2" class="indexterm"></a><div class="sect2" id="RUNTIME-CONFIG-LOGGING-WHERE"><div class="titlepage"><div><div><h3 class="title">19.8.1. Where To Log</h3></div></div></div><a id="id-1.6.6.11.3.2" class="indexterm"></a><a id="id-1.6.6.11.3.3" class="indexterm"></a><div class="variablelist"><dl class="variablelist"><dt id="GUC-LOG-DESTINATION"><span class="term"><code class="varname">log_destination</code> (<code class="type">string</code>) <a id="id-1.6.6.11.3.4.1.1.3" class="indexterm"></a> </span></dt><dd><p> <span class="productname">PostgreSQL</span> supports several methods for logging server messages, including <span class="systemitem">stderr</span>, <span class="systemitem">csvlog</span> and <span class="systemitem">syslog</span>. On Windows, <span class="systemitem">eventlog</span> is also supported. Set this parameter to a list of desired log destinations separated by commas. The default is to log to <span class="systemitem">stderr</span> only. This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line. </p><p> If <span class="systemitem">csvlog</span> is included in <code class="varname">log_destination</code>, log entries are output in <span class="quote">“<span class="quote">comma separated value</span>”</span> (<acronym class="acronym">CSV</acronym>) format, which is convenient for loading logs into programs. See <a class="xref" href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG" title="19.8.4. Using CSV-Format Log Output">Section 19.8.4</a> for details. <a class="xref" href="runtime-config-logging.html#GUC-LOGGING-COLLECTOR">logging_collector</a> must be enabled to generate CSV-format log output. </p><p> When either <span class="systemitem">stderr</span> or <span class="systemitem">csvlog</span> are included, the file <code class="filename">current_logfiles</code> 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: </p><pre class="programlisting"> stderr log/postgresql.log csvlog log/postgresql.csv </pre><p> <code class="filename">current_logfiles</code> is recreated when a new log file is created as an effect of rotation, and when <code class="varname">log_destination</code> is reloaded. It is removed when neither <span class="systemitem">stderr</span> nor <span class="systemitem">csvlog</span> are included in <code class="varname">log_destination</code>, and when the logging collector is disabled. </p><div class="note"><h3 class="title">Note</h3><p> On most Unix systems, you will need to alter the configuration of your system's <span class="application">syslog</span> daemon in order to make use of the <span class="systemitem">syslog</span> option for <code class="varname">log_destination</code>. <span class="productname">PostgreSQL</span> can log to <span class="application">syslog</span> facilities <code class="literal">LOCAL0</code> through <code class="literal">LOCAL7</code> (see <a class="xref" href="runtime-config-logging.html#GUC-SYSLOG-FACILITY">syslog_facility</a>), but the default <span class="application">syslog</span> configuration on most platforms will discard all such messages. You will need to add something like: </p><pre class="programlisting"> local0.* /var/log/postgresql </pre><p> to the <span class="application">syslog</span> daemon's configuration file to make it work. </p><p> On Windows, when you use the <code class="literal">eventlog</code> option for <code class="varname">log_destination</code>, 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. See <a class="xref" href="event-log-registration.html" title="18.11. Registering Event Log on Windows">Section 18.11</a> for details. </p></div></dd><dt id="GUC-LOGGING-COLLECTOR"><span class="term"><code class="varname">logging_collector</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.3.4.2.1.3" class="indexterm"></a> </span></dt><dd><p> This parameter enables the <em class="firstterm">logging collector</em>, which is a background process that captures log messages sent to <span class="systemitem">stderr</span> and redirects them into log files. This approach is often more useful than logging to <span class="application">syslog</span>, since some types of messages might not appear in <span class="application">syslog</span> output. (One common example is dynamic-linker failure messages; another is error messages produced by scripts such as <code class="varname">archive_command</code>.) This parameter can only be set at server start. </p><div class="note"><h3 class="title">Note</h3><p> It is possible to log to <span class="systemitem">stderr</span> without using the logging collector; the log messages will just go to wherever the server's <span class="systemitem">stderr</span> 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. </p></div><div class="note"><h3 class="title">Note</h3><p> 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, <span class="application">syslog</span> 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. </p></div></dd><dt id="GUC-LOG-DIRECTORY"><span class="term"><code class="varname">log_directory</code> (<code class="type">string</code>) <a id="id-1.6.6.11.3.4.3.1.3" class="indexterm"></a> </span></dt><dd><p> When <code class="varname">logging_collector</code> 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 <code class="filename">postgresql.conf</code> file or on the server command line. The default is <code class="literal">log</code>. </p></dd><dt id="GUC-LOG-FILENAME"><span class="term"><code class="varname">log_filename</code> (<code class="type">string</code>) <a id="id-1.6.6.11.3.4.4.1.3" class="indexterm"></a> </span></dt><dd><p> When <code class="varname">logging_collector</code> is enabled, this parameter sets the file names of the created log files. The value is treated as a <code class="function">strftime</code> pattern, so <code class="literal">%</code>-escapes can be used to specify time-varying file names. (Note that if there are any time-zone-dependent <code class="literal">%</code>-escapes, the computation is done in the zone specified by <a class="xref" href="runtime-config-logging.html#GUC-LOG-TIMEZONE">log_timezone</a>.) The supported <code class="literal">%</code>-escapes are similar to those listed in the Open Group's <a class="ulink" href="https://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html" target="_top">strftime </a> specification. Note that the system's <code class="function">strftime</code> is not used directly, so platform-specific (nonstandard) extensions do not work. The default is <code class="literal">postgresql-%Y-%m-%d_%H%M%S.log</code>. </p><p> 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 <code class="literal">%</code> escapes were present, <span class="productname">PostgreSQL</span> would append the epoch of the new log file's creation time, but this is no longer the case. </p><p> If CSV-format output is enabled in <code class="varname">log_destination</code>, <code class="literal">.csv</code> will be appended to the timestamped log file name to create the file name for CSV-format output. (If <code class="varname">log_filename</code> ends in <code class="literal">.log</code>, the suffix is replaced instead.) </p><p> This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line. </p></dd><dt id="GUC-LOG-FILE-MODE"><span class="term"><code class="varname">log_file_mode</code> (<code class="type">integer</code>) <a id="id-1.6.6.11.3.4.5.1.3" class="indexterm"></a> </span></dt><dd><p> On Unix systems this parameter sets the permissions for log files when <code class="varname">logging_collector</code> 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 <code class="function">chmod</code> and <code class="function">umask</code> system calls. (To use the customary octal format the number must start with a <code class="literal">0</code> (zero).) </p><p> The default permissions are <code class="literal">0600</code>, meaning only the server owner can read or write the log files. The other commonly useful setting is <code class="literal">0640</code>, 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 <a class="xref" href="runtime-config-logging.html#GUC-LOG-DIRECTORY">log_directory</a> 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. </p><p> This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line. </p></dd><dt id="GUC-LOG-ROTATION-AGE"><span class="term"><code class="varname">log_rotation_age</code> (<code class="type">integer</code>) <a id="id-1.6.6.11.3.4.6.1.3" class="indexterm"></a> </span></dt><dd><p> When <code class="varname">logging_collector</code> 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 <code class="filename">postgresql.conf</code> file or on the server command line. </p></dd><dt id="GUC-LOG-ROTATION-SIZE"><span class="term"><code class="varname">log_rotation_size</code> (<code class="type">integer</code>) <a id="id-1.6.6.11.3.4.7.1.3" class="indexterm"></a> </span></dt><dd><p> When <code class="varname">logging_collector</code> 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 <code class="filename">postgresql.conf</code> file or on the server command line. </p></dd><dt id="GUC-LOG-TRUNCATE-ON-ROTATION"><span class="term"><code class="varname">log_truncate_on_rotation</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.3.4.8.1.3" class="indexterm"></a> </span></dt><dd><p> When <code class="varname">logging_collector</code> is enabled, this parameter will cause <span class="productname">PostgreSQL</span> 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 <code class="varname">log_filename</code> like <code class="literal">postgresql-%H.log</code> would result in generating twenty-four hourly log files and then cyclically overwriting them. This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line. </p><p> Example: To keep 7 days of logs, one log file per day named <code class="literal">server_log.Mon</code>, <code class="literal">server_log.Tue</code>, etc, and automatically overwrite last week's log with this week's log, set <code class="varname">log_filename</code> to <code class="literal">server_log.%a</code>, <code class="varname">log_truncate_on_rotation</code> to <code class="literal">on</code>, and <code class="varname">log_rotation_age</code> to <code class="literal">1440</code>. </p><p> Example: To keep 24 hours of logs, one log file per hour, but also rotate sooner if the log file size exceeds 1GB, set <code class="varname">log_filename</code> to <code class="literal">server_log.%H%M</code>, <code class="varname">log_truncate_on_rotation</code> to <code class="literal">on</code>, <code class="varname">log_rotation_age</code> to <code class="literal">60</code>, and <code class="varname">log_rotation_size</code> to <code class="literal">1000000</code>. Including <code class="literal">%M</code> in <code class="varname">log_filename</code> allows any size-driven rotations that might occur to select a file name different from the hour's initial file name. </p></dd><dt id="GUC-SYSLOG-FACILITY"><span class="term"><code class="varname">syslog_facility</code> (<code class="type">enum</code>) <a id="id-1.6.6.11.3.4.9.1.3" class="indexterm"></a> </span></dt><dd><p> When logging to <span class="application">syslog</span> is enabled, this parameter determines the <span class="application">syslog</span> <span class="quote">“<span class="quote">facility</span>”</span> to be used. You can choose from <code class="literal">LOCAL0</code>, <code class="literal">LOCAL1</code>, <code class="literal">LOCAL2</code>, <code class="literal">LOCAL3</code>, <code class="literal">LOCAL4</code>, <code class="literal">LOCAL5</code>, <code class="literal">LOCAL6</code>, <code class="literal">LOCAL7</code>; the default is <code class="literal">LOCAL0</code>. See also the documentation of your system's <span class="application">syslog</span> daemon. This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line. </p></dd><dt id="GUC-SYSLOG-IDENT"><span class="term"><code class="varname">syslog_ident</code> (<code class="type">string</code>) <a id="id-1.6.6.11.3.4.10.1.3" class="indexterm"></a> </span></dt><dd><p> When logging to <span class="application">syslog</span> is enabled, this parameter determines the program name used to identify <span class="productname">PostgreSQL</span> messages in <span class="application">syslog</span> logs. The default is <code class="literal">postgres</code>. This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line. </p></dd><dt id="GUC-SYSLOG-SEQUENCE-NUMBERS"><span class="term"><code class="varname">syslog_sequence_numbers</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.3.4.11.1.3" class="indexterm"></a> </span></dt><dd><p> When logging to <span class="application">syslog</span> and this is on (the default), then each message will be prefixed by an increasing sequence number (such as <code class="literal">[2]</code>). This circumvents the <span class="quote">“<span class="quote">--- last message repeated N times ---</span>”</span> suppression that many syslog implementations perform by default. In more modern syslog implementations, repeated message suppression can be configured (for example, <code class="literal">$RepeatedMsgReduction</code> in <span class="productname">rsyslog</span>), so this might not be necessary. Also, you could turn this off if you actually want to suppress repeated messages. </p><p> This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line. </p></dd><dt id="GUC-SYSLOG-SPLIT-MESSAGES"><span class="term"><code class="varname">syslog_split_messages</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.3.4.12.1.3" class="indexterm"></a> </span></dt><dd><p> When logging to <span class="application">syslog</span> 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. </p><p> 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. </p><p> This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line. </p></dd><dt id="GUC-EVENT-SOURCE"><span class="term"><code class="varname">event_source</code> (<code class="type">string</code>) <a id="id-1.6.6.11.3.4.13.1.3" class="indexterm"></a> </span></dt><dd><p> When logging to <span class="application">event log</span> is enabled, this parameter determines the program name used to identify <span class="productname">PostgreSQL</span> messages in the log. The default is <code class="literal">PostgreSQL</code>. This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line. </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-LOGGING-WHEN"><div class="titlepage"><div><div><h3 class="title">19.8.2. When To Log</h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-LOG-MIN-MESSAGES"><span class="term"><code class="varname">log_min_messages</code> (<code class="type">enum</code>) <a id="id-1.6.6.11.4.2.1.1.3" class="indexterm"></a> </span></dt><dd><p> Controls which <a class="link" href="runtime-config-logging.html#RUNTIME-CONFIG-SEVERITY-LEVELS" title="Table 19.2. Message Severity Levels">message levels</a> are written to the server log. Valid values are <code class="literal">DEBUG5</code>, <code class="literal">DEBUG4</code>, <code class="literal">DEBUG3</code>, <code class="literal">DEBUG2</code>, <code class="literal">DEBUG1</code>, <code class="literal">INFO</code>, <code class="literal">NOTICE</code>, <code class="literal">WARNING</code>, <code class="literal">ERROR</code>, <code class="literal">LOG</code>, <code class="literal">FATAL</code>, and <code class="literal">PANIC</code>. Each level includes all the levels that follow it. The later the level, the fewer messages are sent to the log. The default is <code class="literal">WARNING</code>. Note that <code class="literal">LOG</code> has a different rank here than in <a class="xref" href="runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a>. Only superusers can change this setting. </p></dd><dt id="GUC-LOG-MIN-ERROR-STATEMENT"><span class="term"><code class="varname">log_min_error_statement</code> (<code class="type">enum</code>) <a id="id-1.6.6.11.4.2.2.1.3" class="indexterm"></a> </span></dt><dd><p> 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 <a class="link" href="runtime-config-logging.html#RUNTIME-CONFIG-SEVERITY-LEVELS" title="Table 19.2. Message Severity Levels">severity</a> or higher. Valid values are <code class="literal">DEBUG5</code>, <code class="literal">DEBUG4</code>, <code class="literal">DEBUG3</code>, <code class="literal">DEBUG2</code>, <code class="literal">DEBUG1</code>, <code class="literal">INFO</code>, <code class="literal">NOTICE</code>, <code class="literal">WARNING</code>, <code class="literal">ERROR</code>, <code class="literal">LOG</code>, <code class="literal">FATAL</code>, and <code class="literal">PANIC</code>. The default is <code class="literal">ERROR</code>, 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 <code class="literal">PANIC</code>. Only superusers can change this setting. </p></dd><dt id="GUC-LOG-MIN-DURATION-STATEMENT"><span class="term"><code class="varname">log_min_duration_statement</code> (<code class="type">integer</code>) <a id="id-1.6.6.11.4.2.3.1.3" class="indexterm"></a> </span></dt><dd><p> 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 <code class="literal">250ms</code> 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. </p><p> For clients using extended query protocol, durations of the Parse, Bind, and Execute steps are logged independently. </p><div class="note"><h3 class="title">Note</h3><p> When using this option together with <a class="xref" href="runtime-config-logging.html#GUC-LOG-STATEMENT">log_statement</a>, the text of statements that are logged because of <code class="varname">log_statement</code> will not be repeated in the duration log message. If you are not using <span class="application">syslog</span>, it is recommended that you log the PID or session ID using <a class="xref" href="runtime-config-logging.html#GUC-LOG-LINE-PREFIX">log_line_prefix</a> so that you can link the statement message to the later duration message using the process ID or session ID. </p></div></dd></dl></div><p> <a class="xref" href="runtime-config-logging.html#RUNTIME-CONFIG-SEVERITY-LEVELS" title="Table 19.2. Message Severity Levels">Table 19.2</a> explains the message severity levels used by <span class="productname">PostgreSQL</span>. If logging output is sent to <span class="systemitem">syslog</span> or Windows' <span class="systemitem">eventlog</span>, the severity levels are translated as shown in the table. </p><div class="table" id="RUNTIME-CONFIG-SEVERITY-LEVELS"><p class="title"><strong>Table 19.2. Message Severity Levels</strong></p><div class="table-contents"><table class="table" summary="Message Severity Levels" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>Severity</th><th>Usage</th><th><span class="systemitem">syslog</span></th><th><span class="systemitem">eventlog</span></th></tr></thead><tbody><tr><td><code class="literal">DEBUG1..DEBUG5</code></td><td>Provides successively-more-detailed information for use by developers.</td><td><code class="literal">DEBUG</code></td><td><code class="literal">INFORMATION</code></td></tr><tr><td><code class="literal">INFO</code></td><td>Provides information implicitly requested by the user, e.g., output from <code class="command">VACUUM VERBOSE</code>.</td><td><code class="literal">INFO</code></td><td><code class="literal">INFORMATION</code></td></tr><tr><td><code class="literal">NOTICE</code></td><td>Provides information that might be helpful to users, e.g., notice of truncation of long identifiers.</td><td><code class="literal">NOTICE</code></td><td><code class="literal">INFORMATION</code></td></tr><tr><td><code class="literal">WARNING</code></td><td>Provides warnings of likely problems, e.g., <code class="command">COMMIT</code> outside a transaction block.</td><td><code class="literal">NOTICE</code></td><td><code class="literal">WARNING</code></td></tr><tr><td><code class="literal">ERROR</code></td><td>Reports an error that caused the current command to abort.</td><td><code class="literal">WARNING</code></td><td><code class="literal">ERROR</code></td></tr><tr><td><code class="literal">LOG</code></td><td>Reports information of interest to administrators, e.g., checkpoint activity.</td><td><code class="literal">INFO</code></td><td><code class="literal">INFORMATION</code></td></tr><tr><td><code class="literal">FATAL</code></td><td>Reports an error that caused the current session to abort.</td><td><code class="literal">ERR</code></td><td><code class="literal">ERROR</code></td></tr><tr><td><code class="literal">PANIC</code></td><td>Reports an error that caused all database sessions to abort.</td><td><code class="literal">CRIT</code></td><td><code class="literal">ERROR</code></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="RUNTIME-CONFIG-LOGGING-WHAT"><div class="titlepage"><div><div><h3 class="title">19.8.3. What To Log</h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-APPLICATION-NAME"><span class="term"><code class="varname">application_name</code> (<code class="type">string</code>) <a id="id-1.6.6.11.5.2.1.1.3" class="indexterm"></a> </span></dt><dd><p> The <code class="varname">application_name</code> can be any string of less than <code class="symbol">NAMEDATALEN</code> 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 <code class="structname">pg_stat_activity</code> view and included in CSV log entries. It can also be included in regular log entries via the <a class="xref" href="runtime-config-logging.html#GUC-LOG-LINE-PREFIX">log_line_prefix</a> parameter. Only printable ASCII characters may be used in the <code class="varname">application_name</code> value. Other characters will be replaced with question marks (<code class="literal">?</code>). </p></dd><dt><span class="term"><code class="varname">debug_print_parse</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.5.2.2.1.3" class="indexterm"></a> <br /></span><span class="term"><code class="varname">debug_print_rewritten</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.5.2.2.2.3" class="indexterm"></a> <br /></span><span class="term"><code class="varname">debug_print_plan</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.5.2.2.3.3" class="indexterm"></a> </span></dt><dd><p> 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 <code class="literal">LOG</code> 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 <a class="xref" href="runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a> and/or <a class="xref" href="runtime-config-logging.html#GUC-LOG-MIN-MESSAGES">log_min_messages</a>. These parameters are off by default. </p></dd><dt><span class="term"><code class="varname">debug_pretty_print</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.5.2.3.1.3" class="indexterm"></a> </span></dt><dd><p> When set, <code class="varname">debug_pretty_print</code> indents the messages produced by <code class="varname">debug_print_parse</code>, <code class="varname">debug_print_rewritten</code>, or <code class="varname">debug_print_plan</code>. This results in more readable but much longer output than the <span class="quote">“<span class="quote">compact</span>”</span> format used when it is off. It is on by default. </p></dd><dt id="GUC-LOG-CHECKPOINTS"><span class="term"><code class="varname">log_checkpoints</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.5.2.4.1.3" class="indexterm"></a> </span></dt><dd><p> 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 <code class="filename">postgresql.conf</code> file or on the server command line. The default is off. </p></dd><dt id="GUC-LOG-CONNECTIONS"><span class="term"><code class="varname">log_connections</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.5.2.5.1.3" class="indexterm"></a> </span></dt><dd><p> 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 <code class="literal">off</code>. </p><div class="note"><h3 class="title">Note</h3><p> Some client programs, like <span class="application">psql</span>, attempt to connect twice while determining if a password is required, so duplicate <span class="quote">“<span class="quote">connection received</span>”</span> messages do not necessarily indicate a problem. </p></div></dd><dt id="GUC-LOG-DISCONNECTIONS"><span class="term"><code class="varname">log_disconnections</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.5.2.6.1.3" class="indexterm"></a> </span></dt><dd><p> Causes session terminations to be logged. The log output provides information similar to <code class="varname">log_connections</code>, 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 <code class="literal">off</code>. </p></dd><dt id="GUC-LOG-DURATION"><span class="term"><code class="varname">log_duration</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.5.2.7.1.3" class="indexterm"></a> </span></dt><dd><p> Causes the duration of every completed statement to be logged. The default is <code class="literal">off</code>. Only superusers can change this setting. </p><p> For clients using extended query protocol, durations of the Parse, Bind, and Execute steps are logged independently. </p><div class="note"><h3 class="title">Note</h3><p> The difference between setting this option and setting <a class="xref" href="runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT">log_min_duration_statement</a> to zero is that exceeding <code class="varname">log_min_duration_statement</code> forces the text of the query to be logged, but this option doesn't. Thus, if <code class="varname">log_duration</code> is <code class="literal">on</code> and <code class="varname">log_min_duration_statement</code> 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. </p></div></dd><dt id="GUC-LOG-ERROR-VERBOSITY"><span class="term"><code class="varname">log_error_verbosity</code> (<code class="type">enum</code>) <a id="id-1.6.6.11.5.2.8.1.3" class="indexterm"></a> </span></dt><dd><p> Controls the amount of detail written in the server log for each message that is logged. Valid values are <code class="literal">TERSE</code>, <code class="literal">DEFAULT</code>, and <code class="literal">VERBOSE</code>, each adding more fields to displayed messages. <code class="literal">TERSE</code> excludes the logging of <code class="literal">DETAIL</code>, <code class="literal">HINT</code>, <code class="literal">QUERY</code>, and <code class="literal">CONTEXT</code> error information. <code class="literal">VERBOSE</code> output includes the <code class="symbol">SQLSTATE</code> error code (see also <a class="xref" href="errcodes-appendix.html" title="Appendix A. PostgreSQL Error Codes">Appendix A</a>) and the source code file name, function name, and line number that generated the error. Only superusers can change this setting. </p></dd><dt id="GUC-LOG-HOSTNAME"><span class="term"><code class="varname">log_hostname</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.5.2.9.1.3" class="indexterm"></a> </span></dt><dd><p> 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 <code class="filename">postgresql.conf</code> file or on the server command line. </p></dd><dt id="GUC-LOG-LINE-PREFIX"><span class="term"><code class="varname">log_line_prefix</code> (<code class="type">string</code>) <a id="id-1.6.6.11.5.2.10.1.3" class="indexterm"></a> </span></dt><dd><p> This is a <code class="function">printf</code>-style string that is output at the beginning of each log line. <code class="literal">%</code> characters begin <span class="quote">“<span class="quote">escape sequences</span>”</span> 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 <code class="filename">postgresql.conf</code> file or on the server command line. The default is <code class="literal">'%m [%p] '</code> which logs a time stamp and the process ID. </p><div class="informaltable"><table class="informaltable" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Escape</th><th>Effect</th><th>Session only</th></tr></thead><tbody><tr><td><code class="literal">%a</code></td><td>Application name</td><td>yes</td></tr><tr><td><code class="literal">%u</code></td><td>User name</td><td>yes</td></tr><tr><td><code class="literal">%d</code></td><td>Database name</td><td>yes</td></tr><tr><td><code class="literal">%r</code></td><td>Remote host name or IP address, and remote port</td><td>yes</td></tr><tr><td><code class="literal">%h</code></td><td>Remote host name or IP address</td><td>yes</td></tr><tr><td><code class="literal">%p</code></td><td>Process ID</td><td>no</td></tr><tr><td><code class="literal">%t</code></td><td>Time stamp without milliseconds</td><td>no</td></tr><tr><td><code class="literal">%m</code></td><td>Time stamp with milliseconds</td><td>no</td></tr><tr><td><code class="literal">%n</code></td><td>Time stamp with milliseconds (as a Unix epoch)</td><td>no</td></tr><tr><td><code class="literal">%i</code></td><td>Command tag: type of session's current command</td><td>yes</td></tr><tr><td><code class="literal">%e</code></td><td>SQLSTATE error code</td><td>no</td></tr><tr><td><code class="literal">%c</code></td><td>Session ID: see below</td><td>no</td></tr><tr><td><code class="literal">%l</code></td><td>Number of the log line for each session or process, starting at 1</td><td>no</td></tr><tr><td><code class="literal">%s</code></td><td>Process start time stamp</td><td>no</td></tr><tr><td><code class="literal">%v</code></td><td>Virtual transaction ID (backendID/localXID)</td><td>no</td></tr><tr><td><code class="literal">%x</code></td><td>Transaction ID (0 if none is assigned)</td><td>no</td></tr><tr><td><code class="literal">%q</code></td><td>Produces no output, but tells non-session processes to stop at this point in the string; ignored by session processes</td><td>no</td></tr><tr><td><code class="literal">%%</code></td><td>Literal <code class="literal">%</code></td><td>no</td></tr></tbody></table></div><p> The <code class="literal">%c</code> 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 <code class="literal">%c</code> can also be used as a space saving way of printing those items. For example, to generate the session identifier from <code class="literal">pg_stat_activity</code>, use this query: </p><pre class="programlisting"> SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' || to_hex(pid) FROM pg_stat_activity; </pre><p> </p><div class="tip"><h3 class="title">Tip</h3><p> If you set a nonempty value for <code class="varname">log_line_prefix</code>, 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. </p></div><div class="tip"><h3 class="title">Tip</h3><p> <span class="application">Syslog</span> produces its own time stamp and process ID information, so you probably do not want to include those escapes if you are logging to <span class="application">syslog</span>. </p></div><div class="tip"><h3 class="title">Tip</h3><p> The <code class="literal">%q</code> escape is useful when including information that is only available in session (backend) context like user or database name. For example: </p><pre class="programlisting"> log_line_prefix = '%m [%p] %q%u@%d/%a ' </pre><p> </p></div></dd><dt id="GUC-LOG-LOCK-WAITS"><span class="term"><code class="varname">log_lock_waits</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.5.2.11.1.3" class="indexterm"></a> </span></dt><dd><p> Controls whether a log message is produced when a session waits longer than <a class="xref" href="runtime-config-locks.html#GUC-DEADLOCK-TIMEOUT">deadlock_timeout</a> to acquire a lock. This is useful in determining if lock waits are causing poor performance. The default is <code class="literal">off</code>. Only superusers can change this setting. </p></dd><dt id="GUC-LOG-STATEMENT"><span class="term"><code class="varname">log_statement</code> (<code class="type">enum</code>) <a id="id-1.6.6.11.5.2.12.1.3" class="indexterm"></a> </span></dt><dd><p> Controls which SQL statements are logged. Valid values are <code class="literal">none</code> (off), <code class="literal">ddl</code>, <code class="literal">mod</code>, and <code class="literal">all</code> (all statements). <code class="literal">ddl</code> logs all data definition statements, such as <code class="command">CREATE</code>, <code class="command">ALTER</code>, and <code class="command">DROP</code> statements. <code class="literal">mod</code> logs all <code class="literal">ddl</code> statements, plus data-modifying statements such as <code class="command">INSERT</code>, <code class="command">UPDATE</code>, <code class="command">DELETE</code>, <code class="command">TRUNCATE</code>, and <code class="command">COPY FROM</code>. <code class="command">PREPARE</code>, <code class="command">EXECUTE</code>, and <code class="command">EXPLAIN ANALYZE</code> 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). </p><p> The default is <code class="literal">none</code>. Only superusers can change this setting. </p><div class="note"><h3 class="title">Note</h3><p> Statements that contain simple syntax errors are not logged even by the <code class="varname">log_statement</code> = <code class="literal">all</code> 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 <code class="varname">log_min_error_statement</code> to <code class="literal">ERROR</code> (or lower) to log such statements. </p></div></dd><dt id="GUC-LOG-REPLICATION-COMMANDS"><span class="term"><code class="varname">log_replication_commands</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.5.2.13.1.3" class="indexterm"></a> </span></dt><dd><p> Causes each replication command to be logged in the server log. See <a class="xref" href="protocol-replication.html" title="53.4. Streaming Replication Protocol">Section 53.4</a> for more information about replication command. The default value is <code class="literal">off</code>. Only superusers can change this setting. </p></dd><dt id="GUC-LOG-TEMP-FILES"><span class="term"><code class="varname">log_temp_files</code> (<code class="type">integer</code>) <a id="id-1.6.6.11.5.2.14.1.3" class="indexterm"></a> </span></dt><dd><p> 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. </p></dd><dt id="GUC-LOG-TIMEZONE"><span class="term"><code class="varname">log_timezone</code> (<code class="type">string</code>) <a id="id-1.6.6.11.5.2.15.1.3" class="indexterm"></a> </span></dt><dd><p> Sets the time zone used for timestamps written in the server log. Unlike <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a>, this value is cluster-wide, so that all sessions will report timestamps consistently. The built-in default is <code class="literal">GMT</code>, but that is typically overridden in <code class="filename">postgresql.conf</code>; <span class="application">initdb</span> will install a setting there corresponding to its system environment. See <a class="xref" href="datatype-datetime.html#DATATYPE-TIMEZONES" title="8.5.3. Time Zones">Section 8.5.3</a> for more information. This parameter can only be set in the <code class="filename">postgresql.conf</code> file or on the server command line. </p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-LOGGING-CSVLOG"><div class="titlepage"><div><div><h3 class="title">19.8.4. Using CSV-Format Log Output</h3></div></div></div><p> Including <code class="literal">csvlog</code> in the <code class="varname">log_destination</code> list provides a convenient way to import log files into a database table. This option emits log lines in comma-separated-values (<acronym class="acronym">CSV</acronym>) format, with these columns: time stamp with milliseconds, user name, database name, process ID, client host:port number, session ID, per-session line number, command tag, session start time, virtual transaction ID, regular transaction ID, error severity, SQLSTATE code, error message, error message detail, hint, internal query that led to the error (if any), character count of the error position therein, error context, user query that led to the error (if any and enabled by <code class="varname">log_min_error_statement</code>), character count of the error position therein, location of the error in the PostgreSQL source code (if <code class="varname">log_error_verbosity</code> is set to <code class="literal">verbose</code>), and application name. Here is a sample table definition for storing CSV-format log output: </p><pre class="programlisting"> CREATE TABLE postgres_log ( log_time timestamp(3) with time zone, user_name text, database_name text, process_id integer, connection_from text, session_id text, session_line_num bigint, command_tag text, session_start_time timestamp with time zone, virtual_transaction_id text, transaction_id bigint, error_severity text, sql_state_code text, message text, detail text, hint text, internal_query text, internal_query_pos integer, context text, query text, query_pos integer, location text, application_name text, PRIMARY KEY (session_id, session_line_num) ); </pre><p> </p><p> To import a log file into this table, use the <code class="command">COPY FROM</code> command: </p><pre class="programlisting"> COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; </pre><p> It is also possible to access the file as a foreign table, using the supplied <a class="xref" href="file-fdw.html" title="F.14. file_fdw">file_fdw</a> module. </p><p> There are a few things you need to do to simplify importing CSV log files: </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p> Set <code class="varname">log_filename</code> and <code class="varname">log_rotation_age</code> to provide a consistent, predictable naming scheme for your log files. This lets you predict what the file name will be and know when an individual log file is complete and therefore ready to be imported. </p></li><li class="listitem"><p> Set <code class="varname">log_rotation_size</code> to 0 to disable size-based log rotation, as it makes the log file name difficult to predict. </p></li><li class="listitem"><p> Set <code class="varname">log_truncate_on_rotation</code> to <code class="literal">on</code> so that old log data isn't mixed with the new in the same file. </p></li><li class="listitem"><p> The table definition above includes a primary key specification. This is useful to protect against accidentally importing the same information twice. The <code class="command">COPY</code> command commits all of the data it imports at one time, so any error will cause the entire import to fail. If you import a partial log file and later import the file again when it is complete, the primary key violation will cause the import to fail. Wait until the log is complete and closed before importing. This procedure will also protect against accidentally importing a partial line that hasn't been completely written, which would also cause <code class="command">COPY</code> to fail. </p></li></ol></div><p> </p></div><div class="sect2" id="id-1.6.6.11.7"><div class="titlepage"><div><div><h3 class="title">19.8.5. Process Title</h3></div></div></div><p> These settings control how process titles of server processes are modified. Process titles are typically viewed using programs like <span class="application">ps</span> or, on Windows, <span class="application">Process Explorer</span>. See <a class="xref" href="monitoring-ps.html" title="28.1. Standard Unix Tools">Section 28.1</a> for details. </p><div class="variablelist"><dl class="variablelist"><dt id="GUC-CLUSTER-NAME"><span class="term"><code class="varname">cluster_name</code> (<code class="type">string</code>) <a id="id-1.6.6.11.7.3.1.1.3" class="indexterm"></a> </span></dt><dd><p> Sets the cluster name that appears in the process title for all server processes in this cluster. The name can be any string of less than <code class="symbol">NAMEDATALEN</code> characters (64 characters in a standard build). Only printable ASCII characters may be used in the <code class="varname">cluster_name</code> value. Other characters will be replaced with question marks (<code class="literal">?</code>). No name is shown if this parameter is set to the empty string <code class="literal">''</code> (which is the default). This parameter can only be set at server start. </p></dd><dt id="GUC-UPDATE-PROCESS-TITLE"><span class="term"><code class="varname">update_process_title</code> (<code class="type">boolean</code>) <a id="id-1.6.6.11.7.3.2.1.3" class="indexterm"></a> </span></dt><dd><p> Enables updating of the process title every time a new SQL command is received by the server. This setting defaults to <code class="literal">on</code> on most platforms, but it defaults to <code class="literal">off</code> on Windows due to that platform's larger overhead for updating the process title. Only superusers can change this setting. </p></dd></dl></div></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="runtime-config-query.html" title="19.7. Query Planning">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 19. Server Configuration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-statistics.html" title="19.9. Run-time Statistics">Next</a></td></tr><tr><td width="40%" align="left" valign="top">19.7. Query Planning </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 11.12 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 19.9. Run-time Statistics</td></tr></table></div></body></html>