PG 三十六计

《PG 三十六计》面向已经掌握 Linux 与通用 SQL、希望系统完成 PostgreSQL 应用开发和生产落地的读者。PostgreSQL 是核心知识对象；Pigsty 是统一实验载体、 观察窗口与生产参考实现。

全书分为应用开发与运维管理两卷，共六篇三十六章。正文从对象、模型、查询与并发 出发，经过发布、扩展、部署、HA、备份、可观测、容量与维护，最终进入事故响应、 恢复、取证和复盘改进。

阅读与查找

• 全书导读说明读者假设、安全公约、版本边界与实验合同。
• 完整编号目录定位全部章节、独立节页和稳定三级锚点。
• 索引中心按角色、任务、技术边界、事故症状与分区触点查找内容。
• 第 0 章准备可销毁的 Pigsty 实验环境；已有合适环境时可以跳过。

当前复现基线

正文与实验以 PostgreSQL 18.6、Pigsty v4.5.0 为当前基线。涉及其他受支持版本的 结论会就地说明适用范围。示例命令、示例输出和单次跑分都不能脱离目标、前提、 风险、证据、验收与复位链条单独使用。

全书导读

D.1 本书解决什么问题

• 从“会写 SQL”走到“能交付 PostgreSQL 应用”；
• 从“装好了数据库”走到“能运营 PostgreSQL 服务”；
• 从“看见告警”走到“能安全恢复并防止复发”；
• 用 Pigsty 把分散的 PostgreSQL 能力组合成可重复、可观察的生产实践。

D.2 读者假设与知识边界

正文默认读者已经掌握：

• Linux、Shell、SSH、文件与进程的基本操作；
• DDL、CRUD、连接、聚合、子查询、CTE 与基础事务；
• 至少一种后端编程语言；
• 基本的软件工程、版本控制与测试概念。

本书会解释 PostgreSQL 特有的语义与工程方法，但不系统补授 Linux、通用 SQL 或编程基础。第 0 章只解决实验环境，不承担基础课职能。

D.3 PostgreSQL 与 Pigsty 的关系

• PostgreSQL 是全书的核心知识对象；
• Pigsty 是全书统一的实验载体、观察窗口与生产参考实现；
• PostgreSQL 原生层解释数据库自身提供的机制与证据；
• 平台层解释任何生产数据库平台都必须承担的职责；
• Pigsty 层展示这些职责的一种具体组合与实现；
• Pigsty 特有操作若容易被误解为 PostgreSQL 通用行为，会附一行跨平台职责映射，但不扩写成其他平台教程。

作者在序言中披露与 Pigsty 的关系。“36 计”只代表 36 个递进的实战单元，不把每章强行附会为古代计策。

D.4 安全公约与风险标记

• R0·观察：只读查询、状态采样、计划分析，可在明确授权的环境中执行；
• R1·可逆变更：会改变对象、配置或流量，但有经过验证的回退路径；
• R2·破坏性演练：故障注入、切换、恢复、数据损坏，只能在可销毁的隔离环境执行；
• 全书示例使用专用实验凭据，不公开真实密码，不把数据库或管理入口裸露到公网；
• AI 辅助操作遵循最小权限、先预览、再验证原则，不允许“自动批准一切”；
• 任何生产命令都必须结合版本、拓扑、数据量和组织授权重新评估。

D.5 版本契约与勘误机制

当前版本冻结以下复现基线：

PostgreSQL 19 beta 只可用于兼容性观察，不作为生产结论基线。所有容易随版本变化的结论必须标注适用范围。面板讲指标语义，不依赖易漂移的点击路径；Pigsty 结论尽量回到 SQL、配置或原生组件验证。

每章的证据包记录客户端与服务端 PostgreSQL 版本、Pigsty 发布标识、操作系统与关键组件版本。定稿前执行一次“版本增量通过”，出版后通过附录 A：版本矩阵与差异注记维护勘误，而不是悄悄改写旧结论。

D.6 实验契约

每章按需提供以下产物，而不是机械凑齐固定数量：

• setup：建立确定的实验起点；
• exercise：主实验或故障注入；
• verify:state：机器验证系统是否达到预期状态；
• checklist:evidence：验证是否收集了足够决策证据；
• expected：关键输出及解释；
• reset:sql：重置数据库对象与数据；
• reset:cluster：恢复集群、服务与配置；
• reset:host：重建或回滚主机级环境。

实验数据生成器固定随机种子、规模档位与校验和。小规格虚拟机用于证明方法，不用于宣称绝对性能数字。

D.7 贯穿案例与五个阶段

主线案例统一为 pg36_shop：

1. ch01–ch12：持续演进、可运行的电商与支付应用；
2. ch13–ch18：复用核心模式的独立能力场景，不强迫一个后端服务承载所有扩展；
3. ch19–ch30：复用确定性数据与工作负载，把应用建设成生产服务；
4. ch31–ch35：从快照克隆独立事故现场，避免演练相互污染；
5. ch36：将事故证据重新汇总到治理与平台演进。

D.8 三层实验拓扑

• L1：单节点 Pigsty 开发沙箱，用于 ch01–ch18；
• L2：一台控制/客户端 VM 加三节点数据库、可销毁的生产仿真环境，用于 ch19–ch30；
• L3：由已知快照克隆的故障演练环境，用于 ch31–ch35。

随书实验已经冻结 L1/L2/L3 的资源档位、网络、例外与复位边界，见 附录 E。性能章节仍逐次记录硬件、数据量、并发、缓存状态与噪声， 避免把玩具环境结果外推到生产。

D.9 推荐阅读路线

• 完整路线：ch01 → ch36；
• 应用开发：ch01–ch18，再读 ch22、ch23、ch25；
• DBA/SRE：ch01、ch02、ch05–ch10，再读 ch19–ch36；
• 架构与平台：ch01、ch06、ch12、ch14、ch17–ch24、ch36；
• 故障处置：先读 ch31，再按症状索引进入 ch32–ch35，不建议脱离前置知识直接照抄命令。

核心依赖关系：

flowchart LR
  A["ch01–ch06<br/>对象、模型、事务与规约"] --> B["ch07–ch12<br/>诊断、并发、发布与服务"]
  B --> C["ch13–ch18<br/>扩展能力与平台边界"]
  B --> D["ch19–ch24<br/>生产服务规划"]
  D --> E["ch25–ch30<br/>运营与演进"]
  E --> F["ch31–ch36<br/>事故恢复与改进"]
  C --> D

D.10 按任务查找

序言

从 SQL 到生产：PostgreSQL 与 Pigsty 实战

为什么写这本书

PostgreSQL 并不缺功能说明，也不缺零散教程。真正稀缺的是一条完整的工程链：业务规则 怎样落成可靠的数据模型，SQL 为什么在并发下仍然正确，数据库怎样从一个进程变成可交付 的服务，出现故障时又怎样在保住数据和证据的前提下恢复。

很多问题正是断在这些接缝上：会写 SQL，却说不清连接落到了哪个实例；会建索引，却没有 证明慢时间花在执行而非等待；有高可用，却没有定义数据丢失边界；有备份，却从未在隔离 环境恢复；事故结束后写了复盘，却没有把教训固化成可验证控制。

这本书要补的是这些接缝。它面向已经掌握 Linux 与通用 SQL、希望系统完成 PostgreSQL 应用开发和生产落地的读者，从对象与模型开始，经过查询、并发、扩展、部署、运营和恢复， 最终形成三种能力：

1. 解释：能用 PostgreSQL 原理说明现象为什么发生；
2. 行动：能把目标拆成有前提、风险、步骤和停止线的操作；
3. 证明：能用 SQL、目录、日志、指标和实验后验说明结果确实成立。

这本书怎样展开

全书不是命令大全，也不按功能菜单平铺。每章围绕一个工程问题，依次回答：

为什么值得解决
  → 需要建立什么心智模型
  → PostgreSQL 提供了什么机制
  → 应当怎样操作
  → 用什么证据验收
  → 结论在哪些边界外不再成立

贯穿案例 pg36_shop 会持续演进。前几章建立对象、角色和业务模型；中段加入查询、 并发、发布与扩展；下卷再把同一套数据和工作负载放进高可用、备份、监控、容量、升级 与事故恢复中。这样做的价值不是让一个玩具应用假装覆盖所有生产场景，而是让前一章的 决定成为后一章可检查的输入。

实验也不是正文的装饰。凡是可以验证的结论，尽量给出正例、反例、运行证据和复位边界。 性能结果只在记录的硬件、数据、并发和时间窗内成立；破坏性动作只在明确可销毁的隔离 环境执行。读者最终应学会迁移方法，而不是背下某次示例输出。

PostgreSQL 与 Pigsty

PostgreSQL 是全书的核心知识对象；Pigsty 是统一实验载体、观察窗口，也是把 PostgreSQL、 高可用、备份、接入与监控组合成生产服务的一种参考实现。

正文始终区分三层：PostgreSQL 原生机制、任何生产数据库平台都要承担的职责、Pigsty 对这些职责的具体实现。理解原生机制，才能判断平台自动化做对了什么；理解平台职责， 才能把书中的方法迁移到托管数据库、Operator 或其他自建方案。

作者关系与利益披露

作者是 Pigsty 的作者与维护者，因此对其设计、能力与使用方式拥有直接经验，也天然存在偏好。全书要求 Pigsty 结论尽量回到 PostgreSQL 原生 SQL、配置或组件证据验证，并在可能造成迁移误解的位置说明跨平台职责映射。

关于“36 计”

“36 计”表示 36 个递进的实战单元，是目录与教学节奏的品牌表达，不把章节强行附会为古代计策。事故篇优先使用功能标题检索，成语仅作为副标题。

怎样阅读

第一次系统学习，按 ch01 → ch36 顺序阅读；应用开发者可先完成上卷，再补 ch22、ch23 和 ch25；DBA、SRE 与平台工程师应先建立 ch01、ch02、ch05 的共同语言，再进入下卷。 事故现场不要从搜索结果中直接复制恢复命令：先读 ch31 建立分级、现场保护和决策边界， 再进入 ch32–ch35 对应分支。

每章开头给出目标、前置与版本边界，末尾给出验收。最有效的读法是先写下自己的判断， 再运行实验，最后用证据修正判断。能复述术语不等于掌握；能在陌生环境里重新确认前提、 执行步骤并解释结果，才是本书所说的工程能力。

第 0 章（可跳过）扬帆起航——准备实验环境

可跳过说明：本章只为尚未拥有实验环境的读者准备 Pigsty L1 沙箱，并完成首次 PostgreSQL 连通。已有符合版本契约、且确认没有生产数据和流量的 独立环境，可以直接进入 第 1 章。

正文默认读者已经会 Linux、SSH 与 SQL；这里不补授操作系统、网络或 SQL 基础。本章 只建立后续实验共同需要的六项事实：

exact Pigsty/PostgreSQL/OS version
non-production environment authority
node and service identity
safe network boundary
working PostgreSQL connection
redacted baseline evidence

本书采用的复现基线是 Pigsty v4.5.0、PostgreSQL 18.6 与 Ubuntu 24.04 LTS reference environment。安装时固定 Pigsty release 与 PostgreSQL major，随后记录仓库 实际提供的 minor/build；不要为了伪造一致性降级已经修复安全问题的 minor release。

完成本章的标准

进入 ch01 前，读者应能：

1. 明确目标是可销毁、无生产数据与流量的 L1；
2. 记录 CPU、内存、磁盘、OS、架构、网络和成本边界；
3. 从官方来源获取并固定 Pigsty v4.5.0；
4. 在执行前审查生成的 pigsty.yml，保护其中的凭据与 CA key；
5. 完成单节点部署或验证一个等价的已有环境；
6. 分开 PostgreSQL、PgBouncer、HAProxy service 与 Web UI 入口；
7. 从 SQL 内部确认版本、数据库、角色、地址、端口与 recovery state；
8. 保存不含密码、token、private key 的环境摘要；
9. 知道安装失败时应保留日志、修复前置条件或重建 L1，而不是删除 PGDATA 猜测恢复。

本章目录

0.1 选择实验环境

• 0.1.1 本地虚拟机、开发服务器与已有 Pigsty 环境
• 0.1.2 L1 沙箱的最低资源、网络与磁盘要求
• 0.1.3 云主机的防火墙、入口与费用边界

0.2 安装单节点 Pigsty 沙箱

• 0.2.1 获取与核对版本
• 0.2.2 配置、部署与幂等重跑
• 0.2.3 检查 PostgreSQL、连接池与观察组件状态

0.3 完成首次连通

• 0.3.1 找到服务端点、数据库与实验凭据
• 0.3.2 执行 SELECT version() 与只读状态查询
• 0.3.3 确认编码、时区和扩展清单后进入 ch01

安全边界

• 单节点 L1 不提供生产高可用证明；
• 云端不把 PostgreSQL、PgBouncer、Patroni、DCS、监控或管理入口裸露到公网；
• pigsty.yml 与 files/pki/ca/ca.key 视为敏感材料；
• 本章不要求关闭 firewall/SELinux、使用默认密码或以 root 长期运行；
• 重装、清空数据和删除 cluster 都不是普通排错动作。

当前官方参考

• Pigsty v4.5 documentation
• Single-node setup
• Production install prerequisites
• Supported Linux matrix
• Setup security

返回全书导读 · 下一章：PostgreSQL 与 Pigsty 全局地图 · 查看全书目录 · 查看索引中心

0.1 选择实验环境

L1 的目标不是模拟生产，而是给 ch01～ch18 一个身份稳定、可以反复创建对象、能够 观察 PostgreSQL 与 Pigsty 的实验落点。选型优先保证隔离与可恢复，再追求便利。

0.1.1 本地虚拟机、开发服务器与已有 Pigsty 环境

三种可接受入口

macOS/Windows 宿主推荐在 Linux VM 中部署。Pigsty 管理 Linux 服务、package、filesystem 和 network；把它直接改写成容器教程会改变本书实验边界。WSL 可用于体验，但涉及 systemd、网络、存储与多节点演练时要重新验证。

环境接受表

environment_id: pg36-l1-...
owner: ...
purpose: book-labs
production_data: false
production_traffic: false
shared_users: [...]
rebuild_source: vm-image-or-inventory
snapshot_or_backup: ...
expires_at: ...
network_boundary: ...

已有 Pigsty 环境只有在以下问题都得到肯定回答后才可复用：

我能指认 exact node/cluster/database/role 吗？
它没有生产数据、生产流量和真实 secret 吗？
我有权创建和清理本书对象吗？
实验负载不会伤害其他用户吗？
失败后能恢复或重建吗？
版本差异已记录吗？

任一答案不明确，就新建 L1。能登录一台机器不等于获得数据库故障注入、服务重启或 数据清理授权。

命名与 snapshot

为 VM、hostname、SSH alias 和 evidence 使用 pg36-l1 前缀，避免与生产式名字混淆。 在全新 OS、Pigsty 安装完成、重要 extension 安装后分别保留 snapshot；记录 snapshot identity 和创建时间。snapshot 不是 backup 的替代，但适合恢复教学环境。

0.1.2 L1 沙箱的最低资源、网络与磁盘要求

Pigsty 官方单节点 quick start 可以从 1 vCPU / 2 GiB 起步；这只表示能完成基础部署。 本书 L1 同时运行 PostgreSQL、监控栈并执行查询/扩展实验，采用更保守档位：

这些是教学资源档，不是生产 sizing。不同架构、磁盘、extension 和宿主 overcommit 会 改变表现；第 26 章 才讨论有证据的容量结论。

安装前采样

uname -a
cat /etc/os-release
getconf _NPROCESSORS_ONLN
awk '/MemTotal/ {print}' /proc/meminfo
df -hT /
ip -brief address
ip route
timedatectl status

不要只看虚拟磁盘标称容量；确认 filesystem 可用空间和 inode。扩展 package、监控 retention、WAL、backup、fixture 与 evidence 都会增长。L1 的磁盘满也可能让 PostgreSQL 无法安全写入。

网络合同

stable hostname and address inside the lab
SSH from the operator workstation
package/repository reachability or exact offline package
working DNS and time synchronization
no overlap with production/private routes
no public database exposure

NAT 模式通常足够；只有需要从宿主访问 Web UI/数据库时才配置明确端口转发或 host-only 网络。记录从哪个 source CIDR 允许访问，避免使用 0.0.0.0/0。

0.1.3 云主机的防火墙、入口与费用边界

最小暴露

云 security group 默认 deny inbound，只从操作者固定 IP、VPN 或 bastion 允许 SSH。 Web UI 优先通过 VPN/SSH tunnel；确需 443 时限制来源并配置 TLS。PostgreSQL/服务端口 只对明确应用网络开放。

不要向公网开放：

5432 / 6432 / 5433 / 5434 / 5436 / 5438
Patroni API and DCS
Grafana/Victoria/Alertmanager/admin endpoints
node/exporter/backup metrics

端口表随配置变化，以本地 inventory/rendered config 为准。只关闭 OS firewall 而依赖 云 security group，或反过来，都会制造单层防护。

凭据与主机

• 使用普通管理用户 + sudo，不以 root/postgres 作为日常 SSH 用户；
• SSH 使用 key，限制 source，关闭不需要的密码登录；
• ./configure -g 生成随机密码，保护 pigsty.yml；
• 保护 Pigsty CA private key，不复制进聊天、工单或 Git；
• 教学数据保持合成，不上传客户 dump；
• 设置资源 tag、owner、expiration 和预算告警。

费用清单

compute uptime
system/data/backup disk and snapshots
public IPv4
egress and cross-zone traffic
object storage/API requests
retained image and unattached volume

停止 VM 可能仍收磁盘、snapshot 与 IP 费用；删除 VM 可能同时删除唯一 evidence。实验 结束先导出所需的去敏小型 evidence，再按云平台的 exact resource ID 清点。不要在 本书中提供一条通用递归删除命令。

进入安装前的 stop line

以下情况先解决，不继续：

目标可能承载生产数据/流量
OS/architecture 不在当前支持矩阵
磁盘或内存低于接受档
时间/DNS/package source 异常
公网入口无法限制
没有恢复或重建路径
无法保护 inventory 与 CA key

返回本章目录 · 下一节：安装单节点 Pigsty 沙箱 · 查看全书目录 · 查看索引中心

0.2 安装单节点 Pigsty 沙箱

本节以全新、可销毁 Linux 节点为前提，冻结 Pigsty v4.5.0，使用默认单节点 meta 模板和 PostgreSQL 18。官方命令与模板会继续演进；复现本书时固定 release，不追随 main 或“latest”漂移。

0.2.1 获取与核对版本

前置身份

id
hostnamectl
cat /etc/os-release
uname -m
sudo -n true
ssh localhost sudo -n true

Pigsty 需要 Linux、SSH 和 sudo。sudo -n/localhost SSH 失败时先修管理前置，不要把 脚本改成长期 root 运行。对照当前支持矩阵确认 发行版 minor 与架构。

固定 release

官方 bootstrap 方式：

curl -fsSL https://repo.pigsty.io/get | bash -s v4.5.0
cd ~/pigsty

若组织禁止 pipe-to-shell，先下载、记录 SHA-256、人工/安全工具审查，再执行；或：

git clone https://github.com/pgsty/pigsty.git
cd pigsty
git checkout v4.5.0
git status --short
git rev-parse HEAD
./bootstrap

tag 提供版本选择，不自动证明供应链可信。高要求环境应按组织规则验证 release artifact、来源、签名/摘要、package repository 与代理。offline package 必须匹配 OS minor/architecture，并核对对应 release 页面摘要。

保存不含 secret 的 acquisition manifest

{
  date -Is
  git describe --tags --always --dirty 2>/dev/null || true
  git rev-parse HEAD 2>/dev/null || true
  uname -a
  cat /etc/os-release
} > pg36-acquisition.txt

不要把 shell environment、完整 inventory 或 credential 写入该文件。

0.2.2 配置、部署与幂等重跑

生成，再评审

在 Pigsty source directory：

./configure -g -v 18
chmod 0600 pigsty.yml

