Postgresql生产环境推荐配置？

在 PostgreSQL 的生产环境中，合理的配置对于性能、稳定性和安全性至关重要。以下是一套推荐的配置建议（适用于中等以上负载的生产环境），基于常见的硬件和应用场景。具体参数需根据实际业务需求、硬件资源和负载特征进行调整。

一、硬件建议（作为配置前提）

• CPU：至少 4 核以上，高并发场景建议 8 核或更多
• 内存：16GB 起步，建议 32GB 或更高
• 存储：SSD 固态硬盘，RAID 10 或使用可靠的云存储（如 EBS io1/io2）
• 网络：千兆及以上带宽，低延迟

二、核心配置文件：postgresql.conf

1. 内存相关配置（关键）

# 共享缓冲区，通常设置为物理内存的 25%~40%
shared_buffers = 8GB

# 每个会话可使用的私有排序内存和哈希操作内存
work_mem = 64MB

# 维护操作（如 VACUUM、CREATE INDEX）使用的内存
maintenance_work_mem = 1GB

# 自动清理进程使用的最大内存
autovacuum_work_mem = -1  # 使用 maintenance_work_mem

# WAL 缓冲区大小（通常不需要太大）
wal_buffers = 16MB

# 提交时是否等待 WAL 写入磁盘（平衡性能与数据安全）
synchronous_commit = on   # 生产环境推荐开启，确保数据持久性

# 检查点相关
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB

# BGWriter 设置（异步写入脏页）
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4.0

📌 work_mem 不宜设得过高，避免高并发下内存耗尽。例如 100 个连接 × 64MB = 6.4GB，需合理评估。

2. 并发与连接

# 最大连接数（避免过多连接导致性能下降）
max_connections = 100

# 工作进程（用于并行查询）
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4  # 如 CREATE INDEX CONCURRENTLY

建议使用连接池（如 PgBouncer）来管理连接，减少后端进程开销。

3. 自动清理与统计

# 启用自动清理（必须开启）
autovacuum = on

# 自动分析和清理的频率
autovacuum_naptime = 10s
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02

# 单个表的 autovacuum 阈值（小表更敏感）
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

对于大表频繁更新的场景，可能需要调低 scale_factor 或使用自定义策略。

4. 日志配置（生产必备）

# 开启日志记录
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB

# 记录慢查询（重要！）
log_min_duration_statement = 1000  # 记录超过 1 秒的 SQL

# 可选：记录检查点、连接、死锁等
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0  # 记录所有临时文件（用于排查排序问题）

# 日志格式（推荐 JSON 或带时间戳）
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_timezone = 'Asia/Shanghai'

5. 性能优化

# 查询计划器相关
random_page_cost = 1.1    # SSD 环境建议降低（默认 4.0）
effective_cache_size = 24GB  # 估计操作系统和共享缓冲区可缓存的数据量

# 开启 JIT（仅当 CPU 强大且复杂查询多时考虑）
jit = off  # 实际效果因场景而异，建议测试后再启用

# 并行查询（视情况开启）
force_parallel_mode = off

6. 安全与可靠性

# 监听地址
listen_addresses = 'localhost'  # 生产环境应限制 IP，如 '10.0.0.10'
port = 5432

# 数据同步模式（高可用时配置）
synchronous_standby_names = ''  # 如果有备库，可设置为 'standby1'

# WAL 日志级别（逻辑复制或 PITR 需要）
wal_level = replica  # 或 'logical'（如果使用逻辑复制）

# 归档（用于 PITR 和备份）
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'  # 或调用脚本上传到远程

三、pg_hba.conf 配置（访问控制）

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    myapp           app_user        10.0.0.0/24             md5
host    replication     replicator      10.0.1.0/24             md5  # 流复制用户

建议：

• 使用 md5 或 scram-sha-256 加密认证
• 限制 IP 范围
• 不要使用 trust 或 password

四、其他生产建议

1. 定期备份

   • 使用 pg_basebackup + WAL 归档实现 PITR（时间点恢复）
   • 结合 Barman、WAL-G、pgBackRest 等工具自动化

2. 监控

   • 使用 Prometheus + Grafana + postgres_exporter
   • 监控连接数、慢查询、锁、缓冲区命中率、WAL 生成速率等

3. 索引与查询优化

   • 定期分析慢查询日志
   • 使用 EXPLAIN (ANALYZE, BUFFERS) 分析执行计划
   • 避免全表扫描，合理创建索引

4. 定期维护

   • REINDEX, VACUUM FULL（谨慎使用）
   • 更新统计信息：ANALYZE
   • 监控膨胀表（bloat）

5. 高可用

   • 主从流复制 + Patroni + etcd/Consul
   • 或使用云服务（如 AWS RDS、Aurora、阿里云 RDS）

五、配置验证工具

• 使用 PGTune 根据硬件生成初始配置
• 使用 check_postgres 进行健康检查
• 使用 pg_settings 视图查看当前生效配置：

SELECT name, setting, unit, short_desc FROM pg_settings WHERE category = 'Resource Usage';

六、总结

⚠️ 重要提示：
所有配置修改后需重载或重启数据库，并在测试环境充分验证。生产变更建议逐项调整并观察影响。

如果你提供具体的硬件配置（如内存、磁盘类型）、业务类型（OLTP / OLAP）、QPS 要求，我可以给出更精确的配置建议。