Postgresql log configuration tutorial detailed explanation

background

Used in the company's projectspostgresql(Abbreviatedpg) As its database management system, the environment suddenly crashed two days ago and the page could not be opened. After troubleshooting, I found that the disk of the machine where the database is located is full. Sort by directory and file sorting, it turns out that there are too many logs in PG (the logs that have been kept for about half a year have not been cleaned on the disk).

I looked at the log configuration of pg and found that they were basically used by default configurations, and the log scrolling was not enabled. So I did the relevant configuration optimization and restarted pg. Finally, I looked at the log scrolling of pg and returned to normal. The following are the log configuration items I have sorted out about pg.

Configuration details

Configuration file:

Configuration 1: Log on and off

The default is off. If the setting is on, then pg can record related logs. It is recommended to open it. Otherwise, when an exception occurs in the database, there will be no logs to locate specific problems.

# This is used when logging to stderr:
logging_collector =on# Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)

Configuration 2: Log scrolling policy

# These are only used if logging_collector is on:

#Configure the log directory, the default is pg_log
log_directory = 'pg_log' # directory where log files are written,

# can be absolute or relative to PGDATA

#pg log file name and its extension, by default
log_filename = 'postgresql-%Y-%m-%d_%H%M%' # log file name pattern,

# can include strftime() escapes

#pg log file permissions, default
log_file_mode = 0600 # creation mode for log files,

#Enable the log scrolling stage, it needs to be set to on
log_truncate_on_rotation =on# If on, an existing log file with the

#Login retention days, here we look at the actual environment. If it is a test, it is recommended to 1d, and if it is a production environment, it is recommended to 7d
log_rotation_age = 1d # Automatic rotation of logfiles will

#Single log size, default 100MB, relatively standard configuration

Configuration 3: Log printing timing

# The message level sent to the client is recommended to warning. The lower the log level, the more content is printed, and the more loss in performanceclient_min_messages = warning # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
#Write to the level of messages in the database log file, it is recommended to warn. The lower the log level, the more content you print, and the more loss in performancelog_min_messages = warning # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
# Whether to record SQL statements that cause errors in the database, it is recommended to just warn. The lower the log level, the more content you print, and the more loss in performancelog_min_error_statement = error # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)

Configuration 4: Database statistics monitoring

If #log_statement_stats is on, log_parser_stats, log_planner_stats, log_executor_stats will be enabled. It is not recommended to enable the production environment. It is recommended to enable the test environment to be used to locate problems.#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off

Configuration 5: Slow SQL record configuration

#Sql with sql execution time of more than 2 seconds will be recordedlog_min_duration_statement = 2s

After the above configuration is modified, all pg needs to be restarted to take effect.

This is the end of this article about Postgresql log configuration. For more related Postgresql log configuration content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!