PostgreSQL 日志文件维护完全指南

概述

PostgreSQL 日志文件是数据库运维中的重要组成部分，它记录了数据库运行期间的所有重要事件、错误信息和性能数据。合理的日志文件维护策略不仅能帮助快速定位和解决问题，还能确保系统稳定运行。

为什么日志文件维护如此重要

日志文件的价值

日志安全性考虑

WARNING

日志文件可能包含敏感信息：

• DDL 语句中的明文密码
• 应用程序的 SQL 源代码
• 错误信息中的数据行内容
• 用户认证详细信息

实际案例：某金融企业的日志泄露事件

-- 错误的日志记录示例
-- 以下 SQL 会将密码记录到日志中
CREATE USER financial_user WITH PASSWORD 'Secret123!';

-- 日志输出可能包含：
-- 2024-01-15 10:23:45 UTC [12345] LOG:  statement: CREATE USER financial_user WITH PASSWORD 'Secret123!'

解决方案：日志安全最佳实践

# 1. 设置严格的文件权限
chmod 600 /var/log/postgresql/*.log
chown postgres:postgres /var/log/postgresql/*.log

# 2. 配置日志参数以避免记录敏感信息
# postgresql.conf
log_statement = 'ddl'          # 只记录 DDL，避免记录包含数据的 DML
log_min_duration_statement = 1000  # 只记录慢查询

日志轮换策略详解

为什么需要日志轮换

方法一：PostgreSQL 内置日志收集器

配置示例：设置每日轮换的日志系统

# postgresql.conf 配置
logging_collector = on                  # 启用日志收集器
log_directory = 'pg_log'               # 日志目录（相对于 PGDATA）
log_filename = 'postgresql-%Y-%m-%d.log'  # 日志文件名模式
log_truncate_on_rotation = off         # 不截断同名文件
log_rotation_age = 1d                  # 每天轮换
log_rotation_size = 100MB              # 或达到 100MB 时轮换

实际应用：电商平台的日志配置

# 场景：大型电商平台，日交易量百万级
# 需求：保留 30 天日志，支持快速问题定位

# postgresql.conf
logging_collector = on
log_directory = '/data/postgresql/logs'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 1h                  # 每小时轮换（高流量）
log_rotation_size = 1GB                # 或达到 1GB 时轮换

# 日志内容配置
log_min_duration_statement = 500       # 记录超过 500ms 的查询
log_checkpoints = on                   # 记录检查点
log_connections = on                   # 记录连接
log_disconnections = on                # 记录断开连接
log_lock_waits = on                   # 记录锁等待
log_temp_files = 0                    # 记录所有临时文件

# 日志格式
log_line_prefix = '%t [%p] %u@%d from %h: '  # 时间戳、进程ID、用户、数据库、客户端

清理脚本示例

#!/bin/bash
# clean_old_logs.sh - 清理超过 30 天的日志文件

LOG_DIR="/data/postgresql/logs"
DAYS_TO_KEEP=30

# 查找并删除旧日志
find ${LOG_DIR} -name "postgresql-*.log" -type f -mtime +${DAYS_TO_KEEP} -exec rm -f {} \;

# 记录清理操作
echo "$(date): Cleaned logs older than ${DAYS_TO_KEEP} days" >> ${LOG_DIR}/cleanup.log

# 设置 crontab（每天凌晨 3 点执行）
# 0 3 * * * /path/to/clean_old_logs.sh

方法二：使用外部日志轮换工具（rotatelogs）

配置示例：使用 Apache rotatelogs

# 启动 PostgreSQL 并配置 rotatelogs
pg_ctl start -l "|/usr/sbin/rotatelogs -n 10 /var/log/postgresql/postgresql.log.%Y-%m-%d 86400"

# 参数说明：
# -n 10: 保留最近 10 个日志文件
# 86400: 每 86400 秒（24小时）轮换一次

实际应用：中型 SaaS 应用的配置

#!/bin/bash
# start_postgres_with_rotation.sh

# 配置变量
LOG_DIR="/var/log/postgresql"
LOG_PREFIX="postgresql"
ROTATION_TIME=3600  # 每小时轮换
MAX_LOGS=168       # 保留 7 天（7*24）

# 确保日志目录存在
mkdir -p ${LOG_DIR}

# 启动 PostgreSQL
pg_ctl start -D /var/lib/postgresql/data \
  -l "|/usr/sbin/rotatelogs -n ${MAX_LOGS} ${LOG_DIR}/${LOG_PREFIX}.log.%Y%m%d%H ${ROTATION_TIME}"

# 监控脚本
cat > /usr/local/bin/check_postgres_logs.sh << 'EOF'
#!/bin/bash
# 检查日志大小和数量
LOG_COUNT=$(ls -1 /var/log/postgresql/postgresql.log.* 2>/dev/null | wc -l)
TOTAL_SIZE=$(du -sh /var/log/postgresql/ | cut -f1)

echo "Log files: ${LOG_COUNT}"
echo "Total size: ${TOTAL_SIZE}"

# 发送告警（如果日志过多）
if [ ${LOG_COUNT} -gt 200 ]; then
    echo "WARNING: Too many log files!" | mail -s "PostgreSQL Log Alert" [email protected]
fi
EOF

chmod +x /usr/local/bin/check_postgres_logs.sh

方法三：结合 logrotate 使用

配置示例：logrotate 配置文件

# /etc/logrotate.d/postgresql
/var/log/postgresql/*.log {
    daily                    # 每日轮换
    rotate 30               # 保留 30 个文件
    compress                # 压缩旧日志
    delaycompress           # 延迟压缩
    notifempty              # 空文件不轮换
    create 0600 postgres postgres  # 创建新文件的权限
    sharedscripts           # 只执行一次脚本
    postrotate
        # 通知 PostgreSQL 重新打开日志文件
        /usr/bin/pg_ctl logrotate -D /var/lib/postgresql/data > /dev/null 2>&1
    endscript
}

高级配置：基于大小和时间的混合策略

# /etc/logrotate.d/postgresql-advanced
/var/log/postgresql/postgresql.log {
    size 500M               # 达到 500MB 时轮换
    daily                   # 或每天轮换
    rotate 60              # 保留 60 个文件
    compress               # 压缩
    compresscmd /usr/bin/gzip
    compressext .gz
    compressoptions -9     # 最大压缩
    delaycompress          # 延迟压缩
    notifempty
    create 0600 postgres postgres
    
    # 预轮换脚本
    prerotate
        # 记录轮换前的状态
        echo "$(date): Pre-rotation - $(du -h /var/log/postgresql/postgresql.log)" \
            >> /var/log/postgresql/rotation.log
    endscript
    
    # 后轮换脚本
    postrotate
        # 发送信号让 PostgreSQL 切换日志
        /usr/bin/pg_ctl logrotate -D /var/lib/postgresql/data
        
        # 归档到远程存储（可选）
        if [ -f /var/log/postgresql/postgresql.log.1 ]; then
            aws s3 cp /var/log/postgresql/postgresql.log.1 \
                s3://company-backup/postgresql-logs/$(date +%Y%m%d%H%M%S).log
        fi
    endscript
}

方法四：使用 syslog

配置示例：将 PostgreSQL 日志发送到 syslog

# postgresql.conf
log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

# 配置 rsyslog（/etc/rsyslog.d/50-postgresql.conf）
local0.*    /var/log/postgresql/postgresql.log
& stop  # 停止进一步处理

# 配置 logrotate for syslog
# /etc/logrotate.d/postgresql-syslog
/var/log/postgresql/postgresql.log {
    daily
    rotate 30
    compress
    delaycompress
    missingok
    notifempty
    create 0640 syslog adm
    sharedscripts
    postrotate
        /usr/bin/systemctl reload rsyslog > /dev/null 2>&1 || true
    endscript
}

TIP

Syslog 使用注意事项：

• 在高负载系统上可能丢失日志消息
• Linux 上默认同步写入，影响性能
• 可以在配置中使用 - 前缀禁用同步：local0.* -/var/log/postgresql/postgresql.log

实战案例：构建企业级日志管理系统

场景：金融交易系统的日志架构

实现代码：

#!/bin/bash
# enterprise_log_management.sh - 企业级日志管理脚本

# 配置参数
PG_LOG_DIR="/var/log/postgresql"
ARCHIVE_DIR="/data/postgresql/archive"
COLD_STORAGE="/mnt/cold-storage/postgresql"
RETENTION_DAYS_LOCAL=7
RETENTION_DAYS_ARCHIVE=30

# 1. PostgreSQL 配置
cat > /tmp/logging.conf << EOF
# 基础日志配置
logging_collector = on
log_directory = '${PG_LOG_DIR}'
log_filename = 'postgresql-%Y-%m-%d_%H.log'
log_rotation_age = 1h
log_rotation_size = 500MB

# 详细日志内容
log_min_messages = warning
log_min_error_statement = error
log_min_duration_statement = 1000  # 记录慢查询
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = off
log_lock_waits = on
log_statement = 'ddl'
log_temp_files = 0

# CSV 格式日志（便于分析）
log_destination = 'stderr,csvlog'
log_statement_stats = on
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off

# 日志前缀格式
log_line_prefix = '%t [%p-%l] %u@%d %h %a %e: '
log_timezone = 'UTC'
EOF

# 2. 日志轮换和归档脚本
cat > /usr/local/bin/postgresql_log_archive.sh << 'EOF'
#!/bin/bash
# 日志归档脚本

source /etc/postgresql/log_config.sh

# 函数：压缩并移动日志
archive_logs() {
    local src_dir=$1
    local dst_dir=$2
    local days_old=$3
    
    find ${src_dir} -name "*.log" -type f -mtime +${days_old} | while read logfile; do
        basename=$(basename ${logfile})
        
        # 压缩日志
        gzip -9 ${logfile}
        
        # 移动到归档目录
        mv ${logfile}.gz ${dst_dir}/
        
        # 记录归档操作
        echo "$(date): Archived ${basename}" >> ${dst_dir}/archive.log
    done
}

# 函数：清理旧日志
cleanup_old_logs() {
    local dir=$1
    local days=$2
    
    find ${dir} -name "*.log.gz" -type f -mtime +${days} -delete
    echo "$(date): Cleaned logs older than ${days} days from ${dir}" >> ${PG_LOG_DIR}/cleanup.log
}

# 主流程
echo "$(date): Starting log archival process"

# 1. 压缩并归档 7 天前的日志
archive_logs ${PG_LOG_DIR} ${ARCHIVE_DIR} ${RETENTION_DAYS_LOCAL}

# 2. 移动 30 天前的归档到冷存储
find ${ARCHIVE_DIR} -name "*.log.gz" -type f -mtime +${RETENTION_DAYS_ARCHIVE} | while read archive; do
    mv ${archive} ${COLD_STORAGE}/
done

# 3. 清理超过 365 天的冷存储日志
cleanup_old_logs ${COLD_STORAGE} 365

echo "$(date): Log archival process completed"
EOF

chmod +x /usr/local/bin/postgresql_log_archive.sh

# 3. 实时监控脚本
cat > /usr/local/bin/postgresql_log_monitor.sh << 'EOF'
#!/bin/bash
# 实时日志监控脚本

# 监控错误模式
ERROR_PATTERNS=(
    "FATAL"
    "PANIC"
    "deadlock detected"
    "out of memory"
    "too many connections"
    "database system was shut down"
)

# 监控函数
monitor_logs() {
    local logfile=$1
    
    # 使用 tail -F 持续监控
    tail -F ${logfile} 2>/dev/null | while read line; do
        for pattern in "${ERROR_PATTERNS[@]}"; do
            if echo "${line}" | grep -q "${pattern}"; then
                # 发送告警
                send_alert "${pattern}" "${line}"
            fi
        done
        
        # 检查慢查询
        if echo "${line}" | grep -q "duration:"; then
            duration=$(echo "${line}" | grep -oP 'duration: \K[0-9.]+')
            if (( $(echo "$duration > 5000" | bc -l) )); then
                send_alert "SLOW_QUERY" "${line}"
            fi
        fi
    done
}

# 告警发送函数
send_alert() {
    local alert_type=$1
    local message=$2
    local timestamp=$(date '+%Y-%m-%d %H:%M:%S')
    
    # 记录到告警日志
    echo "${timestamp} [${alert_type}] ${message}" >> /var/log/postgresql/alerts.log
    
    # 发送邮件告警（示例）
    echo "PostgreSQL Alert: ${alert_type}
Time: ${timestamp}
Message: ${message}
Server: $(hostname)" | mail -s "PostgreSQL Alert: ${alert_type}" [email protected]
    
    # 发送到监控系统（示例：Prometheus Pushgateway）
    cat <<EOT | curl --data-binary @- http://pushgateway:9091/metrics/job/postgresql_alerts
# TYPE postgresql_alert counter
postgresql_alert{type="${alert_type}",server="$(hostname)"} 1
EOT
}

# 启动监控
CURRENT_LOG="${PG_LOG_DIR}/postgresql-$(date +%Y-%m-%d_%H).log"
monitor_logs ${CURRENT_LOG}
EOF

chmod +x /usr/local/bin/postgresql_log_monitor.sh

# 4. 设置 crontab
cat > /tmp/postgresql_log_cron << EOF
# PostgreSQL 日志维护任务
0 * * * * /usr/local/bin/postgresql_log_archive.sh >> /var/log/postgresql/archive.log 2>&1
*/5 * * * * /usr/local/bin/check_log_space.sh

