PostgreSQL配置

14.10

日志

# 使用logratate日志轮转
# 启用日志收集器（csvlog必须开启）
logging_collector = on

# 日志输出目标
log_destination = 'stderr, csvlog'

# 设置日志目录（相对 $PGDATA）
log_directory = '/var/log/pg_log'

# 按天分割日志文件（YYYY-MM-DD格式）
# log_filename = 'postgresql-%Y-%m-%d.log'
log_filename = 'postgresql.log'

# 每日轮转
# log_rotation_age = 1d
log_rotation_age = 0  # 禁用基于时间的轮转
# 或设置100MB自动轮转
# log_rotation_size = 100MB
log_rotation_size = 0

# 同名覆盖
log_truncate_on_rotation = off

# 记录DDL和DML语句
log_statement = 'mod'

# 记录>1MB的临时文件(kB)
log_temp_files = 1024

# 错误报告
log_min_error_statement = error       # 记录ERROR及以上错误
log_min_messages = warning            # 控制服务器日志详细度

# 自动维护
log_autovacuum_min_duration = 1000   # 记录>1秒的auto/vacuum操作

# 性能与存储优化
log_line_prefix = '%m [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' # 关键信息前缀
log_timezone = 'UTC'                 # 统一使用UTC时间
# log_timezone = 'Asia/Shanghai'
log_duration = off                   # 避免双重计时
log_file_mode = 0600                 # 限制日志文件权限

# CSV日志优化
log_error_verbosity = verbose        # CSV需要详尽信息
log_hostname = on                    # 记录客户端主机名
log_replication_commands = on        # 诊断复制问题

查询优化

# 共享缓冲区大小，提升数据缓存效率。建议系统内存的 25%~40%
shared_buffers = 2048MB
# 每个操作（排序/哈希）的内存，需根据并发计算（例：100 并发 × 64MB = 6.4GB），避免 OOM。（总内存 / 最大并发数）。建议：16MB ~ 64MB
work_mem = 30MB
# 优化器估算的磁盘缓存大小，影响索引选择。建议：系统内存的 50%~75%
effective_cache_size = 8GB

# 随机访问代价，SSD 需降低以鼓励索引扫描。建议：1.1（SSD）2.0（HDD）
random_page_cost = 1.1
# 单查询的并行进程数。建议：4 ~ 8
max_parallel_workers_per_gather = 4

# 禁用全表扫描（调试用，生产慎关）
# enable_seqscan = off

# 查询超时时间
statement_timeout = 30s

事务和死锁

# 单事务最大锁数量（防锁表耗尽）。建议：128 ~ 256
max_locks_per_transaction = 128
# 空闲事务超时（防连接泄漏）。建议：10min
idle_in_transaction_session_timeout = 10min

# 记录超过 deadlock_timeout 的锁等待
log_lock_waits = on
# 单次锁等待超时（避免长时间阻塞） 建议：3s ~ 10s
lock_timeout = 3s
# 死锁检测等待时间（默认 1s）。建议：1s ~ 3s
deadlock_timeout = 1s

慢查询日志配置

log_min_duration_statement = 1000   # 记录超过 1000ms 的查询（单位：毫秒）
log_checkpoints = on                # 记录检查点（分析 I/O 瓶颈）
log_connections = on                # 记录连接建立
log_disconnections = on             # 记录连接断开

自动清理和统计

autovacuum = on                    # 开启自动清理
track_io_timing = on               # 跟踪 I/O 耗时（性能分析）
track_activity_query_size = 2048   # 记录完整长 SQL

日志轮转

logrotate

# logrotate /etc/logrotate.d/postgresql
/var/log/pg_log/postgresql.log
/var/log/pg_log/postgresql.csv {
    # daily                      # 每日轮转
    size 100M                  # 大小轮转
    missingok                  # 日志不存在时不报错
    rotate 30                  # 保留30个归档
    compress                   # 压缩归档
    delaycompress              # 仅压缩前一天的日志
    notifempty                 # 空文件不轮转
    copytruncate               # 关键：复制后清空原文件
    su postgres postgres       # 以postgres用户操作
    dateext                    # 按日期后缀
    dateformat -%Y%m%d         # 日期格式：-20250615
}