PostgreSQL 运维

下面是 “PostgreSQL 运维（Production 运维最佳实践 + 指标体系 + 故障排查）最全指南”，专为生产环境设计（适合大量查询、表分区、TimescaleDB、Zabbix 监控、FastAPI 项目等）。

分为 9 大部分：

PostgreSQL 运行机制与文件结构
日常关键运维任务
常见参数优化
性能监控指标
日志分析与慢查询
高可用与复制
存储与表膨胀管理（VACUUM）
性能排查思路
运维最佳实践总表

1. PostgreSQL 运行机制（必须掌握）

1.1 多进程结构

使用 “1 连接 = 1 进程” 模型：

postgres
  ├── checkpointer
  ├── walwriter
  ├── background writer
  ├── autovacuum launcher
  ├── logical replication launcher
  └── 每个 client 连接一个 postgres 进程

无像 MySQL 一样的线程池，因此必须使用 PgBouncer。

1.2 核心目录结构

PGDATA：

/var/lib/postgresql/17/main/
├── base/           # 表数据文件
├── global/         # 全局元数据
├── pg_wal/         # WAL 日志
├── pg_stat/        # 运行时统计信息
├── pg_xact/        # 事务提交状态
├── postgresql.conf # 主配置
└── pg_hba.conf     # 权限配置

WAL（Write Ahead Log）

Postgres 所有修改必须先写 WAL，保证 ACID 持久性。

2. PostgreSQL 日常运维任务

日常任务列表

任务	说明
监控连接数	防止爆满
监控 autovacuum	最重要
检查膨胀（bloat）	大表膨胀严重会性能下降
分析慢查询	explain / auto_explain
阶段性 ANALYZE	保证优化器准确
备份	pg_dump / pg_basebackup
主从复制监控	延迟、断链
磁盘空间检查	pg_wal 可能爆炸

3. PostgreSQL 性能核心参数调优（实战配置）

以下是 2G~32G RAM 的通用调优（比默认好 10x）。

3.1 内存相关

shared_buffers

PostgreSQL 的数据库缓存（不是 OS Cache）

一般设为 25% 内存

2G 机器 -> 512MB
16G 机器 -> 4GB

work_mem

单个排序 / 哈希操作的临时内存，太大会爆内存

常见值：

work_mem = 32MB 或 64MB

如果有大量并行排序，应降低。

maintenance_work_mem

VACUUM / CREATE INDEX 的内存

可设大一点：

maintenance_work_mem = 1GB

3.2 WAL 相关

wal_level = replica
max_wal_size = 4GB~16GB
min_wal_size = 1GB
checkpoint_timeout = 10min

避免频繁 checkpoint（昂贵）。

3.3 自动清理（autovacuum）

最重要参数之一：

autovacuum = on
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_max_workers = 5
autovacuum_naptime = 10s

大表可单独配置：

ALTER TABLE up_resaon SET (autovacuum_vacuum_scale_factor = 0.02);

4. PostgreSQL 监控指标（Zabbix、Prometheus 必备）

关键指标：

4.1 连接状态

SELECT * FROM pg_stat_activity;

要监控：

active 过多
idle in transaction（最大问题）
waiting（锁等待）

4.2 锁等待

SELECT * FROM pg_locks;

有死锁必须处理。

4.3 autovacuum 状态

SELECT * FROM pg_stat_user_tables;

重点字段：

n_dead_tup（死行数量）
last_autovacuum
vacuum_count

死行太多 -> 性能下降。

4.4 表膨胀（Bloat）

pgstattuple
pg_bloat_check()

膨胀严重会影响查询速度。

5. PostgreSQL 日志与慢查询分析

5.1 开启慢查询日志

log_min_duration_statement = 500ms

分析慢 SQL：

EXPLAIN ANALYZE <SQL>;

5.2 auto_explain

自动记录慢执行计划：

shared_preload_libraries = 'auto_explain'
auto_explain.log_analyze = on
auto_explain.log_min_duration = 500ms

实际运维非常管用。

6. PostgreSQL 高可用（HA）

最推荐方案（企业级）：

Patroni + etcd + HAProxy

自动 failover
一致性高
企业使用最多

原生流复制（Streaming Replication）

primary <-- WAL --> replica

复制类型：

同步复制（强一致性）
异步复制（高性能）

逻辑复制（Logical Replication）

支持按表、按行过滤，更灵活。

7. 表膨胀、VACUUM 与存储管理

PostgreSQL 最大的运维成本就是 膨胀（bloat）处理。

7.1 VACUUM 机制

类型	作用
VACUUM	清理死行
VACUUM FULL	表重写（会锁表）
AUTOVACUUM	自动清理

不要在生产环境手动执行 VACUUM FULL（会阻塞写入）

除非磁盘空间不够。

8. PostgreSQL 性能排查思路（最重要）

优先级从高到低：

1）检查连接数/阻塞

SELECT * FROM pg_stat_activity;

特别关注：

idle in transaction
waiting 锁
query 持续时间

2）检查锁竞争

SELECT * FROM pg_locks;

3）查看慢 SQL

通过日志
通过 auto_explain
通过 pg_stat_statements（强烈推荐）

CREATE EXTENSION pg_stat_statements;

4）检查执行计划

EXPLAIN ANALYZE SQL;

重点关注：

顺序扫描（Seq Scan）
行数估计偏差（rows vs actual rows）
大排序 / 哈希

5）检查膨胀

pg_bloat_check()

6）检查 autovacuum 是否正常运行

9. PostgreSQL 运维最佳实践（总结版）

默认使用 text，而不是 varchar(n)
JSON 一律使用 jsonb
表达到千万级，开启分区
时序数据 -> 使用 TimescaleDB（你当前场景非常适合）
必须部署连接池 PgBouncer
打开 pg_stat_statements
每天检查 autovacuum
备库监控 WAL 延迟
勿随意 VACUUM FULL
WAL 单独放 SSD 卷
大表创建索引时使用 CONCURRENTLY
高频写入表不要创建太多索引
JOIN 字段必须索引
避免长事务（idle in transaction）