PostgreSQL Logging

By default PostgreSQL is logging to standard output of the console. To manage the logging of the PostgreSQL database some of the postgresql.conf options should be set correctly.

log_destination

log_destination is the first parameters should be set in order to control the PostgreSQL logging. There are three options for this parameter which are stderr | csvlog | syslog.

Mostly used logging option is the stderr which pushes the logs to the file defined in log_directory and log_filename

log_directory

Default value of this parameter is pg_log which is under the PostgreSQL database data directory.

log_filename

Determines the name of the file where PostgreSQL will push the logs to. The default value os the parameter is postgresql-%Y-%m-%d_%H%M%S.log.

Options of the % parameters can be summarized as follows;

%y . Replaced by the last two digits of the year as a decimal number [00,99].

%Y . Replaced by the year as a decimal number (for example, 1997).

%m . Replaced by the month as a decimal number [01,12].

%M . Replaced by the minute as a decimal number [00,59].

%d . Replaced by the day of the month as a decimal number [01,31].

%H . Replaced by the hour (24-hour clock) as a decimal number [00,23].

%I . Replaced by the hour (12-hour clock) as a decimal number [01,12].

%M . Replaced by the minute as a decimal number [00,59].

%S . Replaced by the second as a decimal number [00,60].

%a . Replaced by the locale's abbreviated weekday name.

%A . Replaced by the locale's full weekday name.

More details about the options are on http://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html.

logging_collector

This parameter defined whether to start a background process to redirect the logs from standard output to log files. ON | OFF or FALSE | TRUE are the possible values.

log_truncate_on_rotation

During the time based rotation, when a log file with the same name is about the created, old files contents will be cleared instead of appending the logs on the existing ones.

log_rotation_age

Determines the maximum lifetime of a log file. After this amount time time elapses the log file rotates to the next file. To disable this feature set the value to 0.

log_rotation_size

Maximum size of a log file. After the size reaches the log file rotates to new file. To disable this feature set the value to 0.

log_checkpoints

To enable the logging of the checkpoint event, set this parameter to the value ON.

log_connections

To enable the logging of the user connections event, set this parameter to the value ON.

log_disconnections

To enable the logging of the user disconnections event, set this parameter to the value ON.

log_line_prefix

Whether to define a line prefix to proceed the log line. Possible options of the parameters are as follows;

%a	Application name
%u	User name
%d	Database name
%r	Remote host name or IP address, and remote port
%h	Remote host name or IP address
%p	Process ID
%t	Time stamp without milliseconds
%m	Time stamp with milliseconds
%n	Time stamp with milliseconds (as a Unix epoch)
%i	Command tag: type of session's current command
%e	SQLSTATE error code
%c	Session ID: see below
%l	Number of the log line for each session or process, starting at 1
%s	Process start time stamp
%v	Virtual transaction ID (backendID/localXID)
%x	Transaction ID (0 if none is assigned)
%q	Produces no output, but tells non-session processes to stop at this point in the string; ignored by session processes
%%	Literal %

Example Setting with stderr

log_destination = stderr logging_collector = on log_directory = pg_log log_filename = 'postgresql_%a.log' log_truncate_on_rotation = on log_rotation_age = 1d log_rotation_size = 10M log_checkpoints = on log_connections = on log_disconnections = on log_line_prefix = 'TIMESTAMP=%t;PROCESS=[%p];SESSION=%c;LINE=[%l-1];USER=%u;DB=%d;HOST=%h;PID=%p;TIME=%m;AAP=%a;ERRORCODE=%c'

Parsing the log file

Parsing the contents of the log file needs more detailed information on the logging behaviour. Within the <ERRORCODE> section of the log file there may be four different values. Which are highly dependent to the settings of the other parameters.

STATEMENT

Depending on the log_min_error_statement parameter. The statement which is causing the error will be logged in statement header.

ERROR

Depends on the log_min_messages parameter.

LOG

If one of the log_duration, log_statement, log_connections and log_disconnections are set in the postgresql.conf file, then LOG heading will include manner of this log entry.

FATAL

Contains the errorcode. Depends on the log_min_messages parameter.

Reference

Error Reporting and LoggingPostgreSQL Documentation