PostgreSQL日志

PostgreSQL日志系统是数据库管理和故障排查的重要工具。通过合理配置和分析日志，可以监控数据库运行状态、诊断问题、优化性能并确保安全性。PostgreSQL提供了丰富的日志选项，可以记录从基本错误信息到详细查询执行计划的各种信息。本章将详细介绍PostgreSQL日志系统的配置、管理和分析方法。

PostgreSQL日志系统概述

PostgreSQL日志系统提供了多种类型的日志，用于记录数据库的不同方面信息。

日志类型

错误日志（Error Log）：记录数据库错误、警告和通知信息
查询日志（Query Log）：记录执行的SQL语句
连接日志（Connection Log）：记录客户端连接和断开信息
锁日志（Lock Log）：记录锁等待和死锁信息
自动清理日志（Autovacuum Log）：记录自动清理操作
检查点日志（Checkpoint Log）：记录检查点操作
WAL日志（Write-Ahead Log）：记录数据修改操作

日志级别从低到高：

级别	说明
DEBUG	调试信息，用于开发和故障排查
INFO	一般信息，记录正常操作
NOTICE	需要注意的信息
WARNING	警告信息，可能存在问题
ERROR	错误信息，操作失败
LOG	管理日志信息
FATAL	致命错误，导致会话终止
PANIC	系统恐慌，导致所有会话终止

基本日志配置

通过postgresql.conf文件配置日志系统的基本参数。

日志目的地配置

# postgresql.conf 日志配置示例

# 日志输出目的地
log_destination = 'stderr'          # 可选: stderr, csvlog, syslog, eventlog
logging_collector = on              # 启用日志收集器
log_directory = 'log'               # 日志目录
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  # 日志文件名模式
log_file_mode = 0600                # 日志文件权限
log_truncate_on_rotation = off      # 日志轮转时是否截断
log_rotation_age = 1d               # 日志轮转时间间隔
log_rotation_size = 100MB           # 日志轮转文件大小

# CSV日志配置
log_destination = 'csvlog'
log_statement = 'all'
log_min_duration_statement = 0

错误日志配置

# 错误报告配置
log_min_messages = warning          # 记录的最低消息级别
log_min_error_statement = error     # 记录错误SQL语句的最低级别
log_error_verbosity = default       # 错误详细程度 (terse, default, verbose)
log_stack_trace = on                # 是否记录堆栈跟踪

# 客户端连接日志
log_connections = on                 # 记录连接
log_disconnections = on             # 记录断开连接
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '  # 日志行前缀

# 锁相关日志
log_lock_waits = on                 # 记录锁等待
deadlock_timeout = 1s               # 死锁检测超时时间

# 自动清理日志
log_autovacuum_min_duration = 0     # 记录所有自动清理操作

查询日志配置

查询日志可以记录执行的SQL语句，用于性能分析和审计。

SQL语句日志

# SQL语句日志配置
log_statement = 'none'              # 记录SQL语句 (none, ddl, mod, all)
# none: 不记录任何SQL语句
# ddl: 记录数据定义语言 (CREATE, ALTER, DROP)
# mod: 记录数据修改语言 (INSERT, UPDATE, DELETE, TRUNCATE, COPY)
# all: 记录所有SQL语句

# 慢查询日志
log_min_duration_statement = 1000   # 记录执行时间超过1秒的语句 (毫秒)
log_duration = on                   # 记录所有语句的执行时间

# 示例配置
log_statement = 'mod'               # 记录DDL和DML语句
log_min_duration_statement = 500    # 记录超过500毫秒的查询
log_duration = on                   # 记录执行时间
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h,duration=%m '

详细查询日志

# 详细的查询执行信息
log_parser_stats = off              # 记录解析器统计信息
log_planner_stats = off             # 记录规划器统计信息
log_executor_stats = off            # 记录执行器统计信息
log_statement_stats = off           # 记录语句统计信息

# 启用详细统计（性能开销较大）
log_parser_stats = on
log_planner_stats = on
log_executor_stats = on

# 查询计划日志
debug_print_parse = off             # 打印解析树
debug_print_rewritten = off         # 打印重写查询
debug_print_plan = off              # 打印执行计划
debug_pretty_print = on             # 美化打印输出

# 示例：记录详细查询信息
log_statement = 'all'
log_duration = on
debug_print_plan = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

日志管理

有效的日志管理包括日志轮转、清理和监控。

日志轮转配置

# 日志轮转配置
log_rotation_age = 1d               # 每天轮转一次
log_rotation_size = 100MB           # 或者文件达到100MB时轮转
log_truncate_on_rotation = off      # 轮转时创建新文件而不是截断

# 按小时轮转
log_rotation_age = 1h
log_filename = 'postgresql-%Y-%m-%d_%H.log'

# 按文件大小轮转
log_rotation_age = 0                # 禁用时间轮转
log_rotation_size = 50MB            # 每50MB轮转
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# 保留日志文件数量
# 通过外部工具管理，如logrotate

使用logrotate管理日志

