企业官网建设流程全解析

1. 维护的起点：先看懂PostgreSQL的进程与内存模型

很多人拿到PostgreSQL，第一件事就是找配置文件调参数，或者直接在业务高峰期跑一条ALTER TABLE。但真正把日常维护做出体系的人，第一步通常是先花半天时间搞清楚这个数据库是怎么活着的——进程怎么协作、内存怎么分配、数据怎么写进磁盘。这不是学院派的要求，而是因为所有日常维护动作，本质都是在这套模型上做操作。

PostgreSQL的架构是多进程架构，和Oracle的SGA+PGA那种多线程模型完全不同。它有一个主进程叫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 = 300

archive_command里的%p是源WAL文件路径，%f是文件名。加test ! -f是为了防止同一个WAL文件被重复归档。我见过有人忽略这层判断，结果归档目录里全是覆盖的旧文件，恢复时发现关键日志缺失。

archive_timeout = 300的含义是：即使业务不活跃、WAL文件迟迟不切换，也强制每5分钟切换一次并归档。设置了它，崩溃时最多丢失5分钟数据，方便估算RPO。

3.3 定期恢复演练的正确姿势

备份验证不该是半年一次，更不该是出事才做。我个人建议至少每季度做一次完整的恢复演练，具体步骤是：

1. 准备一台与生产配置接近的临时实例
2. 把最新的基础备份解压到数据目录
3. 在recovery.signal文件（PG 12+）中加入恢复目标，比如恢复到最后归档点
4. 启动实例，观察日志里是否有异常
5. 检查关键表的数据条数与生产侧是否一致

这整套流程走一遍，才能真正确认备份链路是通的。平时维护记录里也该写明：上次成功恢复演练的时间、恢复耗时、数据校验结果。出了事情时，这页纸就是救命稻草。

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 = 2000

scale_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 = 5s

1000表示超过1秒的SQL才记录。对于核心交易系统，这个值可以压到500ms甚至200ms，但要评估日志写入带来的开销。log_line_prefix里带上了用户名和数据库名，排查问题时一眼就能看出是哪个应用的SQL。

比日志更重要的是pg_stat_statements扩展，它能把SQL的执行次数、总耗时、平均耗时、缓存命中率统计成一张视图，是找性能瓶颈的一把好手。

CREATE EXTENSION pg_stat_statements;

然后查最耗时的SQL：

SELECT 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 = 100MB

CSV格式可以直接导入到分析工具或表格里，按时间、用户、数据库、错误严重级别做筛选。运维时我常看这几类内容：

• ERROR级别以上的错误，尤其是deadlock detected、out of memory、could not write block
• WARNING级别的锁等待、连接数过高的提示
• 连接断开时的异常码，比如FATAL: terminating connection due to idle-in-transaction timeout

6.2 告警指标的阈值设定

告警不是设得越多越好，而是设得有业务含义。以连接数告警为例，如果max_connections=300，告警阈值设在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. 我在多次维护项目中沉淀的经验清单

说了这么多，最后把那些没法塞进前面章节的零碎经验整理一下。它们单拎出来都很小，但组合在一起，能省掉很多半夜被叫醒的麻烦。

1. 变更必有预案，预案必有回滚。每一次参数调整、索引重建，都先写清楚执行前的基线数据和回滚语句。我在生产上调work_mem前会把旧值存到一个维护记录表里，出了问题一键恢复。
2. 核心库和非核心库的维护门槛分开。核心库的DDL操作必须走审批流，非核心库在非高峰时段可以直接执行。统一标准看似安全，实际上会因为流程繁琐导致该做的维护被拖延。
3. 连接池和max_connections一定要匹配。很多连接耗尽的问题，根源是应用侧的连接池最大连接数远大于数据库允许值。维护时两边一起查，比单调数据库参数有效得多。
4. 监控不是越密越好，但备份是越勤越好。连接的采集频率每分钟一次足够，但WAL归档和基础备份的间隔要结合RPO要求来设。达不到预期就升级方案，不能指望“扛一扛就过去了”。
5. 每周挑一个低峰期，做一次只读的压测。不用搞很重的工具，用pgbench -S跑几分钟，记录TPS和延迟。和上周、上个月对比，趋势比单点绝对值重要——性能下降从来不是突发，都是缓慢累积的。

PostgreSQL的日常维护，说到底是“把正确的事重复做”。每天看指标，每周看趋势，每季度做恢复演练，每次升级做足准备，这套方法没有任何玄学，但能帮你把绝大多数故障扼杀在发生之前。真等到页面打不开、报错满天飞的时候再去查，那就不叫维护，叫救火了。