-g 生成随机密码；默认 meta 是单节点模板。执行前评审：

inventory host/IP is this L1
pg_cluster and instance identity are expected
PostgreSQL major is 18
package/repository source is accepted
data/config/log/backup paths fit the disk
listen/access rules do not expose public networks
generated passwords are not defaults
modules and apps are actually needed

pigsty.yml 含访问和密码信息，不提交本书仓库、不粘贴到公开 issue。files/pki/ca/ca.key 生成后同样限制权限并备份到受控位置。

执行部署

先确认 target：

./install.yml --list-hosts
./install.yml --list-tags

再在 L1 执行：

./install.yml

保留：

start/end time
Pigsty revision
redacted inventory digest
Ansible exit code
failed task and host
package versions

不要把“play recap 全 green”当数据库验收；下一目从 service、component 和 SQL 三侧 验证。

幂等的正确含义

修复临时网络/package 前置后，可以对同一 desired state 重跑部署；幂等意味着系统应 收敛到声明状态，不意味着：

每次没有任何 changed
任何删除 playbook 都安全
运行中手改不会被覆盖
失败后可以删除 PGDATA 再试

若重跑持续出现 unexpected changes，比较 inventory、rendered file 与 runtime， 定位 non-idempotent task 或 drift。不要为“全绿”关闭安全控制。

失败与复位

package/download failed -> 修 repo/DNS/TLS，保留失败输出后重跑
inventory/host wrong    -> 停止；不要在错误目标继续
disk/memory exhausted   -> 扩容或重建 L1，不清理未知 PG/WAL 文件
partial database state  -> 先取证和确认是否已有数据，再决定恢复/重建

对全新、确认无数据的 disposable VM，回到安装前 snapshot 往往比手工拆半套服务可靠。 已有数据的机器不属于本节重建授权。

0.2.3 检查 PostgreSQL、连接池与观察组件状态

四层健康

process
  service active / no crash loop

endpoint
  port accepts and route selects expected backend

protocol
  PostgreSQL authentication and SQL work

semantic
  exact cluster/database/role/version and expected read-write state

检查环境/package：

pig status
postgres --version
psql --version

检查本机 unit（模板禁用某组件时，inactive 不自动等于失败）：

systemctl is-active patroni
systemctl is-active pgbouncer
systemctl is-active haproxy
systemctl --failed

检查 PostgreSQL readiness：

pg_isready -h 127.0.0.1 -p 5432

pg_isready 不验证业务角色、数据库、查询或正确 backend，只是 protocol-level availability。

分清端口

默认 Pigsty service 通常包括：

以本地 pigsty.yml 与 rendered HAProxy config 为准。单节点仍可暴露 replica/offline service 名称，但没有第二故障域，不应宣称 HA。

观察系统

从受限网络访问 Web UI，确认：

PostgreSQL target discovered
host target discovered
Patroni/etcd/PgBouncer/HAProxy as configured
metrics have recent timestamps
logs and alert evaluation available

dashboard 可见不证明采集语义正确；记录 target identity 和 timestamp。不要公开默认 UI 密码或将管理 UI 暴露到公网。

上一节：选择实验环境 · 返回本章目录 · 下一节：完成首次连通 · 查看全书目录 · 查看索引中心

0.3 完成首次连通

最后一步不是“能打开 psql”，而是从连接内部确认操作落点，并保存一份不泄露凭据的 baseline。第 1 章会在此基础上创建本书专用数据库、模式和角色。

0.3.1 找到服务端点、数据库与实验凭据

五元连接身份

host/service
port/route semantics
database
role
TLS/session parameters

默认单节点通常提供 pg-meta cluster、meta database 与管理/业务/只读角色；实际值 从你的 pigsty.yml 读取。该文件含 secret，只在受控终端查看，不把整行 URI 或密码 复制进 shell history、截图和文档。

首次连接可让 psql 单独提示密码：

psql -h 127.0.0.1 -p 5432 -U dbuser_dba -d meta -W

不要把密码写在命令行 URI。需要非交互任务时使用 mode 0600 的 .pgpass、service file 或组织 secret injection，并确保日志不打印环境。

先直连，后比较 service

先用 5432 直连确认 PostgreSQL，再按本地配置比较 6432/5433/5436。对每个 endpoint 执行同一 identity query，记录它是否经过 pool、route 到哪个 backend、支持什么会话 状态。不要把“端口可连”推断成“读写语义正确”。

常见失败

0.3.2 执行 SELECT version() 与只读状态查询

在 psql：

\set ON_ERROR_STOP on
\pset pager off

SELECT
    current_setting('server_version') AS server_version,
    current_setting('server_version_num') AS server_version_num,
    current_database() AS database,
    current_user AS role,
    session_user,
    inet_server_addr() AS server_addr,
    inet_server_port() AS server_port,
    pg_is_in_recovery() AS in_recovery;

SELECT
    current_setting('transaction_read_only') AS transaction_read_only,
    current_setting('default_transaction_read_only')
        AS default_transaction_read_only;

Unix socket 连接时 inet_server_addr()/inet_server_port() 可以是 NULL，这是 transport 语义，不是 PostgreSQL 缺地址。service endpoint 可能返回 backend instance 地址， 不等于客户端连接的 HAProxy 地址。

再检查 search path 与身份：

SHOW search_path;
SELECT current_schemas(true);
SELECT
    pg_catalog.pg_has_role(current_user, 'USAGE') AS current_role_usable;

version() 包含 build 信息，适合证据；程序判断用 server_version_num，不要解析展示 字符串。

保存去敏基线

psql -X -v ON_ERROR_STOP=1 \
  -h 127.0.0.1 -p 5432 -U dbuser_dba -d meta \
  -A -F $'\\t' -t \
  -c \"SELECT current_setting('server_version_num'),
             current_database(), current_user,
             coalesce(inet_server_addr()::text, 'unix-socket'),
             coalesce(inet_server_port()::text, 'unix-socket'),
             pg_is_in_recovery();\" \
  > pg36-l1-identity.tsv

该输出不含 password；仍按内部环境资料保护 host/role。检查退出码与文件非空，不要因 redirect 创建空文件就认为成功。

0.3.3 确认编码、时区和扩展清单后进入 ch01

locale/time/encoding

SELECT
    datname,
    pg_encoding_to_char(encoding) AS encoding,
    datlocprovider,
    datcollate,
    datctype
FROM pg_catalog.pg_database
WHERE datname = current_database();

SHOW TimeZone;
SHOW DateStyle;

SELECT
    now() AS transaction_time,
    statement_timestamp() AS statement_time,
    clock_timestamp() AS wall_time;

后续时间、collation、索引和升级实验依赖这些事实。不要为了“统一”直接修改 cluster； 先记录差异，在对应章节决定是否需要重建或迁移。

installed 与 available extension 分开

当前数据库已安装：

SELECT
    extname,
    extversion,
    extnamespace::pg_catalog.regnamespace AS schema
FROM pg_catalog.pg_extension
ORDER BY extname;

操作系统/仓库可提供但未安装的扩展是另一集合，不能用 pg_available_extensions 冒充 installed。扩展是 database-local object；同一 instance 的另一 database 不会自动继承。

最终 baseline

book_baseline:
  environment: pg36-l1
  production_data: false
  production_traffic: false
  pigsty_release: v4.5.0
  pig_cli: ...
  postgresql_server: ...
  os_kernel_arch: ...
  cluster_database_role: ...
  connection_endpoint: ...
  encoding_locale_timezone: ...
  installed_extensions: [...]
  monitoring_observed_at: ...
  secret_values_recorded: false

进入 ch01 的 gate：

identity query succeeds
server major = 18, exact minor recorded
database and role are expected
L1 is writable and not in recovery
encoding/timezone/collation recorded
installed extension list recorded
monitoring has current target data
no production data/traffic or exposed management endpoint

不满足时留在第 0 章修环境。满足后进入 第 1 章：PostgreSQL 与 Pigsty 全局地图，在那里创建 pg36_shop，不要把默认 meta database 当作全书业务模型。

上一节：安装单节点 Pigsty 沙箱 · 返回本章目录 · 进入第 1 章 · 查看全书目录 · 查看索引中心

完整目录

本页列出全书的章、节、目。36 个正文章直接位于顶层；节标题链接到独立页面，目标题链接到页面内的稳定锚点。

前置内容

• 全书导读
• 第 0 章 扬帆起航——准备实验环境
  • 0.1 选择实验环境
    • 0.1.1 本地虚拟机、开发服务器与已有 Pigsty 环境
    • 0.1.2 L1 沙箱的最低资源、网络与磁盘要求
    • 0.1.3 云主机的防火墙、入口与费用边界
  • 0.2 安装单节点 Pigsty 沙箱
    • 0.2.1 获取与核对版本
    • 0.2.2 配置、部署与幂等重跑
    • 0.2.3 检查 PostgreSQL、连接池与观察组件状态
  • 0.3 完成首次连通
    • 0.3.1 找到服务端点、数据库与实验凭据
    • 0.3.2 执行 SELECT version() 与只读状态查询
    • 0.3.3 确认编码、时区和扩展清单后进入 ch01

36 章正文

上卷：应用开发

从 PostgreSQL 工程认知到应用交付与能力扩展

第一篇：筑基——建立 PostgreSQL 工程认知

ch01 盲人摸象：PostgreSQL 与 Pigsty 全局地图

• 1.1 从连接串识别操作落点
  • 1.1.1 主机、端口、服务、数据库与角色
  • 1.1.2 current_database()、current_user 与 search_path
  • 1.1.3 实例端点、服务端点与只读端点
• 1.2 PostgreSQL 对象与术语坐标
  • 1.2.1 实例、数据库、模式与关系对象
  • 1.2.2 角色为何跨数据库存在
  • 1.2.3 表、索引、序列、视图、函数与扩展
  • 1.2.4 PostgreSQL “database cluster”的特殊含义
• 1.3 一条查询经过了什么
  • 1.3.1 客户端、后端进程与会话
  • 1.3.2 共享内存、数据文件与 WAL
  • 1.3.3 系统目录与统计视图如何描述自身
• 1.4 从数据库实例到数据库服务
  • 1.4.1 单实例、复制组、服务入口与控制面
  • 1.4.2 计算、存储、网络、配置与可观测职责
  • 1.4.3 PostgreSQL 原生能力与平台组合能力的边界
• 1.5 Pigsty 的资源模型
  • 1.5.1 节点、集群、实例与服务
  • 1.5.2 PostgreSQL、Patroni、PgBouncer 与 HAProxy 的职责
  • 1.5.3 配置清单、运行状态与监控事实分别来自哪里
• 1.6 最小 psql 生存卡
  • 1.6.1 用 URI 连接，用 \l、\dn、\d 看对象
  • 1.6.2 用 \c 切库，用 -c 与 -f 执行
  • 1.6.3 用 \o 或 -A -t 保存输出
  • 1.6.4 用 \q、Ctrl-C 安全退出与中断
• 1.7 实战：建立 pg36_shop 地图与实验基线
  • 1.7.1 创建数据库、业务模式和最小角色
  • 1.7.2 生成连接快照、对象树、服务拓扑与环境清单
  • 1.7.3 建立 verify:state 与三档 reset
  • 1.7.4 验收：从 SQL 与 Pigsty 两侧指认同一对象

ch02 手到擒来：psql 与可复现工作流

• 2.1 可靠连接与上下文保护
  • 2.1.1 连接 URI、服务文件与环境变量
  • 2.1.2 application_name、提示符与上下文快照
  • 2.1.3 防止连错库、用错角色和改错模式
• 2.2 用 psql 探索与取证
  • 2.2.1 对象、权限和会话元命令
  • 2.2.2 扩展显示、分页、计时与查询缓冲区
  • 2.2.3 元命令与系统目录查询互相验证
• 2.3 编写可靠 SQL 脚本
  • 2.3.1 ON_ERROR_STOP、退出码与失败即停
  • 2.3.2 变量、条件、包含文件与事务包装
  • 2.3.3 幂等、重入与执行前预览
  • 2.3.4 日志、清单与机器可读结果
• 2.4 输入、输出与确定性数据
  • 2.4.1 COPY 与 \copy 的权限和执行边界
  • 2.4.2 CSV、文本与错误隔离
  • 2.4.3 固定随机种子、规模档位与校验和
• 2.5 最小 pgbench 工作负载
  • 2.5.1 初始化自定义脚本与参数
  • 2.5.2 区分吞吐、延迟、错误与环境噪声
  • 2.5.3 本节只建立可复现基线，不做性能结论
• 2.6 最小逻辑备份闭环
  • 2.6.1 pg_dump 的对象、模式与自定义格式
  • 2.6.2 pg_restore 的清单、选择性恢复与验证
  • 2.6.3 与 ch21《未雨绸缪：备份体系与恢复演练》的边界
• 2.7 实战：把人工操作变成可重跑任务
  • 2.7.1 生成 pg36_shop 初始数据与校验摘要
  • 2.7.2 从 Pigsty 服务端点执行并保存证据
  • 2.7.3 注入脚本错误，验证停止、修复与复位

ch03 正本清源：从业务规则到关系模型

• 3.1 从业务语言提取数据库事实
  • 3.1.1 实体、事件、状态与业务不变量
  • 3.1.2 命令模型、查询模型与数据所有权
  • 3.1.3 哪些规则必须由数据库兜底
• 3.2 标识、主键与业务键
  • 3.2.1 自然键、代理键与外部标识
  • 3.2.2 主键稳定性、键宽度与传播范围
  • 3.2.3 幂等键、去重键与审计标识
• 3.3 关系与引用完整性
  • 3.3.1 一对一、一对多与多对多
  • 3.3.2 外键动作与生命周期
  • 3.3.3 聚合边界与跨表不变量
• 3.4 模式、所有权与对象边界
  • 3.4.1 业务模式、接口模式与内部模式
  • 3.4.2 对象所有者与运行角色分离
  • 3.4.3 避免依赖不受控的 search_path
• 3.5 规范化与有意识的冗余
  • 3.5.1 函数依赖与重复事实
  • 3.5.2 派生数据、快照数据与缓存列
  • 3.5.3 接受冗余前先定义一致性责任
• 3.6 实战：建立逻辑模型 v0
  • 3.6.1 用户、商品、订单、订单项与支付
  • 3.6.2 在 Pigsty L1 的真实数据库中部署并用样例规则审查
  • 3.6.3 产出金额、时间、状态、标识四项未决清单
  • 3.6.4 生成逻辑关系图并链接 ch04 的可靠版本

ch04 量体裁衣：数据类型、约束与可靠数据表达

• 4.1 金额、文本与时间
  • 4.1.1 整数、numeric 与金额精度
  • 4.1.2 text、排序规则与大小写语义
  • 4.1.3 date、timestamptz、时区与业务时间
• 4.2 标识、状态与半结构化数据
  • 4.2.1 bigint、UUID 与标识生成
  • 4.2.2 布尔、枚举、查找表与状态机
  • 4.2.3 数组、范围、JSONB 与拆表边界
• 4.3 NULL、默认值与生成值
  • 4.3.1 “未知”“不存在”与空值语义
  • 4.3.2 默认值、身份列与序列
  • 4.3.3 生成列与数据库派生事实
• 4.4 用约束表达不变量
  • 4.4.1 主键、唯一、外键与检查约束
  • 4.4.2 排他约束与 btree_gist 的适用条件
  • 4.4.3 仅对支持类型使用 DEFERRABLE，并说明事务末校验代价
• 4.5 类型与约束的物理代价
  • 4.5.1 行宽、对齐、TOAST 与更新成本
  • 4.5.2 隐式转换、操作符与索引可用性
  • 4.5.3 约束、索引与写放大的关系
• 4.6 分区决策门
  • 4.6.1 先证明生命周期、体量或裁剪需求再决定分区
  • 4.6.2 分区键与主键、唯一约束必须共同设计
  • 4.6.3 外键、引用方式与未来在线改造代价
  • 4.6.4 产出“现在分区 / 暂不分区”的可复查 ADR
• 4.7 实战：把逻辑模型落成可靠物理模式
  • 4.7.1 闭合金额与时间表达
  • 4.7.2 闭合状态与标识生成
  • 4.7.3 用反例验证类型、约束与错误语义
  • 4.7.4 在 Pigsty L1 输出可靠 DDL、分区决策与 verify:state

ch05 运筹帷幄：查询、事务与锁的核心心智模型

• 5.1 SQL 从文本到结果
  • 5.1.1 解析、重写、规划与执行
  • 5.1.2 关系代数直觉与执行节点
  • 5.1.3 优化器为什么做估算而不是预言
• 5.2 MVCC 与可见性
  • 5.2.1 元组版本、事务 ID 与快照
  • 5.2.2 活跃、提交、中止与可见性判断
  • 5.2.3 读不阻塞写的条件与代价
• 5.3 事务边界与失败语义
  • 5.3.1 自动提交、显式事务与中止状态
  • 5.3.2 原子性、持久性与 WAL
  • 5.3.3 保存点、重试边界与外部副作用
• 5.4 锁与等待
  • 5.4.1 表锁、行锁与轻量级锁的职责
  • 5.4.2 等待图、阻塞链与死锁检测
  • 5.4.3 锁模式名称不等于业务影响
• 5.5 隔离现象与后续路线
  • 5.5.1 脏读、不可重复读、幻读与序列化异常
  • 5.5.2 lost update 必须绑定具体隔离级别与写法
  • 5.5.3 ch07–ch10 如何分别展开计划与并发
• 5.6 实战：观察一笔订单事务
  • 5.6.1 从 SQL 观察会话、快照、锁和 WAL 位置
  • 5.6.2 从 Pigsty 观察连接、事务与等待指标
  • 5.6.3 注入阻塞并解释“现象—证据—原理”

ch06 立木取信：开发规约与交付基线

• 6.1 规约不是口号
  • 6.1.1 从事故、评审和测量中形成规则
  • 6.1.2 每条规则记录动机、证据、例外和检查方式
  • 6.1.3 区分安全底线、团队默认与场景偏好
• 6.2 连接与会话候选规则
  • 6.2.1 连接上下文、超时与 application_name
  • 6.2.2 时区、编码、search_path 与会话状态
  • 6.2.3 用错误连接案例验证规则价值
• 6.3 模式与 DDL 候选规则
  • 6.3.1 命名、所有权、注释与对象边界
  • 6.3.2 类型、约束和默认值的审查问题
  • 6.3.3 可逆迁移与版本化 DDL
• 6.4 查询与事务候选规则
  • 6.4.1 明确列、稳定排序与分页语义
  • 6.4.2 事务大小、超时、重试与幂等
  • 6.4.3 CTE、窗口函数与 LATERAL 的可读性门槛
• 6.5 交付物与质量门
  • 6.5.1 DDL、迁移、数据生成与回滚
  • 6.5.2 自动测试、静态检查与计划证据
  • 6.5.3 变更说明、所有者与风险等级
• 6.6 将规约接入统一实验环境
  • 6.6.1 角色、数据库与服务声明
  • 6.6.2 初始化、验证与重置入口
  • 6.6.3 配置事实与运行事实分开审查
• 6.7 实战：发布规约 baseline v0.1
  • 6.7.1 审查 ch01–ch05 已出现的候选规则
  • 6.7.2 为 pg36_shop 建立最小质量门
  • 6.7.3 预留 ch07–ch11 的证据追加区
  • 6.7.4 在 ch12 汇总为 v1.0 的验收条件

第二篇：应用——从 SQL 正确走向稳定交付

ch07 追本溯源：执行计划与统计信息

• 7.1 优化器如何选择路径
  • 7.1.1 扫描、连接、排序、聚合与物化节点
  • 7.1.2 成本、选择率、行数与路径竞争
  • 7.1.3 计划树的阅读顺序与数据流
• 7.2 正确使用 EXPLAIN
  • 7.2.1 EXPLAIN、ANALYZE、BUFFERS、WAL
  • 7.2.2 规划时间、执行时间与客户端时间
  • 7.2.3 对写语句使用 ANALYZE 的事务保护
