1. 维护的起点先看懂PostgreSQL的进程与内存模型很多人拿到PostgreSQL第一件事就是找配置文件调参数或者直接在业务高峰期跑一条ALTER TABLE。但真正把日常维护做出体系的人第一步通常是先花半天时间搞清楚这个数据库是怎么活着的——进程怎么协作、内存怎么分配、数据怎么写进磁盘。这不是学院派的要求而是因为所有日常维护动作本质都是在这套模型上做操作。PostgreSQL的架构是多进程架构和Oracle的SGAPGA那种多线程模型完全不同。它有一个主进程叫postmaster负责监听端口、管理子进程、处理崩溃恢复。其他辅助进程各司其职checkpointer定期做检查点bgwriter负责把脏页刷到磁盘walwriter专门写预写日志autovacuum launcher负责调度清理任务stats collector收集统计信息。你自己连上来跑的每个会话在PG里都是一个独立的backend进程。这个架构意味着什么意味着任何一支SQL出问题最坏情况下只是杀掉一个backend进程而不会拖垮整个实例。但同时进程多、内存共享的复杂度也高。日常维护里你看到的shared_buffers、work_mem、maintenance_work_mem全是在这张进程图上做文章。shared_buffers是PG的共享缓存池所有进程读到的数据页都会缓存在这里。它的大小直接影响命中率但也别无脑调大。我在生产环境里见过有人把shared_buffers配到内存的80%结果频繁做检查点时整库抖动。经验值通常是物理内存的25%左右超过这个比例后收益会边际递减反而因为PG的共享内存要整块分配太大容易触发内核参数限制。work_mem是每个backend进程做排序、哈希操作时能用的私有内存。这个参数要特别小心因为它是“乘数”——如果同时有100个并发会话在跑带ORDER BY的查询每个都分到64MB那就是6.4GB。日常维护时通过pg_stat_activity看到一堆进程内存暴涨多数时候都是work_mem叠加出来的。还有wal_buffers默认值其实偏保守在写密集场景建议调大但也不能超过shared_buffers的1/32这个隐性的上限约束。这些概念不是背诵用的而是排查问题时的定位地图。比如数据库突然变慢你先看是CPU飙升、IO等待还是锁等待再对应到是backend进程、bgwriter还是锁管理的问题。没有这个模型维护就是盲人摸象。2. 每个工作日必看的四个指标日常维护听起来很宽泛但落到执行层面每天打开数据库其实就看几件事。我的习惯是固定一套SQL清单连数据库后先跑一遍耗时不超过五分钟却能覆盖80%的隐患。2.1 连接数与连接耗尽风险连接耗尽是最常见的生产事故之一。应用侧连接池配置不当、某个SQL卡死导致会话不释放都会把连接数顶到max_connections这时候新连接会被直接拒绝业务马上报错。SELECT count(*) AS total_conn, count(*) FILTER (WHERE state active) AS active_conn, count(*) FILTER (WHERE state idle) AS idle_conn, count(*) FILTER (WHERE state idle in transaction) AS idle_in_tx_conn FROM pg_stat_activity;这里最需要警惕的是idle in transaction。它表示事务已经开启但还没提交连接一直占着对应的行锁也不释放。如果这个数字长期大于0要么是应用代码里忘了提交事务要么是ORM框架的事务边界设置有问题。2.2 长事务与年龄增长PostgreSQL里有个很重要的概念叫事务ID回卷。每个事务都会分配一个递增的XID而XID是32位整数总有耗尽的一天。PG用“事务年龄”当前事务ID减去元组创建时的事务ID来衡量风险年龄越接近2^31越要防止回卷导致的历史数据不可见。SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY xid_age DESC;任何数据库的年龄超过10亿就该认真处理了。做法是执行VACUUM把老元组标记为冻结。如果年龄超过15亿数据库会强制开启autovacuum_freeze_max_age机制期间性能会显著下降。日常维护里最容易忽略的就是这个因为平时看不到一旦告警就是火警级别。2.3 磁盘空间与膨胀率数据目录、WAL目录、表空间所在文件系统这三处空间要天天看。尤其是WAL目录如果归档脚本断了WAL文件会无限累积磁盘很快被打满。PG的数据段文件默认1GB一个表膨胀后文件数量会异常增多。SELECT pg_size_pretty(pg_database_size(current_database())) AS db_size;每个表的空间占用和实际数据量的比例也可以查SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, n_live_tup, n_dead_tup FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;n_dead_tup是理解膨胀的关键数字。它是表里已经更新或删除但还没被清理的死元组数量。死元组太多查询时扫描的页面就多CPU和IO都被浪费性能自然下滑。2.4 复制状态与主从延迟如果你搭了主从架构看复制健康是每天必做的。pg_stat_replication视图会告诉你每个备库当前收到的WAL位置、写入的WAL位置、以及同步还是异步模式。SELECT client_addr, state, sync_state, pg_wal_lsn_diff(pg_current_wal_lsn(), write_lag) AS write_lag_bytes FROM pg_stat_replication;异步复制下备库延迟几秒很正常但如果持续增长就要查是网络带宽问题还是备库IO跟不上。同步复制虽然不会丢数据但主库每个提交都要等备库确认延迟会直接放大到业务侧。3. 备份恢复没验证过的备份不算备份我在这个行业里听过太多惨案某个团队坚持每天用pg_dump做备份结果从没做过恢复演练等真的需要恢复时才发现dump文件因为磁盘坏道早就损坏了。所以说日常维护里备份方案和恢复演练必须是一对二者缺一不可。3.1 合理选型逻辑备份还是物理备份pg_dump属于逻辑备份导出的是一堆SQL和COPY数据适合小库、跨版本迁移、局部数据恢复。它的局限性也很明显大数据量下导出耗时极长而且因为是在线导出备份期间的事务快照无法保证与其他备份完全一致。物理备份用pg_basebackup直接复制整个数据目录加上WAL归档。它做的是二进制级复制恢复时能把数据库精确还原到某个时间点配合归档日志可以实现PITR时间点恢复。生产环境凡是数据量上了几百GB的我都会建议走物理备份路线pg_basebackup -h 127.0.0.1 -U replicator -D /backup/base/$(date %Y%m%d) -X stream -P-X stream表示在备份过程中同步收集WAL这样得到的是一个一致的备份集。注意这里使用的系统用户需要有pg_basebackup权限一般是通过pg_hba.conf里配置的复制用户可以做到。3.2 WAL归档配置的细节光有基础备份还不够因为基础备份只是某个时间点的快照。想恢复到最后几分钟的数据必须依赖WAL归档。归档配置在postgresql.conf里archive_mode on archive_command test ! -f /backup/wal/%f cp %p /backup/wal/%f archive_timeout 300archive_command里的%p是源WAL文件路径%f是文件名。加test ! -f是为了防止同一个WAL文件被重复归档。我见过有人忽略这层判断结果归档目录里全是覆盖的旧文件恢复时发现关键日志缺失。archive_timeout 300的含义是即使业务不活跃、WAL文件迟迟不切换也强制每5分钟切换一次并归档。设置了它崩溃时最多丢失5分钟数据方便估算RPO。3.3 定期恢复演练的正确姿势备份验证不该是半年一次更不该是出事才做。我个人建议至少每季度做一次完整的恢复演练具体步骤是准备一台与生产配置接近的临时实例把最新的基础备份解压到数据目录在recovery.signal文件PG 12中加入恢复目标比如恢复到最后归档点启动实例观察日志里是否有异常检查关键表的数据条数与生产侧是否一致这整套流程走一遍才能真正确认备份链路是通的。平时维护记录里也该写明上次成功恢复演练的时间、恢复耗时、数据校验结果。出了事情时这页纸就是救命稻草。4. 膨胀与autovacuum维护中最容易翻车的环节如果说日常维护里哪个环节最容易被忽略、又最影响生产性能我会把票投给autovacuum。PG的MVCC机制决定了更新和删除不会修改原数据而是生成一个新的元组版本旧版本就留在数据页里靠vacuum机制在后台清理。autovacuum就是那个自动做的“清洁工”。4.1 为什么默认参数会不够用PG的默认配置里autovacuum的触发阈值是50 0.2 * 表行数也就是说单表有100万行时需要积累25万死元组才会触发。这个阈值对跑批任务、频繁更新的业务来说太迟钝常常是表已经膨胀得很严重了才开始清。我在项目里踩过一次很深的坑。那是一个订单流水表每天半夜有个批处理会UPDATE几十万行。白天系统正常但持续一周后表从80GB膨胀到了230GB查询从几百毫秒变成几十秒。打开pg_stat_user_tables一看n_dead_tup超过4000万而上次autovacuum时间已经是12天前——因为它一直达不到默认阈值。这个案例让我彻底改变了对autovacuum参数的维护策略。现在的习惯是autovacuum_vacuum_scale_factor 0.05 autovacuum_vacuum_threshold 5000 autovacuum_vacuum_cost_delay 20ms autovacuum_vacuum_cost_limit 2000scale_factor从0.2降到0.05意味着更新更频繁的表会被更早触发清理。cost_delay和cost_limit的搭配控制清理的IO开销默认值偏保守调得激进一点能加快清理速度但也不能太猛否则会挤占业务的IO带宽。4.2 手动VACUUM什么时候做、怎么做虽然autovacuum是默认开启的但有些场景它帮不上忙大批量DELETE之后死元组瞬间暴涨autovacuum需要时间才能跟上频繁UPDATE的索引列索引页膨胀不比堆表轻执行完pg_terminate_backend()杀掉的会话事务回滚留下的死元组这时候就需要手动介入VACUUM (VERBOSE, ANALYZE) your_table_name;重点提醒普通VACUUM不会把空间还给操作系统它只是标记空间可以复用。如果表膨胀得厉害且需要物理缩小文件大小就得用VACUUM FULL。但这操作会持有ACCESS EXCLUSIVE锁期间表完全不可读写只能在维护窗口做。4.3 膨胀的检测与根治检测膨胀最直接的方法是看文件实际大小和表内有效数据的比值。除了pg_stat_user_tables里的死元组你还可以装pgstattuple扩展CREATE EXTENSION pgstattuple; SELECT * FROM pgstattuple(your_table_name);输出的dead_tuple_percent如果超过20%这表就该重点处理了。膨胀的根治手段通常是重建表常见的做法VACUUM FULL最简单但锁表pg_repack在线重建不锁写但需要额外安装和暂停写入的时间窗口业务侧做一次迁移建新表、导数据、切换依赖最后这个听起来工程量大但遇到几十亿行的大表时往往是最稳妥的。5. 性能维护慢查询、索引与统计信息日常维护不只是“不宕机”还包括“持续跑得快”。这个快不是靠运气而是靠定期观察和调整。5.1 慢查询日志怎么配置才不被淹没开启慢查询日志是第一步但配置不对会带来两个问题日志太吵没人看或者日志太小什么都查不到。我的基本配置log_min_duration_statement 1000 log_line_prefix %t [%p] %q%u%d log_checkpoints on log_connections on log_disconnections on lock_timeout 5s1000表示超过1秒的SQL才记录。对于核心交易系统这个值可以压到500ms甚至200ms但要评估日志写入带来的开销。log_line_prefix里带上了用户名和数据库名排查问题时一眼就能看出是哪个应用的SQL。比日志更重要的是pg_stat_statements扩展它能把SQL的执行次数、总耗时、平均耗时、缓存命中率统计成一张视图是找性能瓶颈的一把好手。CREATE EXTENSION pg_stat_statements;然后查最耗时的SQLSELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;注意total_exec_time的单位是毫秒这能帮你快速锁定“总量贡献最大”的SQL而不是只看单次慢查询。5.2 索引的健康检查与维护索引不是越多越好。维护时至少要关注两类问题第一类是无效索引。如果一个索引的idx_scan长期为0说明优化器从来没用过它它只会拖慢INSERT和UPDATE。这类索引可以直接考虑删除。SELECT schemaname, relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan 0 ORDER BY relname;第二类是索引膨胀。更新索引列时旧索引项不会原地删除而是索引页里堆垃圾。膨胀严重的索引查询时IO反而更大。重建索引用REINDEX INDEX index_name;如果是在线环境PG 12以上支持REINDEX CONCURRENTLY不会阻塞读写。但要注意它有两种实现路径其中一种会占用额外空间磁盘余量不足时别硬跑。5.3 统计信息失效时的处理优化器依赖统计信息决定执行计划。如果统计信息过时优化器可能选错索引、走错join顺序表现就是本来很快的SQL突然变慢。正常情况下autovacuum和autoanalyze会同步更新统计信息但批量导入、大批量更新后统计信息很可能落后于实际数据。手动更新统计信息是维护的老手艺ANALYZE table_name;做完之后最好再跑一遍业务方反馈的慢SQL对比执行计划是否变化。这一步经常能解决“没改任何代码但SQL突然变慢”的诡异问题。6. 日志与告警让问题在爆发前被听见上面聊的大部分都是被动排查但一个成熟的日常维护体系里主动发现和主动预防的权重更高。想要做到这一点日志和告警是不可或缺的。6.1 PostgreSQL日志里那些值得关注的线索PG的日志默认写到数据目录的log子目录里但纯文本日志难以检索。我的习惯是开启CSV日志logging_collector on log_destination csvlog log_directory pg_log log_filename postgresql-%Y-%m-%d_%H%M%S.log log_rotation_age 1d log_rotation_size 100MBCSV格式可以直接导入到分析工具或表格里按时间、用户、数据库、错误严重级别做筛选。运维时我常看这几类内容ERROR级别以上的错误尤其是deadlock detected、out of memory、could not write blockWARNING级别的锁等待、连接数过高的提示连接断开时的异常码比如FATAL: terminating connection due to idle-in-transaction timeout6.2 告警指标的阈值设定告警不是设得越多越好而是设得有业务含义。以连接数告警为例如果max_connections300告警阈值设在260比较合理留出足够缓冲。会话空闲但持有事务锁的比例高于20%就该排查应用侧事务逻辑。磁盘和WAL的要特别注意。WAL目录如果超过了基础备份到归档点之间的预期大小通常说明归档链路有问题。我见过一个案例归档服务器磁盘满了archive_command一直报错主库的WAL持续累积最后主库磁盘100%直接宕机。针对这个告警应该同时盯两个点归档命令是否成功以及WAL目录大小。6.3 从日志噪音中找到真正的故障前兆日志里的噪音很多比如正常的连接断开、错误密码尝试。如果你设置了log_connections和log_disconnections每次应用重启都会刷出一堆日志久了人就麻木了。我的建议是日志保留权限分层全量日志只保留7天用于深度回溯巡检时只看当天的ERROR和FATAL通过脚本自动聚合真正重要的告警走外部监控系统如短信或IM机器人通知不让它埋在日志文件里只有让“主动发现”变得轻松日常维护才不会是走过场而是真正能提前抓住故障的苗头。7. 版本升级与迁移维护不是原地踏步日常维护如果只有“维持现状”总有一天会被现状淘汰。PostgreSQL社区的版本迭代非常快每一年都有重要更新。9.6之前用pg_upgrade做跨大版本升级还要小心翼翼现在的流程已经成熟很多但依然有坑。7.1 升级前必做的五件事确认升级路径大版本只能往上跳不支持降级读一遍官方Release Notes看目标版本有没有已知行为变更在测试环境完整跑一遍升级流程包括所有扩展的兼容性验证检查第三方扩展比如PostGIS、pg_cron确认版本支持安排业务侧的验证清单升级后逐项核对7.2 用pg_upgrade跑一次升级pg_upgrade的核心原理是新老实例并存用新的bin目录直接读取旧数据目录完成升级。流程大致是# 先停业务做一次干净的备份这里用物理备份或pg_dump都行 # 然后用新的bin目录执行检查 /usr/pgsql-15/bin/pg_upgrade \ -b /usr/pgsql-14/bin \ -B /usr/pgsql-15/bin \ -d /var/lib/pgsql/14/data \ -D /var/lib/pgsql/15/data \ -o -c config_file/var/lib/pgsql/14/data/postgresql.conf \ -O -c config_file/var/lib/pgsql/15/data/postgresql.conf \ --check # 检查通过后去掉 --check 真正执行升级完成后还要记得重建统计信息、更新扩展ANALYZE;另外要特别提醒pg_upgrade不能跨操作系统架构比如Linux x86_64不能直接升级到ARM上的版本这类场景需要逻辑导出导入。7.3 新版本带来的新机会每次升级不只是追新更是维护工具的进化。比如PG 13引入的增量排序、PG 14引入的并行建索引改进、PG 15新增的MERGE语法、PG 16对逻辑复制的性能提升。日常维护表上PG 15以后pg_basebackup默认走流复制协议配置更简单PG 16还支持了pg_stat_io视图可以更精细地观察IO行为。升级完成后把旧版本里的临时优化清一遍该用的新特性引入进来维护工作才算真正闭环。8. 我在多次维护项目中沉淀的经验清单说了这么多最后把那些没法塞进前面章节的零碎经验整理一下。它们单拎出来都很小但组合在一起能省掉很多半夜被叫醒的麻烦。变更必有预案预案必有回滚。每一次参数调整、索引重建都先写清楚执行前的基线数据和回滚语句。我在生产上调work_mem前会把旧值存到一个维护记录表里出了问题一键恢复。核心库和非核心库的维护门槛分开。核心库的DDL操作必须走审批流非核心库在非高峰时段可以直接执行。统一标准看似安全实际上会因为流程繁琐导致该做的维护被拖延。连接池和max_connections一定要匹配。很多连接耗尽的问题根源是应用侧的连接池最大连接数远大于数据库允许值。维护时两边一起查比单调数据库参数有效得多。监控不是越密越好但备份是越勤越好。连接的采集频率每分钟一次足够但WAL归档和基础备份的间隔要结合RPO要求来设。达不到预期就升级方案不能指望“扛一扛就过去了”。每周挑一个低峰期做一次只读的压测。不用搞很重的工具用pgbench -S跑几分钟记录TPS和延迟。和上周、上个月对比趋势比单点绝对值重要——性能下降从来不是突发都是缓慢累积的。PostgreSQL的日常维护说到底是“把正确的事重复做”。每天看指标每周看趋势每季度做恢复演练每次升级做足准备这套方法没有任何玄学但能帮你把绝大多数故障扼杀在发生之前。真等到页面打不开、报错满天飞的时候再去查那就不叫维护叫救火了。