PostgreSQL Logging

PostgreSQL has a number of different configurations where you can change the logging level and these are all outlined in the PostgreSQL documentation (for a different version of PostgreSQL just change the version number in the URL).

This is held in the postgresql.conf file which you will find in the data folder under your PostgreSQL installation unless you have defined PGDATA as an Environment Variable.

Please backup your existing postgresql.conf file before making any changes.

Open PgAdmin III and from the File menu select Open postgresql.conf.... Locate the postgresql.conf file. Double click on an entry to change it.

Some of these settings can only be changed by a Superuser.

You need to be aware when changing any of these settings. Changing the verbosity of the logging will have an affect on the speed of data access as well increased size of the log files. You need to make sure that you have a daily clear out in place to prevent your disk filling up and potential application failure. Please see the topic Remove MapServer / PostgreSQL & iShare temporary files with Studio Workflow for details.

Some of the logging entries you may wish to change are:

Setting name	Description
log_min_message	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.
log_min_error_statement	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.
log_statement	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.
log_line_prefix	This is a `printf`-style string that is output at the beginning of each log line. % characters begin "escape sequences" that are replaced with status information as outlined below. Unrecognized escapes are ignored. Other characters are copied straight to the log line. Some escapes are only recognized by session processes, and are ignored by background processes such as the main server process. This parameter can only be set in the postgresql.conf file or on the server command line. The default is an empty string.