Logging and Debugging

SERVER_MIN_MESSAGES(string)

This controls how much message detail is written to the server logs. Valid values areDEBUG5, DEBUG4,DEBUG3,DEBUG2,DEBUG1,INFO,NOTICE,WARNING,ERROR,LOG,FATAL, andPANIC. Later values send less detail to the logs. The default isNOTICE. Note thatLOGhas a different precedence here than inCLIENT_MIN_MESSAGES.

Here is a summary of the various message types:

DEBUG[1-5]

Provides information for use by developers.

INFO

Provides information implicitly requested by the user, e.g., duringVACUUM VERBOSE.

NOTICE

Provides information that may be helpful to users, e.g., truncation of long identifiers and index creation as part of primary keys.

WARNING

Provides warnings to the user, e.g.,COMMIToutside a transaction.

ERROR

Reports the error that caused a transaction to abort.

LOG

Reports information of interest to administrators, e.g., checkpoint activity.

FATAL

Reports why a backend session terminated.

PANIC

Reports why all backend sessions restarted.

CLIENT_MIN_MESSAGES(string)

This controls how much message detail is written to the client. Valid values areDEBUG5, DE-BUG4,DEBUG3,DEBUG2,DEBUG1,LOG,NOTICE,WARNING, andERROR. Later values send less information to the client. The default isNOTICE. Note thatLOGhas a different precedence here than inSERVER_MIN_MESSAGES. Also see that section for an explanation of the various values.

DEBUG_ASSERTIONS(boolean)

Turns on various assertion checks. This is a debugging aid. If you are experiencing strange problems or crashes you might want to turn this on, as it might expose programming mistakes. To use this option, the macroUSE_ASSERT_CHECKINGmust be defined when PostgreSQL is built (accomplished by theconfigureoption--enable-cassert). Note thatDEBUG_ASSERTIONS defaults to on if PostgreSQL has been built with assertions enabled.

DEBUG_PRINT_PARSE(boolean) DEBUG_PRINT_REWRITTEN(boolean) DEBUG_PRINT_PLAN(boolean) DEBUG_PRETTY_PRINT(boolean)

These flags enable various debugging output to be sent to the server log. For each executed query, print either the query text, the resulting parse tree, the query rewriter output, or the execution plan.DEBUG_PRETTY_PRINTindents these displays to produce a more readable but much longer output format.

EXPLAIN_PRETTY_PRINT(boolean)

Determines whetherEXPLAIN VERBOSEuses the indented or non-indented format for display-ing detailed query-tree dumps.

HOSTNAME_LOOKUP(boolean)

By default, connection logs only show the IP address of the connecting host. If you want it to show the host name you can turn this on, but depending on your host name resolution setup it might impose a non-negligible performance penalty. This option can only be set at server start.

LOG_CONNECTIONS(boolean)

This outputs a line to the server logs detailing each successful connection. This is off by de-fault, although it is probably very useful. This option can only be set at server start or in the postgresql.confconfiguration file.

LOG_DURATION(boolean)

Causes the duration of every completed statement to be logged. To use this option, enable LOG_STATEMENTandLOG_PIDso you can link the statement to the duration using the process ID.

LOG_MIN_ERROR_STATEMENT(string)

This controls for which message levels the SQL statement causing that message is to be recorded in the server log. All statements causing a message of the level of the setting or higher are logged.

The default isPANIC(effectively turning this feature off). Valid values are DEBUG5,DEBUG4, DEBUG3,DEBUG2,DEBUG1,INFO,NOTICE,WARNING,ERROR,FATAL, andPANIC. For example, if you set this toERRORthen all SQL statements causing errors, fatal errors, or panics will be logged.

It is recommended you enableLOG_PIDas well so you can more easily match the error statement with the error message.

LOG_PID(boolean)

Prefixes each server message in the log file with the process ID of the backend process. This is useful to sort out which messages pertain to which connection. The default is off. This parameter does not affect messages logged via syslog, which always contain the process ID.

LOG_STATEMENT(boolean)

Causes each SQL statement to be logged.

LOG_TIMESTAMP(boolean)

Prefixes each server log message with a time stamp. The default is off.

SHOW_STATEMENT_STATS(boolean) SHOW_PARSER_STATS(boolean) SHOW_PLANNER_STATS(boolean) SHOW_EXECUTOR_STATS(boolean)

For each query, write performance statistics of the respective module to the server log. This is a crude profiling instrument.

SHOW_SOURCE_PORT(boolean)

Shows the outgoing port number of the connecting host in the connection log messages. You could trace back the port number to find out what user initiated the connection. Other than that, it’s pretty useless and therefore off by default. This option can only be set at server start.

STATS_COMMAND_STRING(boolean) STATS_BLOCK_LEVEL(boolean) STATS_ROW_LEVEL(boolean)

These flags determine what information backends send to the statistics collector process: current commands, block-level activity statistics, or row-level activity statistics. All default to off. En-abling statistics collection costs a small amount of time per query, but is invaluable for debugging and performance tuning.

STATS_RESET_ON_SERVER_START(boolean)

If on, collected statistics are zeroed out whenever the server is restarted. If off, statistics are accumulated across server restarts. The default is on. This option can only be set at server start.

STATS_START_COLLECTOR(boolean)

Controls whether the server should start the statistics-collection subprocess. This is on by default, but may be turned off if you know you have no interest in collecting statistics. This option can only be set at server start.

SYSLOG(integer)

PostgreSQL allows the use of syslog for logging. If this option is set to 1, messages go both to syslog and the standard output. A setting of 2 sends output only to syslog. (Some messages will still go to the standard output/error.) The default is 0, which means syslog is off. This option must be set at server start.

SYSLOG_FACILITY(string)

This option determines the syslog “facility” to be used when syslog is enabled. You may choose fromLOCAL0,LOCAL1,LOCAL2,LOCAL3,LOCAL4,LOCAL5,LOCAL6,LOCAL7; the default is LOCAL0. See also the documentation of your system’s syslog.

SYSLOG_IDENT(string)

If logging to syslog is enabled, this option determines the program name used to identify Post-greSQL messages in syslog log messages. The default ispostgres.

TRACE_NOTIFY(boolean)

Generates a great amount of debugging output for theLISTENandNOTIFYcommands.