# /etc/logrotate.d/postgresql
/var/lib/pgsql/data/log/*.log {
    hourly
    rotate 168
    compress
    delaycompress
    missingok
    notifempty
    create 0600 postgres postgres
    postrotate
        /usr/bin/pg_ctl reload -D /var/lib/pgsql/data >/dev/null 2>&1 || true
    endscript
}

# 或者使用天级轮转
/var/lib/pgsql/data/log/*.log {
    daily
    rotate 30
    compress
    delaycompress
    missingok
    notifempty
    create 0600 postgres postgres
    postrotate
        /usr/bin/pg_ctl reload -D /var/lib/pgsql/data >/dev/null 2>&1 || true
    endscript
}

日志清理脚本

#!/bin/bash
# PostgreSQL日志清理脚本 cleanup_logs.sh

# 配置变量
LOG_DIR="/var/lib/postgresql/data/log"
RETENTION_DAYS=30

# 删除指定天数前的日志文件
find $LOG_DIR -name "postgresql-*.log" -name "postgresql-*.log.gz" -mtime +$RETENTION_DAYS -delete

# 压缩旧日志文件
find $LOG_DIR -name "postgresql-*.log" -mtime +7 -exec gzip {} \;

# 统计日志文件大小
echo "Log directory size:"
du -sh $LOG_DIR

# 检查最大日志文件
echo "Largest log files:"
find $LOG_DIR -name "postgresql-*.log*" -exec ls -lh {} \; | sort -k5 -hr | head -10

# 记录清理操作
echo "$(date): Cleaned up logs older than $RETENTION_DAYS days" >> /var/log/postgresql/cleanup.log

日志分析

通过分析日志文件可以发现性能问题、安全威胁和系统异常。

常用日志分析命令

# 查看错误日志
tail -f /var/lib/postgresql/data/log/postgresql-*.log

# 查找错误信息
grep -i "error" /var/lib/postgresql/data/log/postgresql-*.log

# 查找警告信息
grep -i "warning" /var/lib/postgresql/data/log/postgresql-*.log

# 查找慢查询
grep "duration:" /var/lib/postgresql/data/log/postgresql-*.log | grep -v "duration: 0" 

# 统计慢查询数量
grep "duration:" /var/lib/postgresql/data/log/postgresql-*.log | awk -F'duration: ' '{if($2+0 > 1000) print $0}' | wc -l

# 查找连接信息
grep "connection received" /var/lib/postgresql/data/log/postgresql-*.log
grep "disconnection" /var/lib/postgresql/data/log/postgresql-*.log

# 统计连接数
grep "connection received" /var/lib/postgresql/data/log/postgresql-*.log | wc -l

# 查找死锁信息
grep "deadlock" /var/lib/postgresql/data/log/postgresql-*.log

# 查找自动清理信息
grep "automatic vacuum" /var/lib/postgresql/data/log/postgresql-*.log

日志分析脚本

#!/bin/bash
# PostgreSQL日志分析脚本 analyze_logs.sh

LOG_FILE="/var/lib/postgresql/data/log/postgresql-$(date +%Y-%m-%d).log"

# 分析慢查询
echo "=== Slow Queries (over 1 second) ==="
grep "duration:" $LOG_FILE | awk -F'duration: ' '{if($2+0 > 1000) print $0}' | head -10

# 分析错误信息
echo -e "\n=== Error Messages ==="
grep -i "error\|fatal\|panic" $LOG_FILE | head -10

# 分析警告信息
echo -e "\n=== Warning Messages ==="
grep -i "warning" $LOG_FILE | head -10

# 连接统计
echo -e "\n=== Connection Statistics ==="
echo "Total connections: $(grep "connection received" $LOG_FILE | wc -l)"
echo "Total disconnections: $(grep "disconnection" $LOG_FILE | wc -l)"

# 自动清理统计
echo -e "\n=== Autovacuum Operations ==="
echo "Autovacuum operations: $(grep "automatic vacuum" $LOG_FILE | wc -l)"

# 死锁检测
echo -e "\n=== Deadlock Information ==="
grep "deadlock" $LOG_FILE

# 生成报告
echo -e "\n=== Summary Report ==="
echo "Log file: $LOG_FILE"
echo "File size: $(du -h $LOG_FILE | cut -f1)"
echo "Date range: $(head -1 $LOG_FILE | cut -d' ' -f1) to $(tail -1 $LOG_FILE | cut -d' ' -f1)"

使用pgBadger分析日志

# 安装pgBadger
# Ubuntu/Debian
sudo apt-get install pgbadger

# CentOS/RHEL
sudo yum install pgbadger

# 配置PostgreSQL生成CSV日志
# 在postgresql.conf中添加
log_destination = 'csvlog'
log_statement = 'all'
log_min_duration_statement = 0

# 生成HTML报告
pgbadger /var/lib/postgresql/data/log/*.csv -o report.html

# 指定时间范围
pgbadger /var/lib/postgresql/data/log/*.csv -b "2025-10-15 00:00:00" -e "2025-10-15 23:59:59" -o daily_report.html

# 包含查询详情
pgbadger /var/lib/postgresql/data/log/*.csv --detail-html --disable-checkpoint --disable-temporary-file -o detailed_report.html

# 自动化报告生成
#!/bin/bash
# daily_report.sh
DATE=$(date +%Y-%m-%d)
pgbadger /var/lib/postgresql/data/log/*.csv -b "$DATE 00:00:00" -e "$DATE 23:59:59" -o "/var/www/html/reports/pg_report_$DATE.html"

日志监控和告警

建立日志监控机制，及时发现和处理异常情况。

实时日志监控

# 使用tail监控实时日志
tail -f /var/lib/postgresql/data/log/postgresql-*.log

# 监控特定错误
tail -f /var/lib/postgresql/data/log/postgresql-*.log | grep -i "error\|fatal\|panic"

# 监控慢查询
tail -f /var/lib/postgresql/data/log/postgresql-*.log | grep "duration:" | awk -F'duration: ' '{if($2+0 > 5000) print $0}'

# 使用 multitail 同时监控多个日志文件
multitail /var/lib/postgresql/data/log/postgresql-*.log

# 使用lnav查看结构化日志
lnav /var/lib/postgresql/data/log/postgresql-*.log

日志告警脚本

#!/bin/bash
# PostgreSQL日志告警脚本 alert_logs.sh

LOG_FILE="/var/lib/postgresql/data/log/postgresql-$(date +%Y-%m-%d).log"
ALERT_EMAIL="admin@example.com"

# 检查错误
ERROR_COUNT=$(grep -i "error\|fatal\|panic" $LOG_FILE | grep "$(date +%Y-%m-%d)" | wc -l)
if [ $ERROR_COUNT -gt 0 ]; then
    echo "PostgreSQL Errors Detected: $ERROR_COUNT errors found in today's log" | mail -s "PostgreSQL Error Alert" $ALERT_EMAIL
fi

# 检查死锁
DEADLOCK_COUNT=$(grep "deadlock" $LOG_FILE | grep "$(date +%Y-%m-%d)" | wc -l)
if [ $DEADLOCK_COUNT -gt 0 ]; then
    echo "PostgreSQL Deadlocks Detected: $DEADLOCK_COUNT deadlocks found in today's log" | mail -s "PostgreSQL Deadlock Alert" $ALERT_EMAIL
fi

# 检查慢查询
SLOW_QUERY_COUNT=$(grep "duration:" $LOG_FILE | awk -F'duration: ' '{if($2+0 > 10000) print $0}' | wc -l)
if [ $SLOW_QUERY_COUNT -gt 5 ]; then
    echo "PostgreSQL Slow Queries: $SLOW_QUERY_COUNT queries over 10 seconds found in today's log" | mail -s "PostgreSQL Slow Query Alert" $ALERT_EMAIL
fi

# 检查连接数
CONNECTION_COUNT=$(grep "connection received" $LOG_FILE | grep "$(date +%Y-%m-%d)" | wc -l)
if [ $CONNECTION_COUNT -gt 1000 ]; then
    echo "High Connection Count: $CONNECTION_COUNT connections today" | mail -s "PostgreSQL Connection Alert" $ALERT_EMAIL
fi

使用ELK Stack分析日志

# 配置Filebeat收集PostgreSQL日志
# filebeat.yml
filebeat.inputs:
- type: log
  enabled: true
  paths:
    - /var/lib/postgresql/data/log/*.log
  fields:
    type: postgresql
  fields_under_root: true

# Logstash配置
# postgresql.conf
input {
  beats {
    port => 5044
  }
}

filter {
  if [type] == "postgresql" {
    grok {
      match => { "message" => "%{TIMESTAMP_ISO8601:timestamp} \[%{NUMBER:pid}\]: \[%{NUMBER:log_line}-1\] user=%{USERNAME:user},db=%{WORD:database},app=%{WORD:application},client=%{IP:client_ip} %{GREEDYDATA:log_message}" }
    }
    date {
      match => [ "timestamp", "yyyy-MM-dd HH:mm:ss" ]
    }
  }
}

output {
  elasticsearch {
    hosts => ["localhost:9200"]
    index => "postgresql-%{+YYYY.MM.dd}"
  }
}

# Kibana可视化配置
# 创建索引模式: postgresql-*
# 创建仪表板显示:
# - 错误趋势图
# - 慢查询统计
# - 连接数监控
# - 用户活动分析

PostgreSQL日志管理最佳实践：

合理配置日志级别：根据实际需求设置适当的日志级别，避免产生过多日志
定期轮转日志：配置日志轮转机制，防止日志文件过大
监控关键指标：重点关注错误、警告、慢查询和连接信息
使用专业工具：利用pgBadger、ELK等工具进行日志分析
建立告警机制：对关键事件设置自动告警
保护日志安全：设置适当的日志文件权限，防止未授权访问
定期清理日志：根据保留策略定期清理过期日志
备份重要日志：对包含重要信息的日志进行备份