postgres

PostgreSQL 运维

使用此技能可以安全地运维 PostgreSQL 数据库引擎：根据工作负载审查配置，查找索引和查询计划问题，管理 vacuum 和膨胀，设置并验证 WAL 归档和时间点恢复，采用可靠的故障转移方案运行复制，处理扩展，规划主版本升级，并通过证据诊断故障。这是针对一种指定工具（PostgreSQL）的工具技能。数据库方法论——跨数据库引擎的备份策略、迁移模式、SQL 分析模式——属于 data-engineering；应用层数据访问模式属于 backend-engineering；架构和数据建模属于 data-architect 和 data-engineering。Supabase 平台管理属于 supabase。

运维约定

1. 任何变更前先进行只读探查。 首先检查配置、目录统计信息和日志。随附的 pgdiag 脚本会收集只读证据，并以 default_transaction_read_only=on 打开每个会话。
2. 操作前确认目标、范围和回滚路径。 无需确认即可进行只读探查。变更操作——例如执行 pg_ctl 停止、提升为主库、安装扩展或运行 pg_upgrade——都必须获得明确的人类指令，并且指令中要指出实例。
3. 备份不等于可恢复性证据。 按计划在临时实例上验证恢复；切勿仅凭备份日志就声称可以恢复。
4. 控制证据范围。 概述目录查询结果和日志摘录；切勿在对话中倾倒完整日志、postgresql.conf 或包含密码的连接字符串。
5. 在交付边界进行验证。 SELECT 1 的结果只能证明连接正常，不能证明运行健康；重放 pg_basebackup 只能证明可恢复性，不能证明今天的 WAL 正在归档。

pgdiag 脚本

scripts/pgdiag 是面向 Agent 的只读诊断收集器。它会调用 psql，以 default_transaction_read_only=on 打开每个会话，并输出有界的 JSON。即使没有服务器且未安装 psql，也可以使用 --help。

scripts/pgdiag --help                      # no cluster needed
scripts/pgdiag --json                      # all checks, machine-readable
scripts/pgdiag --host db1 --dbname app --json
scripts/pgdiag --check identity --check wal_archive --json
scripts/pgdiag --plan-for "SELECT * FROM orders WHERE id = 42" --json

退出代码：0 表示正常，1 表示运行时错误／收集错误，2 表示用法错误，127 表示未找到 psql 二进制文件，124 表示超时。--check 会运行指定子集；--plan-for 会为一条只读语句添加 EXPLAIN (FORMAT JSON) 查询计划。脚本不会执行任何更改数据的语句；服务器端的只读会话设置会拒绝任何意外执行的此类语句。

运维流程

1. 识别实例：版本、恢复状态、配置文件位置、连接字符串格式，以及该实例是主库还是备用库。
2. 收集证据：运行 pgdiag --json，检查配置、连接、索引使用情况、膨胀信号、WAL 归档、恢复、复制和数据库大小。
3. 根据症状分诊：将报告的问题与证据对应起来（查询缓慢 → 查询计划和索引使用情况；备份停滞 → 归档器；数据不一致 → 复制延迟）。
4. 经确认后操作：获得人类指令后，执行范围明确、影响受限的变更，并事先说明回滚路径。
5. 验证：重新运行相关检查，并确认交付边界上的可观察结果。

配置

• 运行时的事实来源是 pg_settings，而不是配置文件：SHOW/current_setting() 会反映重新加载和覆盖项（ALTER SYSTEM、命令行 -c、环境变量 PGOPTIONS）。pgdiag 的 config 检查会列出对运维人员至关重要的值。
• 了解哪些更改需要重新加载（pg_ctl reload / SELECT pg_reload_conf()），哪些需要重启：内存相关参数（shared_buffers、max_connections、wal_level、max_wal_senders）需要重启；大多数调优和日志参数可以重新加载。
• 检查 log_destination、logging_collector 和 log_min_duration_statement，确保在需要慢查询证据之前就已记录；启用 track_io_timing=on 后，pg_stat_database 中的 I/O 计时才有意义。
• 连接压力：将 pg_stat_activity 的状态计数与 max_connections 对比；连接池器属于应用架构决策，适用于 backend-engineering。
• GUC 的设置理由、重新加载与重启对照表，以及参数更改审查模式：references/01-configuration.md。

索引与查询计划

• 先看证据：pg_stat_user_indexes 会显示 idx_scan/idx_tup_read/idx_tup_fetch；如果某个表存在筛选条件，却被顺序扫描且 seq_tup_read 很大，那么它可能缺少索引。
• 对实际工作负载查询执行 EXPLAIN (ANALYZE, BUFFERS)，比凭猜测判断更可靠；比较估算行数与实际行数——差异很大，说明规划器统计信息可能已过时（pg_statistic 新鲜度问题），或某个参数设置不当（random_page_cost、effective_cache_size）。
• 未使用的索引（长时间窗口内 idx_scan = 0）会增加写入和维护成本；无效索引（pg_index.indisvalid = false）会在下次 vacuum 时被删除，应有计划地修复或移除。
• 索引选择（BRIN 与 btree、部分索引、覆盖索引）属于模式设计，应由 data-architect 负责；本技能负责衡量和运维现有索引。
• 查询计划解读、索引用法 SQL 探查和计划审查清单：references/02-indexes-and-query-plans.md。