• 7.3 统计信息与估算偏差
  • 7.3.1 直方图、高频值、空值率与相关性
  • 7.3.2 扩展统计解决跨列相关
  • 7.3.3 数据倾斜、陈旧统计与采样误差
• 7.4 分区裁剪的两种时机
  • 7.4.1 规划时裁剪与常量条件
  • 7.4.2 执行时裁剪、参数化节点与通用计划
  • 7.4.3 分区父表统计需要显式 ANALYZE
  • 7.4.4 产出裁剪生效与失效的计划对照
• 7.5 参数、缓存计划与计划漂移
  • 7.5.1 自定义计划与通用计划
  • 7.5.2 预备语句、数据倾斜与参数敏感
  • 7.5.3 计划变化是症状，不自动等于回归
• 7.6 建立计划证据基线
  • 7.6.1 pg_stat_statements 的归一化视角
  • 7.6.2 auto_explain 的阈值、采样与日志成本
  • 7.6.3 从 Pigsty 时间窗保存 SQL、参数、统计、计划与环境上下文
• 7.7 实战：解释订单查询的计划变化
  • 7.7.1 用固定数据种子制造估算偏差
  • 7.7.2 修复统计与条件表达后重新比较
  • 7.7.3 把一条有证据的计划规则追加到规约

ch08 抽丝剥茧：慢 SQL 诊断方法论

• 8.1 先定义“慢”
  • 8.1.1 延迟分位数、吞吐、并发与错误率
  • 8.1.2 单次慢、持续慢与整体退化
  • 8.1.3 应用时间、排队时间与数据库时间
• 8.2 从会话到语句定位范围
  • 8.2.1 活跃、等待、阻塞与空闲事务
  • 8.2.2 按调用、总时长、均值和尾延迟排序
  • 8.2.3 查询指纹、参数与时间窗口
• 8.3 关联日志、指标与计划
  • 8.3.1 日志最小基线与慢语句记录
  • 8.3.2 SQL 指标、主机资源与部署事件
  • 8.3.3 用同一时间轴排除巧合
• 8.4 建立而不是猜测假设
  • 8.4.1 计划与估算问题
  • 8.4.2 锁、I/O、CPU、内存与临时文件
  • 8.4.3 客户端取数、网络与连接池排队
• 8.5 设计受控实验
  • 8.5.1 每次只改变一个解释变量
  • 8.5.2 冷热缓存、参数与数据规模控制
  • 8.5.3 反证、回退与副作用观察
• 8.6 从可观测面板回到原生证据
  • 8.6.1 用指标语义定位时间、实例与查询
  • 8.6.2 用 SQL、日志和计划复核面板判断
  • 8.6.3 不把截图、颜色或当前点击路径当作知识
• 8.7 实战：三种“慢”只修真正瓶颈
  • 8.7.1 估算错误、锁等待与客户端慢消费
  • 8.7.2 随机隐藏一种根因，先独立诊断再揭晓
  • 8.7.3 输出证据包、假设树、修复前后对照与复位结果

ch09 巧夺天工：索引设计与效果验证

• 9.1 索引方法与操作符类
  • 9.1.1 B-tree 与 Hash 的适用查询
  • 9.1.2 GiST、SP-GiST 与空间、范围、近邻问题
  • 9.1.3 GIN 与数组、JSONB、文本检索
  • 9.1.4 BRIN 与物理相关的大表；Bloom 的扩展边界
• 9.2 从谓词、连接与排序推导索引
  • 9.2.1 等值、范围与多列顺序
  • 9.2.2 连接键、排序、分组与 Top-N
  • 9.2.3 选择率、相关性与访问路径
• 9.3 表达式、部分与覆盖索引
  • 9.3.1 表达式必须与查询语义一致
  • 9.3.2 部分索引的谓词蕴含与参数陷阱
  • 9.3.3 INCLUDE、index-only scan 与可见性图
• 9.4 索引也有写入和生命周期成本
  • 9.4.1 写放大、缓存占用与 WAL
  • 9.4.2 HOT 更新、页分裂与填充因子
  • 9.4.3 重复、未使用与失效索引的判断
• 9.5 验证而不是“加完就快”
  • 9.5.1 计划、缓冲区、延迟分布与写入代价
  • 9.5.2 数据规模和缓存状态一致的 A/B 对照
  • 9.5.3 线上创建、失败回收与监控窗口
• 9.6 实战：为订单、库存与搜索入口设计索引
  • 9.6.1 从真实查询清单提出候选索引
  • 9.6.2 在 Pigsty L1 保留、合并或拒绝并记录证据
  • 9.6.3 将索引审查规则追加到规约

ch10 顾此失彼：并发控制与隔离异常

• 10.1 隔离级别与可观察现象
  • 10.1.1 Read Committed 的语句快照
  • 10.1.2 Repeatable Read 的事务快照与写冲突
  • 10.1.3 Serializable、谓词冲突与序列化失败
• 10.2 Lost update 不是一句口号
  • 10.2.1 读—算—写在 Read Committed 下如何丢更新
  • 10.2.2 原子更新与带版本条件的更新
  • 10.2.3 更高隔离级别何时拒绝而不是静默覆盖
• 10.3 悲观锁与锁队列
  • 10.3.1 FOR UPDATE、NO KEY UPDATE 与引用关系
  • 10.3.2 NOWAIT、SKIP LOCKED 与任务领取
  • 10.3.3 锁顺序、阻塞链与死锁
• 10.4 乐观控制、重试与幂等
  • 10.4.1 版本列、唯一键与条件写入
  • 10.4.2 重试只包围可重放的事务
  • 10.4.3 支付、消息与外部副作用的边界
• 10.5 咨询锁与跨行协调
  • 10.5.1 会话级与事务级咨询锁
  • 10.5.2 键空间、碰撞与所有权
  • 10.5.3 不用咨询锁掩盖缺失的数据约束
• 10.6 观察与诊断并发
  • 10.6.1 pg_stat_activity、pg_locks 与等待事件
  • 10.6.2 从 Pigsty 定位锁等待与长事务
  • 10.6.3 保存阻塞图，而不是先杀会话
• 10.7 实战：库存扣减与支付幂等
  • 10.7.1 在指定隔离级别重现丢更新和死锁
  • 10.7.2 比较原子更新、行锁与可重试事务
  • 10.7.3 用并发测试验证业务不变量并追加规约

ch11 守正出奇：模式变更与安全发布

• 11.1 识别 DDL 的四类风险
  • 11.1.1 锁等级与持锁时间
  • 11.1.2 表重写、全表扫描与 WAL 放大
  • 11.1.3 新旧应用版本的兼容窗口
  • 11.1.4 数据回填的节奏与失败恢复
• 11.2 Expand–Migrate–Contract
  • 11.2.1 先扩展兼容结构
  • 11.2.2 分批迁移、双读校验与切换
  • 11.2.3 观察稳定后再收缩旧结构
• 11.3 索引与约束的在线化路径
  • 11.3.1 CREATE INDEX CONCURRENTLY 的阶段与失败残留
  • 11.3.2 NOT VALID、VALIDATE CONSTRAINT 与验证扫描
  • 11.3.3 默认值、非空与类型变更的版本边界
• 11.4 在线分区化
  • 11.4.1 从 ch04 的分区 ADR 选择迁移路径
  • 11.4.2 预建约束、ATTACH 与扫描规避
  • 11.4.3 DETACH、并发能力与锁等级必须按版本说明
  • 11.4.4 产出一次可回退的分区化发布
• 11.5 数据回填与流量切换
  • 11.5.1 批次、限速、水位与断点续跑
  • 11.5.2 影子读、双写的风险与一致性验证
  • 11.5.3 何时暂停、回退或前滚
• 11.6 发布窗口中的平台观察
  • 11.6.1 从服务入口隔离实验流量
  • 11.6.2 观察锁、复制延迟、WAL 与资源水位
  • 11.6.3 配置变更与模式变更分别留证
• 11.7 实战：无中断演进订单模式
  • 11.7.1 加字段、回填、建约束与新旧版本共存
  • 11.7.2 注入锁等待和回填中断，验证中止与恢复
  • 11.7.3 把发布证据与新规则追加到规约

ch12 一气呵成：从数据库契约到后端服务

• 12.1 数据库契约与应用边界
  • 12.1.1 模式、查询、错误与兼容性契约
  • 12.1.2 业务不变量在应用与数据库之间分工
  • 12.1.3 迁移版本与服务发布的依赖
• 12.2 为服务设计查询接口
  • 12.2.1 参数化 SQL 与稳定结果语义
  • 12.2.2 CTE、窗口函数和 LATERAL 的工程用法
  • 12.2.3 错误码、约束名与领域错误映射
• 12.3 Go 服务中的连接与事务
  • 12.3.1 连接池大小、超时与上下文取消
  • 12.3.2 事务函数、失败重试与外部副作用
  • 12.3.3 健康检查不等于业务可用
• 12.4 会话状态与连接池陷阱
  • 12.4.1 session、transaction 与 statement pooling
  • 12.4.2 预备语句行为必须绑定 PgBouncer 与驱动版本
  • 12.4.3 SET LOCAL、事务边界与 RLS 上下文
  • 12.4.4 在 ch22、ch23 分别深化池化与权限
• 12.5 服务级可观测性
  • 12.5.1 请求、事务、查询指纹与 application_name
  • 12.5.2 延迟、错误、连接等待和数据库等待
  • 12.5.3 从一次请求追到数据库证据
• 12.6 部署与接入 pg36_shop
  • 12.6.1 角色、数据库、服务与凭据声明
  • 12.6.2 通过连接池和服务端点接入
  • 12.6.3 用平台指标验证部署，而非只看进程存活
• 12.7 实战：交付应用闭环与规约 v1.0
  • 12.7.1 跑通下单、扣库存、支付幂等与查询
  • 12.7.2 注入数据库超时、重试与连接耗尽
  • 12.7.3 汇总 ch07–ch11 的证据，发布规约 v1.0
  • 12.7.4 冻结服务样例，后续改用 SQL 与工作负载脚本

第三篇：扩展——扩大 PostgreSQL 的能力边界

ch13 言出法随：函数、触发器与存储过程

• 13.1 先决定逻辑放在哪里
  • 13.1.1 数据不变量、批处理与接口封装
  • 13.1.2 数据库内聚与应用可演进性的权衡
  • 13.1.3 不用触发器隐藏跨系统工作流
• 13.2 SQL 与 PL/pgSQL 函数
  • 13.2.1 参数、返回值、集合与多态
  • 13.2.2 波动性、严格性、并行安全与规划影响
  • 13.2.3 异常、子事务与错误契约
• 13.3 触发器与约束触发器
  • 13.3.1 行级、语句级与 transition table
  • 13.3.2 BEFORE、AFTER 与 INSTEAD OF
  • 13.3.3 递归、顺序、批量写入与隐藏成本
• 13.4 过程、任务与事务控制
  • 13.4.1 procedure 与 function 的边界
  • 13.4.2 批处理、维护任务与显式事务
  • 13.4.3 调度属于平台职责，不由过程本身解决
• 13.5 安全、测试与观测
  • 13.5.1 SECURITY DEFINER、固定 search_path 与最小权限
  • 13.5.2 单元测试、属性测试与并发测试
  • 13.5.3 函数级统计、日志与慢调用定位
• 13.6 实战：为订单状态建立数据库端护栏
  • 13.6.1 比较约束、函数、触发器与应用实现
  • 13.6.2 注入绕过应用的错误写入
  • 13.6.3 在 Pigsty L1 输出实现选择、测试证据与回退脚本

ch14 博采众长：内核分支与扩展生态

• 14.1 PostgreSQL 扩展机制
  • 14.1.1 control、SQL 脚本、动态库与对象所有权
  • 14.1.2 普通扩展、预加载库与超级用户需求
  • 14.1.3 扩展依赖、版本与 ALTER EXTENSION
• 14.2 内核、发行版与托管服务
  • 14.2.1 上游 PostgreSQL、补丁内核与兼容性承诺
  • 14.2.2 包仓库、容器镜像与托管白名单
  • 14.2.3 “兼容 PostgreSQL”需要逐层验证
• 14.3 扩展选型的六个问题
  • 14.3.1 它解决的具体问题和原生替代是什么
  • 14.3.2 数据格式是否锁定、能否导出和退出
  • 14.3.3 维护活跃度、许可证与商业连续性
  • 14.3.4 权限、崩溃面与供应链风险
• 14.4 生命周期与升级耦合
  • 14.4.1 安装版本不等于数据库对象版本
  • 14.4.2 大版本升级、备份恢复与逻辑复制兼容
  • 14.4.3 依赖扩展不可用时的降级策略
• 14.5 用 Pigsty 管理扩展可用性
  • 14.5.1 包、仓库、模板与节点差异
  • 14.5.2 声明安装与数据库内 CREATE EXTENSION
  • 14.5.3 从监控和日志识别加载失败
• 14.6 建立可复用扩展 ADR
  • 14.6.1 问题、候选、假设与成功标准
  • 14.6.2 最小 PoC、风险清单与退出路径
  • 14.6.3 结论的版本范围和复审触发器
• 14.7 实战：评审三个候选扩展
  • 14.7.1 一个接受、一个试点、一个拒绝
  • 14.7.2 在 L1 安装并验证原生对象与平台状态
  • 14.7.3 产出供 ch15–ch17 复用的 ADR 模板

ch15 见微知著：全文、模糊与向量检索

• 15.1 先定义检索任务与评估集
  • 15.1.1 精确筛选、词法相关与语义相似
  • 15.1.2 查询、候选、排序和过滤的分层
  • 15.1.3 建立带人工相关性标签的查询集合
• 15.2 PostgreSQL 全文检索
  • 15.2.1 文档、词典、配置与 tsvector
  • 15.2.2 查询语法、权重与相关性排序
  • 15.2.3 GIN/GiST 索引、更新与语言边界
• 15.3 模糊匹配与拼写容错
  • 15.3.1 pg_trgm 相似度与距离
  • 15.3.2 前缀、包含、拼写错误与候选召回
  • 15.3.3 与全文检索的互补和重复
• 15.4 可复现的向量检索
  • 15.4.1 维度、距离度量与归一化
  • 15.4.2 精确近邻与近似索引
  • 15.4.3 索引参数、过滤条件与召回代价
  • 15.4.4 冻结文本、模型标识、许可证、向量文件与校验和
• 15.5 混合检索与排序验证
  • 15.5.1 词法、模糊和语义候选合并
  • 15.5.2 分数归一、倒数排名融合与重排
  • 15.5.3 用标注集比较质量，不用单个“好看案例”
• 15.6 扩展部署与运行代价
  • 15.6.1 安装检索扩展并核对版本
  • 15.6.2 观察索引体积、构建、查询和维护
  • 15.6.3 外部嵌入生成的权限、费用与数据边界
• 15.7 实战：pg36_shop 商品混合检索 PoC
  • 15.7.1 用冻结向量离线复现实验
  • 15.7.2 比较全文、模糊、向量与混合结果
  • 15.7.3 输出 ADR、质量证据、生产代价与退出路径
  • 15.7.4 验收采用 checklist:evidence，不设脱离场景的性能线

ch16 经天纬地：时序、空间与时空查询

• 16.1 时间语义先于时序扩展
  • 16.1.1 事件时间、处理时间与有效时间
  • 16.1.2 时区、迟到、乱序与重复事件
  • 16.1.3 范围类型、窗口与时间对齐
• 16.2 时序表与时间分区
  • 16.2.1 从 ch04 的分区 ADR 选择时间键
  • 16.2.2 写入模式、冷热生命周期与保留
  • 16.2.3 原生分区、聚合与可选时序扩展
  • 16.2.4 不在本章重复在线分区化和维护细节
• 16.3 空间类型与坐标参考
  • 16.3.1 geometry、geography 与测量语义
  • 16.3.2 SRID、投影、单位与坐标转换
  • 16.3.3 点、线、面、边界与有效几何
• 16.4 空间谓词与索引
  • 16.4.1 包含、相交、邻近与最近邻
  • 16.4.2 包围盒过滤与精确计算
  • 16.4.3 GiST/SP-GiST 计划与选择率验证
• 16.5 时空联合查询是本章收束目标
  • 16.5.1 某时段、某区域内的配送事件
  • 16.5.2 轨迹、停留、地理围栏与迟到修正
  • 16.5.3 时间裁剪、空间索引与二阶段过滤
• 16.6 时空扩展的交付与观察
  • 16.6.1 安装 PostGIS 与可选时序扩展
  • 16.6.2 核对版本、依赖、备份和升级边界
  • 16.6.3 观察分区、索引、写入与聚合成本
• 16.7 实战：配送事件的时空 PoC
  • 16.7.1 生成确定性事件与地理数据
  • 16.7.2 验证 SRID 错误、裁剪失效和空间索引
  • 16.7.3 输出 ADR、PoC 证据与生产代价清单
  • 16.7.4 超预算时先删扩展专属细节，不删基础判断力

ch17 合纵连横：分析加速与分布式选型

• 17.1 先证明单机边界
  • 17.1.1 定义数据量、并发、延迟与新鲜度目标
  • 17.1.2 区分 CPU、I/O、内存、锁与计划瓶颈
  • 17.1.3 单机未被正确使用前不急于分布式
• 17.2 单机分析能力
  • 17.2.1 并行扫描、连接、聚合与限制
  • 17.2.2 分区、物化视图、增量汇总与批处理
  • 17.2.3 列式能力候选必须写入版本基线
  • 17.2.4 OLTP 与分析负载在同机共存的代价
• 17.3 何时需要分布式
  • 17.3.1 容量、吞吐、地域与组织边界
  • 17.3.2 分片键、数据局部性与跨分片事务
  • 17.3.3 一致性、运维复杂度与退出成本
• 17.4 比较分布式候选
  • 17.4.1 PostgreSQL 扩展、兼容数据库与专用分析系统
  • 17.4.2 SQL 兼容不等于事务、扩展和运维兼容
  • 17.4.3 用同一工作负载和失败条件比较
• 17.5 部署最小分布式 PoC
  • 17.5.1 明确 PoC 只验证一个关键假设
  • 17.5.2 记录组件、版本、拓扑和数据分布
  • 17.5.3 不把演示集群的绝对性能外推到生产
• 17.6 实战：从单机证据到选型 ADR
  • 17.6.1 证明一个边界，拒绝一个伪瓶颈
  • 17.6.2 比较单机加速与一个分布式候选
  • 17.6.3 输出 ADR、PoC 证据、生产代价和撤退路线
  • 17.6.4 验收采用 checklist:evidence

ch18 万法归宗：PostgreSQL 数据平台与替代边界

• 18.1 从数据库产品到能力组合
  • 18.1.1 事务、检索、时空、分析与任务能力
  • 18.1.2 计算、存储、接入、控制与观察平面
  • 18.1.3 组件组合必须有统一服务目标
• 18.2 PostgreSQL 的强项与代价
  • 18.2.1 关系一致性、可扩展类型与统一查询
  • 18.2.2 通用性带来的资源竞争与维护责任
  • 18.2.3 扩展能力不自动等于生产就绪
• 18.3 明确替代边界
  • 18.3.1 缓存、消息、对象存储与离线湖仓
  • 18.3.2 超大规模检索、流处理与专用分析
  • 18.3.3 用数据所有权、时效和一致性决定分工
• 18.4 平台服务目录与多租户
  • 18.4.1 服务等级、规格、版本与扩展套餐
  • 18.4.2 数据库、模式、实例和集群隔离
  • 18.4.3 成本归属、配额和生命周期
• 18.5 Pigsty 作为参考实现
  • 18.5.1 把 PostgreSQL、HA、备份、接入和观察组合起来
  • 18.5.2 哪些能力开箱可用，哪些仍需组织流程
  • 18.5.3 不把参考实现冒充唯一架构
• 18.6 实战：设计 pg36_shop 生产蓝图
  • 18.6.1 选择保留在 PostgreSQL 内的能力
  • 18.6.2 选择外置组件及其数据契约
  • 18.6.3 输出服务目录草案、架构 ADR 与下卷验收问题

下卷：运维管理

