Postgresql生产环境推荐配置？-CDNK博客

在 PostgreSQL 生产环境中，合理的配置对性能、稳定性和安全性至关重要。以下是一套推荐的配置，适用于中等以上负载的生产环境（如 Web 应用、OLTP 系统等）。具体参数需根据实际硬件（CPU、内存、磁盘）、负载类型（OLTP/OLAP）和数据量进行调整。

一、硬件建议（作为配置基础）

内存：至少 16GB，推荐 32GB 或以上
CPU：4 核以上，高并发建议 8 核+
磁盘：SSD（NVMe 更佳），RAID 10 或使用可靠的备份机制
操作系统：Linux（推荐 CentOS/RHEL、Ubuntu LTS）

二、主配置文件 `postgresql.conf` 推荐设置

# 连接和认证
max_connections = 200                 # 根据应用连接池调整，过高会增加内存消耗
superuser_reserved_connections = 10   # 保留给管理员连接

# 内存配置（假设系统内存为 32GB）
shared_buffers = 8GB                  # 推荐为物理内存的 25%
effective_cache_size = 24GB           # 推荐为物理内存的 75%，仅用于查询规划
work_mem = 64MB                       # 每个排序/哈希操作可用内存，避免过高
maintenance_work_mem = 1GB            # VACUUM、CREATE INDEX 等操作使用
autovacuum_work_mem = -1              # 使用 maintenance_work_mem 的值

# 并行查询（根据 CPU 核心数调整）
max_worker_processes = 8              # 系统最大后台进程数
max_parallel_workers_per_gather = 4   # 每个查询最多并行 worker 数
max_parallel_workers = 8              # 系统级并行 worker 总数
max_parallel_maintenance_workers = 4  # CREATE INDEX CONCURRENTLY 等并行数

# WAL（Write Ahead Log）配置
wal_level = replica                   # 支持流复制和逻辑复制
synchronous_commit = on               # 保证数据持久性，可考虑 'remote_write' 提高性能
wal_sync_method = fsync               # 默认，可靠
checkpoint_completion_target = 0.9    # 平滑 checkpoint，减少 I/O 峰值
checkpoint_timeout = 15min            # 避免 checkpoint 太频繁
max_wal_size = 4GB
min_wal_size = 1GB

# 自动清理与统计
autovacuum = on                       # 必须开启
autovacuum_max_workers = 6            # 同时运行的 autovacuum worker 数
autovacuum_naptime = 10s              # 检查频率
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.1  # 表大小的 10% 被修改时触发
autovacuum_analyze_scale_factor = 0.05

# 查询规划器
random_page_cost = 1.1                # SSD 环境下降低（默认 4.0 用于机械盘）
effective_io_concurrency = 200        # SSD 支持高并发 I/O

# 临时文件和排序
temp_file_limit = 1GB                 # 限制临时文件大小

# 日志配置
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 0
log_min_duration_statement = 1000     # 记录超过 1 秒的慢查询
log_checkpoints = on
log_connections = off                 # 高并发下避免日志爆炸
log_disconnections = off
log_lock_waits = on                   # 记录锁等待
log_temp_files = 0                    # 记录所有临时文件

# 时区和本地化
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'

三、`pg_hba.conf` 安全配置示例

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

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

# 复制用户（用于主从）
host    replication     repuser         192.168.1.100/32        md5

# 禁止公网直接访问，或使用 scram-sha-256
host    all             all             0.0.0.0/0               reject

✅ 建议使用 scram-sha-256 替代 md5（PostgreSQL 10+）

四、操作系统级优化建议

文件系统：使用 XFS 或 ext4，挂载选项：
```
noatime,nodiratime,barrier=1
```
I/O 调度器：SSD 使用 noop 或 deadline，NVMe 使用 none（Linux 5.4+）

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

vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
vm.swappiness = 1
kernel.shmmax = 34359738368         # 32GB，略小于物理内存
kernel.shmall = 8388608             # 共享内存页数

打开文件限制（/etc/security/limits.conf）：

postgres soft nofile 65536
postgres hard nofile 65536

五、高可用与备份建议

流复制（Streaming Replication） + Hot Standby
- 使用 synchronous_commit = remote_write 平衡性能与数据安全
备份策略：
- 使用 pg_basebackup + WAL 归档（archive_mode = on）
- 结合 wal-g、barman、pgBackRest 等工具实现增量备份
监控：
- 使用 Prometheus + Grafana + pg_stat_statements
- 启用 pg_stat_statements 扩展分析慢查询

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

在 postgresql.conf 中添加：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

六、其他建议

定期维护：
- ANALYZE、VACUUM（autovacuum 通常足够）
- 重建膨胀索引（REINDEX CONCURRENTLY）
连接池：
- 使用 pgBouncer 或 PgPool-II 减少连接开销
版本选择：
- 使用长期支持版本（如 PostgreSQL 14、15、16）
- 避免使用 EOL 版本

七、验证与调优工具

EXPLAIN (ANALYZE, BUFFERS) 分析查询
pg_stat_activity 查看当前连接
pg_stat_user_tables 查看表访问统计
使用 pgTune 工具（https://pgtune.leopard.in.ua/）生成初始配置参考

总结

类别	推荐值（32GB 内存为例）
shared_buffers	8GB
effective_cache_size	24GB
work_mem	64MB
max_connections	200
wal_level	replica
autovacuum	on
log_min_duration_statement	1000ms
random_page_cost	1.1（SSD）

⚠️ 重要提示：任何配置更改后需重启 PostgreSQL 生效（部分参数可 pg_reload_conf() 重载），建议在测试环境验证后再上线。

如提供具体硬件配置和业务场景（如读写比例、数据量、QPS），可进一步定制优化方案。