Vacuum 与膨胀

• Vacuum 会回收死元组并刷新规划器统计信息；autovacuum 应自动完成这些工作。确认它确实在运行：检查 autovacuum=on、工作进程数量，以及各表 relfrozenxid/n_dead_tup 的变化趋势。
• 膨胀是表文件大小与有效数据之间的差距：pg_stat_user_tables.n_dead_tup 的增长速度超过 vacuum 的运行频率，是最主要的信号；因 vacuum 失败或跳过而产生的堆膨胀，则表现为 relpages 较大而有效元组较少。
• 如果 autovacuum 跟不上，应安排有针对性且经过确认的维护窗口（对指定表执行 VACUUM，而非不加区分地全面执行），然后查明 autovacuum 落后的原因（长事务、连接饱和、工作进程资源不足）。
• 切勿将 VACUUM FULL 视为例行操作：它会重写表、获取锁，并且需要维护窗口和经验证的备份方案。
• 膨胀测量探查和 autovacuum 调优模式：references/03-vacuum-and-bloat.md。

备份：WAL 归档与时间点恢复

• 恢复模型：基础备份加上连续的 WAL 归档可实现时间点恢复（PITR）——还原基础备份，并重放归档的 WAL，直到目标时间。
• 恢复目标分为两类：基于时间（pitr|point.in.time 模式是这一类的简写——例如“昨天 02:00”这样的墙上时钟时间目标）和基于位置（特定的 LSN 或时间线标记）。两者都是有效的 recovery_target 输入。
• WAL 归档就绪状态要求 archive_mode=on、archive_command 正常工作且归档进程健康：pg_stat_archiver 中的 archived_count 必须持续增长，failed_count 保持稳定，且 last_failed_wal 为空或记录较早。
• 归档和流复制都要求 wal_level 为 replica（或更高）；更改该设置需要重启。
• 使用 pg_basebackup（或专用工具）进行备份，并确保与 WAL 一致：为每个备份添加标签，记录其 pg_stop_backup() LSN / 时间线，并先使用归档测试还原，再信任该备份。
• PITR 流程、recovery_target 选项、还原到某个时间点的步骤，以及 RPO/RTO 的考量方式（方法见 data-engineering）：references/04-backups-wal-pitr.md。

复制与故障转移

• 流复制：备用节点通过复制槽连接并持续接收 WAL；使用 pg_stat_replication（state=streaming、replay_lsn 保持同步、replay_lag 较小）以及备用节点的恢复状态进行验证。
• 应审慎决定采用同步还是异步复制：同步复制（synchronous_standby_names）以提交延迟换取持久性保证；异步复制在故障转移时可能丢失最近提交的事务。
• 故障转移计划不只是执行 pg_ctl promote：还应明确由谁提升备用节点、如何重定向客户端、旧主节点恢复后如何处理，以及如何验证数据（提升时的延迟、时间线分歧）。
• 提升是一次变更——请先确认目标和范围。使用复制管理工具（Patroni、repmgr）时，应使用其切换命令，而不是手动提升；将旧主节点重新加入为备用节点，绝不可让两个主节点同时写入。
• 流复制配置、复制槽管理、延迟测量和故障转移操作手册：references/05-replication-and-failover.md。

扩展

• 先盘点：pg_extension（已安装）和 pg_available_extensions（可用）会告诉你当前有哪些扩展，以及磁盘上有哪些版本；pgdiag 的 extensions 检查也会执行此操作。
• 安装扩展会更改共享目录，而且某些扩展会以难以撤销的方式更改数据库——安装扩展是一项需要回滚方案的变更，而不是条件反射式地执行 CREATE EXTENSION。
• 主版本升级通常需要在新的二进制文件上重新安装或重新构建扩展（例如 PostGIS、pgvector）；执行 pg_upgrade 前，请查看每个扩展的升级说明。
• 受信任的扩展可由非超级用户安装到其自己的数据库中；扩展策略和共享库可用性属于 platform-engineering 的基础设施决策。
• 常见扩展、生命周期及版本升级注意事项：references/06-extensions.md。

升级

