| PostgreSQL 8.3.23 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 18. Server Configuration | Fast Forward | Next |
PostgreSQL supports several methods for logging server messages, including stderr, csvlog and syslog. On Windows, eventlog is also supported. Set this parameter to a list of desired log destinations separated by commas. The default is to log to stderr only. This parameter can only be set in the postgresql.conf file or on the server command line.
If csvlog is included in log_destination, log entries are output in "comma separated value" format, which is convenient for loading them into programs. See Section 18.7.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's syslog daemon in order to make use of the syslog option for log_destination. PostgreSQL can log to syslog facilities LOCAL0 through LOCAL7 (see syslog_facility), but the default syslog configuration on most platforms will discard all such messages. You will need to add something like
local0.* /var/log/postgresqlto the syslog daemon's configuration file to make it work.
This parameter allows messages sent to stderr, and CSV-format log output, to be captured and redirected into log files. This approach is often more useful than logging to syslog, since some types of messages might not appear in syslog output (a common example is dynamic-linker failure messages). This parameter can only be set at server start.
When logging_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 the postgresql.conf file or on the server command line.
When logging_collector is enabled, this parameter 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. (Note that if there are any time-zone-dependent %-escapes, the computation is done in the zone specified by log_timezone.) If no %-escapes are present, PostgreSQL will append the epoch of the new log file's creation 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 parameter can only be set in the postgresql.conf file or on the server command line.
If CSV-format output is enabled in log_destination, .csv will be appended to the timestamped log file name to create the file name for CSV-format output. (If log_filename ends in .log, the suffix is replaced instead.) In the case of the example above, the CSV file name will be server_log.1093827753.csv.
When logging_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 the postgresql.conf file or on the server command line.
When logging_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 the postgresql.conf file or on the server command line.
When logging_collector is enabled, this parameter 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 setting 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 parameter can only be set in the postgresql.conf file or on the server command line.
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 might occur to select a file name different from the hour's initial file name.
When logging to syslog is enabled, this parameter determines the syslog "facility" to be used. You can 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 parameter can only be set in the postgresql.conf file or on the server command line. This parameter is unavailable unless the server is compiled with support for syslog.
When logging to syslog is enabled, this parameter determines the program name used to identify PostgreSQL messages in syslog logs. The default is postgres. This parameter can only be set in the postgresql.conf file or on the server command line. This parameter is unavailable unless the server is compiled with support for syslog.
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 be 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.
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 250ms 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 with log_statement, the text of statements that are logged because of log_statement will not be repeated in the duration log message. 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.
Runs the server silently. If this parameter is set, the server will automatically run in background and any controlling terminals are disassociated. 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 logging_collector is enabled, using this parameter is discouraged because it makes it impossible to see error messages. This parameter can only be set at server start.
Table 18-1 explains the message severity levels used by PostgreSQL. If logging output is sent to syslog or Windows' eventlog, the severity levels are translated as shown in the table.
Table 18-1. Message severity levels
| Severity | Usage | syslog | eventlog |
|---|---|---|---|
| DEBUG1..DEBUG5 | Provides successively-more-detailed information for use by developers. | DEBUG | INFORMATION |
| INFO | Provides information implicitly requested by the user, e.g., output from VACUUM VERBOSE. | INFO | INFORMATION |
| NOTICE | Provides information that might be helpful to users, e.g., notice of truncation of long identifiers. | NOTICE | INFORMATION |
| WARNING | Provides warnings of likely problems, e.g., COMMIT outside a transaction block. | NOTICE | WARNING |
| ERROR | Reports an error that caused the current command to abort. | WARNING | ERROR |
| LOG | Reports information of interest to administrators, e.g., checkpoint activity. | INFO | INFORMATION |
| FATAL | Reports an error that caused the current session to abort. | ERR | ERROR |
| PANIC | Reports an error that caused all database sessions to abort. | CRIT | ERROR |
These parameters 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 parameters are off by default.
Causes checkpoints to be logged in the server log. Some statistics about each checkpoint 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 postgresql.conf file or on the server command line. The default is off.
Causes each attempted connection to the server to be logged, as well as successful completion of client authentication. This parameter can only be set in the postgresql.conf file or on the server command line. The default is off.
Note: 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 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 parameter can only be set in the postgresql.conf file or on the server command line.
Causes the duration of every completed statement to be logged. The default is off. 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 setting log_min_duration_statement to zero is that exceeding log_min_duration_statement forces the text of the query to be logged, but this option doesn't. Thus, if log_duration is on and log_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.
By default, connection log messages only show the IP address of the connecting host. Turning on this parameter 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 postgresql.conf file or on the server command line.
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 do not apply to 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.
| 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 without milliseconds | no |
| %m | Time stamp with milliseconds | no |
| %i | Command tag: type of session's current command | yes |
| %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 |
Tip: If you set a nonempty value for log_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 could be used too.
Tip: Syslog produces its own time stamp and process ID information, so you probably do not want to use those escapes if you are logging to syslog.
Controls whether a log message is produced when a session waits longer than deadlock_timeout to acquire a lock. This is useful in determining if lock waits are causing poor performance. The default is off.
Controls which SQL statements are logged. Valid values are none, ddl, mod, and all. 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.
Note: Statements that contain simple syntax errors are not logged even by the log_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). Set log_min_error_statement to ERROR (or lower) to log such statements.
Controls whether temporary files are logged when deleted. Temporary files can be created for sorts, hashes, and temporary query results. A value of zero logs all temporary files, and positive values log only files whose size is equal or greater than the specified number of kilobytes. The default is -1, which disables this logging.
Sets the time zone used for timestamps written in the log. Unlike timezone, this value is cluster-wide, so that all sessions will report timestamps consistently. The default is unknown, which means to use whatever the system environment specifies as the time zone. See Section 8.5.3 for more information. This parameter can only be set in the postgresql.conf file or on the server command line.
Including csvlog in the log_destination list provides a convenient way to import log files into a database table. This option emits log lines in comma-separated-value format, with these columns: timestamp with milliseconds, user name, database name, process ID, host:port number, session ID, per-session or -process line number, command tag, session start time, virtual transaction ID, regular transaction id, error severity, SQL state code, error message, error message detail, hint, internal query that led to the error (if any), character count of the error position thereof, error context, user query that led to the error (if any and enabled by log_min_error_statement), character count of the error position thereof, location of the error in the PostgreSQL source code (if log_error_verbosity is set to verbose). Here is a sample table definition for storing CSV-format log output:
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, PRIMARY KEY (session_id, session_line_num) );
To import a log file into this table, use the COPY FROM command:
COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
There are a few things you need to do to simplify importing CSV log files easily and automatically:
Set log_filename and log_rotation_age 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.
Set log_rotation_size to 0 to disable size-based log rotation, as it makes the log file name difficult to predict.
Set log_truncate_on_rotation to on so that old log data isn't mixed with the new in the same file.
The table definition above includes a primary key specification. This is useful to protect against accidentally importing the same information twice. The COPY 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 COPY to fail.