Postgresql生产环境推荐配置？-云服务器

在 PostgreSQL 的生产环境中，合理的配置对于性能、稳定性和可维护性至关重要。以下是一套推荐的配置参数，适用于大多数中高负载的生产环境。具体数值需根据实际硬件（CPU、内存、磁盘 I/O）、工作负载（OLTP、OLAP、混合）和业务需求进行调整。

一、硬件建议（前提）

内存：至少 16GB，推荐 32GB 或以上
CPU：8 核以上
磁盘：SSD，RAID 10 或使用 ZFS/Btrfs 等支持写时复制的文件系统
操作系统：Linux（如 CentOS/RHEL、Ubuntu LTS）

二、postgresql.conf 关键配置参数

1. 内存相关配置

# 共享缓冲区，通常设为物理内存的 25%（但不超过 8GB 对于 OLTP 更优）
shared_buffers = 8GB

# 每个会话的排序和哈希操作可用内存（避免过度依赖 shared_buffers）
work_mem = 64MB

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

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

# WAL 缓冲区（通常无需修改，除非 WAL 写入频繁）
wal_buffers = 16MB

# 用于排序、哈希、物化等的临时工作空间
temp_buffers = 64MB

2. WAL（Write-Ahead Log）配置

# WAL 写入策略，平衡性能与持久性
wal_level = replica

# 每隔多久执行一次 checkpoint（避免 I/O 突峰）
checkpoint_timeout = 15min

# checkpoint 最大写入量，避免一次写太多导致卡顿
max_wal_size = 4GB
min_wal_size = 1GB

# 控制 checkpoint 过程中写入速度，避免 I/O 压力
checkpoint_completion_target = 0.9

# 同步提交，确保数据不丢失（生产环境建议开启）
synchronous_commit = on

# WAL 写入方式（Linux 推荐 fsync）
wal_sync_method = fsync

3. 并发与连接

# 最大连接数，避免过多连接拖垮性能
max_connections = 200

# 工作进程数（后台工作进程）
max_worker_processes = 8

# 用于并行查询的后台进程
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# 并行维护操作（如 CREATE INDEX）
max_parallel_maintenance_workers = 4

4. 自动清理（Autovacuum）

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

# 自动清理启动阈值（默认值通常足够）
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

# 根据表大小调整清理频率
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

# 自动清理最大延迟
autovacuum_max_workers = 3

# 自动清理纳管进程延迟
autovacuum_naptime = 10s

# 清理操作的 I/O 限制（避免影响业务）
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 2000

5. 查询优化与日志

# 开启查询计划收集（用于性能分析）
track_activities = on
track_counts = on
track_io_timing = on

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

# 日志输出格式
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# 日志输出目标
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_checkpoints = on
log_connections = on
log_disconnections = on

6. 复制与高可用（如使用流复制）

# 启用流复制
wal_level = replica
max_wal_senders = 5
wal_keep_size = 2GB  # 或 wal_keep_segments（旧版本）

# 同步复制（可选，保证主从一致性）
# synchronous_standby_names = 'standby1'

# 归档模式（用于 PITR）
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'  # 或使用 rsync/wal-g 等工具

三、pg_hba.conf 推荐配置（安全）

# 本地连接
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

# 生产应用连接（限制 IP 范围）
host    mydb            appuser         192.168.1.0/24          md5

# 复制连接
host    replication     replicator      standby_ip/32           md5

# 禁止 public 网络直接访问
# host    all             all             0.0.0.0/0               reject

四、操作系统层面优化

文件系统：
- 使用 XFS 或 ext4，挂载选项添加 noatime
- 示例：/data/postgres ext4 defaults,noatime 0 0

内核参数（/etc/sysctl.conf）：

vm.dirty_ratio = 15
vm.dirty_background_ratio = 5
vm.swappiness = 1
fs.aio-max-nr = 1048576
fs.file-max = 767246

资源限制（/etc/security/limits.conf）：

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

五、备份与监控

备份策略：
- 使用 pg_basebackup + WAL 归档实现 PITR（时间点恢复）
- 定期逻辑备份（pg_dump / pg_dumpall）
- 推荐工具：Barman、pgBackRest、WAL-G
监控工具：
- Prometheus + Grafana + pg_stat_statements
- Zabbix、Datadog、PMM（Percona Monitoring and Management）

六、其他建议

定期运行 ANALYZE 和 VACUUM（autovacuum 通常足够）
避免使用 SELECT *，合理使用索引
使用连接池（如 PgBouncer）减少连接开销
分区大表（尤其是日志、时间序列数据）
升级到最新稳定版（如 PostgreSQL 15/16），享受性能和功能改进

七、参考工具

PGTune：根据硬件自动生成配置建议
pg_stat_statements：分析慢查询
EXPLAIN (ANALYZE, BUFFERS)：诊断查询性能

✅ 总结：生产环境的配置应以 稳定性 > 性能 > 可维护性 为原则。建议先在测试环境验证配置，再逐步上线。定期审查 pg_stat_* 视图，持续优化。

如有具体场景（如高并发 OLTP、数据仓库、只读报表等），可进一步细化配置。