PostgreSQL在生产环境中的服务器配置建议有哪些？

在生产环境中部署 PostgreSQL 时，合理的服务器配置对性能、稳定性和安全性至关重要。以下是常见的 PostgreSQL 生产环境配置建议，涵盖硬件、操作系统、PostgreSQL 配置参数和运维最佳实践。

一、硬件建议

1. CPU

   • 建议使用多核 CPU（8 核以上），以支持高并发查询。
   • PostgreSQL 能较好利用多核，但单个查询通常只能使用一个核心。

2. 内存（RAM）

   • 至少 16GB，推荐 32GB 或更高。
   • 内存越大，shared_buffers 和操作系统的文件缓存（OS cache）越有效。

3. 磁盘

   • 使用 SSD（NVMe 更佳），避免机械硬盘。
   • 数据目录、WAL 日志、备份应尽量分离到不同物理磁盘。
   • 启用 RAID 10 或使用 ZFS/Btrfs 等提供冗余和性能的文件系统。

4. 网络

   • 低延迟、高带宽网络（如 1Gbps 或更高）。
   • 若跨机房部署主从复制，需保证网络稳定性。

二、操作系统优化

1. 文件系统

   • 推荐使用 XFS 或 ext4，确保启用 noatime 挂载选项以减少 I/O 开销。

2. 内核参数调优

   # 增大共享内存（单位：页）
   kernel.shmmax = 17179869184    # 16GB
   kernel.shmall = 4194304        # 根据物理内存调整
   
   # 提高最大打开文件数
   fs.file-max = 100000

   修改 /etc/sysctl.conf 并执行 sysctl -p 加载。

3. 用户资源限制
   在 /etc/security/limits.conf 中增加：

   postgres soft nofile 65536
   postgres hard nofile 65536
   postgres soft nproc 16384
   postgres hard nproc 16384

三、PostgreSQL 配置参数优化（postgresql.conf）

以下为常见关键参数，具体数值需根据实际硬件调整。

1. 内存相关

# 共享缓冲区，通常设为物理内存的 25%（不超过 8GB ~ 32GB，取决于总内存）
shared_buffers = 8GB

# 工作内存，每个查询可用内存
work_mem = 64MB

# 维护操作内存（VACUUM、CREATE INDEX 等）
maintenance_work_mem = 1GB

# 自动 WAL 检查点写入的内存缓冲
wal_buffers = 16MB

# 排序、哈希等使用的内存上限（会话级）
temp_buffers = 8MB

2. WAL（Write-Ahead Logging）设置

# WAL 日志大小，影响 checkpoint 频率
min_wal_size = 1GB
max_wal_size = 4GB

# 检查点目标间隔（秒），控制检查点频率
checkpoint_completion_target = 0.9

# 是否开启同步提交（强一致性 vs 性能）
synchronous_commit = on     # 生产环境建议开启

# WAL 写入方式，推荐 fsync 或 faster 的方法
wal_sync_method = fsync

3. 并发与连接

# 最大连接数，过高会导致内存耗尽
max_connections = 200         # 根据应用需求调整

# 连接池推荐使用 PgBouncer 或 pgbouncer

4. 查询计划与统计

# 自动分析表的阈值
autovacuum_analyze_scale_factor = 0.02
autovacuum_analyze_threshold = 50

# 自动清理（autovacuum）必须开启
autovacuum = on
log_autovacuum_min_duration = 0   # 记录 autovacuum 执行时间

# 统计信息收集
track_counts = on
track_io_timing = on              # 监控 I/O 性能

5. 日志设置

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

# 记录慢查询（>500ms）
log_min_duration_statement = 500

# 记录错误级别
log_error_verbosity = verbose
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

6. 性能相关

# 顺序扫描开销估算（SSD 可调低）
random_page_cost = 1.1          # SSD 推荐值
effective_cache_size = 24GB     # 约为系统总内存的 50%-75%

# 并行查询设置（视 CPU 核心数而定）
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

四、安全配置

1. 认证方式（pg_hba.conf）

   # 示例：仅允许本地连接和特定网段通过 md5 登录
   local   all             all                                     peer
   host    all             all             127.0.0.1/32            md5
   host    all             all             192.168.1.0/24          md5
   host    replication     replicator      192.168.1.10/32         md5

   • 避免使用 trust，生产环境推荐 md5 或 scram-sha-256。
   • 启用 SSL 连接（ssl = on）并配置证书。

2. 密码策略

   • 使用强密码。
   • 定期轮换密码。
   • 启用 password_encryption = scram-sha-256。

3. 权限最小化

   • 避免使用超级用户运行应用。
   • 按需分配角色权限（GRANT SELECT, INSERT…）。

五、高可用与备份

1. 主从复制（Streaming Replication）

   • 配置异步或同步复制。
   • 使用 recovery.conf（PG < 12）或 standby.signal + primary_conninfo（PG ≥ 12）。

2. 逻辑复制 / 逻辑解码

   • 适用于跨版本迁移或微服务场景。

3. 备份策略

   • 物理备份：使用 pg_basebackup + WAL 归档。
   • 逻辑备份：pg_dump / pg_dumpall，适合小数据量。
   • 归档 WAL：启用 archive_mode = on 和 archive_command。
   • 推荐工具：Barman、pgBackRest、WAL-G。

4. 监控与告警

   • 使用 Prometheus + Grafana + postgres_exporter。
   • 或 Zabbix、Nagios 等传统监控。
   • 关注：连接数、慢查询、WAL 延迟、锁等待、磁盘空间。

六、其他建议

• 定期维护：

  • VACUUM FULL（谨慎使用）、REINDEX、ANALYZE。
  • 启用 autovacuum 并监控其行为。

• 连接池：

  • 使用 PgBouncer 或 Pgpool-II 减少连接开销。

• 版本选择：

  • 使用长期支持版本（如 PostgreSQL 14、15、16），避免使用 EOL 版本。

• 升级策略：

  • 测试环境先行，使用 pg_upgrade 或逻辑复制进行版本迁移。

总结

⚠️ 注意：所有配置需根据实际负载进行压测和调优，切勿盲目套用。

如需进一步优化，可结合 EXPLAIN ANALYZE 分析慢查询，并使用 pg_stat_statements 扩展追踪 SQL 性能。