PostgreSQL 运维最佳实践

下面是一份体系化、生产可落地的 PostgreSQL 运维最佳实践，覆盖安装、配置、监控、备份、性能、容量、安全、高可用等关键内容。内容非常实用，适合新建生产集群或优化现有集群。

系统层面
安装与目录规划
配置文件最佳实践
连接管理
性能优化（CPU/内存/IO）
统计信息与 Vacuum 维护
监控与告警
日志管理
备份与恢复
安全
高可用（HA）
扩展与插件管理
常见运维命令
日常运维 checklist

1. 🖥️ 系统层面最佳实践

操作系统

项	最佳实践
Linux 发行版	Debian/Ubuntu、RHEL/AlmaLinux/Rocky
运行用户	使用独立用户 postgres
时钟	chrony 同步时间，避免主备漂移
I/O 调度	deadline（SAS）、none（NVMe）
文件系统	xfs 或 ext4
Swappiness	vm.swappiness = 1（避免换出）
Hugepage	建议关闭（除非 PostgreSQL 大共享内存配置场景）

2. 📁 目录结构规划

/data/postgresql/
  ├── data/        # PGDATA
  ├── wal/         # WAL 独立盘强烈建议
  ├── backup/      # 备份
  └── log/         # 日志

**WAL 强烈建议放独立 SSD/NVMe，**提升事务吞吐。

3. ⚙️ PostgreSQL 配置最佳实践

以下以 PostgreSQL 14–17 通用配置为主。

3.1 基础配置

shared_buffers = 25% RAM
work_mem = 64MB
maintenance_work_mem = 1GB
wal_level = replica
max_wal_size = 8GB
checkpoint_timeout = 15min
effective_cache_size = 70% RAM

解释：

shared_buffers 25% 是经验值
effective_cache_size 告诉优化器文件系统缓存容量
work_mem 过大会导致 OOM（巨坑）

3.2 WAL & checkpoint

max_wal_size = 8GB
min_wal_size = 2GB
checkpoint_completion_target = 0.9

保证 checkpoint 平滑，不阻塞。

3.3 并发/连接池

不要用 PostgreSQL 作为连接池。

max_connections = 200

如果业务很多：

使用 PGBouncer（强烈推荐）

3.4 Autovacuum 配置

autovacuum = on
autovacuum_vacuum_cost_limit = -1
autovacuum_max_workers = 4

如果高更新量系统：

autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

4. ⚡ 性能优化（CPU / 内存 / 磁盘）

4.1 CPU

PostgreSQL 是单线程执行查询，跨查询并行
提升性能的方法：
- 提升 CPU 主频
- 增加并行配置：

max_parallel_workers = 8
max_parallel_workers_per_gather = 4

4.2 内存

shared_buffers = 25%
work_mem 不要设置太高（每个排序都用一次）

大坑案例：

work_mem=512MB，SQL 同时 20 条并行 -> 内存飙升至 10GB -> OOM -> PostgreSQL 崩溃。

4.3 磁盘

WAL 写入吞吐较高：建议 NVMe
数据目录可使用 RAID10 或 NVMe
Filesystem：XFS > EXT4

5. 🧹 Vacuum / Autovacuum / Freeze 维护

这部分是 PostgreSQL 运维核心。

5.1 为什么需要 Vacuum？

PostgreSQL 是 MVCC
update/delete 不立即删除旧数据
长时间不 Vacuum -> 表膨胀 -> 性能暴跌

5.2 定期 Vacuum 手动巡检

VACUUM VERBOSE table;
ANALYZE table;

5.3 检查膨胀率

扩展：pgstattuple

SELECT * FROM pgstattuple('mytable');

6. 📈 监控与告警

推荐监控系统：Prometheus + Grafana + postgres_exporter

关键指标：

类别	指标
连接	active connections、idle in transaction
锁	lock time、deadlocks
WAL	write rate、replication lag
Autovacuum	freeze age、running workers
IO	read/write latency
事务	TPS、XID 使用情况

7. 📜 日志最佳实践

logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'

log_min_duration_statement = 500ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

日志必须轮转（logrotate）。

8. 🔐 安全最佳实践

8.1 网络安全

listen_addresses = '0.0.0.0'

pg_hba.conf 配置基于 IP + SSL

8.2 权限管理

业务库必须独立用户
禁止使用超级用户操作业务
重要表只读/写分离权限

9. 💾 备份与恢复（核心）

9.1 全量备份

pg_dump
pg_basebackup

9.2 增量备份（WAL）

使用：

pgBackRest（最推荐）
WAL-G

9.3 PITR（按时间点恢复）

restore_command = 'cp /backup/wal/%f %p'

10. 💠 高可用（HA）最佳实践

推荐方案（生产优先级排列）：

10.1 Patroni + Etcd/Consul（最强推荐）

自动 failover
自动 leader 选举
支持复制槽
生产 BAT / 金融大量使用

10.2 Stolon

轻量级 HA 管理器

10.3 手工流复制（适合小型系统）

primary_conninfo = 'host=... user=replica'

11. 🧩 扩展与插件管理

常用扩展：

pg_stat_statements
pgstattuple
uuid-ossp
citext
hstore
ltree

启用：

CREATE EXTENSION pg_stat_statements;

12. 🔧 常见运维命令

重载配置（不重启）

SELECT pg_reload_conf();

查看当前配置

SHOW ALL;

查看连接

SELECT * FROM pg_stat_activity;

终止连接

SELECT pg_terminate_backend(pid);

13. 📋 日常运维 Checklist（可直接用于生产 SOP）

每天

检查连接数、锁
检查复制延迟
检查磁盘剩余空间

每周

手动 vacuum 大表
检查 autovacuum 情况
检查 XID wraparound 风险

每月

执行全库 ANALYZE
清理旧 WAL、旧备份
依赖扩展更新/版本检查