# 每日生成 pgBadger 报告
0 2 * * * /usr/bin/pgbadger -j 4 -o /var/www/html/pgbadger/$(date +\%Y\%m\%d).html ${PG_LOG_DIR}/postgresql-$(date +\%Y-\%m-\%d)*.log
EOF

crontab /tmp/postgresql_log_cron

日志分析工具集成

pgBadger 高级配置

场景：电商网站的性能分析系统

#!/bin/bash
# pgbadger_analysis.sh - 自动化日志分析脚本

# 配置
LOG_DIR="/var/log/postgresql"
REPORT_DIR="/var/www/html/pgbadger"
DATE=$(date +%Y%m%d)

# 生成详细报告
pgbadger \
    --jobs 4 \
    --format stderr \
    --outfile ${REPORT_DIR}/report_${DATE}.html \
    --title "PostgreSQL Performance Report - ${DATE}" \
    --average 5 \
    --maxlength 10000 \
    --nohilit \
    --pie-limit 20 \
    --exclude-query "^(COPY|VACUUM)" \
    --include-query "^(SELECT|INSERT|UPDATE|DELETE)" \
    --top 50 \
    ${LOG_DIR}/postgresql-*.log

# 生成增量报告（最近1小时）
pgbadger \
    --last-parsed ${REPORT_DIR}/.pgbadger_last_state \
    --outfile ${REPORT_DIR}/incremental_$(date +%Y%m%d%H).html \
    --incremental \
    ${LOG_DIR}/postgresql-$(date +%Y-%m-%d)*.log