从生产服务规划到日常运营、事故恢复与改进

第四篇：规划——建设可交付的 PostgreSQL 服务

ch19 开天辟地：环境规划与部署基线

• 19.1 先写服务需求
  • 19.1.1 业务重要性、数据分类与所有者
  • 19.1.2 负载形态、增长、峰谷和批处理窗口
  • 19.1.3 可用性、RPO、RTO 与维护窗口
• 19.2 计算、内存、存储与网络
  • 19.2.1 CPU 核数、频率、NUMA 与虚拟化
  • 19.2.2 内存预算、页缓存与 OOM 边界
  • 19.2.3 IOPS、吞吐、时延、容量与冗余
  • 19.2.4 时钟、DNS、带宽、防火墙与故障域
• 19.3 操作系统与主机基线
  • 19.3.1 文件系统、挂载、预读与透明大页
  • 19.3.2 用户、目录、权限、时间同步与日志
  • 19.3.3 基线检查必须记录事实而非套用调优模板
• 19.4 版本与数据库初始化契约
  • 19.4.1 PostgreSQL、locale、collation、编码与 checksum
  • 19.4.2 WAL、页大小、扩展与认证前提
  • 19.4.3 版本矩阵、升级窗口与勘误入口
• 19.5 拓扑、命名与故障域
  • 19.5.1 主节点、同步副本、异步副本与仲裁
  • 19.5.2 节点、实例、集群、服务的统一词表
  • 19.5.3 环境、区域、租户与业务命名
• 19.6 用声明式清单交付两个服务单元
  • 19.6.1 inventory、参数模板与主机分组
  • 19.6.2 生产服务与隔离验证服务
  • 19.6.3 幂等部署、差异检查与失败重跑
• 19.7 实战：L2 部署验收
  • 19.7.1 核对版本、拓扑、资源、端点和安全入口
  • 19.7.2 从 SQL、主机与 Pigsty 三侧验证同一事实
  • 19.7.3 产出基线清单、风险例外与 reset:cluster

ch20 狡兔三窟：高可用拓扑与容灾目标

• 20.1 从失败模型设计高可用
  • 20.1.1 进程、主机、磁盘、网络、机房与控制面故障
  • 20.1.2 RPO、RTO、降级目标与数据风险
  • 20.1.3 高可用不等于备份，也不等于零数据丢失
• 20.2 物理流复制
  • 20.2.1 WAL 发送、接收、重放与 LSN
  • 20.2.2 timeline、恢复目标与历史分叉
  • 20.2.3 复制槽、归档与 WAL 保留
• 20.3 同步策略与提交语义
  • 20.3.1 异步、同步与远程应用确认
  • 20.3.2 多副本同步集合与退化条件
  • 20.3.3 延迟、可用性和数据保护的交换
• 20.4 选主、DCS 与防脑裂
  • 20.4.1 Patroni、租约、leader lock 与健康判断
  • 20.4.2 fencing、watchdog 与旧主隔离
  • 20.4.3 DCS 可用性与数据库可用性不是同一件事
• 20.5 切换、故障转移与重加入
  • 20.5.1 planned switchover 与 unplanned failover
  • 20.5.2 端点切换、客户端恢复与只读窗口
  • 20.5.3 pg_rewind、重建与时间线验证
• 20.6 交付并观察 HA 集群
  • 20.6.1 拓扑、同步策略与服务端点声明
  • 20.6.2 从 Patroni、SQL 和指标验证角色
  • 20.6.3 演练动作对应的 Pigsty 入口与原生证据
• 20.7 实战：一次有证据的计划切换
  • 20.7.1 预检查、切换、客户端观察与数据核对
  • 20.7.2 测量实际 RTO、提交风险与恢复时间
  • 20.7.3 输出拓扑证据、时间线和改进项
  • 20.7.4 记录把本章迁移到新版本基线的工时

ch21 未雨绸缪：备份体系与恢复演练

• 21.1 从恢复场景设计备份
  • 21.1.1 误删、介质故障、区域故障与合规留存
  • 21.1.2 RPO、RTO、恢复粒度与保留周期
  • 21.1.3 逻辑、物理、快照和副本的职责边界
• 21.2 物理备份与 WAL 连续性
  • 21.2.1 基础备份、检查点与一致性起点
  • 21.2.2 归档、timeline history 与恢复链
  • 21.2.3 复制槽、归档失败与 WAL 保留者
• 21.3 备份仓库与保留策略
  • 21.3.1 全量、差异、增量与过期
  • 21.3.2 校验、加密、不可变与异地副本
  • 21.3.3 容量预算、失败告警与责任人
• 21.4 恢复流程与验证
  • 21.4.1 选择备份集、目标时间和恢复位置
  • 21.4.2 启动前核对时间线与 WAL 完整性
  • 21.4.3 数据库一致不等于业务数据正确
• 21.5 用 pgBackRest 交付备份策略
  • 21.5.1 仓库、策略、调度与凭据
  • 21.5.2 备份状态、归档状态与容量观察
  • 21.5.3 在隔离目标而不是原集群上恢复
• 21.6 实战：完成一次隔离恢复演练
  • 21.6.1 创建已知业务检查点并执行备份
  • 21.6.2 恢复到隔离集群，核对数据库与业务不变量
  • 21.6.3 输出 RPO/RTO 实测、证据链和失败处理 SOP

ch22 四通八达：服务接入、连接池与路由

• 22.1 服务端点的语义
  • 22.1.1 主写、只读、同步只读与直连管理端点
  • 22.1.2 复制延迟、一致性与 read-your-writes
  • 22.1.3 DNS、VIP、代理与客户端发现
• 22.2 连接的服务端成本
  • 22.2.1 后端进程、内存、事务与会话状态
  • 22.2.2 max_connections 不是容量规划答案
  • 22.2.3 应用池、代理池与数据库预算
• 22.3 PgBouncer 池化模式
  • 22.3.1 session、transaction、statement pooling
  • 22.3.2 临时表、会话 GUC、监听与咨询锁
  • 22.3.3 预备语句支持必须绑定 PgBouncer 与驱动版本
  • 22.3.4 池等待、服务时间与背压
• 22.4 路由与故障切换
  • 22.4.1 HAProxy 健康检查与角色判断
  • 22.4.2 旧连接、重连风暴与客户端退避
  • 22.4.3 故障切换中的 DNS、连接池和事务失败
• 22.5 连接预算与过载边界
  • 22.5.1 按服务分配连接、并发与队列
  • 22.5.2 超时层级、取消传播与熔断
  • 22.5.3 为 ch34 的止血动作预留控制点
• 22.6 Pigsty 服务接入层
  • 22.6.1 服务定义、角色选择与端口
  • 22.6.2 PgBouncer、HAProxy 与数据库的证据链
  • 22.6.3 配置变更、reload 与连接行为验证
• 22.7 实战：写入、只读与管理三类接入
  • 22.7.1 为 pg36_shop 配置端点和连接预算
  • 22.7.2 验证会话状态、预备语句与只读一致性
  • 22.7.3 注入切换与连接风暴，观察退避和恢复

ch23 固若金汤：认证、授权与数据安全

• 23.1 威胁模型与信任边界
  • 23.1.1 用户、应用、运维、平台与第三方
  • 23.1.2 网络、凭据、SQL、备份和日志攻击面
  • 23.1.3 数据分级、租户边界与应急权限
• 23.2 认证与连接准入
  • 23.2.1 pg_hba.conf 的匹配顺序与证据
  • 23.2.2 SCRAM、证书与外部身份
  • 23.2.3 TLS 验证、吊销与密钥轮换
• 23.3 角色与最小权限
  • 23.3.1 login、group、owner 与 runtime role
  • 23.3.2 schema、table、sequence、function 权限
  • 23.3.3 默认权限、所有权迁移与越权路径
• 23.4 行级安全与连接池上下文
  • 23.4.1 RLS policy、owner bypass 与强制 RLS
  • 23.4.2 租户身份通过事务参数传递
  • 23.4.3 transaction pooling 下使用 SET LOCAL
  • 23.4.4 验证复用连接不会泄漏上一个租户状态
• 23.5 密钥、审计与敏感信息
  • 23.5.1 凭据生成、存放、轮换和撤销
  • 23.5.2 审计目标、日志范围与访问控制
  • 23.5.3 参数、SQL 文本与日志脱敏
• 23.6 Pigsty 安全基线
  • 23.6.1 角色、HBA、证书与服务入口声明
  • 23.6.2 管理面、监控面和数据库面的网络边界
  • 23.6.3 从配置渲染到运行事实的差异检查
• 23.7 实战：隔离两个租户
  • 23.7.1 建立应用角色、迁移角色和只读角色
  • 23.7.2 通过 PgBouncer 事务池验证 RLS
  • 23.7.3 注入会话状态泄漏与越权访问并修复
  • 23.7.4 输出权限矩阵、轮换证据与应急回收步骤

ch24 纲举目张：SLO、SOP 与组织治理

• 24.1 服务目录与责任模型
  • 24.1.1 服务所有者、数据所有者与平台所有者
  • 24.1.2 等级、规格、依赖、值班与升级路径
  • 24.1.3 实例健康不等于业务服务健康
• 24.2 SLI、SLO 与错误预算
  • 24.2.1 可用性、延迟、正确性与数据新鲜度
  • 24.2.2 测量点、统计窗口与排除条件
  • 24.2.3 错误预算如何约束变更速度
• 24.3 SOP、Runbook 与变更治理
  • 24.3.1 日常操作、故障处置与恢复演练
  • 24.3.2 申请、评审、执行、验证与回退
  • 24.3.3 高风险动作的双人或延迟确认
• 24.4 观察与告警契约
  • 24.4.1 每个 SLI 的数据源、查询、维度和缺失语义
  • 24.4.2 告警必须绑定用户影响、首个安全动作和所有者
  • 24.4.3 症状告警、原因告警与容量预测分开
  • 24.4.4 产出供 ch25 实现的告警规则清单
• 24.5 证据、审计与合规
  • 24.5.1 配置、变更、访问和恢复证据
  • 24.5.2 保留、不可抵赖与隐私边界
  • 24.5.3 用自动检查减少人工表格
• 24.6 实战：把 pg36_shop 纳入服务治理
  • 24.6.1 发布服务卡、SLO、责任人与升级路径
  • 24.6.2 为备份、切换、权限和发布建立 SOP
  • 24.6.3 输出观察契约，并拒绝没有动作的告警候选

第五篇：运营——用证据驱动日常维护与演进

ch25 望闻问切：监控体系与可观测诊断

• 25.1 从问题选择可观测信号
  • 25.1.1 用户体验、服务入口、数据库与主机四层
  • 25.1.2 指标、日志、事件和追踪各回答什么
  • 25.1.3 标签基数、采样、保留与缺失数据
• 25.2 PostgreSQL 核心运行信号
  • 25.2.1 会话、事务、等待与锁
  • 25.2.2 缓冲、I/O、WAL、检查点与复制
  • 25.2.3 vacuum、冻结、膨胀与对象增长
• 25.3 SQL 可观测基线
  • 25.3.1 pg_stat_statements 的统计口径与重置
  • 25.3.2 慢语句、锁等待、临时文件与错误日志
  • 25.3.3 auto_explain 的采样、嵌套语句与开销
  • 25.3.4 日志不得泄漏密码、令牌和敏感参数
• 25.4 把观察契约变成告警
  • 25.4.1 SLO 燃烧率与用户影响
  • 25.4.2 阈值、持续时间、去抖、抑制与分组
  • 25.4.3 每条告警绑定所有者、证据和首个安全动作
  • 25.4.4 用演练验证告警，而不是等生产事故
• 25.5 Pigsty 可观测体系
  • 25.5.1 采集、存储、规则、面板与通知链
  • 25.5.2 以指标语义定位集群、实例、数据库和查询
  • 25.5.3 面板结论回到 SQL、日志与主机事实复核
• 25.6 从告警到诊断包
  • 25.6.1 自动保存时间窗、拓扑、变更与关键查询
  • 25.6.2 区分首发症状、伴随现象与根因证据
  • 25.6.3 证据采集本身的负载和权限边界
• 25.7 实战：实现并演练观察契约
  • 25.7.1 为延迟、错误、复制、备份和资源建立规则
  • 25.7.2 注入可控症状，验证触发、路由、抑制和恢复
  • 25.7.3 输出告警覆盖表、盲区与 ch31 的诊断入口

ch26 胸有成竹：容量规划与压测基线

• 26.1 从需求建立容量模型
  • 26.1.1 事务类型、读写比、并发和数据增长
  • 26.1.2 平均值、峰值、突发与批处理叠加
  • 26.1.3 延迟目标、错误预算与安全余量
• 26.2 设计代表性工作负载
  • 26.2.1 内置 pgbench 与业务自定义脚本
  • 26.2.2 参数分布、事务混合和数据倾斜
  • 26.2.3 think time、连接方式和客户端瓶颈
• 26.3 建立可信实验
  • 26.3.1 固定硬件、版本、配置、数据与随机种子
  • 26.3.2 预热、重复、置信区间与异常值
  • 26.3.3 冷热缓存、后台任务与邻居噪声
  • 26.3.4 绝对性能结论只适用于记录过的环境
• 26.4 找到饱和点与瓶颈
  • 26.4.1 吞吐—延迟曲线与排队拐点
  • 26.4.2 CPU、内存、I/O、WAL 与锁的证据
  • 26.4.3 连接数增加为何可能降低吞吐
• 26.5 从测量推导容量与成本
  • 26.5.1 单位业务量的资源消耗
  • 26.5.2 增长、保留、备份与维护空间
  • 26.5.3 扩容触发线、提前期与失效假设
• 26.6 实战：pg36_shop 容量基线
  • 26.6.1 运行三个规模和两个并发档位
  • 26.6.2 用 Pigsty 与原生视图解释饱和点
  • 26.6.3 输出可复现实验报告、容量模型和未知项

ch27 精益求精：参数调优与资源治理

• 27.1 调优是一套实验方法
  • 27.1.1 先定义目标、瓶颈和不可退化指标
  • 27.1.2 一次改变一个机制并准备回退
  • 27.1.3 参数变化不修复错误 SQL 和错误模型
• 27.2 内存预算
  • 27.2.1 shared buffers、操作系统页缓存与双重缓存
  • 27.2.2 work_mem 按节点、并发和并行放大
  • 27.2.3 maintenance、autovacuum 与后台进程内存
  • 27.2.4 OOM 风险必须用最坏并发估算
• 27.3 WAL、检查点与写入平滑
  • 27.3.1 WAL 生成、刷盘与提交延迟
  • 27.3.2 检查点频率、写突发与恢复时间
  • 27.3.3 压缩、全页写与归档代价
• 27.4 规划器、并行与连接参数
  • 27.4.1 成本参数只能用硬件和计划证据校准
  • 27.4.2 并行 worker 的全局预算与退化条件
  • 27.4.3 连接上限、超时和锁等待边界
• 27.5 参数作用域与变更方式
  • 27.5.1 编译、初始化、启动、reload 与会话级
  • 27.5.2 系统、数据库、角色与事务覆盖层
  • 27.5.3 配置漂移、审计、回退和滚动风险
• 27.6 模板参数与集群变更
  • 27.6.1 从模板生成实例配置
  • 27.6.2 区分 reload、restart 与滚动执行
  • 27.6.3 用 SQL 和文件事实验证最终生效值
• 27.7 实战：只调一个已证实的瓶颈
  • 27.7.1 从 ch26 证据提出参数假设
  • 27.7.2 比较收益、副作用和故障恢复
  • 27.7.3 输出参数 ADR、回退条件与拒绝修改项

ch28 除旧布新：VACUUM、冻结与膨胀治理

• 28.1 死元组与可见性
  • 28.1.1 UPDATE/DELETE 如何产生旧版本
  • 28.1.2 vacuum、prune、HOT 与可见性图
  • 28.1.3 回收可重用空间不等于归还文件系统
• 28.2 autovacuum 的触发与资源
  • 28.2.1 阈值、比例、插入触发与表级覆盖
  • 28.2.2 worker、cost delay、I/O 与业务竞争
  • 28.2.3 进度、阻塞与“为什么没清掉”
• 28.3 冻结、XID 与保留者
  • 28.3.1 XID 年龄、冻结与回卷保护
  • 28.3.2 长事务、backend_xmin 与 idle in transaction
  • 28.3.3 复制槽 xmin 与孤儿 pg_prepared_xacts
  • 28.3.4 紧急态先解除保留并让 VACUUM 完成
• 28.4 膨胀与重建
  • 28.4.1 表膨胀、索引膨胀与统计误判
  • 28.4.2 VACUUM FULL、在线重建与额外空间
  • 28.4.3 REINDEX CONCURRENTLY 的版本和失败处理
• 28.5 分区生命周期
  • 28.5.1 新分区预建、约束和父表显式 ANALYZE
  • 28.5.2 DETACH、归档、验证后删除
  • 28.5.3 用分区退役替代大批量 DELETE
  • 28.5.4 将 ch04/ch07/ch11/ch16/ch28 串成能力索引
• 28.6 amcheck 与例行完整性检查
  • 28.6.1 bt_index_check 与更深检查的成本
  • 28.6.2 heapallindexed、锁与业务窗口
  • 28.6.3 amcheck 不替代数据页 checksum 和备份恢复
• 28.7 实战：建立维护节奏
  • 28.7.1 制造膨胀、长事务与分区到期
  • 28.7.2 从指标与原生视图判定维护优先级
  • 28.7.3 执行清理、检查和分区退役并验证副作用
  • 28.7.4 输出维护清单及 ch34/ch35 的安全路由

ch29 移花接木：逻辑复制、迁移与异构同步

• 29.1 逻辑复制原语
  • 29.1.1 publication、subscription 与 replication slot
  • 29.1.2 初始同步、流式变更与复制身份
  • 29.1.3 DDL、序列、大对象与冲突边界
• 29.2 CDC 与复制槽治理
  • 29.2.1 逻辑解码、插件与消费位点
  • 29.2.2 至少一次、重复事件与下游幂等
  • 29.2.3 槽停滞、WAL 保留与磁盘风险
• 29.3 批量装载与数据校验
  • 29.3.1 COPY、并行、约束和索引顺序
  • 29.3.2 行数、摘要、分桶与业务不变量
  • 29.3.3 装载速度不能牺牲可追溯错误
• 29.4 在线迁移状态机
  • 29.4.1 预检查、全量、增量、追平与冻结窗口
  • 29.4.2 影子读、双读、切流和观察
  • 29.4.3 回退点、前滚点与不可逆动作
• 29.5 异构同步的语义损失
  • 29.5.1 类型、精度、排序规则与时区
  • 29.5.2 约束、事务顺序与删除语义
  • 29.5.3 目标端可查询不等于语义等价
• 29.6 多集群迁移环境
  • 29.6.1 源、目标、验证端点与隔离凭据
  • 29.6.2 观察槽、WAL、延迟与切换流量
  • 29.6.3 保留源环境直到退出观察窗口
• 29.7 实战：迁移 pg36_shop
  • 29.7.1 完成全量加增量同步
  • 29.7.2 注入消费者停滞与数据差异
  • 29.7.3 验证、切流、回退并输出迁移证据包

ch30 推陈出新：版本升级与回滚策略

• 30.1 先识别变化类型
  • 30.1.1 小版本、安全修复与大版本
  • 30.1.2 SQL 行为、系统目录、参数和默认值
  • 30.1.3 驱动、连接池、备份与观察组件兼容
• 30.2 三类大版本升级路径
  • 30.2.1 pg_upgrade 与停机窗口
  • 30.2.2 逻辑复制与渐进切换
  • 30.2.3 dump/restore 与重建机会
  • 30.2.4 没有一种路径天然“滚动无感”
• 30.3 扩展与依赖升级
  • 30.3.1 二进制包、数据库对象和预加载顺序
  • 30.3.2 扩展升级脚本与不可降级路径
  • 30.3.3 从 ch14 ADR 获取退出与兼容信息
