PostgreSQL配置log_min_duration_statement时如何将慢查询输出到单独日志文件？

原生PostgreSQL没有内置直接将慢查询输出到独立日志文件的功能，可通过以下两种主流方案实现需求

方案1：配合syslog实现实时日志分流 #

该方案可以实现慢查询产生时直接写入独立日志文件，不会和普通PG日志混合。
操作步骤如下：

• 修改PostgreSQL配置文件postgresql.conf，调整以下参数：

# 调整日志输出目的地为syslog
log_destination = 'syslog'
# 配置syslog设施，建议使用未被占用的local0
syslog_facility = 'local0'
# 配置PG日志的syslog标识，方便后续过滤
syslog_ident = 'postgres'
# 配置慢查询阈值，单位为毫秒，示例为超过1秒的查询记录日志
log_min_duration_statement = 1000

• 新增rsyslog规则，创建/etc/rsyslog.d/50-postgres-slow.conf，写入以下内容：

# 匹配postgres产生的、包含duration关键字的慢查询日志
if $programname == 'postgres' and $msg contains 'duration:' then /var/log/postgresql/slowlog/slowlog.log
# 匹配到的慢查询日志写入目标文件后不再向下传递，避免写入主syslog
& stop

• 创建慢查询日志目录并配置权限：

mkdir -p /var/log/postgresql/slowlog
chown postgres:postgres /var/log/postgresql/slowlog
chmod 700 /var/log/postgresql/slowlog

• 依次重启rsyslog服务和PostgreSQL服务生效。如果需要按日期自动切割日志，可以新增logrotate规则配置自动切割、过期自动删除。

方案2：事后从主PG日志中拆分慢查询 #

如果不想改动系统syslog配置，可以选择先将所有PG日志写入统一文件，之后通过工具过滤拆分出慢查询。
操作步骤如下：

• 修改postgresql.conf配置，开启CSV格式日志输出，方便后续解析过滤：

log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
# 慢查询阈值配置同方案1
log_min_duration_statement = 1000

• 可以通过awk命令直接过滤出慢查询写入独立文件，示例命令如下：

awk -F ',' '$13 > 1000' /var/lib/postgresql/15/main/pg_log/postgresql-*.csv > /var/log/postgresql/slowlog/slowlog-$(date +%Y%m%d).log

注意：不同PostgreSQL版本的csv日志字段顺序可能存在差异，需要对应自己的版本调整$13的字段序号

注意事项 #

• 生产环境不建议将log_min_duration_statement设置为0或者过低的数值，避免产生大量日志拖慢数据库性能
• 两种方案都建议配置logrotate规则，避免日志文件占满磁盘空间
• 慢查询日志目录必须保证postgres用户有读写权限，否则会出现日志写入失败的问题

内容的提问来源于stack exchange，提问作者Paweł