PostgreSQL教程FG068-PG 日志参数：log_statement 与 log_min_duration_statement 配置
本文档详细介绍PostgreSQL中log_statement和log_min_duration_statement参数的配置与使用，风哥教程参考PostgreSQL官方文档内容，适合数据库管理员在生产环境中进行日志配置，以便监控和分析数据库活动。
Part01-基础概念与理论知识
1.1 PostgreSQL日志系统概述
PostgreSQL的日志系统用于记录数据库的各种活动，包括错误信息、警告、查询执行情况等。通过配置不同的日志参数，可以控制日志的详细程度和格式，以便于监控和分析数据库运行状态。更多视频教程www.fgedu.net.cn
- 故障排查：记录错误和警告信息，帮助排查故障
- 性能分析：记录慢查询，帮助分析性能问题
- 安全审计：记录用户操作，帮助审计安全事件
- 合规要求：满足某些行业的合规要求
1.2 log_statement 参数概述
log_statement参数用于控制哪些SQL语句会被记录到日志中。该参数的可选值包括：
- none：不记录任何SQL语句
- ddl：记录所有数据定义语言（DDL）语句，如CREATE、ALTER、DROP等
- mod：记录所有DDL语句和数据修改语言（DML）语句，如INSERT、UPDATE、DELETE等
- all：记录所有SQL语句，包括SELECT语句
1.3 log_min_duration_statement 参数概述
log_min_duration_statement参数用于控制记录执行时间超过指定毫秒数的SQL语句。当SQL语句的执行时间超过该值时，会被记录到日志中。该参数的单位是毫秒，默认值为-1（不记录任何语句）。
Part02-生产环境规划与建议
2.1 日志配置最佳实践
— 1. log_statement 配置建议
— 风哥教程针对生产环境建议：
— – 一般生产环境：设置为 ‘ddl’，记录所有DDL语句
— – 安全敏感环境：设置为 ‘mod’，记录DDL和DML语句
— – 调试环境：设置为 ‘all’，记录所有SQL语句
— 2. log_min_duration_statement 配置建议
— 风哥教程针对生产环境建议：
— – 一般生产环境：设置为 500-1000 毫秒，记录执行时间超过半秒的语句
— – 性能敏感环境：设置为 200-500 毫秒，记录执行时间超过200毫秒的语句
— – 调试环境：设置为 0，记录所有语句的执行时间
— 3. 其他相关参数配置
— 日志格式
log_line_prefix = ‘%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ‘
— 日志文件
log_directory = ‘log’
log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log’
log_rotation_age = 1d
log_rotation_size = 100MB
— 日志级别
log_error_verbosity = ‘default’
log_min_messages = ‘warning’
— 慢查询分析
log_explain_threshold = 1000
log_parameter_max_length = 1024
log_parameter_max_length_on_error = 1024
2.2 性能考虑
日志配置的性能考虑：
- 日志详细程度：过度详细的日志会增加I/O开销，影响数据库性能
- 日志存储：大量日志会占用存储空间，需要定期清理或归档
- 日志同步：同步写入日志会影响性能，建议使用异步写入
- 日志格式：选择合适的日志格式，便于分析但不过度详细
2.3 安全考虑
日志配置的安全考虑：
- 敏感信息：日志中可能包含敏感信息，如密码、个人数据等
- 访问控制：确保日志文件的访问权限适当，防止未授权访问
- 加密：考虑对敏感日志进行加密存储
- 审计：确保日志记录足够的信息用于安全审计
Part03-生产环境项目实施方案
3.1 log_statement 配置方法
— 1. 修改postgresql.conf文件
$ vi /postgresql/fgdata/postgresql.conf
— 设置log_statement
log_statement = ‘ddl’ — 记录所有DDL语句
— 2. 重新加载配置
$ systemctl reload postgresql-18
— 3. 验证配置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW log_statement;”
— 输出：
— log_statement
— —————
— ddl
— (1 row)
— 4. 测试日志记录
— 执行一个DDL语句
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb
fgedudb=# CREATE TABLE fgedu_test_log (id SERIAL PRIMARY KEY, name VARCHAR(50));
— 查看日志
$ tail -f /postgresql/fgdata/log/postgresql-2026-04-07_120000.log
— 日志输出示例：
— 2026-04-07 12:34:56 UTC [12345]: [1-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: statement: CREATE TABLE fgedu_test_log (id SERIAL PRIMARY KEY, name VARCHAR(50));
— 执行一个DML语句（不会被记录，因为log_statement设置为’ddl’）
fgedudb=# INSERT INTO fgedu_test_log (name) VALUES (‘test’);
— 查看日志（不会有DML语句的记录）
$ tail -f /postgresql/fgdata/log/postgresql-2026-04-07_120000.log
3.2 log_min_duration_statement 配置方法
— 1. 修改postgresql.conf文件
$ vi /postgresql/fgdata/postgresql.conf
— 设置log_min_duration_statement
log_min_duration_statement = 500 — 记录执行时间超过500毫秒的语句
— 2. 重新加载配置
$ systemctl reload postgresql-18
— 3. 验证配置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW log_min_duration_statement;”
— 输出：
— log_min_duration_statement
— —————————-
— 500ms
— (1 row)
— 4. 测试日志记录
— 执行一个慢查询
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb
fgedudb=# SELECT pg_sleep(1); — 睡眠1秒，超过500毫秒
— 查看日志
$ tail -f /postgresql/fgdata/log/postgresql-2026-04-07_120000.log
— 日志输出示例：
— 2026-04-07 12:34:56 UTC [12345]: [1-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: duration: 1001.234 ms statement: SELECT pg_sleep(1);
— 执行一个快速查询（不会被记录）
fgedudb=# SELECT 1;
— 查看日志（不会有快速查询的记录）
$ tail -f /postgresql/fgdata/log/postgresql-2026-04-07_120000.log
3.3 日志格式配置
— 1. 修改postgresql.conf文件
$ vi /postgresql/fgdata/postgresql.conf
— 设置日志格式
log_line_prefix = ‘%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ‘
— 设置日志文件
log_directory = ‘log’
log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log’
log_rotation_age = 1d
log_rotation_size = 100MB
— 设置日志级别
log_error_verbosity = ‘default’
log_min_messages = ‘warning’
— 设置慢查询分析
log_explain_threshold = 1000
log_parameter_max_length = 1024
log_parameter_max_length_on_error = 1024
— 2. 重新加载配置
$ systemctl reload postgresql-18
— 3. 验证配置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW log_line_prefix;”
— 输出：
— log_line_prefix
— ———————————–
— %t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h
— (1 row)
— 4. 查看日志格式
$ tail -f /postgresql/fgdata/log/postgresql-2026-04-07_120000.log
— 日志输出示例：
— 2026-04-07 12:34:56 UTC [12345]: [1-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: duration: 1001.234 ms statement: SELECT pg_sleep(1);
Part04-生产案例与实战讲解
4.1 log_statement 配置案例
— 场景：需要审计所有数据修改操作
— 配置步骤：
— 1. 修改postgresql.conf文件
$ vi /postgresql/fgdata/postgresql.conf
— 设置log_statement为’mod’，记录DDL和DML语句
log_statement = ‘mod’
— 2. 重新加载配置
$ systemctl reload postgresql-18
— 3. 验证配置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW log_statement;”
— 4. 测试日志记录
— 执行DDL语句
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “CREATE TABLE fgedu_audit_test (id SERIAL PRIMARY KEY, data VARCHAR(100));”
— 执行DML语句
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “INSERT INTO fgedu_audit_test (data) VALUES (‘test data’);”
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “UPDATE fgedu_audit_test SET data = ‘updated data’ WHERE id = 1;”
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “DELETE FROM fgedu_audit_test WHERE id = 1;”
— 执行SELECT语句（不会被记录）
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT * FROM fgedu_audit_test;”
— 5. 查看日志
$ tail -f /postgresql/fgdata/log/postgresql-2026-04-07_120000.log
— 日志输出示例：
— 2026-04-07 12:34:56 UTC [12345]: [1-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: statement: CREATE TABLE fgedu_audit_test (id SERIAL PRIMARY KEY, data VARCHAR(100));
— 2026-04-07 12:34:57 UTC [12345]: [2-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: statement: INSERT INTO fgedu_audit_test (data) VALUES (‘test data’);
— 2026-04-07 12:34:58 UTC [12345]: [3-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: statement: UPDATE fgedu_audit_test SET data = ‘updated data’ WHERE id = 1;
— 2026-04-07 12:34:59 UTC [12345]: [4-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: statement: DELETE FROM fgedu_audit_test WHERE id = 1;
4.2 log_min_duration_statement 配置案例
— 场景：需要监控执行时间超过1秒的慢查询
— 配置步骤：
— 1. 修改postgresql.conf文件
$ vi /postgresql/fgdata/postgresql.conf
— 设置log_min_duration_statement为1000毫秒
log_min_duration_statement = 1000
— 2. 重新加载配置
$ systemctl reload postgresql-18
— 3. 验证配置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW log_min_duration_statement;”
— 4. 创建测试表并插入数据
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb
fgedudb=# CREATE TABLE fgedu_performance_test (id SERIAL PRIMARY KEY, data VARCHAR(100));
fgedudb=# INSERT INTO fgedu_performance_test (data) SELECT ‘test data ‘ || generate_series(1, 1000000);
— 5. 测试慢查询
— 执行一个慢查询（全表扫描）
fgedudb=# SELECT * FROM fgedu_performance_test WHERE data LIKE ‘%test%’;
— 执行一个快速查询（使用索引）
fgedudb=# CREATE INDEX idx_fgedu_performance_test_data ON fgedu_performance_test(data);
fgedudb=# SELECT * FROM fgedu_performance_test WHERE id = 500000;
— 6. 查看日志
$ tail -f /postgresql/fgdata/log/postgresql-2026-04-07_120000.log
— 日志输出示例：
— 2026-04-07 12:34:56 UTC [12345]: [1-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: duration: 2500.123 ms statement: SELECT * FROM fgedu_performance_test WHERE data LIKE ‘%test%’;
— （快速查询不会被记录）
4.3 日志分析案例
— 场景：分析数据库中的慢查询，找出性能瓶颈
— 分析步骤：
— 1. 配置日志参数
$ vi /postgresql/fgdata/postgresql.conf
— 设置log_min_duration_statement为200毫秒
log_min_duration_statement = 200
— 设置详细的日志格式
log_line_prefix = ‘%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ‘
— 2. 重新加载配置
$ systemctl reload postgresql-18
— 3. 收集日志数据
— 运行应用程序，产生实际负载
— 4. 分析日志
— 使用pgBadger分析日志
$ pgbadger -o slow_queries_report.html /postgresql/fgdata/log/postgresql-2026-04-07_*.log
— 5. 手动分析慢查询
— 提取慢查询
$ grep “duration:” /postgresql/fgdata/log/postgresql-2026-04-07_*.log | sort -rn -k 3 | head -20
— 示例输出：
— 2026-04-07 12:34:56 UTC [12345]: [1-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.101 LOG: duration: 5000.123 ms statement: SELECT * FROM fgedu_large_table WHERE unindexed_column = ‘value’;
— 2026-04-07 12:35:00 UTC [12346]: [1-1] user=fgedu,db=fgedudb,app=psql,client=192.168.1.102 LOG: duration: 3500.456 ms statement: SELECT * FROM fgedu_another_table WHERE date_column < '2026-01-01';
-- 6. 优化慢查询
-- 为unindexed_column创建索引
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c "CREATE INDEX idx_fgedu_large_table_unindexed ON fgedu_large_table(unindexed_column);"
-- 为date_column创建索引
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c "CREATE INDEX idx_fgedu_another_table_date ON fgedu_another_table(date_column);"
-- 7. 验证优化效果
-- 再次执行相同的查询，查看执行时间
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c "EXPLAIN ANALYZE SELECT * FROM fgedu_large_table WHERE unindexed_column = 'value';"
-- 查看日志，确认查询执行时间是否减少
$ tail -f /postgresql/fgdata/log/postgresql-2026-04-07_120000.log
Part05-风哥经验总结与分享
5.1 日志配置技巧
日志配置技巧：
- 根据环境调整日志级别：
- 生产环境：使用较保守的日志级别，如只记录DDL和慢查询
- 测试环境：可以使用更详细的日志级别，如记录所有语句
- 开发环境：使用最详细的日志级别，便于调试
- 合理设置log_min_duration_statement：
- 根据应用的响应时间要求设置阈值
- 对于OLTP系统，阈值可以设置得较低（如200-500毫秒）
- 对于OLAP系统，阈值可以设置得较高（如1000-2000毫秒）
- 优化日志格式：
- 包含足够的上下文信息，如用户、数据库、应用、客户端IP等
- 使用一致的日志格式，便于自动化分析
- 避免包含敏感信息，如密码
5.2 日志问题排查
— 1. 日志不记录问题
— 症状：
— – 预期的语句没有被记录到日志中
— 排查步骤：
— – 检查log_statement设置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW log_statement;”
— – 检查log_min_duration_statement设置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW log_min_duration_statement;”
— – 检查日志级别
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW log_min_messages;”
— – 检查日志目录权限
$ ls -la /postgresql/fgdata/log/
— 2. 日志过大问题
— 症状：
— – 日志文件过大，占用大量存储空间
— 排查步骤：
— – 检查log_statement设置，是否设置为’all’
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW log_statement;”
— – 检查log_min_duration_statement设置，是否设置为0
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW log_min_duration_statement;”
— – 检查日志轮转设置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW log_rotation_age, log_rotation_size;”
— 3. 日志性能问题
— 症状：
— – 数据库性能下降，与日志写入有关
— 排查步骤：
— – 检查日志同步设置
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW synchronous_commit;”
— – 检查日志详细程度
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SHOW log_statement, log_min_duration_statement;”
— – 检查日志存储位置，是否使用SSD
$ df -h /postgresql/fgdata/log/
5.3 日志管理最佳实践
— 1. 日志轮转与归档
— – 配置合适的日志轮转策略
log_rotation_age = 1d
log_rotation_size = 100MB
— – 实现日志归档
— 创建归档脚本
$ vi /postgresql/scripts/archive_logs.sh
#!/bin/bash
# archive_logs.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
LOG_DIR=”/postgresql/fgdata/log”
ARCHIVE_DIR=”/backup/logs/$(date +%Y-%m-%d)”
mkdir -p “$ARCHIVE_DIR”
find “$LOG_DIR” -name “postgresql-*.log” -mtime +7 -exec mv {} “$ARCHIVE_DIR” \;
— 设置定时任务
$ crontab -e
0 0 * * * /postgresql/scripts/archive_logs.sh
— 2. 日志分析
— – 使用pgBadger分析日志
$ pgbadger -o /var/www/html/pg_stats.html /postgresql/fgdata/log/postgresql-*.log
— – 定期生成性能报告
$ vi /postgresql/scripts/generate_report.sh
#!/bin/bash
# generate_report.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
REPORT_DIR=”/var/www/html/reports/$(date +%Y-%m-%d)”
mkdir -p “$REPORT_DIR”
pgbadger -o “$REPORT_DIR/pg_stats.html” /postgresql/fgdata/log/postgresql-*.log
— 设置定时任务
$ crontab -e
0 1 * * * /postgresql/scripts/generate_report.sh
— 3. 日志安全
— – 设置日志文件权限
$ chmod 600 /postgresql/fgdata/log/*.log
— – 定期清理敏感信息
— 创建清理脚本
$ vi /postgresql/scripts/clean_logs.sh
#!/bin/bash
# clean_logs.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
LOG_DIR=”/postgresql/fgdata/log”
# 清理密码等敏感信息
sed -i ‘s/password=[^ ]*/password=******/g’ “$LOG_DIR”/*.log
— 设置定时任务
$ crontab -e
0 2 * * * /postgresql/scripts/clean_logs.sh
— 4. 监控日志
— – 使用Prometheus和Grafana监控日志
— 配置Prometheus收集PostgreSQL日志指标
— 配置Grafana仪表板显示慢查询统计
— – 设置日志告警
— 创建告警脚本
$ vi /postgresql/scripts/log_alert.sh
#!/bin/bash
# log_alert.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
LOG_FILE=”/postgresql/fgdata/log/postgresql-$(date +%Y-%m-%d)_*.log”
# 检查错误信息
ERROR_COUNT=$(grep -c “ERROR:” “$LOG_FILE”)
if [ “$ERROR_COUNT” -gt 10 ]; then
echo “PostgreSQL错误数量过多：$ERROR_COUNT” | mail -s “PostgreSQL日志告警” admin@fgedu.net.cn
fi
# 检查慢查询
SLOW_QUERY_COUNT=$(grep -c “duration: [0-9]*\.[0-9]* ms” “$LOG_FILE” | grep -v “duration: 0\.”)
if [ “$SLOW_QUERY_COUNT” -gt 50 ]; then
echo “慢查询数量过多：$SLOW_QUERY_COUNT” | mail -s “PostgreSQL日志告警” admin@fgedu.net.cn
fi
— 设置定时任务
$ crontab -e
*/15 * * * * /postgresql/scripts/log_alert.sh
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