• 30.4 locale、collation 与索引风险
  • 30.4.1 libc、ICU 与排序规则版本
  • 30.4.2 排序变化对唯一性和索引顺序的影响
  • 30.4.3 识别受影响对象并规划重建
• 30.5 升级前检查与业务验证
  • 30.5.1 系统目录、无效对象与长事务
  • 30.5.2 amcheck、checksum 状态与备份恢复证据
  • 30.5.3 查询结果、计划、性能和业务不变量基线
  • 30.5.4 amcheck 与 checksum 检测对象不同
• 30.6 用隔离环境完成升级彩排
  • 30.6.1 克隆数据与版本化配置
  • 30.6.2 执行升级、扩展更新和服务接入验证
  • 30.6.3 比较 Pigsty 面板与原生证据的前后差异
• 30.7 实战：前滚、回退与发布决策
  • 30.7.1 注入一个扩展或排序规则兼容问题
  • 30.7.2 在业务写入恢复前验证回退路径
  • 30.7.3 输出升级 runbook、决策门和观察窗口

第六篇：出山——按响应目标演练恢复与改进

ch31 事件分级、现场保护与应急决策——枕戈待旦

• 31.1 事件分级与响应目标
  • 31.1.1 用户影响、数据风险、范围与持续时间
  • 31.1.2 恢复数据、恢复拓扑、释放流量压力、抢救完整性
  • 31.1.3 严重度决定节奏，不替代技术判型
• 31.2 第一原则：保护现场与可恢复性
  • 31.2.1 暂停自动化、危险变更与证据覆盖
  • 31.2.2 记录时间、拓扑、版本、告警与最近变更
  • 31.2.3 先克隆、隔离或只读，再做破坏性尝试
• 31.3 从症状路由而不是猜根因
  • 31.3.1 误删误改与恢复目标 → ch32《PITR 与误操作恢复》
  • 31.3.2 主节点、复制或 DCS 异常 → ch33《故障切换与集群重建》
  • 31.3.3 连接、延迟、CPU、内存、I/O 或磁盘表象 → ch34《过载保护与资源故障判型》
  • 31.3.4 checksum、索引、排序或逻辑不一致 → ch35《数据抢救与工程取证》
• 31.4 决策、沟通与变更纪律
  • 31.4.1 事实、假设、动作、预期与停止条件
  • 31.4.2 单一指挥、记录员、执行者与业务接口
  • 31.4.3 高风险动作的复核、审批与回退
• 31.5 单人值守与团队响应
  • 31.5.1 单人时先稳定、记录，再逐级升级
  • 31.5.2 团队时避免多人同时改同一系统
  • 31.5.3 何时必须请求业务、存储、安全或厂商协助
• 31.6 实战：盲抽症状的桌面演练
  • 31.6.1 随机症状、误导性首发告警与缺失信息
  • 31.6.2 分别按单人和团队模式完成前十五分钟
  • 31.6.3 在 Pigsty L3 输出时间线、决策日志、证据包与路由选择

ch32 PITR 与误操作恢复——妙手回春

• 32.1 先界定误操作
  • 32.1.1 错误 UPDATE、DELETE、DROP 与批处理
  • 32.1.2 影响对象、开始时间、结束时间和持续写入
  • 32.1.3 逻辑补偿、对象恢复与整库 PITR 的选择
• 32.2 恢复目标与时间线
  • 32.2.1 时间、LSN、事务 ID 与命名恢复点
  • 32.2.2 timeline 分叉与“恢复到刚刚之前”
  • 32.2.3 时钟、时区和证据误差
• 32.3 隔离恢复策略
  • 32.3.1 不覆盖仍可取证的原集群
  • 32.3.2 选择备份、WAL 与目标环境
  • 32.3.3 控制网络、凭据和外部副作用
• 32.4 执行恢复并观察进度
  • 32.4.1 从已验证备份克隆恢复目标
  • 32.4.2 监控 WAL 重放、目标达成与启动状态
  • 32.4.3 用原生文件、日志和 SQL 复核工具状态
• 32.5 数据验证与安全回切
  • 32.5.1 行数、摘要、关键交易与跨表不变量
  • 32.5.2 提取差异、逻辑补回或切换整个服务
  • 32.5.3 防止恢复环境向外重复发送消息和支付
• 32.6 实战：随机恢复目标演练
  • 32.6.1 在隐藏时间窗内注入误更新
  • 32.6.2 独立定位目标、恢复、验证与回切
  • 32.6.3 测量 RPO/RTO 并记录版本迁移工时
  • 32.6.4 输出恢复证据和备份体系改进项

ch33 故障切换与集群重建——力挽狂澜

• 33.1 先识别失败域
  • 33.1.1 进程失败、主机失败与存储失败
  • 33.1.2 网络分区、客户端不可达与代理误判
  • 33.1.3 主库失败、复制停滞与控制面失败
• 33.2 复制状态与时间线证据
  • 33.2.1 发送、接收、重放位置与延迟
  • 33.2.2 timeline、历史文件与分叉
  • 33.2.3 数据最新不等于可以安全提升
• 33.3 自动故障转移的保护条件
  • 33.3.1 候选健康、数据风险与多数判断
  • 33.3.2 fencing 旧主与防止双写
  • 33.3.3 自动化不确定时何时转人工
• 33.4 DCS 故障的安全处理
  • 33.4.1 DCS 不可达、失去多数与延迟
  • 33.4.2 先保护当前数据库角色，不盲目重置选举状态
  • 33.4.3 恢复控制面后核对 leader lock 与数据库事实
• 33.5 旧主重加入与集群重建
  • 33.5.1 pg_rewind 的前提、失败与验证
  • 33.5.2 从备份或新基础备份重建
  • 33.5.3 复制槽、端点和客户端状态清理
• 33.6 切换与重建 runbook
  • 33.6.1 计划切换、故障切换与人工干预入口
  • 33.6.2 Patroni、DCS、代理和 SQL 证据互证
  • 33.6.3 集群恢复后重新建立监控与备份健康
• 33.7 实战：主库故障与 DCS 干扰
  • 33.7.1 随机注入主机、网络或 DCS 症状
  • 33.7.2 保护旧主、选择候选、测量 RTO/RPO
  • 33.7.3 重建旧主并验证时间线、端点和业务写入

ch34 过载保护与资源故障判型——李代桃僵

• 34.1 第一动作：流量型还是保留型
  • 34.1.1 流量增长、慢查询、锁与连接导致的竞争
  • 34.1.2 WAL、XID、复制槽、归档和长事务导致的保留
  • 34.1.3 同样表现为“磁盘满”或“延迟高”，动作可以相反
  • 34.1.4 判型不清时先停止破坏性清理
• 34.2 连接风暴与排队失控
  • 34.2.1 数据库连接、代理池与应用池三层
  • 34.2.2 重试放大、健康检查和短连接
  • 34.2.3 限流、队列、连接预算与指数退避
• 34.3 失控查询、锁与事务
  • 34.3.1 识别高消耗查询和阻塞根节点
  • 34.3.2 cancel、terminate 与中止后成本
  • 34.3.3 长事务和大事务结束前先评估后果
• 34.4 CPU、内存、I/O 与 OOM
  • 34.4.1 饱和、排队、抖动与抢占
  • 34.4.2 临时文件、并行、checkpoint 与后台维护
  • 34.4.3 内存最坏并发、OOM killer 与进程重启
• 34.5 流量型止血动作
  • 34.5.1 限流、熔断、摘除非关键负载
  • 34.5.2 取消查询、暂停批处理与只读降级
  • 34.5.3 每个动作写清收益、代价、停止条件和回退
• 34.6 保留型故障的安全路由
  • 34.6.1 XID：检查 backend_xmin、复制槽 xmin 与 pg_prepared_xacts
  • 34.6.2 WAL 撑盘：检查归档失败、复制槽和未完成备份
  • 34.6.3 绝不手工删除 pg_wal；保护现场后转 ch21/ch28/ch35
  • 34.6.4 本章的一般止血动作不构成保留型修复
• 34.7 平台级流量控制与证据
  • 34.7.1 从服务端点隔离批处理和只读流量
  • 34.7.2 用连接池、代理与应用控制点逐级减压
  • 34.7.3 从面板判断范围，再用 SQL 与主机证据判型
• 34.8 实战：同一症状、两种成因
  • 34.8.1 随机注入连接风暴或 WAL 保留
  • 34.8.2 在不知道答案时先判型，再选择动作
  • 34.8.3 对流量型恢复服务，对保留型完成安全路由
  • 34.8.4 输出动作时间线、误判代价与容量改进项

ch35 数据抢救与工程取证——起死回生

• 35.1 现场保护与操作边界
  • 35.1.1 停写、只读、快照、克隆与证据哈希
  • 35.1.2 记录硬件、内核、日志、版本和最近变更
  • 35.1.3 不在唯一副本上反复试错
  • 35.1.4 绝不手工删除 pg_wal 或原始损坏文件
• 35.2 先分类再抢救
  • 35.2.1 物理页、存储与 checksum 错误
  • 35.2.2 索引、排序规则与派生结构错误
  • 35.2.3 逻辑不变量、应用写错与语义损坏
  • 35.2.4 三类问题的恢复来源不同
• 35.3 页与 checksum 证据
  • 35.3.1 checksum 是否启用及其检测边界
  • 35.3.2 日志、块号、关系文件与物理定位
  • 35.3.3 存储故障先修基础设施，再谈数据库重建
• 35.4 索引、collation 与 amcheck
  • 35.4.1 bt_index_check、heapallindexed 与锁成本
  • 35.4.2 collation 版本变化与索引顺序异常
  • 35.4.3 索引可重建不意味着堆表数据安全
• 35.5 抽取、跳过与重建策略
  • 35.5.1 优先从备份、健康副本或逻辑来源恢复
  • 35.5.2 对可读数据做分段抽取和校验
  • 35.5.3 危险恢复参数只在克隆现场、明确损失下使用
  • 35.5.4 何时停止自救并升级到专业支持
• 35.6 工程取证与业务验证
  • 35.6.1 保留原始证据、操作副本和完整时间线
  • 35.6.2 区分“数据库能启动”与“业务数据可信”
  • 35.6.3 记录不可恢复范围与合规沟通
• 35.7 实战：在克隆环境分类并恢复
  • 35.7.1 仅在启用 checksum 的专用镜像注入可控页异常
  • 35.7.2 另设索引或 collation 异常，随机隐藏故障类型
  • 35.7.3 用备份、重建或抽取恢复并验证不变量
  • 35.7.4 用 reset:host 重建 Pigsty L3，不复用损坏现场

ch36 事故复盘、控制固化与平台演进——举一反三

• 36.1 服务恢复不等于事件结束
  • 36.1.1 恢复用户影响、数据正确性与运行余量
  • 36.1.2 清理临时降级、应急权限和旁路配置
  • 36.1.3 通知、观察窗口与正式结案条件
• 36.2 从时间线建立因果链
  • 36.2.1 触发条件、放大机制与失效防线
  • 36.2.2 技术、流程、组织与认知因素
  • 36.2.3 避免单一根因和个人归罪
• 36.3 证据质量与决策复盘
  • 36.3.1 哪些事实当时可见，哪些后来才知道
  • 36.3.2 哪些假设被验证，哪些动作靠运气
  • 36.3.3 告警、日志和时间同步缺口
• 36.4 把行动项变成控制
  • 36.4.1 所有者、截止时间、验证方法与失效条件
  • 36.4.2 自动检查、发布门、容量线与恢复演练
  • 36.4.3 不能验证的“加强意识”不是合格行动项
• 36.5 回写 SLO、SOP 与架构 ADR
  • 36.5.1 更新观察契约、告警规则与 runbook
  • 36.5.2 修正 RPO/RTO、容量和安全假设
  • 36.5.3 将必要变更纳入服务目录与版本路线
• 36.6 将控制固化到平台
  • 36.6.1 配置模板、验证脚本与策略即代码
  • 36.6.2 备份、切换、容量和维护的周期演练
  • 36.6.3 从单个补丁升级为默认护栏
• 36.7 实战：复盘四类事故并完成全书结业
  • 36.7.1 汇总 ch32–ch35 的证据、决策与用户影响
  • 36.7.2 找出跨事故重复出现的控制缺口
  • 36.7.3 输出 90 天改进路线、平台 backlog 与复验计划
  • 36.7.4 回看从 SQL 到生产的能力地图

附录

• 附录 A
• 附录 B
• 附录 C
• 附录 D
• 附录 E
• 附录 F

上卷：应用开发

本卷导读：上卷面向应用开发者与数据库工程实践者，沿着“认识系统—可靠建模—正确查询—性能与并发—安全交付—能力扩展”的路径，建立从 PostgreSQL 原理到 Pigsty 实验闭环的完整开发能力。

本卷定位

从 PostgreSQL 工程认知到应用交付与能力扩展

第一次系统学习，建议按三篇顺序推进；已有明确问题时，可从下方索引直接进入相应章节。

本卷索引

第一篇：筑基——建立 PostgreSQL 工程认知

ch01 盲人摸象：PostgreSQL 与 Pigsty 全局地图

回答“我连到了什么、数据对象在哪里、一次查询经过什么、Pigsty 又管理了什么”，并固化后续章节共同使用的实验基线。

• 1.1 从连接串识别操作落点
• 1.2 PostgreSQL 对象与术语坐标
• 1.3 一条查询经过了什么
• 1.4 从数据库实例到数据库服务
• 1.5 Pigsty 的资源模型
• 1.6 最小 psql 生存卡
• 1.7 实战：建立 pg36_shop 地图与实验基线

ch02 手到擒来：psql 与可复现工作流

掌握后续 34 章反复使用的最小工具链，把临时手工操作变成可审查、可验证、可重跑的任务。

• 2.1 可靠连接与上下文保护
• 2.2 用 psql 探索与取证
• 2.3 编写可靠 SQL 脚本
• 2.4 输入、输出与确定性数据
• 2.5 最小 pgbench 工作负载
• 2.6 最小逻辑备份闭环
• 2.7 实战：把人工操作变成可重跑任务

ch03 正本清源：从业务规则到关系模型

先表达业务事实、不变量与所有权，再把逻辑模型 v0 跑进真实数据库；本章模式是过渡版本，可靠物理模式在 ch04 闭合。

• 3.1 从业务语言提取数据库事实
• 3.2 标识、主键与业务键
• 3.3 关系与引用完整性
• 3.4 模式、所有权与对象边界
• 3.5 规范化与有意识的冗余
• 3.6 实战：建立逻辑模型 v0

ch04 量体裁衣：数据类型、约束与可靠数据表达

利用 PostgreSQL 类型系统、约束与分区决策，把 ch03 的逻辑模型逐项落成可靠物理模式。

• 4.1 金额、文本与时间
• 4.2 标识、状态与半结构化数据
• 4.3 NULL、默认值与生成值
• 4.4 用约束表达不变量
• 4.5 类型与约束的物理代价
• 4.6 分区决策门
• 4.7 实战：把逻辑模型落成可靠物理模式

ch05 运筹帷幄：查询、事务与锁的核心心智模型

建立执行计划、并发控制和故障诊断共同依赖的原理地图，不在本章穷举后续专题。

• 5.1 SQL 从文本到结果
• 5.2 MVCC 与可见性
• 5.3 事务边界与失败语义
• 5.4 锁与等待
• 5.5 隔离现象与后续路线
• 5.6 实战：观察一笔订单事务

ch06 立木取信：开发规约与交付基线

不提前宣判“最佳实践”，而是建立“候选规则—证据—适用范围—例外—验证”的规约生成方法，产出 baseline v0.1。

• 6.1 规约不是口号
• 6.2 连接与会话候选规则
• 6.3 模式与 DDL 候选规则
• 6.4 查询与事务候选规则
• 6.5 交付物与质量门
• 6.6 将规约接入统一实验环境
• 6.7 实战：发布规约 baseline v0.1

第二篇：应用——从 SQL 正确走向稳定交付

ch07 追本溯源：执行计划与统计信息

学会读计划、验证估算、解释计划变化，并建立分区裁剪和自动计划采样的正确边界。

• 7.1 优化器如何选择路径
• 7.2 正确使用 EXPLAIN
• 7.3 统计信息与估算偏差
• 7.4 分区裁剪的两种时机
• 7.5 参数、缓存计划与计划漂移
• 7.6 建立计划证据基线
• 7.7 实战：解释订单查询的计划变化

ch08 抽丝剥茧：慢 SQL 诊断方法论

从“用户说慢”出发，建立从范围界定、证据收集、假设排序到受控验证的诊断闭环，并集中示范全书反复使用的故障诊断方法。

• 8.1 先定义“慢”
• 8.2 从会话到语句定位范围
• 8.3 关联日志、指标与计划
• 8.4 建立而不是猜测假设
• 8.5 设计受控实验
• 8.6 从可观测面板回到原生证据
• 8.7 实战：三种“慢”只修真正瓶颈

ch09 巧夺天工：索引设计与效果验证

从访问模式而不是字段直觉设计索引，并用读取收益、写入代价与维护成本共同验收。

• 9.1 索引方法与操作符类
• 9.2 从谓词、连接与排序推导索引
• 9.3 表达式、部分与覆盖索引
• 9.4 索引也有写入和生命周期成本
• 9.5 验证而不是“加完就快”
• 9.6 实战：为订单、库存与搜索入口设计索引

ch10 顾此失彼：并发控制与隔离异常

在明确隔离级别和业务不变量的前提下重现并发异常，选择锁、条件更新、重试与幂等策略。

• 10.1 隔离级别与可观察现象
• 10.2 Lost update 不是一句口号
• 10.3 悲观锁与锁队列
• 10.4 乐观控制、重试与幂等
• 10.5 咨询锁与跨行协调
• 10.6 观察与诊断并发
• 10.7 实战：库存扣减与支付幂等

ch11 守正出奇：模式变更与安全发布

先判断锁、重写、扫描和兼容性，再设计可观察、可中止、可回退的模式发布；完成分区能力的第三个触点。

• 11.1 识别 DDL 的四类风险
• 11.2 Expand–Migrate–Contract
• 11.3 索引与约束的在线化路径
• 11.4 在线分区化
• 11.5 数据回填与流量切换
• 11.6 发布窗口中的平台观察
• 11.7 实战：无中断演进订单模式

ch12 一气呵成：从数据库契约到后端服务

把前十一章收束成一个可运行、可观测、可部署的最小服务；后续章节不再长期维护同一套 Go 代码。

• 12.1 数据库契约与应用边界
• 12.2 为服务设计查询接口
• 12.3 Go 服务中的连接与事务
• 12.4 会话状态与连接池陷阱
• 12.5 服务级可观测性
• 12.6 部署与接入 pg36_shop
• 12.7 实战：交付应用闭环与规约 v1.0

第三篇：扩展——扩大 PostgreSQL 的能力边界

ch13 言出法随：函数、触发器与存储过程

判断逻辑应该位于 SQL、数据库函数、触发器还是应用中，并能测试、观测和安全发布数据库端逻辑。

• 13.1 先决定逻辑放在哪里
• 13.2 SQL 与 PL/pgSQL 函数
• 13.3 触发器与约束触发器
• 13.4 过程、任务与事务控制
• 13.5 安全、测试与观测
• 13.6 实战：为订单状态建立数据库端护栏

ch14 博采众长：内核分支与扩展生态

不从“能安装”推导“该使用”，建立扩展发现、选型、供应链、升级与退出的统一 ADR。

• 14.1 PostgreSQL 扩展机制
• 14.2 内核、发行版与托管服务
• 14.3 扩展选型的六个问题
• 14.4 生命周期与升级耦合
• 14.5 用 Pigsty 管理扩展可用性
• 14.6 建立可复用扩展 ADR
• 14.7 实战：评审三个候选扩展

ch15 见微知著：全文、模糊与向量检索

从检索质量与业务语义出发，完成全文、模糊、向量和混合检索的最小可复现 PoC，并知道生产代价。

• 15.1 先定义检索任务与评估集
• 15.2 PostgreSQL 全文检索
• 15.3 模糊匹配与拼写容错
• 15.4 可复现的向量检索
• 15.5 混合检索与排序验证
• 15.6 扩展部署与运行代价
• 15.7 实战：pg36_shop 商品混合检索 PoC