• 次要版本升级是原地替换二进制文件（然后重启）；主要版本升级（例如，从 15 升至 16）会改变磁盘上的格式，需要使用 pg_upgrade 或逻辑转储/恢复。
• 先规划升级路径：阅读整个版本跨度的发行说明和升级指南，检查扩展和不受支持的功能，选择方法（使用链接模式的 pg_upgrade，或逻辑升级），并在具有真实数据形态的临时环境中演练。
• pg_upgrade 是需要停机的变更操作，必须有经过验证的备份：停止写入，使用 --old/--new 二进制文件运行升级，在新集群上运行 analyze，并在退役旧集群之前，从应用层验证。
• 逻辑复制（发布者/订阅者）可作为近乎零停机的主要版本升级路径；这也是一种迁移模式，其方法详见 data-engineering。
• 版本矩阵、升级操作手册和回滚决策：references/07-upgrades.md。

依据证据进行诊断

按证据顺序诊断：身份/版本 → 配置 → 连接 → 索引使用情况和执行计划 → 清理/膨胀 → WAL 归档 → 复制 → 扩展。

• pgdiag --json 可在一个有界载荷中收集第一层证据；任何变更后，都要重新运行受影响的检查。
• 查询缓慢 → 使用 EXPLAIN (ANALYZE, BUFFERS) 和 pg_stat_user_indexes/seq_tup_read；调整 random_page_cost 之前，先检查规划器统计信息是否足够新。
• 备份停滞 → 检查 pg_stat_archiver：failed_count、last_failed_wal，以及归档目标的磁盘/网络。
• 备用服务器落后 → 检查 pg_stat_replication 的延迟列、槽保留情况（pg_replication_slots），以及主服务器与备用服务器之间的网络饱和情况。
• 切勿将相关性说成因果关系：查询缓慢和 n_dead_tup 偏高只是证据，并非诊断结果——应说明测量了什么、发生了什么变化，以及验证了什么。
• 故障模式路由及“症状→探测→修复”表：references/08-diagnostics.md。

参考资料索引

加载时机	参考资料
调优、GUC 审查、重新加载与重启	references/01-configuration.md
查询缓慢、索引使用情况、执行计划审查	references/02-indexes-and-query-plans.md
自动清理、死元组、膨胀测量	references/03-vacuum-and-bloat.md
WAL 归档、基础备份、PITR、恢复演练	references/04-backups-wal-pitr.md
流复制设置、槽、延迟、故障转移操作手册	references/05-replication-and-failover.md
扩展清单与升级注意事项	references/06-extensions.md
次要和主要版本升级、pg_upgrade、回滚	references/07-upgrades.md
按症状查找探测方式的诊断表	references/08-diagnostics.md
来源、版本观察结果、刷新流程	references/00-source-index.md

随附产物

• scripts/pgdiag：只读诊断收集器（仅使用标准库；--json、--check、--plan-for、--help 可在无集群时运行）。
• tests/test_pgdiag.py：针对伪造的 psql 存根的确定性测试，包括只读约束测试。
• references/：九份带日期并按来源索引的参考资料，涵盖上述运维主题。

验证边界

声明	最低证据要求
实例可访问且已确认版本	pgdiag --check identity --json 可解析，并报告版本和恢复状态
配置已知	pgdiag 的 config 检查列出运维人员必须关注的 GUC
归档运行正常	pg_stat_archiver：archived_count 持续增加，failed_count 未上升，last_failed_wal 已过时
复制处于最新状态	pg_stat_replication：state=streaming，且延迟在约定范围内
备份可用于恢复	在临时实例上恢复基础备份，并将 WAL 重放至目标时间
诊断可靠	提出结论前已收集证据，且通过重新运行检查验证了修复效果

严格边界

• 未经人工明确指示目标并说明回滚路径，绝不执行变更操作（pg_ctl stop、提升、pg_upgrade、安装扩展、维护性 VACUUM）。只读探查可随时进行。
• 绝不将未经验证的说法作为证据：说明测量了什么、何时测量以及如何测量。
• 绝不公开完整日志、postgresql.conf 内容或包含密码的连接字符串。
• 绝不使用具备写入权限的会话运行 pgdiag；该工具本身按设计为只读。

不适用场景

• 应用层数据访问模式（应用代码中的连接池、ORM 用法、查询构造、服务中的事务）——请参阅 backend-engineering。
• 架构设计和数据建模（表、键、规范化、维度模型）——请参阅 data-architect 和 data-engineering。
• 跨数据库引擎的方法论（备份策略、迁移模式、分析型 SQL）——请参阅 data-engineering。
• Supabase 平台管理（托管项目、CLI 堆栈、自托管的 Supabase 堆栈）——请参阅 supabase；不使用 Supabase 约定的常规 PostgreSQL 操作属于此处。要衡量 Agent 执行 Supabase 任务的能力，请使用该技能的 agent evals harness reference。
• 其他数据库引擎（Redis、MongoDB、Elasticsearch、向量存储）——这些内容归 data-engineering 参考资料所有；此技能仅负责 PostgreSQL。