# 生成 JSON 格式供 API 使用
pgbadger \
    --format stderr \
    --outfile ${REPORT_DIR}/api/stats_${DATE}.json \
    --output json \
    --quiet \
    ${LOG_DIR}/postgresql-$(date +%Y-%m-%d).log

# 发送每日摘要
cat > /tmp/daily_summary.txt << EOF
PostgreSQL 日志分析摘要 - ${DATE}

报告链接: http://monitoring.company.com/pgbadger/report_${DATE}.html

主要指标:
$(pgbadger --format stderr --quiet --output text ${LOG_DIR}/postgresql-$(date +%Y-%m-%d).log | grep -E "(Total query duration|Number of queries|Queries per second)")

Top 5 慢查询已发送至 DBA 团队邮箱。
EOF

mail -s "PostgreSQL Daily Report - ${DATE}" [email protected] < /tmp/daily_summary.txt

check_postgres 集成监控

#!/bin/bash
# check_postgres_integration.sh - Nagios 集成脚本

# 安装 check_postgres
wget https://github.com/bucardo/check_postgres/archive/master.zip
unzip master.zip
cd check_postgres-master
perl Makefile.PL
make
make install

# 配置日志检查
cat > /etc/nagios/nrpe.d/postgresql_logs.cfg << EOF
# 检查致命错误
command[check_pg_fatal]=/usr/local/bin/check_postgres_log --critical=1 --logfile=/var/log/postgresql/postgresql-$(date +%Y-%m-%d).log --find="FATAL"

