Debian下PostgreSQL日志管理实用技巧
一 快速定位与查看日志
tail -f /var/log/postgresql/postgresql-12-main.log;按关键字过滤可用:grep ERROR /var/log/postgresql/postgresql-12-main.log。以上路径与命令适用于Debian环境。二 核心配置与生效方式
logging_collector = on:开启日志收集器(文件日志)log_directory = 'pg_log':日志目录(相对数据目录,Debian常见为/var/log/postgresql)log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log':按时间命名,便于轮转与检索log_statement = 'mod':记录DDL与数据修改;可选值:none/ddl/mod/alllog_min_duration_statement = 1000:记录执行时间超过1000 ms的慢SQLlog_checkpoints = on、log_connections = on、log_disconnections = on、log_lock_waits = on:记录检查点、连接/断开、锁等待等关键信息log_destination = 'csvlog':CSV格式便于解析与导入分析工具log_line_prefix = '%m %p %u %d %r':前缀含时间、进程、用户、数据库、客户端地址,便于过滤systemctl restart postgresql(或service postgresql restart)使配置生效。三 日志轮转与清理
log_rotation_age = 1d:单个日志保留1天log_rotation_size = 10MB:达到10MB即轮转log_truncate_on_rotation = on:同名文件轮转时覆盖而非追加/var/log/postgresql/*.log {
daily
rotate 7
compress
missingok
notifempty
create 0644 postgres postgres
sharedscripts
postrotate
systemctl reload postgresql >/dev/null 2>&1 || true
endscript
}
postrotate中执行reload可确保文件句柄正常切换。四 日志分析与可视化
pgbadger /var/log/postgresql/*.log -o /var/www/html/pg-report.htmlpgbadger --begin='2025-04-20 00:00:00' --end='2025-04-24 23:59:59' *.log0 2 * * * /usr/bin/pgbadger /var/log/postgresql/postgresql-$(date -d "yesterday" +"%Y-%m-%d").log -o /var/www/html/report.html五 故障排查与运行时诊断
SELECT pid, usename, application_name, client_addr, query_start, state, query FROM pg_stat_activity;EXPLAIN SELECT ...; 识别全表扫描、缺少索引等问题postgresql.conf中设置shared_preload_libraries = 'pg_stat_statements',重启后创建扩展并查询:SELECT * FROM pg_stat_statements;grep -E 'log_filename|log_directory' /etc/postgresql/{version}/main/postgresql.conf