PostgreSQL 在 CentOS 的最佳实践指南
一 环境准备与版本选择
- 选择受支持的 CentOS Stream 8/9 或迁移至 Rocky Linux 8/9、RHEL 8/9，避免使用已停止维护的 CentOS 7/8，以获得安全更新与生态兼容。
- 规划实例规格：为数据库分配专用服务器，优先使用 SSD/NVMe，并结合业务选择多核 CPU 与充足内存。
- 规划目录与端口：将数据目录（PGDATA）与 WAL、临时表空间分离到不同磁盘；默认端口 5432；准备备份与归档策略。
- 安全基线：启用 firewalld，仅开放必要端口；采用 SSL/TLS 加密传输；禁用不必要服务与端口。
二 安装与初始化
- 启用官方仓库（示例为 EL-8，其他版本替换为对应仓库）：
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
- 安装服务器与工具：
sudo yum install -y postgresql13-server postgresql13-contrib
- 初始化与启停：
sudo postgresql-setup --initdb
sudo systemctl start postgresql-13
sudo systemctl enable postgresql-13
- 安全初始化：
sudo -u postgres psql -c “ALTER USER postgres WITH PASSWORD ‘StrongPass!’;”
- 验证：
psql --version
sudo -u postgres psql -c “SELECT version();”
三 安全与网络配置
- 配置文件位置：数据目录通常为 /var/lib/pgsql/{version}/data/，核心文件为 postgresql.conf 与 pg_hba.conf。
- 监听与访问控制：
- postgresql.conf：设置 listen_addresses = ‘*’（或限定内网网段）。
- pg_hba.conf：优先使用 scram-sha-256 认证；示例：
- 本地：local all all scram-sha-256
- 内网：host all all 192.168.1.0/24 scram-sha-256
- 远程管理：host postgres postgres 0.0.0.0/0 reject（按需收紧）
- 启用 SSL：在 postgresql.conf 中设置 ssl = on，并配置服务器证书与私钥，强制应用使用 SSL 连接。
- 防火墙：
sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload
四 性能与存储优化
- 配置调优（示例为 16GB 内存，OLTP 场景，需结合实际压测微调）：
- shared_buffers：物理内存的 25%–40%（示例：4GB）
- work_mem：按并发与操作复杂度设置（示例：64MB，总占用≈ work_mem × 并发排序/哈希操作数）
- maintenance_work_mem：大表维护与索引创建（示例：512MB–1GB）
- effective_io_concurrency：SSD 建议 200 左右
- wal_buffers：约为 shared_buffers 的 1/32（示例：128MB）
- checkpoint_timeout：15min–30min；checkpoint_completion_target：0.9
- 并行：max_parallel_workers_per_gather、max_worker_processes 结合 CPU 核心数 设定
- 工具辅助：使用 pgTune 生成参数建议，作为基线再压测微调。
- 存储与 I/O：
- 分离 数据文件 / WAL / 临时表空间 到不同磁盘，降低 I/O 争用；优先 SSD。
- 文件系统挂载选项使用 noatime；必要时调整 readahead（blockdev --setra）。
- 透明大页（THP）建议设为 never，减少内存管理抖动。
- 索引与查询：
- 合理选择 B-Tree、GiST、GIN、BRIN、Partial 索引；多列条件使用 复合索引；定期维护索引。
- 使用 EXPLAIN (ANALYZE) 分析慢查询，避免全表扫描与不必要的排序/哈希。
- 连接池：高并发使用 PgBouncer 或 pgpool-II 管理连接。
- 并行与 JIT：在 CPU 充足、查询稳定的场景开启 JIT 编译（需安装 LLVM 并在编译时启用 --with-llvm）。
五 维护监控与备份高可用
- 例行维护：
- 确保 autovacuum 正常运行；批量导入或大量更新后及时执行 VACUUM ANALYZE。
- 空间回收谨慎使用 VACUUM FULL（建议维护窗口进行）。
- 统计与日志：
- 启用 pg_stat_statements 定位慢 SQL 与热点对象。
- 使用 pgBadger 分析日志，生成性能与错误报告。
- 备份与恢复：
- 物理备份：使用 pg_basebackup 进行全量备份，结合 WAL 归档 实现时间点恢复（PITR）。
- 传输与压缩：启用 gzip/LZ4/zstd 减少备份体积与传输时间。
- 高可用：
- 流复制（Streaming Replication）搭建 主从，配合 Patroni/Repmgr 或 pgpool-II 实现自动故障切换与读写分离。
- 变更管理：任何参数或结构变更先在 测试环境 验证，变更窗口内做好 回滚预案 与 监控告警。