ch16 经天纬地：时序、空间与时空查询

分别建立时间与空间数据的正确模型，最终用时空联合查询证明两者为何值得在同一章出现。

• 16.1 时间语义先于时序扩展
• 16.2 时序表与时间分区
• 16.3 空间类型与坐标参考
• 16.4 空间谓词与索引
• 16.5 时空联合查询是本章收束目标
• 16.6 时空扩展的交付与观察
• 16.7 实战：配送事件的时空 PoC

ch17 合纵连横：分析加速与分布式选型

先用证据证明单机边界，再比较单机分析加速与分布式方案，完成最小 PoC；不预演下卷的复制、路由和多集群运维。

• 17.1 先证明单机边界
• 17.2 单机分析能力
• 17.3 何时需要分布式
• 17.4 比较分布式候选
• 17.5 部署最小分布式 PoC
• 17.6 实战：从单机证据到选型 ADR

ch18 万法归宗：PostgreSQL 数据平台与替代边界

把上卷能力组合成一张数据平台地图，同时明确 PostgreSQL 不应该承担的工作，为下卷的服务建设建立边界。

• 18.1 从数据库产品到能力组合
• 18.2 PostgreSQL 的强项与代价
• 18.3 明确替代边界
• 18.4 平台服务目录与多租户
• 18.5 Pigsty 作为参考实现
• 18.6 实战：设计 pg36_shop 生产蓝图

前后衔接

• 开始前：全书导读 · 第 0 章（可跳过）
• 完成本卷后：下卷：运维管理

1 盲人摸象：PostgreSQL 与 Pigsty 全局地图

会写 SQL，并不等于知道 SQL 落在了哪里。一个连接 URI 里同时出现主机、端口、数据库和角色；连接成功后又会遇到实例、模式、关系、后端进程、WAL、服务端点与集群等词。它们属于不同层次，却经常被笼统地叫作“数据库”。许多误操作、权限错误和接入故障，都始于这张地图没有画清楚。

本章不急着介绍 PostgreSQL 的所有功能。我们只完成一件事：建立一套此后能够反复使用的坐标系。读者将从一条真实连接出发，逐层确认连接落点、对象边界、查询路径与服务拓扑，最后创建全书贯穿案例 pg36_shop 的最小基线。

版本基线：本章按 PostgreSQL 18.6、Pigsty v4.5.0 和单节点 L1 沙箱编写。核心 PostgreSQL 概念适用于当前受支持的大版本；Pigsty 的端口、组件和配置入口以 v4.5.0 为准。所有命令都先查询实际运行版本，书中示例输出只展示需要判断的字段。

本章目标

回答“我连到了什么、数据对象在哪里、一次查询经过什么、Pigsty 又管理了什么”，并固化后续章节共同使用的实验基线。

读者前置

开始本章前，你应当已经：

• 掌握 Linux 终端、环境变量和基本文件操作；
• 会写常用 SQL，但不要求熟悉 PostgreSQL 的系统目录；
• 拥有一个可连接的 PostgreSQL 环境；推荐使用第 0 章准备的 Pigsty L1 沙箱；
• 知道实验管理员连接信息存放在哪里，但不会把密码写进书稿、脚本或 Git。

如果已经有其他 PostgreSQL 环境，也可以完成 1.1–1.3 与 1.6；1.4、1.5 和 1.7 中的服务拓扑与平台证据需要 Pigsty。

学习完成标准

完成本章后，你应当能够拿出证据完成以下任务，而不是凭名称猜测：

1. 从连接 URI 中指出主机、端口、数据库和登录角色，并用 SQL 确认服务器、数据库、会话角色、模式搜索路径与读写状态；
2. 解释实例、database cluster、数据库、模式和关系对象的包含关系，说明角色为什么不隶属于某一个数据库；
3. 画出“客户端 → 服务入口 → PostgreSQL 后端 → 共享内存／数据文件／WAL”的最小查询路径；
4. 区分 PostgreSQL 原生能力、生产平台必须承担的职责和 Pigsty 的具体实现；
5. 使用最少的一组 psql 命令探索对象、切换数据库、执行脚本、保存证据和安全中断；
6. 创建并验证 pg36_shop 数据库、shop 模式与最小角色，生成后续章节可复用的环境快照。

贯穿场景

假设应用团队交给你下面这样的连接入口：

postgresql://pg36_app@pg-meta:5433/pg36_shop

这串字符并没有告诉你所有事实。pg-meta 可能是主机名，也可能是随主节点漂移的集群域名；5433 在 Pigsty 中通常是读写服务，而不是 PostgreSQL 进程直接监听的 5432；pg36_shop 是数据库名，不是实例名；pg36_app 是数据库角色，也不是 Linux 用户。只有把连接参数与服务器返回的证据合在一起，才能确认操作落点。

本章沿着同一条连接向内、再向外展开：

flowchart LR
  C["客户端与连接 URI"] --> S["Pigsty 服务入口<br/>HAProxy / PgBouncer"]
  S --> B["PostgreSQL 后端进程<br/>一个连接对应一个会话"]
  B --> O["数据库中的对象<br/>模式、表、索引、函数"]
  B --> M["实例共享资源<br/>共享内存、数据文件、WAL"]
  P["Pigsty 配置与控制面"] --> S
  P --> B
  E["日志、系统目录与指标"] -.复核.-> S
  E -.复核.-> B
  E -.复核.-> O

这张图不是完整架构图，而是本章的读图顺序：先确认连接参数，再让服务器说明自己是谁，然后才讨论平台如何把实例组合成服务。

本章路线

1.1 从连接串识别操作落点

先把 URI 中的五个名字拆开，并通过一条上下文快照查询确认“我到底连到了哪里”。这一节还会第一次区分实例端点、读写服务端点和只读服务端点。

1.2 PostgreSQL 对象与术语坐标

建立实例、database cluster、数据库、模式与关系对象的层级图，特别处理 PostgreSQL 中 “cluster” 与 Pigsty 集群容易混淆的问题。

1.3 一条查询经过了什么

用一个会话和一条查询观察客户端、后端进程、共享内存、数据文件、WAL、系统目录与统计视图各自扮演的角色。

1.4 从数据库实例到数据库服务

从单个 postgres 进程向外扩展，说明复制组、稳定入口、控制面、计算、存储、网络与可观测性为什么属于“服务”问题。

1.5 Pigsty 的资源模型

把通用职责映射到 Pigsty 的节点、实例、集群和服务，以及 PostgreSQL、Patroni、PgBouncer 与 HAProxy 的分工。

1.6 最小 psql 生存卡

只学习完成后续实验所需的最小命令集。更系统的连接保护、变量、脚本与可复现工作流留到 ch02《psql 与可复现工作流》。

1.7 实战：建立 pg36_shop 地图与实验基线

创建最小对象，采集连接、对象、服务与版本证据，建立 verify:state 和三档复位边界，并从 SQL 与 Pigsty 两侧指认同一对象。

本章交付物

完成实验后，至少保留以下内容：

• 一份不含密码的连接上下文快照；
• 一份 pg36_shop 对象树；
• 一份 L1 节点、实例、集群、服务与端口映射；
• pg36_shop 数据库、shop 模式和三类最小角色；
• 一次通过的 verify:state 输出；
• 明确的 reset:sql、reset:cluster、reset:host 适用边界。

这些产物从 ch02 开始会被直接复用。不要为了得到“好看”的输出而手工修改证据；环境差异本身也是需要记录的事实。

复习与迁移问题

1. postgresql://alice@db.example:5433/shop 中，哪一部分由客户端决定，哪一部分必须由服务器返回才能确认？
2. 为什么同一个角色可能连接多个数据库，而同一个普通表不能跨数据库直接访问？
3. 直连实例与连接稳定服务端点，各自暴露了什么假设？
4. pg_is_in_recovery() 能证明什么，不能证明什么？
5. 如果配置清单写着某实例是主库，而 SQL 显示它正在恢复，你会把哪一项当作当前运行事实？为什么？
6. 在托管数据库或 Kubernetes Operator 中，Pigsty 的“节点、实例、服务、控制面”分别可能映射成什么职责？

下一章如何使用本章

ch02《psql 与可复现工作流》不再解释这些对象是什么，而会把本章的临时命令整理成安全、可审查、可重跑的工作流。届时会加入服务文件、环境保护、失败即停、变量、确定性数据与机器可读输出。

如果此刻你仍不能在不查看答案的情况下画出连接到对象的完整路径，请先重做 1.7 的验收；后面的每一章都会默认这张地图已经建立。

参考基线

• PostgreSQL 18：连接字符串
• PostgreSQL 18：体系结构基础
• PostgreSQL 18：数据库角色
• PostgreSQL 18：psql
• Pigsty v4.5：PGSQL 集群模型
• Pigsty v4.5：服务与接入

返回上卷导读 · 下一章：手到擒来：psql 与可复现工作流 · 查看全书目录 · 查看索引中心

1.1 从连接串识别操作落点

一条连接字符串表达的是客户端的连接意图，不是服务器的自我证明。主机名可能经过 DNS 或 VIP，端口可能属于代理，登录角色还可能在会话内切换。可靠的第一步不是看到提示符就开始执行，而是把“我打算连到哪里”与“服务器说我落在哪里”对上。

本节全部操作属于 R0·观察。请使用第 0 章提供的实验凭据，不要把密码写入命令历史、书稿或 Git。pg36_shop 尚未创建，因此先用 Pigsty L1 已有的管理数据库观察；将 <L1_HOST> 替换为实际域名或 IP：

export PG36_BOOTSTRAP_URL='postgresql://dbuser_dba@<L1_HOST>:5436/postgres?application_name=pg36-ch01'
psql -X "$PG36_BOOTSTRAP_URL"

-X 表示暂不读取个人 psqlrc，避免本地定制改变示例行为。安全保存凭据、服务文件和连接保护会在 ch02《psql 与可复现工作流》中展开。

1.1.1 主机、端口、服务、数据库与角色

全书最终要交给应用的是类似下面的 URI。此刻先把它当作待解释的目标，而不是可以立即连接的成品：

postgresql://pg36_app@pg-meta:5433/pg36_shop?application_name=pg36-ch01
             └──角色──┘ └主机─┘└端口┘└─数据库──┘ └────连接参数─────┘

URI 支持 postgresql:// 和 postgres:// 两种 scheme。用户名、密码或数据库名含有 @、:、/、?、# 等保留字符时必须进行百分号编码。更重要的是，不要为了省事把密码直接写入可被 shell 历史、进程列表或日志记录的 URI；本章让 psql 交互式询问密码。

“服务”在这里是平台语义，而不是 URI 中额外的一段。它通常由“可访问的主机或域名 + 端口 + 路由规则”共同构成。Pigsty 的 pg-meta:5433 是读写服务入口；同样的 pg-meta 配上 5432，通常变成对当前 VIP 所在节点的 PostgreSQL 直连。端口改变，路径与故障语义也随之改变。

连接成功后，先执行一份上下文快照：

SELECT
    version()                         AS server_version,
    current_database()               AS database_name,
    session_user                     AS session_user,
    current_user                     AS current_user,
    inet_server_addr()                AS server_addr,
    inet_server_port()                AS server_port,
    pg_backend_pid()                  AS backend_pid,
    pg_is_in_recovery()               AS in_recovery;

关键判断不是输出长什么样，而是每一列证明了什么：

• version() 来自服务端，可以揭示服务器版本与构建信息；它不等于本机 psql --version；
• inet_server_addr() 和 inet_server_port() 是 PostgreSQL 后端接受连接的地址与端口。经过 HAProxy、PgBouncer 后，它们通常显示最后一跳 PostgreSQL 的地址与 5432，而不是客户端最初访问的 5433；
• 通过 Unix socket 连接时，inet_server_addr() 和 inet_server_port() 会是 NULL，这不是故障；
• pg_backend_pid() 是当前 PostgreSQL 后端进程号，只在该实例当前生命周期内有意义；
• pg_is_in_recovery() 为 false 表示当前实例不在恢复状态，通常是可写主库；为 true 表示处于恢复或热备状态。它不单独证明整套高可用系统健康。

客户端意图与服务器证据必须同时保留。只记 URI，会丢失实际落点；只记 SQL 输出，又会丢失客户端究竟通过哪个入口到达。

1.1.2 current_database()、current_user 与 search_path

进入服务器以后，还要确认三个会直接改变 SQL 含义的上下文：当前数据库、当前角色与模式搜索路径。

SELECT
    current_database()       AS database_name,
    session_user             AS authenticated_as,
    current_user             AS effective_as,
    current_setting('search_path') AS configured_path,
    current_schemas(true)    AS effective_path;

current_database() 返回当前连接所在数据库。PostgreSQL 的一个普通会话一次只连接一个数据库；\c 看似在会话内“切库”，实际是 psql 断开后重新建立连接。数据库之间不是类似 MySQL database.table 那样可以随意跨库限定访问的命名空间。

session_user 是最初通过认证的角色，通常在连接期间保持不变；current_user 是当前权限检查使用的有效角色。执行 SET ROLE 或进入使用 SECURITY DEFINER 的函数时，两者可能不同：

SELECT session_user, current_user;
-- 只有在当前角色有权切换时才能执行：
SET ROLE pg36_owner;
SELECT session_user, current_user;
RESET ROLE;

因此，审计“谁连进来”时看 session_user，判断“当前 SQL 以谁的权限运行”时看 current_user。两者都不等于操作系统账号。

search_path 决定没有写模式限定符的对象名如何解析，也决定未显式指定模式时新对象创建在哪里。假设有效路径是：

pg_catalog, shop

那么系统对象优先从 pg_catalog 解析，业务对象再从 shop 查找。current_setting('search_path') 返回配置文本；current_schemas(true) 返回去除不存在或不可访问项后的有效路径，并按参数决定是否包含隐含的系统模式。

不要把 search_path 当成界面便利设置。若不可信用户可以在搜索路径靠前的模式中创建对象，未限定名称的函数或操作符可能解析到攻击者提供的对象。应用与迁移脚本应采用受控路径，安全敏感 SQL 则显式写出模式名，例如 pg_catalog.set_config(...) 或 shop.orders。

本书为运行角色约定：

ALTER ROLE pg36_app IN DATABASE pg36_shop
SET search_path = pg_catalog, shop;

这条语句是 R1·可逆变更，只对角色 pg36_app 连接数据库 pg36_shop 时生效。回退方法是：

ALTER ROLE pg36_app IN DATABASE pg36_shop RESET search_path;

执行位置、权限与对象创建将在 1.7 一并处理。

1.1.3 实例端点、服务端点与只读端点

端点可以指向固定实例，也可以表达一种稳定服务意图。两者都能建立连接，但承诺不同。

这些是 Pigsty 的默认配置，不是 PostgreSQL 标准端口；用户可以修改。5432 才是 PostgreSQL 常见默认端口，6432 是 PgBouncer 常见默认端口。

最容易犯的错误，是把“replica 服务”理解成数据库层面的强制只读。服务名首先表达路由策略，不等同于授权策略。在单节点 L1 中没有专用备库，replica 服务可能没有可用后端，或者按具体配置回退到主库；即使连接到了备库，未来故障切换也可能改变承载实例。应用是否有写权限，仍应由角色授权、事务只读属性与数据库策略共同约束。

每次需要判断读写能力时，至少采集：

SELECT
    pg_is_in_recovery()                    AS in_recovery,
    current_setting('transaction_read_only')::boolean AS transaction_read_only,
    has_database_privilege(
        current_user,
        current_database(),
        'CREATE'
    )                                     AS can_create_in_database;

三个结果分别回答“实例是否在恢复”“当前事务是否只读”“角色是否拥有数据库级 CREATE 权限”，它们不是同一个问题。has_database_privilege 也不能穷举写入能力：表级权限、行级安全策略、函数权限和对象所有权仍可能改变结果。

一个两端互证练习

分别通过实例直连与 primary 服务建立连接，运行相同快照，并对比客户端入口与后端证据：

export PG36_INSTANCE_URL='postgresql://dbuser_dba@<INSTANCE_HOST>:5432/postgres?application_name=pg36-ch01-instance'
export PG36_PRIMARY_URL='postgresql://dbuser_dba@<L1_HOST>:5433/postgres?application_name=pg36-ch01-primary'

psql -X "$PG36_INSTANCE_URL" -c \
  "SELECT inet_server_addr(), inet_server_port(), pg_backend_pid(), pg_is_in_recovery();"

psql -X "$PG36_PRIMARY_URL" -c \
  "SELECT inet_server_addr(), inet_server_port(), pg_backend_pid(), pg_is_in_recovery();"

在单节点环境中，两次查询可能落到同一个 PostgreSQL 实例，但路径仍不同；在高可用环境中，primary 服务应随主库角色变化，而固定实例端点不会。不要为了让示例输出与书中一致而忽略差异，把实际结果写进环境清单。

本节验收

关闭终端前，确认你能回答：

• URI 中哪个字段选择数据库角色，哪个字段选择数据库？
• 为什么访问 5433 后，inet_server_port() 常常仍返回 5432？
• current_user 在什么情况下会与 session_user 不同？
• replica 服务、恢复状态、事务只读和角色权限为什么是四个不同判断？

若任何一个答案仍依赖“端口名字看起来像……”，重新执行上下文快照，用查询结果作答。

参考资料

• PostgreSQL 18：libpq 连接字符串
• PostgreSQL 18：系统信息函数
• PostgreSQL 18：模式与搜索路径
• Pigsty v4.5：服务与接入

返回本章目录 · 下一节：PostgreSQL 对象与术语坐标 · 查看全书目录 · 查看索引中心

1.2 PostgreSQL 对象与术语坐标

PostgreSQL 的对象不是装在一只名叫“数据库”的大盒子里。数据库和角色处在同一套服务器范围内，模式与关系对象则处在某一个数据库内部；一条普通连接只能进入其中一个数据库。只要层级画错，权限、命名、备份和迁移的判断就会跟着错。

先记住这张最小坐标图：

flowchart TD
  I["一个 PostgreSQL 实例<br/>进程 + 配置 + 数据目录"] --> D1["数据库 postgres"]
  I --> D2["数据库 pg36_shop"]
  I --> D3["数据库 template1"]
  I --> R["共享角色集合"]
  D2 --> S1["模式 shop"]
  D2 --> S2["模式 public"]
  S1 --> O1["表 / 分区表 / 序列"]
  S1 --> O2["索引 / 视图 / 物化视图"]
  S1 --> O3["函数 / 类型 / 其他对象"]

箭头表达包含或作用域，不表达磁盘上的目录结构。系统目录才是验证这些关系的权威入口。

1.2.1 实例、数据库、模式与关系对象

PostgreSQL 文档更常使用“服务器”或“服务器进程”描述运行实体。工程语境中的一个 PostgreSQL 实例，通常指一套正在运行的服务器进程，以及它们共同使用的配置、共享内存和数据目录。实例是运行边界，不是可以用 CREATE INSTANCE 创建的 SQL 对象。

一个实例管理多个数据库。数据库是连接边界：客户端在启动连接时选定数据库，普通 SQL 名称不能直接写成 另一个数据库.模式.表 跨库访问。确有跨库需求时，需要应用发起另一条连接，或显式使用 postgres_fdw、dblink 等机制；那是后续章节的内容。

每个数据库内部有多个模式（schema）。模式是数据库内的命名空间，shop.orders 中 shop 是模式，orders 是关系名。不同模式可以有同名对象，例如 shop.orders 与 archive.orders。

“关系”（relation）是 PostgreSQL 中一个比“表”更宽的家族。普通表、分区表、索引、序列、视图和物化视图等，都在 pg_class 中占有记录。先用系统目录观察层级：

-- 这张共享目录列出实例管理的数据库
SELECT oid, datname, datallowconn
FROM pg_catalog.pg_database
ORDER BY datname;

-- 以下目录只描述当前数据库中的对象
SELECT oid, nspname
FROM pg_catalog.pg_namespace
WHERE nspname !~ '^pg_temp_'
ORDER BY nspname;

