PostgreSQL服务运行日志配置-实现对数据结构定义(如删表)、数据增删改、服务连接建立和销毁等关键操作的监控

1.前言

PostgreSQL服务运行日志记录默认是关闭的，不会记录数据库服务发生的事件和错误信息，在线上数据库出现问题后难以追溯原因及过程。本文通过配置PostgreSQL服务运行日志，实现对数据结构定义(如删表)、数据增删改、服务连接建立和销毁等关键操作的监控，监控内容包括请求的主机及端口、操作用户、操作的数据库名称、请求的应用名称、执行的语句等。PostgreSQL日志相关的配置众多，除文中提到的配置外还有如慢查询记录、检查点记录等，可根据文末提供的官方文档开启相应的日志功能。

实测环境

操作系统：CentOS7
部署方式：docker
数据库版本：PostgreSQL14.9

2.配置postgresql.conf

postgresql.conf配置文件在data目录下，如Linux下的所在目录为/var/lib/postgresql/data

2.1.Where to Log

# 日志输出目的地
# 共有四种：stderr(默认)，csvlog(记入csv文件)，syslog(记入系统日志)，eventlog(依赖于系统平台)。
log_destination = 'stderr'

# 激活日志采集器
# 激活后会启动后台进程，将捕获到的stderr中的信息记录到日志文件中
logging_collector = on

# 日志路径
# logging_collector激活后生效，可以配置为绝对路径或基于/var/lib/postgresql/data的相对路径
log_directory = 'pg_log'

# 日志文件名称
# logging_collector激活后生效，带有时间变量支持"日志滚动"
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# 单一文件不发生滚动的最长时间
# logging_collector激活后生效，不加单位的情况下默认为分钟，配置为0时禁用按时间进行滚动，此处配置含义为1天
log_rotation_age = 1d

# 单一文件不发生滚动的最大文件大小
# logging_collector激活后生效，不加单位的情况下默认为KB，不配置时默认为10MB，配置为0时禁用按文件大小进行滚动。
log_rotation_size = 20MB

2.2.When to Log

# 控制写入日志-信息等级
# 可选项包含：debug5，debug4，debug3，debug2，debug1，info，notice，warning，error，log，fatal，panic
# 默认为WARNING，即包含WARNING和之后的等级可以被记录
log_min_messages = warning

# 控制写入日志-会导致错误的SQL语句
# 可选项包含：debug5，debug4，debug3，debug2，debug1，info，notice，warning，error，log，fatal，panic
# 默认为error，即包含WARNING和之后的等级可以被记录，可设置为panic关闭失败语句记录
log_min_error_statement = error

下面是官网提供的消息严重性级别说明

2.3.What to Log

# 记录服务连接信息
# 默认为关闭
log_connections = on

# 记录会话中断
# 默认为关闭
log_disconnections = on

# 控制写入日志的详细程度
# 可选项包含：terse，default，verbose
# 默认为default，越往后越详细
log_error_verbosity = default

# 记录连接主机名
# 默认为关闭
# 注意：开启可能造成性能损失
log_hostname = on

# 日志行前缀
# 默认为'%m [%p] '，即时间戳+进程ID
# 可选内容包括
#   %a = application name
#   %u = user name
#   %d = database name
#   %r = remote host and port
#   %h = remote host
#   %b = backend type
#   %p = process ID
#   %P = process ID of parallel group leader
#   %t = timestamp without milliseconds
#   %m = timestamp with milliseconds
#   %n = timestamp with milliseconds (as a Unix epoch)
#   %Q = query ID (0 if none or not computed)
#   %i = command tag
#   %e = SQL state
#   %c = session ID
#   %l = session line number
#   %s = session start timestamp
#   %v = virtual transaction ID
#   %x = transaction ID (0 if none)
#   %q = stop here in non-session processes
#   %% = '%'
# 记录时间戳、远程主机及端口、进程ID、数据库用户名、数据库名和调用者的应用名称
log_line_prefix = '%m [%r] [%p] %q%u@%d/%a '

# 控制SQL查询记录
# 可选项包含：none，ddl，mod，all
# 配置为ddl会记录如CREATE，ALTER和DROP数据定义的执行语句。
# 配置为mod，除记录ddl的内容外，还会记录INSERT, UPDATE, DELETE, TRUNCATE等改变数据的执行语句
# 默认值为none
log_statement=mod

配置完成进行数据库服务重启，重启后会在data目录下找到新增的pg_log目录，服务的运行日志会保存在该目录下。

3.参考资料

• https://www.postgresql.org/docs/14/runtime-config-logging.html