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 100MBlogging_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] postgresmydb 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 offlog_statement有四个级别none不记录默认ddl记录CREATE/ALTER/DROPmod记录DDLINSERT/UPDATE/DELETEall记录所有SQL慎用重要提示生产环境不要设log_statementall我曾见过因此导致日志暴涨把磁盘撑满的案例。建议配合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 ms3.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 -delete4. 日志分析与可视化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避坑指南日志目录要单独挂载分区避免占满数据盘CSV格式日志更利于分析但会多占用30%空间高频UPDATE/DELETE业务建议设log_statementddl连接数超过500时关闭log_connections避免性能影响最后分享一个真实案例某次大促期间我们通过日志发现大量idle in transaction连接结合log_lock_waits定位到未提交的事务阻塞了关键表更新。添加事务超时参数后问题解决idle_in_transaction_session_timeout 10s