14.PostgreSQL日志配置：从基础到高级监控实战

1. PostgreSQL日志系统基础配置

PostgreSQL的日志系统就像数据库的"黑匣子"，记录着数据库运行的每一个关键动作。作为DBA，合理配置日志是我们排查问题的第一道防线。先来看看几个核心参数：

# 启用日志收集器（需重启生效） logging_collector = on # 日志存放目录（默认$PGDATA/pg_log） log_directory = 'pg_log' # 日志文件名格式（支持strftime时间格式） log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # 单个日志文件最大存活时间 log_rotation_age = 1d # 单个日志文件最大尺寸 log_rotation_size = 100MB

logging_collector是总开关，必须设为on才能启用日志文件记录。我遇到过不少新手DBA忘记开这个参数，结果所有日志都输出到stderr，重启后日志就消失了。

log_rotation_age和log_rotation_size共同控制日志轮转。建议生产环境设置为100MB-1GB，避免单个文件过大。有个坑要注意：如果同时设置这两个参数，只要满足任一条件就会触发日志轮转。

2. 精细化日志记录策略

2.1 连接追踪配置

# 记录客户端连接事件 log_connections = on # 记录客户端断开事件 log_disconnections = on # 日志行前缀格式（记录时间、用户、数据库等信息） log_line_prefix = '%m [%p] %u@%d %r '

开启连接日志后，每条日志会显示：

2024-03-20 15:30:45 CST [12345] postgres@mydb 192.168.1.100(55678)

log_line_prefix的配置很有讲究：

• %m带毫秒的时间戳
• %p进程ID
• %u用户名
• %d数据库名
• %r客户端IP和端口

我曾经用这个功能追踪到某个应用连接泄漏的问题——发现大量来自同一IP的短连接，最终定位到连接池配置错误。

2.2 SQL语句记录策略

# 记录慢查询（单位：毫秒） log_min_duration_statement = 500 # 记录特定类型SQL log_statement = 'ddl' # 记录所有SQL耗时 log_duration = off

log_statement有四个级别：

• none：不记录（默认）
• ddl：记录CREATE/ALTER/DROP
• mod：记录DDL+INSERT/UPDATE/DELETE
• all：记录所有SQL（慎用！）

重要提示：生产环境不要设log_statement=all！我曾见过因此导致日志暴涨把磁盘撑满的案例。建议配合log_min_duration_statement只记录慢查询。

3. 高级监控配置技巧

3.1 锁等待监控

# 记录超过死锁超时的锁等待 log_lock_waits = on # 死锁检测超时时间（默认1s） deadlock_timeout = 1s

这个组合能帮我们发现潜在的死锁问题。当某个事务等待锁超过1秒时，日志会记录类似：

LOG: process 12345 still waiting for ShareLock on transaction 678 after 1000.123 ms

3.2 自动清理日志

# 自动清理超过7天的日志 log_retention_age = 7d # 日志目录最大占用1GB log_retention_size = 1GB

这两个参数需要PostgreSQL 12+版本支持。对于旧版本，可以通过crontab定期清理：

# 每天凌晨清理7天前日志 0 0 * * * find /var/lib/pgsql/12/data/pg_log -name "*.log" -mtime +7 -delete

4. 日志分析与可视化

4.1 使用pgBadger分析日志

pgBadger是PostgreSQL官方推荐的日志分析工具，安装使用很简单：

# 安装 sudo apt-get install pgbadger # 分析日志（生成HTML报告） pgbadger /var/lib/pgsql/12/data/pg_log/postgresql-*.log -o report.html

报告包含：

• 查询统计（最频繁/最慢查询）
• 会话统计（连接数峰值）
• 锁等待分析
• 错误分类统计

4.2 集成Prometheus监控

通过postgres_exporter可以采集日志相关指标：

# prometheus.yml配置示例 scrape_configs: - job_name: 'postgres' static_configs: - targets: ['localhost:9187']

关键监控指标：

• pg_log_errors_total错误日志计数
• pg_log_lock_waits_total锁等待次数
• pg_log_slow_queries_total慢查询计数

5. 生产环境最佳实践

经过多个生产环境的验证，推荐以下配置组合：

# 基础配置 logging_collector = on log_directory = '/var/log/postgresql' log_filename = 'postgresql-%Y-%m-%d.log' log_rotation_age = 1d log_rotation_size = 500MB # 安全审计 log_connections = on log_disconnections = on log_hostname = on # 性能监控 log_min_duration_statement = 200 log_lock_waits = on # 格式优化 log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' log_timezone = 'Asia/Shanghai'

避坑指南：

1. 日志目录要单独挂载分区，避免占满数据盘
2. CSV格式日志更利于分析，但会多占用30%空间
3. 高频UPDATE/DELETE业务建议设log_statement=ddl
4. 连接数超过500时，关闭log_connections避免性能影响

最后分享一个真实案例：某次大促期间，我们通过日志发现大量idle in transaction连接，结合log_lock_waits定位到未提交的事务阻塞了关键表更新。添加事务超时参数后问题解决：

idle_in_transaction_session_timeout = 10s