log_destination
(string
)
PostgreSQL supports several methods
for logging server messages, including
stderr 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.
redirect_stderr
(boolean
)
This parameter allows messages sent to stderr to be captured and redirected into log files. This method, 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 parameter can only be set at server start.
log_directory
(string
)
When redirect_stderr
is enabled, this parameter
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 parameter can only be set in the postgresql.conf
file or on the server command line.
log_filename
(string
)
When redirect_stderr
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.
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 parameter can only be set in the postgresql.conf
file or on the server command line.
log_rotation_age
(integer
)
When redirect_stderr
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.
log_rotation_size
(integer
)
When redirect_stderr
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.
log_truncate_on_rotation
(boolean
)
When redirect_stderr
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 may occur to select a file name
different from the hour's initial file name.
syslog_facility
(string
)
When logging to syslog is enabled, this parameter
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 parameter can only be set in the postgresql.conf
file or on the server command line.
syslog_ident
(string
)
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.
client_min_messages
(string
)
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
.
log_min_messages
(string
)
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.
log_error_verbosity
(string
)
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.
log_min_error_statement
(string
)
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
,
FATAL
, and PANIC
.
The default is ERROR
, which means statements
causing errors, 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_min_duration_statement
(integer
)
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.
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.
silent_mode
(boolean
)
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
redirect_stderr
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.
Here is a list of the various message severity levels used in these settings:
DEBUG[1-5]
Provides information for use by developers.
INFO
Provides information implicitly requested by the user,
e.g., during VACUUM VERBOSE
.
NOTICE
Provides information that may be helpful to users, e.g., truncation of long identifiers and the creation of indexes as part of primary keys.
WARNING
Provides warnings to the user, e.g., COMMIT
outside a transaction block.
ERROR
Reports an error that caused the current command to abort.
LOG
Reports information of interest to administrators, e.g., checkpoint activity.
FATAL
Reports an error that caused the current session to abort.
PANIC
Reports an error that caused all sessions to abort.
debug_print_parse
(boolean
)debug_print_rewritten
(boolean
)debug_print_plan
(boolean
)debug_pretty_print
(boolean
)
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.
log_connections
(boolean
)
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 parameter can only be set in the postgresql.conf
file or on the server command line.
log_disconnections
(boolean
)
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.
log_duration
(boolean
)
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.
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.
log_line_prefix
(string
)
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 main server process. 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 parameter can only be set in the postgresql.conf
file or on the server command line.
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 timezone on Windows) | 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 |
log_statement
(string
)
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.
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.
log_hostname
(boolean
)
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.