# 检查死锁
command[check_pg_deadlock]=/usr/local/bin/check_postgres_log --warning=1 --critical=5 --logfile=/var/log/postgresql/postgresql-$(date +%Y-%m-%d).log --find="deadlock detected"

# 检查慢查询数量
command[check_pg_slow_queries]=/usr/local/bin/check_postgres_log --warning=100 --critical=500 --logfile=/var/log/postgresql/postgresql-$(date +%Y-%m-%d).log --find="duration:" --greater=1000
EOF

# 重启 NRPE
systemctl restart nrpe

性能优化最佳实践

日志写入性能优化

# postgresql.conf 优化配置
# 针对高并发 OLTP 系统

# 异步提交（提高性能，但可能丢失最近的事务）
synchronous_commit = off

# 调整 WAL 写入
wal_writer_delay = 200ms
wal_buffers = 16MB

# 日志优化
log_min_duration_statement = 2000  # 只记录超过 2 秒的查询
log_checkpoints = on
log_lock_waits = on
log_temp_files = 10MB  # 只记录大于 10MB 的临时文件

# 减少日志详细程度
log_error_verbosity = terse
log_statement_stats = off
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off

存储优化策略

#!/bin/bash
# storage_optimization.sh - 日志存储优化

# 1. 使用专用日志分区
# 创建独立的日志分区，避免影响数据库性能
mount -t ext4 -o noatime,nodiratime /dev/sdb1 /var/log/postgresql

