【赵渝强老师】PostgreSQL的运行日志文件

PostgreSQL数据库的物理存储结构主要是指硬盘上存储的文件，包括：数据文件、日志文件、参数文件、控制文件、WAL预写日志文件等等。而PostgreSQL的日志文件又分为运行日志、WAL预写日志、事务日志和服务器日志。

下面重点讨论一下PostgreSQL的运行日志文件。视频讲解如下：
https://www.bilibili.com/video/BV1cBWoemEr8/?aid=113014833480...

在默认的情况下，运行日志没有开启。通过查看主postgresql.conf文件的配置可以看到相关的参数设置，开启后会自动生成该日志文件。运行时日志一般是记录数据库服务器与数据库的状态，比如各种错误信息、定位慢查询SQL、数据库的启动关闭信息、发生检查点过于频繁等的告警信息等等。该日志有.csv格式和.log格式，建议使用.csv格式。因为.csv格式一般会按大小和时间自动切割。pg_log是可以被清理删除、压缩打包或者转移，同时不影响数据库的正常运行。当有遇到数据库无法启动或者更改参数没有生效时，第一步就可以查看运行时日志。下面展示了主参数文件postgresql.conf中关于运行日志的配置参数。

#log_destination = 'stderr'             # Valid values are combinations of
                                        # stderr, csvlog, jsonlog, syslog, and
                                        # eventlog, depending on platform.
                                        # csvlog and jsonlog require
                                        # logging_collector to be on.

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

# These are only used if logging_collector is on:
#log_directory = 'log'                  # directory where log files are written,
                                        # can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        # log file name pattern,
                                        # can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
#log_rotation_age = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
#log_rotation_size = 10MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.
#log_truncate_on_rotation = off         # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.

一个推荐的PostgreSQL运行日志的参数配置如下：

logging_collector = on
log_destination = 'csvlog'
log_directory = 'logs'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.csv'
log_truncate_on_rotation = on
log_connections = on
log_disconnections = on
log_statement = ddl
log_min_duration_statement = 60s
log_checkpoints = on
log_lock_waits = on
deadlock_timeout = 1s

这里的参数设置还将死锁的超时时间设置为了1秒钟。当重启PostgreSQL完成后，尝试创建一张简单的表，例如：

postgres=# \c scott 
scott=# create table testaudit as select * from emp;

查看logs目录下生成的日志文件，如下：

[postgres@mydb logs]$ pwd
/home/postgres/training/pgsql/data/logs
[postgres@mydb logs]$ ll
total 8
... postgresql-2024-10-06_084043.csv
... postgresql-2024-10-06_084043.csv.csv

查看日志文件postgresql-2024-10-06_084043.csv.csv的内容，将得到如下的日志信息：

...
2024-10-06 08:41:31.165 CST,"postgres","scott",87330,"[local]",
6455a22f.15522,3,"idle",2024-10-06 08:41:19 CST,4/4,0,LOG,00000,
"statement: create table testaudit as select * from emp;",,,,,,,,,
"psql","client backend"
...