如何提升CentOS上PostgreSQL的稳定性

提升 CentOS 上 PostgreSQL 稳定性的实用方案

一 系统资源与内核参数

• 提升文件句柄与进程数限制，避免“Too many open files / cannot fork”等问题：
  • 在 /etc/security/limits.conf 增加：
    • postgres soft nofile 65535
    • postgres hard nofile 65535
    • postgres soft nproc 4096
    • postgres hard nproc 4096
  • 注意：某些系统路径为 /etc/security/limits.d/90-nproc.conf，需保持一致。
• 平滑后台刷脏，降低大内存机器写峰：
  • 将 vm.dirty_background_ratio=0 与 vm.dirty_background_bytes=1073741824（1GB） 写入 /etc/sysctl.conf，执行 sysctl -p 生效。
• 降低换页倾向，减少抖动：
  • 设置 vm.swappiness=10（默认值通常为60，过高会诱发swap）。
• 持久化生效与验证：
  • 执行 sysctl -p；用 ulimit -n/-u 与 cat /proc/sys/vm/swappiness 校验。

二 存储与文件系统

• 使用带电池保护写缓存（BBU）的 RAID10，显著提升随机写与崩溃一致性；只读副本可用 RAID0 提升吞吐。
• 优先选择 SSD/NVMe，并合理设置 effective_io_concurrency（SSD 可尝试 200 左右，视设备而定）。
• 文件系统建议 XFS/ext4，挂载选项使用 noatime,nodiratime；确保充足的 IOPS 与稳定的写缓存策略。

三 PostgreSQL 关键配置

• 内存与并行（按总内存与并发谨慎设置）：
  • shared_buffers：物理内存的 25%–40%
  • work_mem：按并发与操作复杂度设置，避免过大导致内存压力
  • maintenance_work_mem：VACUUM/建索引等维护操作使用
  • max_parallel_workers_per_gather：依据 CPU 核心数 合理开启
• WAL 与复制（保障持久性与恢复能力）：
  • wal_buffers：约为 shared_buffers 的 1/32
  • wal_level：replica
  • max_wal_senders：≥复制实例数
  • max_replication_slots：≥实际复制槽数
  • 建议开启 synchronous_commit=on（或按业务权衡设置为 remote_apply）以强化主从一致性
• 连接与连接池：
  • 避免过高 max_connections，使用 PgBouncer 等连接池复用连接，减少 fork/销毁开销
• 变更流程：
  • 逐项小步调整，重启或 SELECT pg_reload_conf() 生效，变更前在测试环境验证。

四 高可用与故障切换

• 推荐基于 Patroni + etcd/Consul/ZooKeeper 的集群管理，自动故障切换与配置分发：
  • 关键配置：bootstrap.dcs 中 ttl/loop_wait/retry_timeout、启用 use_pg_rewind、use_slots，以及 postgresql.parameters 中的复制与 WAL 参数
  • 示例要点：
    • scope: postgres
    • name: node1
    • restapi.listen: 0.0.0.0:8008
    • etcd: host 127.0.0.1:2379
    • bootstrap.postgresql.parameters:
      • wal_level: replica
      • max_wal_senders: 10
      • max_replication_slots: 10
      • synchronous_commit: on
      • synchronous_standby_names: ‘*’
• 读写分离与会话保持可叠加 Pgpool-II（负载均衡、故障转移、健康检查）。
• 需要对外统一接入 IP 时，可叠加 Keepalived VIP，避免应用改连接串。

五 监控维护与变更管理

• 监控与诊断：
  • 启用 pg_stat_statements 定位慢 SQL 与异常调用
  • 使用 pgBadger 分析日志，发现高峰与错误趋势
  • 结合系统监控（如 sar/collectd）观察 I/O、负载、swap 与 WAL 写入速率
• 例行维护：
  • 合理设置 autovacuum，在业务低峰执行 VACUUM/ANALYZE；大表考虑分区与并发维护
  • 定期校验复制延迟与复制槽健康，避免 WAL 堆积
• 变更与回滚：
  • 任何参数/版本/架构变更先在 预发环境 验证，准备回滚预案与窗口期
  • 重要操作前做好 基础备份（如 pg_basebackup 或企业级备份方案）
• 安全提示：
  • 生产环境请启用 SSL/TLS、强口令与最小权限访问控制，限制复制与超级用户来源网段