PostgreSQL 维护这个词在小团队和大公司里完全是两种命运。小团队往往建好库、导完数据、写完业务代码就再也不看一眼大公司则专门养 DBA 做巡检、调优和演练。我两类环境都待过可以负责任地说PostgreSQL 绝不是装上就能放养三年的东西。它本身的工程质量很高但放弃维护的代价也实实在在——表膨胀、事务号回卷、WAL 堆积、复制槽失控任何一个都能让你的服务在毫无预兆的情况下挂掉。这篇文章提供一套可以直接抄作业的维护框架一共十条实践来自我在多个生产环境的踩坑总结。内容覆盖自动清理、统计信息、索引健康、WAL 与归档、备份恢复、监控告警、连接管理、参数调优和版本升级适合正在负责 PostgreSQL 的同学也适合准备把 PostgreSQL 投入生产、想提前避坑的团队。下面我一条一条拆开讲每条都会说清楚为什么要做怎么做不做的后果是什么。1. 维护之前先理解什么是健康的 PostgreSQL1.1 健康检查的三个层次很多刚上手维护的同学第一反应是看磁盘、CPU、内存。这些基础指标当然要看但它们只是最外层。我认为一份合格的 PostgreSQL 健康检查报告至少应该覆盖三个层次缺一不可。最外层是系统资源磁盘剩余量、IO 延迟、CPU 负载、内存水位。这一层的问题最容易被监控工具发现但也最容易被误判。举个例子你看到 CPU 飙到 95%以为是业务流量上涨实际可能是某个 autovacuum 后台进程在扫一张大表。只看 CPU 不看进程列表排查方向从一开始就是错的。中间层是数据库进程与内在机制autovacuum 有没有在跑、WAL 归档是否积压、checkpoint 频率是否合理、复制延时多大、有没有长时间未消费的复制槽。这一层的问题往往在系统资源指标还没异常时就埋下了等资源层报警基本已经晚了半拍。最内层是数据健康度表的膨胀率、dead tuple 数量、索引使用率、统计信息新鲜度、事务号水位。这一层是 PostgreSQL 特有的体检项其他数据库没有完全对应的概念也是被忽略最严重的一层。我遇到的大部分慢查询突然出现的故障根因都在这一层而不是在 CPU 或磁盘上。1.2 预防性维护远比救火式维护值钱我的体会是数据库维护做在平时和等到出事再救的成本相差至少一个数量级。平时花十分钟看一眼复制槽状态可能避免一次通宵抢修每季度做一次备份恢复演练能避免在真正需要恢复时手足无措。这里可以类比汽车保养。你不会等发动机异响才去换机油也不会等刹车片磨光才去检查刹车。数据库同理表膨胀、统计信息过期都是慢病不会一天之内要命但会在某一天集中爆发而且一旦爆发症状会非常凶险。预防性维护的价值不在于当时看起来有用而在于它让你始终对系统有掌控感。维护清单这个东西平时用它觉得多余出事用它才觉得命都是它给的。2. 十条核心维护实践逐条拆解2.1 实践一给 autovacuum 做定制别只靠默认值autovacuum 是 PostgreSQL 自带的自动清理机制默认开启但它不是万能的。默认触发阈值是 50 0.2 × 行数对一张一亿行的大表来说意味着要积累大约两千万 dead tuple 才会触发一次清理。在频繁更新的大表上这个节奏完全跟不上表膨胀就是从这里开始的。我的做法是先分析每张核心表的写入和更新特点再分表设置参数。对于高并发更新的核心表把 autovacuum_vacuum_scale_factor 调到 0.01把 autovacuum_vacuum_threshold 设为 1000让清理动作更积极。同时要检查 autovacuum_work_mem如果让它沿用 maintenance_work_mem遇到大表清理时会捉襟见肘扫表能力明显下降。把这两个参数理顺autovacuum 才算真正干活。还有一个高频踩坑点自动清理跑得太猛会把 IO 打满尤其是机械盘或共享存储。我的经验是配合 autovacuum_cost_delay 做限流比如设置为 10ms让清理任务主动让出部分 IO。清理慢一点没关系优先保证业务查询不受影响这个优先级顺序一定要拎清。2.2 实践二统计信息要新鲜执行计划才不会跑偏PostgreSQL 的查询规划器依赖统计信息估算行数。统计信息过期执行计划就会跑偏该走索引的不走、顺序扫描满天飞都是统计信息过期的杰作。autovacuum 默认会顺带做 ANALYZE但它和 VACUUM 共用同一套触发阈值对高频写入的表依然可能滞后。我印象最深的一次故障一张表每天写入几十万行统计信息却一周没更新。某条查询本来该走索引规划器偏偏选了全表扫描把原本几十毫秒的查询拖到十几秒。定位了半天最后手动执行了一次 ANALYZE执行计划立刻恢复正常。所以我的规则是大批量导入、大批量删除、更新大量行之后必须手动 ANALYZE不能等自动任务。至于具体频率不用教条。数据变化快的核心表可以每小时做一次轻量 ANALYZE普通表跟着 autovacuum 走就行。需要提醒的是ANALYZE 本身也要采样、要扫一部分数据太频繁同样会浪费资源找到适合自己业务的那个节奏就行。2.3 实践三索引是双刃剑必须定期体检索引能加速查询也会拖慢 DML、占用存储。维护里很重要的一项是找出那些从没被用过的索引。pg_stat_user_indexes 视图里idx_scan 字段表示该索引被用来做索引扫描的次数。如果一个索引几周甚至几个月都为零它大概率是个摆设。但我得提醒一句idx_scan 为 0 不代表一定要删。有些索引承担唯一约束有些支撑外键有些是给特定的低频报表查询用的。删之前问自己三个问题这个索引有没有约束作用有没有团队成员的历史查询依赖它删掉后能不能通过 EXPLAIN 做前后对比验证都确认过了再动手。删索引很快但重建索引要花的时间可不少误删之后的代价往往比留着它更大。至于索引碎片化B-tree 索引会因页分裂产生空洞膨胀到一定程度就该 REINDEX。PG 12 之后可以用 REINDEX CONCURRENTLY 在不阻塞读写的情况下重建但要注意它比普通 REINDEX 慢还会消耗更多资源建议在低峰期执行并且事先看一下磁盘剩余空间。2.4 实践四盯紧 WAL 与归档防止磁盘被静默耗尽WAL 是 PostgreSQL 可靠性的基石但失控的 WAL 也是存储杀手。如果启用了归档归档端写不进去pg_wal 目录就会慢慢变大直到磁盘写满、数据库宕机。这个故障最烦人的地方在于它的隐蔽性业务一切正常日志也没有报错只有磁盘空间在悄悄减少。我的巡检清单里有一条死命令随时查看 pg_wal 目录的大小。正常情况下它应该处在一个合理的区间大致是几个 checkpoint 之间的 WAL 量。一旦发现持续膨胀优先排查三件事有没有长事务在阻止 WAL 回收有没有复制槽处于不活跃状态归档命令是不是在报错一个复制槽如果下游长期断连WAL 会一直保留在本地磁盘说满就满这是生产事故级别的问题。另外建议关注 checkpoint 频率。如果日志里大量出现 checkpoint 相关输出说明检查点过于频繁可以考虑调大 max_wal_size 和 checkpoint_timeout。频繁 checkpoint 不仅增加 IO 压力还会让恢复点变多拖慢崩溃恢复时间属于一个容易忽略的隐性成本。2.5 实践五监控事务号水位躲开强制冻结PostgreSQL 的事务号是 32 位总量约 42 亿。虽然看起来很充裕但在高并发系统里消耗比想象中快得多。一旦最老事务号接近回卷点数据库会进入自我保护模式只允许只读操作并且会疯狂触发清理来推进水位。真到这一步基本就是重大事故业务会被直接打断。我维护的库有一条定时任务专门监控每个数据库的事务号水位。阈值的经验值是达到 10 亿就要告警超过 15 亿必须介入干预。这个指标在 pg_database 系统表里就能查SELECT datname, age(datfrozenxid) AS age, datfrozenxid FROM pg_database ORDER BY age(datfrozenxid) DESC;这条 SQL 连一秒钟都用不了但能帮你躲过常见的高并发强制冻结事故。很多人从来没查过这个指标我强烈建议你今天就跑一次。特别要注意的是长事务会拖住 vacuum 导致冻结推进停滞所以长事务管理和 XID 水位监控必须连在一起做缺一个都不行。2.6 实践六备份不是备了就行要能恢复、能计时备份环节我要多说几句因为这是唯一一个平时看不出差别、出事才分高下的维护项。PostgreSQL 生态里有逻辑备份pg_dump、pg_dumpall和物理备份pg_basebackup WAL 归档两条路线适用场景完全不同不能互相替代。我见过不少团队每天用 pg_dump 导一次库就自认为做了万全备份。真到要恢复的时候要么备份文件损坏要么恢复出来的库缺对象、权限对不上。物理备份适合做整库的底线保障逻辑备份适合做特定 schema、特定表的灵活恢复。我现在的标准流程是物理备份打底、逻辑备份补充、每季度做一次完整的恢复演练。恢复性能不能拍脑袋直接拿计时器测。如果恢复需要 8 小时而业务最多只能停 2 小时这个备份方案在 SLA 层面就是不达标的。备份还要防备份本身坏掉。建议给备份文件加校验把备份日志单独纳入监控。备份失败了却没有告警比没做备份更可怕因为你会在最需要它的时候才发现它根本不存在。这是很多团队用真金白银换来的教训。2.7 实践七建立有提前量的监控告警体系没有监控的维护等于盲人摸象。我至少会盯以下几项连接数剩余量、活跃事务数量、慢查询通过 log_min_duration_statement 记录、锁等待时长、复制延迟、磁盘增长曲线。工具层面pg_stat_statements 是找 Top SQL 的最佳入口pgBadger 适合做日志分析Prometheus 加 pg_exporter 是社区最常用的监控组合。但工具只是载体真正重要的是告警阈值的合理性。我见过有人把 CPU 告警设成 95%等收到告警时业务已经卡了十分钟也有人把慢查询阈值设成 10 毫秒结果整天淹没在告警噪音里真出问题时反而没人在意告警了。告警的意义是提前量不是事后的广播。以我自己为例慢查询的观察线通常设 1 秒、告警线设 5 秒复制延迟 30 秒以上才告警磁盘使用率 80% 时预警85% 时严重告警。这些阈值需要结合业务量级动态调整但原则是一致的宁可让告警早点响也不能让故障先到。2.8 实践八连接管理从源头防止雪崩连接数被打满是 PostgreSQL 生产环境最常见的故障之一。PG 是进程模型每个连接对应一个操作系统进程连接数过高会直接推高内存占用和上下文切换开销。很多团队把 max_connections 从 100 调到 1000以为解决了并发问题实际上只是推迟了崩溃的时间。我建议从两端下手。业务端引入连接池比如 PgBouncer把前端连接收敛到一个可控范围数据库端设置合理的 max_connections并定期用 pg_stat_activity 排查睡着的连接。很多应用忘记释放连接日积月累就攒出一堆 idle in transaction 状态把连接池慢慢堵死。用 pg_stat_activity 按 state 分组统计能快速定位是哪个应用在漏连接。更彻底的兜底方案是设置 idle_in_transaction_session_timeout 和 idle_session_timeout让失控会话自动被清理。这两个参数看起来像偷懒方案但生产环境里它们能拦住大部分应用代码缺陷造成的连接泄漏。参数兜底和代码修复要同时推进只做任何一边都留不住长久的稳定。2.9 实践九给配置参数做季度体检PostgreSQL 的默认配置偏向保守设计目标是在什么环境都能装上跑起来代价是性能远非最优。生产环境必须调优而且要定期回顾。我最关注五个参数shared_buffers、work_mem、effective_cache_size、maintenance_work_mem、max_connections。下面这张表是我常用的参考基准以 4GB 内存、SSD 存储的服务器为例生产环境一定要按实测调整不能照搬参数常见基准注意事项shared_buffers内存的 25% 左右超过 8GB 后部分场景收益递减需实测work_mem4~16MB 起步作用于每次排序/哈希过高会被并发放大effective_cache_size总内存的 50%~75%只影响优化器估算不实际分配内存maintenance_work_mem64~512MB用于 VACUUM、CREATE INDEX 等维护操作max_connections依据连接池规模规划过高会放大内存和上下文切换开销work_mem 是最容易翻车的参数。它是每个排序或哈希操作可用的内存如果设成 256MB20 个并发排序就是 5GB 起步直接把内存干爆。我见过 postmaster 被 OOM Kill 直接杀掉的案例恢复过程极其狼狈。所以参数调整一定要拿着监控数据做前后对比一次只改一两个改完用压测或真实流量观察几天。改之前还要确认参数的 context很多参数支持 ALTER SYSTEM 在线修改并 RELOAD但有些需要重启实例搞错变更方式会给自己挖坑。2.10 实践十版本升级与补丁跟得紧更要跑得稳PostgreSQL 每年出一个大版本同时持续推送包含安全修复和 bug 修复的小版本。我一直建议跟进小版本但反对盲目追新大版本。小版本通常可以平滑升级风险低收益明确大版本升级则要从长计议保守一点没有错。大版本升级用 pg_upgrade在支持 --link 模式且数据目录位于同一文件系统时速度非常快几乎不复制数据。但 --link 模式有个隐蔽的坑升级后旧的数据目录不能直接再启动旧版本因为新旧数据文件是硬链接指向同一个 inode。这意味着一旦升级回滚非常困难。所以操作前必须有验证过的备份且必须在 staging 环境完整演练一遍。我自己的标准流程是先在模拟环境用生产库副本恢复跑一遍核心应用测试确认无误后再排维护窗口执行正式升级。版本策略上我推荐生产环境跟随上一个稳定大版本 及时打小版本补丁。比如 PG 17 发布后生产先用 PG 16等 17.1、17.2 这类小版本稳定后再规划迁移。这套策略牺牲了一点新特性尝鲜的机会换来了可预期的稳定性对大多数业务场景来说是划算的。3. 把日常维护武器化工具与自动化3.1 三条撑起日常巡检的 SQL日常维护我不太依赖图形工具更信任几条 SQL 模板。第一是 pg_stat_activity看当前谁在跑、跑了多久、在等什么锁第二是 pg_stat_user_tables看每张表的 seq_scan、idx_scan、n_dead_tup、last_vacuum、last_autovacuum一眼扫过去就知道哪些表长期没被清理第三是 pg_stat_statements把累计耗时最长的查询捞出来逐一分析执行计划。以 pg_stat_user_tables 为例我常用的排查 SQL 是SELECT relname, seq_scan, idx_scan, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables WHERE n_dead_tup 10000 ORDER BY n_dead_tup DESC LIMIT 20;这三张视图配合起来能回答大部分数据库为什么慢的问题。我把它们做成固定模板存在本地排障时直接替换库名和表名就能用。建议你也维护一套自己的运维 SQL 集平时跑一遍熟悉基线异常时才有参照物可以对比。3.2 自动化定时任务的设计原则维护不能靠记性必须靠定时任务。我用 cron 或 systemd timer 做四类任务每小时收集关键指标快照并写入监控表每天早上做一次轻量 ANALYZE每周末做一次膨胀检查和索引使用率报告每天检查备份任务与归档状态。特别提醒一个原则大任务要错峰重活要分散。我踩过坑曾经在凌晨 3 点同时启动备份和全库 ANALYZE结果 IO 被打满凌晨的批量任务全部阻塞最后影响了早晨业务。从那以后我养成了习惯把重任务拆散到不同时段并且每个任务在启动前检查系统负载负载过高就自动跳过并告警。自动化不是把任务全丢给调度器就完事调度策略本身也是维护的一部分。4. 真实排障实录三个让我折腾到凌晨的故障4.1 核心表膨胀到 3 倍查询从秒级恶化到分钟级接手的一个旧系统核心订单表膨胀严重。查 pg_stat_user_tables 发现 n_dead_tup 高达几千万而 autovacuum 一直没跑成原因是一个长事务把最老的快照长时间顶住vacuum 无法清理。定位后我先在应用侧把长事务拆短再在维护窗口执行 VACUUM FULL表物理大小缩了 60%查询性能立刻恢复。这里补充两点普通 VACUUM 不锁表但空间只还给表内部复用不还给操作系统VACUUM FULL 会把空间还给操作系统但会重写表并获取 ACCESS EXCLUSIVE 锁。生产环境要恢复物理空间优先考虑 VACUUM FULL维护窗口内或者 pg_repack在线重建但需要额外空间。普通 VACUUM 能解决膨胀趋势问题VACUUM FULL 才是解决已有膨胀问题的最终手段两者定位完全不同。4.2 复制槽失联WAL 涨到几乎撑爆磁盘另一个印象深刻的故障一个从库断连两天主库对应复制槽一直保留那两天产生的 WALpg_wal 涨到几百 GB。还好磁盘监控在 85% 时发出预警赶在满盘之前定位到问题并清理了失联的复制槽。这个案例给我两条教训一是告警阈值必须足够早90% 再告警基本来不及二是复制槽管理必须制度化用脚本定期检查 pg_replication_slots 中 active 为 false 的槽位超过阈值就报警。复制槽这个东西建了就要有人管没人管的复制槽就是定时炸弹。事后我把检查脚本加进了每周例行任务之后再没出现过同类事故。4.3 idle in transaction 堆积连接池被悄悄堵死还有一次生产连接数告警查 pg_stat_activity 发现大量 state 为 idle in transaction 的会话全是同一个应用进程留下的。这个状态意味着事务已开启但既没 commit 也没 rollback长时间挂着会阻塞 vacuum还可能占着锁不释放。定位到具体应用后我在代码层补了连接的自动释放和超时机制同时在数据库侧设置了 idle_in_transaction_session_timeout。这是典型的应用代码的锅、数据库来背的问题但作为维护者两边都要管代码要写对数据库的兜底参数也要设好。几类高频故障我整理成了一张速查表直接贴在工位上故障现象常见根因排查入口应急处置查询突然变慢统计信息过期 / 表膨胀pg_stat_user_tables EXPLAIN手动 ANALYZE / VACUUM FULL磁盘持续增长WAL 堆积 / 归档失败pg_wal 大小 pg_replication_slots清理复制槽 / 修复归档链路连接数告警连接泄漏 / 长事务残留pg_stat_activity 按 state 分组设置会话超时 / 重启应用释放连接最后说点个人经验。这十条维护实践看起来不少但它们背后其实是同一个思路对数据库保持敬畏并且把敬畏转化成固定动作。你可以先从监控告警和备份演练开始这两个是保命项再逐步补上膨胀检查、事务号水位、连接管理这些进阶项。每踩一个坑就把它写进自己的运维手册注明当时为什么没发现、下次怎么提前发现。我自己的手册已经迭代了好几年每次维护都是照单执行、逐项勾选。养成了这套习惯之后数据库事故真的会变少——不是数据库变好了而是你终于知道了它什么时候会出问题并且赶在问题之前动了手。希望这十条实践也能成为你的起点少走我走过的那些弯路。