SELECT
    n.nspname AS schema_name,
    c.relname AS relation_name,
    c.relkind
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = 'shop'
ORDER BY c.relname;

oid 是 PostgreSQL 为许多对象分配的内部标识。把它用于取证和目录连接很方便，但不要把 OID 当成跨数据库、跨重建仍稳定的业务标识。应用数据应有自己的键。

可以用一个简单实验确认数据库边界：分别连接 postgres 和 pg36_shop，查询 pg_namespace。你会看到两个数据库各自拥有一套模式目录；而查询 pg_database 时，两边都能看到同一组数据库。

1.2.2 角色为何跨数据库存在

角色（role）属于整个 PostgreSQL database cluster，而不是某一个数据库。原因很直接：客户端必须先用角色通过实例级认证，服务器才能允许它进入目标数据库。若角色本身藏在目标数据库里，认证顺序就会形成循环。

使用普通可见的 pg_roles 视图观察角色：

SELECT
    rolname,
    rolcanlogin,
    rolsuper,
    rolcreatedb,
    rolcreaterole
FROM pg_catalog.pg_roles
WHERE rolname LIKE 'pg36_%'
ORDER BY rolname;

在 postgres 与 pg36_shop 中执行这条查询，会看到相同的角色集合。底层的共享系统目录是 pg_authid，其中包含敏感认证信息，普通用户不应直接依赖；pg_roles 会隐藏密码字段。

角色跨数据库存在，不代表权限也自动跨数据库生效。需要分开看三件事：

1. 角色身份与成员关系：在整个 database cluster 中存在；
2. 进入数据库的资格：由数据库的 CONNECT 权限和认证规则控制；
3. 使用数据库内对象的权限：由模式、表、序列、函数等各级授权控制。

例如，pg36_app 可以同时存在于所有数据库，却只被授予进入 pg36_shop 和使用 shop 模式的权限。角色名称与 Linux 用户也相互独立；本地 peer 认证可以建立两者的映射，但那是认证配置，不是二者天然相同。

用 PostgreSQL 自带的权限函数验证，不要只读 GRANT 脚本猜测最终结果：

SELECT
    has_database_privilege('pg36_app', 'pg36_shop', 'CONNECT')
        AS can_connect,
    has_schema_privilege('pg36_app', 'shop', 'USAGE')
        AS can_use_shop;

第二个函数必须在包含 shop 模式的数据库中执行。这个差异本身正好说明：角色是共享的，模式对象是数据库本地的。

1.2.3 表、索引、序列、视图、函数与扩展

psql 的 \d 不是“describe table”的缩写式替代，而是进入 PostgreSQL 对象体系的一扇门。不同对象在目录中的位置和生命周期不同：

使用下面的查询把 relkind 翻译成可读类型：

SELECT
    n.nspname,
    c.relname,
    CASE c.relkind
      WHEN 'r' THEN 'table'
      WHEN 'p' THEN 'partitioned table'
      WHEN 'i' THEN 'index'
      WHEN 'I' THEN 'partitioned index'
      WHEN 'S' THEN 'sequence'
      WHEN 'v' THEN 'view'
      WHEN 'm' THEN 'materialized view'
      WHEN 'f' THEN 'foreign table'
      ELSE c.relkind::text
    END AS object_type
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = 'shop'
ORDER BY object_type, c.relname;

扩展尤其容易被误解。CREATE EXTENSION 不是启动一个数据库外部插件进程，而是让 PostgreSQL 按扩展控制文件和 SQL 脚本创建并登记一组对象；某些扩展另外需要预加载共享库或外部服务，但不能据此概括所有扩展。查看当前数据库已安装扩展：

SELECT extname, extversion, extnamespace::regnamespace
FROM pg_catalog.pg_extension
ORDER BY extname;

扩展按数据库安装。软件包在操作系统上“可用”，不等于已经在每个数据库中执行了 CREATE EXTENSION。ch14《内核分支与扩展生态》会系统处理安装、启用、升级和退出成本。

1.2.4 PostgreSQL “database cluster”的特殊含义

PostgreSQL 官方文档中的 database cluster，是“由一套服务器实例管理、存放在共同数据区域中的数据库集合”。initdb 的工作就是初始化这样一个 database cluster。它不天然表示多节点、高可用或分布式。

Pigsty 文档中的 PGSQL 集群，则是一个平台级业务单元：由一个主实例和零个或多个复制实例组成，通过服务暴露能力。每个实例都有自己的 PostgreSQL 数据目录；物理备库的数据来自主库复制，但仍是独立运行的服务器实例。

下面两条观察命令能把概念落到当前实例：

SHOW data_directory;

SELECT oid, datname
FROM pg_catalog.pg_database
ORDER BY oid;

data_directory 是当前实例的数据区域；pg_database 列出该实例所管理的数据库集合。生产环境不应依靠直接浏览数据目录理解对象，更不能手工移动或删除其中的文件。数据目录路径属于运维信息，公开证据包时应按环境敏感度处理。

若需要识别物理复制成员是否来自同一 PostgreSQL database cluster，可由有权限的管理员查询控制文件信息：

SELECT system_identifier, pg_control_version, catalog_version_no
FROM pg_catalog.pg_control_system();

物理主备通常共享 system_identifier；逻辑复制目标不会因此自动相同。这个值是取证线索，不是业务主键，也不等于 Pigsty 的 pg_cluster 名称。

本节验收：不用“数据库”糊弄过去

请为下面每项写出完整限定描述：

• pg36_shop：一个数据库；
• shop：pg36_shop 数据库中的模式；
• pg36_app：当前 PostgreSQL database cluster 中的角色；
• pg-meta-1：Pigsty 管理的 PostgreSQL 实例名；
• pg-meta：Pigsty PGSQL 集群名，也可能被配置为集群服务域名。

随后在 postgres 与 pg36_shop 各执行一次 pg_roles、pg_database 和 pg_namespace 查询。验收标准是：你能够根据结果解释哪些目录共享、哪些目录随数据库改变，而不是只说“两个库看起来差不多”。

参考资料

• PostgreSQL 18：管理数据库概览
• PostgreSQL 18：数据库角色
• PostgreSQL 18：系统目录
• PostgreSQL 18：数据库物理存储
• Pigsty v4.5：PGSQL 集群模型

上一节：从连接串识别操作落点 · 返回本章目录 · 下一节：一条查询经过了什么 · 查看全书目录 · 查看索引中心

1.3 一条查询经过了什么

现在沿着已经确认的连接继续向内走。客户端发送的不是“直接操作磁盘”的命令，而是 PostgreSQL 前后端协议中的消息；服务器端后端进程在一个会话上下文中解析、规划并执行 SQL，再通过共享资源读写数据。

本节只建立全景路径。查询优化器、事务、锁和执行器的细节会在 ch05《查询、事务与锁的核心心智模型》和 ch07《执行计划与统计信息》中展开。

1.3.1 客户端、后端进程与会话

PostgreSQL 采用客户端／服务器模型。psql、应用驱动和 GUI 都是客户端；postgres 服务器进程接受连接，并为每个直接连接创建一个后端进程（backend）。这个后端在连接存续期间保存会话状态，例如当前角色、当前数据库、参数、临时对象和事务状态。

连接池会改变观察方式。直连 PostgreSQL 时，一个客户端连接稳定对应一个后端；经过 PgBouncer 的事务池时，客户端连接与 PostgreSQL 后端只在事务期间绑定，下一个事务可能换到另一个后端。不要把某次观察到的 PID 永久记作“这个应用的进程”。

在当前连接中查看自己的会话：

SELECT
    pid,
    backend_type,
    datname,
    usename,
    application_name,
    client_addr,
    backend_start,
    state,
    wait_event_type,
    wait_event
FROM pg_catalog.pg_stat_activity
WHERE pid = pg_backend_pid();

这里有三类事实：

• pid、backend_start 描述当前后端生命周期；
• datname、usename、application_name 描述会话上下文；
• state、wait_event_type、wait_event 描述采样时刻正在做什么。

state = 'active' 只表示该后端当时正在执行查询，不等于它“健康”或“很忙”；idle 也不等于可以随意终止，因为客户端可能正在两次请求之间等待。活动视图是现场快照，解释它必须结合时间、事务状态和应用协议。

一条普通查询的最小路径如下：

1. 客户端建立连接并完成认证；
2. 客户端发送 SQL 或带参数的协议消息；
3. 后端解析语法，解析对象名称与权限；
4. 重写器处理规则和视图，规划器选择执行方案；
5. 执行器读取或修改关系页，必要时等待锁、I/O 或其他资源；
6. 后端把行、命令状态或错误返回客户端。

并行查询可能临时使用并行工作进程，但会话仍由领导后端承接；后台还有 autovacuum、WAL writer 等进程。它们都出现在进程体系中，却不是“一位用户连接一个会话”的反例。

双会话观察

打开终端 A，使用直连端点运行：

SELECT pg_backend_pid(), pg_sleep(10);

立刻在终端 B 中查询：

SELECT
    pid,
    application_name,
    state,
    wait_event_type,
    wait_event,
    left(query, 60) AS query_sample
FROM pg_catalog.pg_stat_activity
WHERE datname = current_database()
  AND query LIKE '%pg_sleep%'
  AND pid <> pg_backend_pid();

预期能看到终端 A 的后端处于 active，并等待 Timeout/PgSleep。这是受控 L1 实验，不要在共享生产连接池中用 pg_sleep 制造占用。

1.3.2 共享内存、数据文件与 WAL

不同后端进程需要看到同一份数据库状态，因此 PostgreSQL 使用共享内存协调缓存、锁、WAL 缓冲区和其他全局结构。最常被提到的 shared_buffers 是 PostgreSQL 管理的数据页缓存，但它不是数据库使用的全部内存；后端私有内存、操作系统页缓存和外部组件都在同一台主机上争用资源。

SELECT name, setting, unit, context
FROM pg_catalog.pg_settings
WHERE name IN (
    'shared_buffers',
    'wal_buffers',
    'work_mem',
    'maintenance_work_mem'
)
ORDER BY name;

context 提示参数需要怎样生效，但不直接说明“最佳值”。参数机制与资源预算留到 ch27《参数调优与资源治理》。

表和索引最终以页面形式保存在数据文件中。系统目录知道对象对应的相对路径：

SELECT
    'pg_catalog.pg_class'::regclass AS relation,
    pg_relation_filepath('pg_catalog.pg_class') AS relative_path;

返回的是相对于数据目录的路径线索，不是让用户绕过 PostgreSQL 直接读取或修改文件的接口。数据文件可能分段、使用表空间，并受到版本、存储管理器和关系类型影响。手工编辑数据目录几乎从来不是合格的应用操作。

WAL（Write-Ahead Log，预写式日志）记录数据库变化所需的重做信息。“先写”指的是：相关 WAL 必须在脏数据页落盘之前持久化；事务提交通常要等待其提交记录达到所要求的持久性级别，而数据页可以稍后由后台进程写出。这个顺序使崩溃恢复能够从一致检查点继续重放。

观察当前实例的 WAL 位置：

SELECT
    CASE
      WHEN pg_is_in_recovery()
        THEN pg_last_wal_replay_lsn()
      ELSE pg_current_wal_lsn()
    END AS observed_lsn,
    pg_is_in_recovery() AS in_recovery;

LSN（Log Sequence Number）是 WAL 流中的位置，不是墙上时钟，也不能脱离时间线和实例角色直接比较。WAL 也不是“把每条 SQL 原文记下来”的审计日志，更不能替代经过验证的备份；这些边界会在 ch20、ch21 和 ch32 分别用于复制、高可用与恢复。

可以先形成一个高层心智模型：

sequenceDiagram
  participant C as 客户端
  participant B as 后端进程
  participant M as 共享缓冲区
  participant W as WAL
  participant D as 数据文件
  C->>B: INSERT / UPDATE / DELETE
  B->>M: 修改内存中的数据页
  B->>W: 生成 WAL 记录
  W-->>B: 按持久性要求刷新
  B-->>C: COMMIT 成功
  M-->>D: 脏页稍后写回

图中省略了锁、检查点、全页镜像、复制和存储栈等细节；它只用来记住 WAL 与数据页写出的先后约束。

1.3.3 系统目录与统计视图如何描述自身

PostgreSQL 很大一部分可观测性来自“用 SQL 描述自己”，但系统目录与统计视图回答的是两类不同问题。

系统目录保存数据库的结构事实，例如：

• pg_database：有哪些数据库；
• pg_namespace：当前数据库有哪些模式；
• pg_class：有哪些关系对象；
• pg_attribute：关系有哪些列；
• pg_proc：有哪些函数与过程。

目录变化参与事务。例如，在事务中创建表后，当前事务能立即从 pg_class 看到它；回滚后记录消失。应用不应直接修改系统目录，而应使用 CREATE、ALTER、DROP 和 GRANT 等 SQL 接口。

统计视图描述运行活动与累计现象，例如：

• pg_stat_activity：当前会话、状态与等待；
• pg_stat_database：按数据库累计的事务、读写与冲突计数；
• pg_stat_user_tables：用户表扫描、修改与维护计数；
• pg_stat_replication：主库看到的复制发送状态。

统计信息可能因采样时刻、权限、统计重置和事务快照而变化。0 表示在当前统计口径下没有观测到，不等于历史上从未发生。监控系统从这些视图采集并保存时间序列，正是为了补上“当前快照没有历史”的缺口。

下面的查询把结构事实、运行事实和配置事实放在一张快照里：

SELECT jsonb_pretty(
    jsonb_build_object(
        'database', current_database(),
        'database_oid', (
            SELECT oid
            FROM pg_catalog.pg_database
            WHERE datname = current_database()
        ),
        'user', current_user,
        'backend_pid', pg_backend_pid(),
        'in_recovery', pg_is_in_recovery(),
        'server_version_num', current_setting('server_version_num'),
        'search_path', current_setting('search_path')
    )
) AS connection_snapshot;

JSON 只是便于保存的输出格式，不改变证据强度。对每个字段仍要问：

• 它来自客户端标签、配置、系统目录还是运行统计？
• 它描述当前连接、当前数据库、当前实例还是整套平台？
• 它会不会在重连、故障切换、统计重置或升级后改变？

本节验收：复述一条查询的旅程

不看本页，画出以下路径并为每一段写出一个可观察证据：

客户端 →（可选代理）→ 后端进程 → 对象目录／执行器
                         ├→ 共享内存
                         ├→ WAL
                         └→ 数据文件

最低验收结果：

• 用 pg_backend_pid() 与 pg_stat_activity 从两个会话观察到同一个后端；
• 用 pg_settings 记录共享内存相关配置，而不是猜测；
• 用 pg_relation_filepath() 找到关系路径线索，但没有直接修改数据文件；
• 能解释系统目录的结构事实与统计视图的运行事实为什么不能混为一谈。

参考资料

• PostgreSQL 18：体系结构基础
• PostgreSQL 18：服务器进程
• PostgreSQL 18：系统目录
• PostgreSQL 18：累计统计系统
• PostgreSQL 18：WAL 可靠性

上一节：PostgreSQL 对象与术语坐标 · 返回本章目录 · 下一节：从数据库实例到数据库服务 · 查看全书目录 · 查看索引中心

1.4 从数据库实例到数据库服务

一个 PostgreSQL 实例可以接受连接、执行事务并持久化数据，却还不自动等于一项可交付的数据库服务。生产用户关心的是稳定入口、可用性、恢复目标、容量、安全与责任人，而不是某个进程今天恰好运行在哪台主机上。

从实例走向服务，不是贬低 PostgreSQL“功能不全”，而是把数据库内核与平台工程放在正确边界上。这个边界也是本书后半卷的总地图。

1.4.1 单实例、复制组、服务入口与控制面

先把四个层次分开：

1. 单实例：一套 PostgreSQL 服务器进程与数据目录。它可以是完整、可用的开发数据库，但主机或实例故障会直接中断服务；
2. 复制组：主实例产生 WAL，一个或多个备实例接收并重放。复制提供数据副本与追赶机制，不自动回答何时提升、如何避免双主、客户端去哪里重连；
3. 服务入口：用稳定的域名、VIP、代理端口或服务发现名称表达“读写”“只读”“管理直连”等访问意图，并把流量路由到当前合格实例；
4. 控制面：保存期望状态、观察实际状态，并协调初始化、配置、选主、切换、备份、扩缩和维护等动作。

可以把请求路径与控制路径分开看：

flowchart LR
  A["应用"] --> E["稳定服务入口"]
  E --> P["当前主实例"]
  P --> R["备实例"]
  P -- "WAL 流" --> R

  C["控制面"] -. "观察角色与健康" .-> P
  C -. "观察角色与健康" .-> R
  C -. "更新路由或期望状态" .-> E

实线是数据面：应用查询和复制数据真实流动的路径。虚线是控制面：决定谁有资格承载流量，以及系统应当处于什么状态。控制面发生故障，不一定让当前 PostgreSQL 事务立即停止；但它可能让后续切换、配置变更或成员管理失效。

PostgreSQL 原生提供物理复制、同步提交、恢复、时间线和角色状态等基础机制，却有意不规定唯一的高可用编排方案。你可以用 Patroni，也可以使用托管数据库控制面、Kubernetes Operator 或组织自建系统。ch20《高可用拓扑与容灾目标》会根据故障模型重新审视这些选择。

在单节点 L1 中，复制组退化为一个主实例，服务入口与控制面仍然存在。这很适合学习组件关系，却不能证明多节点高可用已经成立。不要从“我能访问 5433”推导出“这套环境能容忍主机故障”。

1.4.2 计算、存储、网络、配置与可观测职责

数据库服务需要同时管理五类资源。每一类都要有“期望状态、运行事实、变更入口和失败边界”。

表格中的“平台”不是某个特定产品，而是任何生产方案都绕不开的责任集合。托管数据库把很多责任交给云厂商；自建系统则必须明确由谁实现、谁值守，以及产品边界之外还剩什么。

一个常见错误是只记录配置文件中的目标值。例如清单写着 shared_buffers: 8GB，实际实例可能尚未重启，SHOW shared_buffers 仍是旧值；清单写着实例角色为 primary，运行中的 PostgreSQL 却可能已经因为切换成为备库。声明、落地和运行事实是三层证据，不能互相替代。

另一个错误是把监控面板当成事实源本身。面板是对指标和日志的解释界面；当图表异常时，应当能够追到采集查询、标签、时间范围和 PostgreSQL 原生证据。反过来，只查询当前系统目录也没有历史，因此不能取代时间序列监控。

1.4.3 PostgreSQL 原生能力与平台组合能力的边界

全书采用三层叙述，避免把某个平台的按钮写成数据库原理：

第一层：PostgreSQL 原生机制

包括 SQL 语义、事务与锁、MVCC、WAL、复制协议、备份与恢复接口、角色权限、配置参数、系统目录、统计视图和扩展机制。关键结论优先回到 PostgreSQL SQL、日志、配置或官方文档验证。

第二层：数据库平台通用职责

包括主机置备、软件分发、拓扑编排、故障检测、选主与防脑裂、稳定接入、连接池、备份调度、密钥管理、监控告警、变更审计和恢复演练。这些责任客观存在，但实现方式不唯一。

第三层：Pigsty 参考实现

Pigsty 使用声明式配置与自动化，把 PostgreSQL、Patroni、etcd、HAProxy、PgBouncer、pgBackRest 和可观测组件组合起来。它提供一套可以拆开验证的具体答案，而不是 PostgreSQL 唯一的运行方式。

面对任意平台操作，都按下面的证据链追问：

这套追问可以迁移到其他平台。即使界面、CLI 和组件名称完全不同，“服务目标—原生状态—控制规则—运行证据—回退路径”的结构仍然成立。

分类练习

把下面动作分别归入“PostgreSQL 原生机制”“平台通用职责”“Pigsty 具体实现”，允许一项跨两层，但要说明边界：

