|
|
| PostgreSQL 8.1.4 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 17. Server Configuration | Fast Forward | Next |
PostgreSQL supports several methods for logging server messages, including stderr and syslog. On Windows, eventlog is also supported. Set this option to a list of desired log destinations separated by commas. The default is to log to stderr only. This option can be set at server start or in the postgresql.conf configuration file.
This option allows messages sent to stderr to be captured and redirected into log files. This option, in combination with logging to stderr, is often more useful than logging to syslog, since some types of messages may not appear in syslog output (a common example is dynamic-linker failure messages). This option can only be set at server start.
When redirect_stderr is enabled, this option determines the directory in which log files will be created. It may be specified as an absolute path, or relative to the cluster data directory. This option can be set at server start or in the postgresql.conf configuration file.
When redirect_stderr is enabled, this option 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. If no %-escapes are present, PostgreSQL will append the epoch of the new log file's open time. For example, if log_filename were server_log, then the chosen file name would be server_log.1093827753 for a log starting at Sun Aug 29 19:02:33 2004 MST. This option can be set at server start or in the postgresql.conf configuration file.
When redirect_stderr is enabled, this option 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 option can be set at server start or in the postgresql.conf configuration file.
When redirect_stderr is enabled, this option 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 option can be set at server start or in the postgresql.conf configuration file.
When redirect_stderr is enabled, this option 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 option 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 option can be set at server start or in the postgresql.conf configuration file.
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 may occur to select a file name different from the hour's initial file name.
When logging to syslog is enabled, this option determines the syslog "facility" to be used. You may 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 option can be set at server start or in the postgresql.conf configuration file.
When logging to syslog is enabled, this option determines the program name used to identify PostgreSQL messages in syslog logs. The default is postgres. This option can be set at server start or in the postgresql.conf configuration file.
Controls which message levels are sent to the client. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, ERROR, FATAL, and PANIC. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. The default is NOTICE. Note that LOG has a different rank here than in log_min_messages.
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 NOTICE. Note that LOG has a different rank here than in client_min_messages. Only superusers can change this setting.
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. Only superusers can change this setting.
Controls whether or not the SQL statement that causes an error condition will also be recorded in the server log. All SQL statements that cause an error of the specified level or higher are logged. The default is PANIC (effectively turning this feature off for normal use). Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, FATAL, and PANIC. For example, if you set this to ERROR then all SQL statements causing errors, fatal errors, or panics will be logged. Enabling this option can be helpful in tracking down the source of any errors that appear in the server log. Only superusers can change this setting.
Logs the statement and its duration on a single log line if its duration is greater than or equal to the specified number of milliseconds. Setting this to zero will print all statements and their durations. Minus-one (the default) disables the feature. For example, if you set it to 250 then all SQL statements that run 250ms or longer will be logged. Enabling this option can be useful in tracking down unoptimized queries in your applications. This setting is independent of log_statement and log_duration. Only superusers can change this setting.
Runs the server silently. If this option is set, the server will automatically run in background and any controlling terminals are disassociated (same effect as postmaster's -S option). The server's standard output and standard error are redirected to /dev/null, so any messages sent to them will be lost. Unless syslog logging is selected or redirect_stderr is enabled, using this option is discouraged because it makes it impossible to see error messages.
Here is a list of the various message severity levels used in these settings:
Provides information for use by developers.
Provides information implicitly requested by the user, e.g., during VACUUM VERBOSE.
Provides information that may be helpful to users, e.g., truncation of long identifiers and the creation of indexes as part of primary keys.
Provides warnings to the user, e.g., COMMIT outside a transaction block.
Reports an error that caused the current command to abort.
Reports information of interest to administrators, e.g., checkpoint activity.
Reports an error that caused the current session to abort.
Reports an error that caused all sessions to abort.
These options enable various debugging output to be emitted. For each executed query, they print the resulting parse tree, the query rewriter output, or the execution plan. debug_pretty_print indents these displays to produce a more readable but much longer output format. client_min_messages or log_min_messages must be DEBUG1 or lower to actually send this output to the client or the server log, respectively. These options are off by default.
This outputs a line to the server log detailing each successful connection. This is off by default, although it is probably very useful. Some client programs, like psql, attempt to connect twice while determining if a password is required, so duplicate "connection received" messages do not necessarily indicate a problem. This option can only be set at server start or in the postgresql.conf configuration file.
This outputs a line in the server log similar to log_connections but at session termination, and includes the duration of the session. This is off by default. This option can be set at server start or in the postgresql.conf configuration file.
Causes the duration of every completed statement which satisfies log_statement to be logged. When using this option, if you are not using syslog, it is recommended that you log the PID or session ID using log_line_prefix so that you can link the statement message to the later duration message using the process ID or session ID. The default is off. Only superusers can change this setting.
This is a printf-style string that is output at the
beginning of each log line. The default is an empty string.
Each recognized escape is replaced as outlined
below - anything else that looks like an escape is ignored. Other
characters are copied straight to the log line. Some escapes are
only recognized by session processes, and do not apply to
background processes such as the postmaster. Syslog
produces its own
time stamp and process ID information, so you probably do not want to
use those escapes if you are using syslog.
This option can be set at server start or in the
postgresql.conf configuration file.
| Escape | Effect | Session only |
|---|---|---|
| %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 (no milliseconds) | no |
| %m | Time stamp with milliseconds | no |
| %i | Command tag: This is the command that generated the log line. | yes |
| %c | Session ID: A unique identifier for each session. It is 2 4-byte hexadecimal numbers (without leading zeros) separated by a dot. The numbers are the session start time and the process ID, so this can also be used as a space saving way of printing these items. | yes |
| %l | Number of the log line for each process, starting at 1 | no |
| %s | Session start time stamp | yes |
| %x | Transaction ID | yes |
| %q | Does not produce any output, but tells non-session processes to stop at this point in the string. Ignored by session processes. | no |
| %% | Literal % | no |
Controls which SQL statements are logged. Valid values are none, ddl, mod, and all. ddl logs all data definition commands like CREATE, ALTER, and DROP commands. mod logs all ddl statements, plus INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type.
The default is none. Only superusers can change this setting.
Note: The EXECUTE statement is not considered a ddl or mod statement. Statements that generate syntax errors are not logged. Set log_min_error_statement to error to log such statements.
When a function is defined in the PL/pgSQLserver-side language, any queries executed by the function will only be logged the first time that the function is invoked in a particular session. This is because PL/pgSQL keeps a cache of the query plans produced for the SQL statements in the function.
By default, connection log messages only show the IP address of the connecting host. Turning on this option 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 option can be set at server start or in the postgresql.conf file.