秒懂云在 PostgreSQL 生产环境中，合理的配置对性能、稳定性和安全性至关重要。以下是一套推荐的配置指南，适用于中等至大型生产环境（如 16GB+ 内存、多核 CPU、SSD 存储），你可以根据实际硬件和业务需求进行调整。
🛠 一、核心配置（postgresql.conf）
1. 连接和并发
max_connections = 200 # 根据应用连接池调整，过高会消耗内存
superuser_reserved_connections = 10 # 保留连接给管理员
listen_addresses = 'localhost,192.168.x.x' # 指定监听地址，不要用 '*'
port = 5432 # 默认端口⚠️ 建议使用连接池（如 PgBouncer）来减少实际后端进程数。
2. 内存设置（关键）
shared_buffers = 4GB # 通常设为物理内存的 25%
effective_cache_size = 12GB # 约为系统总内存的 50%-75%，表示OS+DB缓存能力
work_mem = 64MB # 每个排序/哈希操作可用内存，避免过高
maintenance_work_mem = 1GB # VACUUM、CREATE INDEX 等维护操作内存
autovacuum_work_mem = -1 # 使用 maintenance_work_mem📌 注意：
work_mem是每个操作的内存，多个排序可能同时使用，总内存 =max_connections * 并发操作数 * work_mem，需避免 OOM。
3. WAL（Write-Ahead Log）设置
wal_level = replica # 支持流复制和逻辑复制
synchronous_commit = on # 强一致性，可考虑 'remote_write' 或 'off' 提高性能
checkpoint_timeout = 15min # 默认值，可增至 30min 减少 I/O
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9 # 平滑写入 checkpoint，减少 I/O 颠峰
wal_buffers = 16MB # 通常 shared_buffers 的 1/32，自动设置为 -1 即可4. 自动清理（Autovacuum）
autovacuum = on
autovacuum_max_workers = 5 # 根据表数量调整
autovacuum_naptime = 10s # 检查频率
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05 # 大表触发 vacuum 的比例
autovacuum_analyze_scale_factor = 0.02
autovacuum_freeze_max_age = 800000000 # 防止事务ID回卷5. 查询优化
random_page_cost = 1.1 # SSD 环境建议 1.1，HDD 可为 4.0
effective_io_concurrency = 200 # SSD 启用异步 I/O，并发数
default_statistics_target = 100 # 提高统计信息精度，大表可设为 5006. 并行查询（适用于复杂查询）
max_worker_processes = 8
max_parallel_workers_per_gather = 4 # 每个查询最多并行进程
max_parallel_workers = 8 # 系统级并行进程总数
max_parallel_maintenance_workers = 4 # CREATE INDEX CONCURRENTLY 等🔐 二、安全与访问控制（pg_hba.conf）
# TYPE DATABASE USER ADDRESS METHOD
# 本地连接
local all all peer
# 本地 TCP 连接（推荐使用 TLS）
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
# 应用服务器连接（限定 IP）
host mydb app_user 192.168.10.100/32 md5
# 复制连接
host replication repl_user 192.168.10.101/32 md5
# 强烈建议使用 scram-sha-256 而非 md5（PostgreSQL 10+）
# METHOD 可改为: scram-sha-256✅ 建议：启用 SSL 并配置
ssl = on，使用证书加密连接。
📁 三、存储与表空间
- 使用 SSD 存储。
- 将
pg_wal放在独立的高速磁盘上（如果可能）。 - 大表或 I/O 密集型表使用独立表空间。
- 定期归档 WAL 日志（配合
archive_mode = on和archive_command）。
🔄 四、备份与高可用
1. 物理备份（推荐）
- 使用
pg_basebackup+ WAL 归档实现 PITR（时间点恢复）。 - 结合工具如 Barman、pgBackRest 或 WAL-G。
2. 逻辑备份
pg_dump/pg_dumpall用于小数据量或迁移。
3. 高可用
- 流复制 + 故障转移（使用 Patroni、repmgr、oranger 等）。
- 设置
hot_standby = on在备库。
📊 五、监控与日志
日志配置（postgresql.conf）
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 = 100MB
# 记录慢查询（重要！）
log_min_duration_statement = 1000 # 记录超过 1 秒的 SQL
log_checkpoints = on
log_connections = off # 可打开调试，生产建议关闭
log_disconnections = off
log_lock_waits = on
log_temp_files = 0 # 记录所有临时文件（用于诊断）
log_autovacuum_min_duration = 1000 # 记录耗时超过 1s 的 autovacuum监控工具推荐
- Prometheus + Grafana +
pg_exporter - pg_stat_statements（启用以分析慢查询）
- pgAdmin, Zabbix, Datadog
-- 启用 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或REINDEX CONCURRENTLY）
-
版本选择
- 使用长期支持版本（如 PostgreSQL 14、15、16）
- 及时打补丁，避免已知漏洞
-
资源限制
- 使用
pg_limits或操作系统 cgroups 限制资源 - 避免单个查询占用过多内存
- 使用
-
定期测试备份恢复
- 确保备份可用，进行恢复演练
🧩 示例：16GB RAM + SSD 服务器推荐配置片段
shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
maintenance_work_mem = 1GB
max_connections = 150
wal_level = replica
checkpoint_timeout = 30min
max_wal_size = 4GB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_io_concurrency = 200
autovacuum_max_workers = 5
log_min_duration_statement = 1000
shared_preload_libraries = 'pg_stat_statements'📚 参考工具
- PGTune：根据硬件生成配置建议
- PostgreSQL Official Docs
- Depesz Check Config
如提供具体硬件配置（CPU、内存、磁盘、负载类型），可进一步定制优化建议。