• CREATE ROLE；
• 为主库提供稳定域名；
• pg_basebackup 复制基础数据；
• 发现主实例故障后选择备实例提升；
• HAProxy 在 5433 暴露读写服务；
• pg_stat_activity 观察会话；
• 保存 30 天指标并在 SLO 违约时告警。

参考判断：CREATE ROLE 与 pg_stat_activity 是原生接口；稳定域名、故障切换和长期告警是通用职责；“HAProxy + 5433”是 Pigsty 的具体实现。pg_basebackup 是原生工具，但“何时运行、保存在哪里、如何校验”仍属于平台工作流。

本节验收

选取你当前连接的 pg36_shop 服务，写出一条完整证据链：

服务目标
→ 客户端入口
→ 路由组件
→ PostgreSQL 实例
→ 原生 SQL 证据
→ 平台状态证据
→ 不一致时的停止条件

合格答案必须包含至少一个 PostgreSQL 原生查询，且不能只引用面板颜色或配置文件。下一节会把这条抽象链具体映射到 Pigsty。

参考资料

• PostgreSQL 18：高可用、负载均衡与复制
• PostgreSQL 18：监控数据库活动
• Pigsty v4.5：PGSQL 架构

上一节：一条查询经过了什么 · 返回本章目录 · 下一节：Pigsty 的资源模型 · 查看全书目录 · 查看索引中心

1.5 Pigsty 的资源模型

上一节把数据库平台拆成一组通用职责。本节只做一件事：把这些职责映射到 Pigsty v4.5 的实体、组件和证据源。记住映射比背命令重要，因为端口、界面和组件版本会变化，而“谁负责数据、谁负责角色判断、谁负责路由”必须始终说得清。

1.5.1 节点、集群、实例与服务

Pigsty 的 PGSQL 模块使用四个核心实体组织 PostgreSQL：

Pigsty 默认采用节点与 PostgreSQL 实例 1:1 的独占部署模型，因此节点名经常借用实例名；这是 Pigsty 的部署约定，不是 PostgreSQL 限制。pg_cluster、pg_seq 和 pg_role 三个身份参数构成最小声明：

pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-meta

由此可以推导：

节点：10.10.10.10（默认命名可为 pg-meta-1）
实例：pg-meta-1
集群：pg-meta
服务：pg-meta-primary / pg-meta-replica / pg-meta-default / pg-meta-offline

配置中的 pg_role: primary 表示初始化或编排意图，不是永远不变的运行角色。多节点集群发生切换后，主库可以从 pg-meta-1 变成其他实例，而实例编号不变。服务名表达访问意图，也不应跟着当前主实例改名。

在 Pigsty 管理节点的安装目录中，用只读命令观察解析后的配置范围：

cd ~/pigsty

# 只显示分组与主机关系，不输出包含密码的完整变量
ansible-inventory --graph

# 只从源文件定位非敏感身份字段；动态清单环境应改查相应 CMDB
grep -nE 'pg-meta:|pg_cluster:|pg_seq:|pg_role:' pigsty.yml

不要把完整 ansible-inventory --list 直接贴进工单或书稿，它可能包含密码、令牌和内部地址。证据包只采集解决当前问题所需的字段。

单节点 L1 会让四种服务最终落到同一台节点甚至同一个 PostgreSQL 实例，但实体仍然不同。就像一位工程师可以兼任开发、值班和发布审批，职责名称相同不意味着角色边界消失。

1.5.2 PostgreSQL、Patroni、PgBouncer 与 HAProxy 的职责

一条默认生产读写连接的路径是：

客户端
  → HAProxy :5433
  → 当前主实例上的 PgBouncer :6432
  → PostgreSQL :5432

组件之间不是相互替代，而是逐层收窄职责：

在高可用集群中，Patroni 通常使用 etcd 之类的分布式配置存储（DCS）协调领导者信息。HAProxy 请求 Patroni 健康接口判断实例角色，再把 5433 流量送往主库的 PgBouncer。PgBouncer 最后通过本地连接进入 PostgreSQL。

Pigsty v4.5 的默认入口如下，均可配置：

“默认目标”必须结合配置阅读。例如 pg_default_service_dest 可以让 primary/replica 服务绕过 PgBouncer。不要仅凭端口号推断实际路径。

在管理节点上查看控制面状态：

# R0：列出集群成员、角色和复制状态
pg list pg-meta

在数据库中从另一侧复核：

SELECT
    pg_is_in_recovery() AS in_recovery,
    current_setting('port') AS postgres_port,
    inet_server_addr() AS server_addr,
    inet_server_port() AS accepted_port;

若 pg list 报告 Leader，而 SQL 的 pg_is_in_recovery() 为 true，不要挑一个自己喜欢的结果继续操作。先停止角色相关变更，确认两条命令是否观察了同一集群、同一实例和同一时刻，再检查 Patroni 与 PostgreSQL 日志。配置标签、控制面判断和内核运行状态不一致，本身就是需要处理的事件。

本节暂不演练切换、连接池模式或代理重载；它们分别属于 ch20《高可用拓扑与容灾目标》和 ch22《服务接入、连接池与路由》。

1.5.3 配置清单、运行状态与监控事实分别来自哪里

Pigsty 环境至少存在三类事实源：

配置清单：系统应当是什么

默认静态清单是 ~/pigsty/pigsty.yml，也可以使用动态 inventory 或 CMDB。它声明节点、集群、初始角色、数据库、用户、服务和参数。清单适合回答“期望如何配置”，不能单独证明变更已经执行并生效。

运行状态：系统现在是什么

运行状态分散在各组件中：

• PostgreSQL：SQL、系统目录、统计视图和日志；
• Patroni：成员与角色状态、DCS 信息、健康接口和日志；
• PgBouncer：连接池状态、管理控制台和日志；
• HAProxy：服务后端、健康检查、运行配置和日志；
• systemd 与主机：进程、端口、文件、资源和服务状态。

例如，“当前谁是主库”首先要看 PostgreSQL 与 Patroni 的实时状态，而不是初始化清单中的 pg_role 标签。

监控事实：系统在一段时间内发生了什么

Pigsty 的采集系统把 PostgreSQL、主机、组件和日志事实转换成带标签的时间序列与日志流。常见身份标签包括：

• cls：集群；
• ins：实例；
• ip：节点地址；
• job：采集任务或日志来源；
• datname、relname、idxname：数据库内部对象。

监控适合回答趋势、持续时间和事件先后，但它仍可能受采集间隔、标签错误、查询权限和数据保留影响。面板显示“主库”时，应能追到指标标签与原生 SQL；SQL 显示瞬时正常时，也不能据此否认五分钟前的告警。

建立一张证据优先级表：

实战：生成 L1 资源快照

在管理节点执行：

cd ~/pigsty

{
  printf 'captured_at=%s\n' "$(date -Is)"
  printf 'host=%s\n' "$(hostname -f 2>/dev/null || hostname)"
  printf 'pigsty_source=%s\n' "$(git describe --tags --always 2>/dev/null || printf unknown)"
  printf '%s\n' '--- inventory graph ---'
  ansible-inventory --graph
  printf '%s\n' '--- cluster runtime ---'
  pg list pg-meta
} > pg36-l1-platform.txt

这段脚本只采集身份与拓扑，不输出完整变量。若环境不是 Git 安装，pigsty_source=unknown 是有效结果，随后应从发行包或发布记录补充版本，而不是编造标签。

在数据库端另存原生快照：

psql -X "$PG36_BOOTSTRAP_URL" -A -t -c "
SELECT jsonb_build_object(
  'captured_at', clock_timestamp(),
  'database', current_database(),
  'user', current_user,
  'server_addr', inet_server_addr(),
  'server_port', inet_server_port(),
  'version', current_setting('server_version'),
  'in_recovery', pg_is_in_recovery()
);" > pg36-l1-postgres.json

两份文件共同构成证据：一份描述平台，一份描述 PostgreSQL。提交或分享前检查是否含有内部地址、用户名或其他不应公开的信息；密码和令牌在任何情况下都不应进入证据包。

本节验收

你应当能够从 L1 环境指出：

• 哪个名字是节点、实例、集群和服务；
• 5432、6432、5433 各由哪个组件接收；
• 配置清单、pg list、SQL 与监控分别回答什么问题；
• 当这些证据冲突时，为什么“重新运行自动化让它一致”不是安全的第一动作。

参考资料

• Pigsty v4.5：PGSQL 集群模型
• Pigsty v4.5：PGSQL 架构
• Pigsty v4.5：服务与接入
• Pigsty v4.5：发布注记

上一节：从数据库实例到数据库服务 · 返回本章目录 · 下一节：最小 psql 生存卡 · 查看全书目录 · 查看索引中心

1.6 最小 psql 生存卡

psql 同时是交互式终端、脚本执行器和 PostgreSQL 取证工具。本节只保留完成第 1 章所需的最小操作；变量、条件、服务文件、失败即停、批量输入输出和可靠脚本会在 ch02《psql 与可复现工作流》中系统展开。

先区分两种输入：

• 以反斜线开头的是 psql 元命令，由客户端解释，通常不加分号；
• SQL 发送给 PostgreSQL 服务器，以分号结束，受事务与权限约束。

看到一个命令时先问“它由客户端还是服务器执行”，很多困惑会自动消失。

1.6.1 用 URI 连接，用 \l、\dn、\d 看对象

使用连接 URI 可以让终端、应用驱动和文档共享同一种参数表达：

psql -X "$PG36_BOOTSTRAP_URL"

连接成功后，第一条元命令应是：

\conninfo

它显示当前数据库、角色、主机或 socket、端口以及 TLS 等连接信息。随后按从大到小的顺序探索对象：

\l+
\dn+
\d
\dt shop.*
\d+ shop.orders
\du+
\dx

它们依次列出数据库、当前数据库中的模式、可见关系、shop 模式中的表、指定对象详情、角色和已安装扩展；+ 表示请求更详细的信息。对象尚未创建时，\d+ shop.orders 会明确报错。

\d 系列支持 psql 自己的对象模式匹配，不是 SQL 的 LIKE。例如 shop.* 表示模式 shop 下的对象；大小写与引号仍遵循 PostgreSQL 标识符规则。

元命令适合人类快速探索，系统目录查询适合明确筛选、保存和自动验证。两者应互相复核：

SELECT nspname
FROM pg_catalog.pg_namespace
ORDER BY nspname;

若 \dn 与查询结果看起来不同，先检查 \dn 是否过滤系统模式、用户是否有可见性权限，以及是否连接了同一个数据库，不要立即断言工具出错。

1.6.2 用 \c 切库，用 -c 与 -f 执行

在交互会话中切换数据库：

\c pg36_shop
\conninfo

\c 实际上会断开当前连接并建立新连接。未显式指定的主机、端口和角色通常沿用当前值；所以切换后必须再次执行 \conninfo 或上下文快照。若切换失败，psql 在交互模式下通常保留原连接，不要误以为已经进入目标库。

从 shell 执行一条 SQL：

psql -X "$PG36_BOOTSTRAP_URL" \
  -c 'SELECT current_database(), session_user, current_user;'

执行一个 SQL 文件：

psql -X "$PG36_BOOTSTRAP_URL" \
  -v ON_ERROR_STOP=1 \
  -f setup.sql

-c 适合短小、可见的一次性观察；-f 让错误消息包含文件与行号，适合可审查脚本。ON_ERROR_STOP=1 要求 psql 遇到脚本错误后停止，避免第一步失败后继续执行一串建立在错误前提上的语句。

不要把多行复杂 SQL 塞进 shell 的 -c 参数：shell 引号、SQL 引号和变量展开叠在一起，很容易产生与屏幕看起来不同的实际输入。复杂内容放入版本控制的 .sql 文件，并在执行前查看差异。

交互会话内也可以执行文件：

\i setup.sql

但自动化与验收更适合从 shell 使用 -f，因为调用方可以读取退出码并保存标准输出、标准错误。

1.6.3 用 \o 或 -A -t 保存输出

人读的表格与机器读的结果需要不同输出形式。

交互式保存随后产生的查询输出：

\o pg36-connection.txt
SELECT current_database(), current_user, pg_is_in_recovery();
\o

第二个不带文件名的 \o 恢复到终端输出。忘记恢复时，后续查询“没有输出”往往只是仍在写文件。

从 shell 生成机器友好的单值或逐行结果：

psql -X "$PG36_BOOTSTRAP_URL" \
  -A -t \
  -v ON_ERROR_STOP=1 \
  -c 'SELECT current_database();'

• -A 使用不对齐输出，去掉表格边框；
• -t 只输出元组，去掉列名与行数提示；
• -X 避免个人 psqlrc 改写格式；
• ON_ERROR_STOP 让失败产生可判断的非成功退出。

若有多列，显式选择分隔符和空值表示，或者直接输出 JSON；不要让下游脚本解析为人类排版的表格：

psql -X "$PG36_BOOTSTRAP_URL" -A -t -c "
SELECT jsonb_build_object(
  'database', current_database(),
  'user', current_user,
  'in_recovery', pg_is_in_recovery()
);"

保存输出不等于保存证据上下文。文件旁还应记录采集时间、客户端入口、服务端版本和命令来源，否则一行 false 很快会失去解释价值。

1.6.4 用 \q、Ctrl-C 安全退出与中断

正常退出：

\q

如果正在输入但尚未发送一条 SQL，Ctrl-C 会清空当前查询缓冲区并回到提示符。可以先用 \p 查看缓冲区内容，用 \r 主动清空：

\p
\r

前者显示尚未发送的查询，后者重置查询缓冲区。

如果服务器正在执行查询，Ctrl-C 会请求取消当前语句，而不是粗暴终止服务器进程。取消可能需要等待服务器到达可中断位置；网络中断时，客户端也未必能确认取消请求是否送达。

取消事务中的语句通常会让当前事务进入失败状态。此时后续 SQL 会收到“current transaction is aborted”，必须明确回滚：

ROLLBACK;

不要连续按键后在不知道状态的情况下继续操作。中断后立即执行：

SELECT
    current_database(),
    current_user,
    pg_is_in_recovery();

若查询能正常执行，说明连接仍可用且不在失败事务中；若连接已经断开，由 psql 明确重连后再重新采集上下文。

Ctrl-Z 只是把本地 psql 挂起，服务器连接和可能的事务仍然存在。它不是安全退出手段。遗留的 idle in transaction 会话可能长期持有快照和锁，是后续并发与膨胀问题的常见来源。

一张够用的生存卡

本节验收

从一个新终端完成以下闭环：

1. 用 URI 进入 postgres，执行 \conninfo；
2. 用 \l+ 查看实例中的数据库，再用 \c postgres 明确重连；
3. 用 \dn+ 找到 public 与系统模式，用系统目录查询复核；
4. 把当前数据库名以无表头单值形式保存到文件；
5. 运行 SELECT pg_sleep(10);，用一次 Ctrl-C 取消；
6. 执行上下文查询确认连接可用，最后用 \q 退出。

验收文件中数据库名必须精确为 postgres，且终端中没有遗留失败事务提示。1.7 创建 pg36_shop 后，再用同一组命令完成章级验收。

参考资料

• PostgreSQL 18：psql
• PostgreSQL 18：错误与消息字段

上一节：Pigsty 的资源模型 · 返回本章目录 · 下一节：实战：建立 pg36_shop 地图与实验基线 · 查看全书目录 · 查看索引中心

1.7 实战：建立 `pg36_shop` 地图与实验基线

现在把前六节的地图落到一个真实对象上。本实验会在 L1 沙箱创建 pg36_shop 数据库、shop 模式和三个专用角色，随后从 PostgreSQL 与 Pigsty 两侧收集证据。

实验分为两个风险级别：

• setup 与快照采集：R1·可逆变更，只创建以 pg36_ 命名的教学对象；
• reset:sql：R2·破坏性演练，会删除整个 pg36_shop 数据库，只能在确认可销毁的 L1 中执行。

准备两个不含密码的连接 URI。将 <L1_HOST> 替换为实际域名或 IP；密码由 psql 询问或使用 ch02 将介绍的安全凭据机制：

export PG36_BOOTSTRAP_URL='postgresql://dbuser_dba@<L1_HOST>:5436/postgres?application_name=pg36-ch01-admin'
export PG36_SHOP_ADMIN_URL='postgresql://dbuser_dba@<L1_HOST>:5436/pg36_shop?application_name=pg36-ch01-admin'

这里使用 5436 default 服务，目的是跟随主库且绕过事务连接池执行管理脚本。若你的环境修改了 Pigsty 默认服务，请根据实际配置替换，不能照抄端口猜路径。

1.7.1 创建数据库、业务模式和最小角色

本章只建立权限骨架，不创建订单、商品或支付表：

对象所有者使用 NOLOGIN，避免应用直接以所有者身份绕过授权边界。两个登录角色在本章故意不设置密码；这既避免在教程中分发固定密码，也使它们在默认密码认证规则下暂时无法远程登录。ch02 会为连接与凭据建立正式工作流。

下载或打开三份伴随实验文件：

• setup.sql：幂等创建角色、数据库、模式和默认权限；
• verify.sql：机器验证状态并输出摘要；
• reset.sql：带确认口令的实验清理。

执行初始化：

psql -X "$PG36_BOOTSTRAP_URL" \
  -v ON_ERROR_STOP=1 \
  -f setup.sql

执行身份为 dbuser_dba 或等价的实验管理员；目标必须是 L1 的 postgres 数据库。脚本会：

1. 仅在缺失时创建三个 pg36_ 角色，并收敛高风险属性；
2. 仅在缺失时从 template0 创建 UTF-8 数据库；
3. 创建由 pg36_owner 拥有的 shop 模式；
4. 撤销 public 模式的公共建对象权限；
5. 为两个运行角色授予模式使用权和未来对象的默认权限；
6. 为 pg36_shop 中的两个运行角色设置 pg_catalog, shop 搜索路径。

成功末尾应出现：

[setup] complete: roles intentionally have no password in this chapter

这条消息只证明脚本执行完毕，不能替代下一目的状态验证。

与 Pigsty 声明式配置对齐

SQL 能证明 PostgreSQL 对象机制，但 Pigsty 管理的长期环境还应把期望状态写入 inventory，避免下次自动化执行时出现配置漂移。最小声明可采用下面的结构；不要把实际明文密码直接写进公开配置：

pg_users:
  - { name: pg36_owner, login: false, pgbouncer: false, comment: pg36_shop object owner }
  - { name: pg36_app,   login: true,  pgbouncer: false, comment: pg36_shop runtime role }
  - { name: pg36_ro,    login: true,  pgbouncer: false, comment: pg36_shop read-only role }

pg_databases:
  - name: pg36_shop
    owner: pg36_owner
    encoding: UTF8
    pgbouncer: true
    schemas:
      - { name: shop, owner: pg36_owner }

本章先将 pgbouncer: false 用于尚无凭据的两个登录角色；数据库本身可以加入连接池。设置正式认证材料后，再把用户加入 PgBouncer。若在既有集群中应用声明，应先评审差异，然后使用：

cd ~/pigsty
bin/pgsql-user pg-meta pg36_owner
bin/pgsql-user pg-meta pg36_app
bin/pgsql-user pg-meta pg36_ro
bin/pgsql-db pg-meta pg36_shop

这些是 R1 操作，目标集群名不一定是 pg-meta。运行前用 ansible-inventory --graph 确认限制范围；若清单里已经存在同名但含义不同的对象，立即停止，不要让自动化强行“收敛”。

1.7.2 生成连接快照、对象树、服务拓扑与环境清单

建立证据目录：

mkdir -p evidence/ch01

连接快照

psql -X "$PG36_SHOP_ADMIN_URL" -A -t -v ON_ERROR_STOP=1 -c "
SELECT jsonb_build_object(
  'captured_at', clock_timestamp(),
  'database', current_database(),
  'session_user', session_user,
  'current_user', current_user,
  'server_addr', inet_server_addr(),
  'server_port', inet_server_port(),
  'backend_pid', pg_backend_pid(),
  'server_version', current_setting('server_version'),
  'search_path', current_setting('search_path'),
  'in_recovery', pg_is_in_recovery()
);" > evidence/ch01/connection.json