# 2. 配置日志压缩流水线
# 实时压缩日志以节省空间
mkfifo /var/log/postgresql/postgresql.pipe
gzip -c < /var/log/postgresql/postgresql.pipe > /var/log/postgresql/postgresql-$(date +%Y%m%d).log.gz &

# 修改 PostgreSQL 配置使用管道
# log_destination = 'stderr'
# logging_collector = off
# 然后启动时重定向到管道：
# postgres -D /var/lib/postgresql/data 2>/var/log/postgresql/postgresql.pipe

# 3. 设置日志目录配额
# 防止日志占满磁盘
setquota -u postgres 10G 12G 0 0 /var/log/postgresql

故障排查案例集

案例1：日志轮换失败导致的服务中断

问题描述： 某电商平台在黑五促销期间，PostgreSQL 突然无法写入新数据。

排查过程：

# 1. 检查磁盘空间
df -h
# 输出显示 /var 分区使用率 100%

# 2. 查找大文件
du -sh /var/log/postgresql/*
# 发现 postgresql.log 文件达到 50GB

# 3. 检查日志轮换配置
grep rotation /etc/postgresql/postgresql.conf
# 发现 logging_collector = off

# 4. 紧急处理
# 清空日志文件（保留最后 1000 行用于分析）
tail -n 1000 /var/log/postgresql/postgresql.log > /tmp/pg_recent.log
> /var/log/postgresql/postgresql.log

# 5. 永久解决方案
# 启用日志收集器并配置轮换
cat >> /etc/postgresql/postgresql.conf << EOF
logging_collector = on
log_rotation_age = 1h
log_rotation_size = 1GB
EOF

# 重新加载配置
pg_ctl reload

案例2：日志分析发现的性能问题

问题描述： 用户反馈系统响应变慢，需要定位原因。

分析脚本：

#!/bin/bash
# performance_analysis.sh - 性能问题分析

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

echo "=== 慢查询分析 ==="
# 提取所有慢查询（超过 1 秒）
grep -E "duration: [0-9]{4,}" ${LOG_FILE} | \
    awk '{print $1, $2, $NF}' | \
    sort -k3 -nr | \
    head -20

echo -e "\n=== 锁等待分析 ==="
# 统计锁等待
grep "lock wait" ${LOG_FILE} | \
    awk '{print $1, $2}' | \
    uniq -c | \
    sort -nr | \
    head -10

echo -e "\n=== 连接数分析 ==="
# 每小时连接数统计
grep "connection authorized" ${LOG_FILE} | \
    awk '{print substr($1,1,13)}' | \
    uniq -c

echo -e "\n=== 错误统计 ==="
# 错误类型分布
grep -E "(ERROR|FATAL|PANIC)" ${LOG_FILE} | \
    awk '{for(i=5;i<=NF;i++) printf "%s ", $i; print ""}' | \
    sort | \
    uniq -c | \
    sort -nr | \
    head -20

# 生成可视化报告
pgbadger --quiet \
    --outfile /tmp/performance_report.html \
    --top 20 \
    ${LOG_FILE}

echo -e "\n详细报告已生成: /tmp/performance_report.html"

总结

PostgreSQL 日志文件维护是数据库运维的核心工作之一。通过合理的日志策略，不仅能够确保系统稳定运行，还能在问题发生时快速定位和解决。记住以下要点：

1. 安全第一：始终注意日志中的敏感信息，设置适当的访问权限
2. 自动化运维：使用脚本和工具自动化日志轮换和分析工作
3. 监控预警：建立实时监控机制，及时发现和处理问题
4. 定期优化：根据业务发展调整日志策略，平衡详细度和性能
5. 工具集成：充分利用 pgBadger、check_postgres 等工具提升效率

通过本指南提供的方法和示例，您可以构建适合自己业务需求的日志管理体系，确保 PostgreSQL 数据库的稳定高效运行。