> For the complete documentation index, see [llms.txt](https://ergemp.gitbook.io/data-blog/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://ergemp.gitbook.io/data-blog/postgresql/postgresql-logging.md). # 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 . ### 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 \ 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 {% embed url="" %}