Examining the Log File

| 
logging_collector = on

log_destination = stderr

log_directory = log

# To enable these settings, a restart is required.

# To truncate the log file.

postgres@srv1:~$ > /pg/data/13.6/log/postgresql-Mon.log

# Example

log_filename = ‘postgresql-%a.log’

or

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
  • Each database has its own log file. Events in the database are written to this log file. By default, it writes to the /var/log/posgresql/ directory. Example; /var/log/postgresql/postgresql-13-main.log
  • Detailed logging can be done by changing the logging level.
  • Logs can be redirected to $PGDATA with the log_directory parameter.
  • Database starting and stopping information.
  • Recovery processes.
  • Failed access attempts.
  • Replication information.
  • Syntax errors.
  • It contains information such as resource bottlenecks and IPs used.
  • log_min_messages changes logging level (requires restart). The default logging level is warning.
  • DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, PANIC
  • Debug options. Sorted from detailed to non-detailed.
  • When DEBUG levels are increased, the log file can grow very quickly. It should be changed immediately after the analyzes are finished.
  • If there is no space left on the disk, the database will stop because it cannot write to the WAL files. In such a case, you can free up space by truncating the log file. If escape (%) is not used, log rotation should be used so that the log file does not become too large. (Enabled when logging_collector = on. )

log_destination supports stderr, syslog and csv methods. Logs in CSV format can
be imported into the analysis software

log_rotation_age determines the maximum time the log file will be written. When the
time is up, it creates a new file. If it is set without a unit, it is set for minutes.
(Enabled when logging_collector = on.)
Example: log_rotation_age = 1d

With the log_line_prefix parameter, the information that each line will contain will be
determined. For details, the postgresql.conf file can be examined.

log_min_duration_statement logs SQLs that take longer than the time given in the
parameter.
client_min_messages the details of the message to be sent to the clients are
determined.
debug5, debug4, debug3, debug2, debug1, log, notice,
warning, error These are available levels. The default setting is notice.

Note:
1- If logs are directed to syslog, dynamic-linker, error messages in archive_command
may not appear.
2- The logging collector is designed not to miss any logs. On overloaded systems,
the server may block processes if it lags behind while trying to send additional log
messages. In such a case, the system gives priority to the operation of the system by
ignoring the log writing.

log_statement controls which SQL statements are logged. The default value is none. It
can take DDL, MOD and ALL values.
DDL: ddl statements
MOD: ddl and insert/update/delete/truncate/copy from/prepare/execute/explain
analyze sentences,
ALL: Logs all SQL statements.