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

会执行一条 SQL，不等于能可靠地完成一次数据库任务。真正可交付的操作必须知道自己连到了哪里，遇错立即停止，留下足够证据，允许安全重跑，并能证明失败没有留下半成品。本章把这些要求压缩成后续 34 章共同使用的最小工作流。

这里不会把 psql 写成命令手册，也不会假装一套脚本能消除所有变更风险。我们的目标更具体：把“登录服务器后临时敲几条命令”改造成一个输入明确、行为可审查、结果可验证、退出码可信的任务。

本章目标

完成本章后，读者应当能够：

• 用 URI、service file 与 passfile 分离目标、行为和秘密，并理解连接参数的覆盖顺序；
• 让提示符、application_name 与上下文快照共同暴露当前数据库、角色、端点和事务状态；
• 用 psql 元命令探索对象，同时回到系统目录验证其来源；
• 编写遇错即停、退出码可信、变量引用安全、事务边界明确的 SQL 脚本；
• 用确定性夹具、校验摘要与机器可读输出建立可复现输入；
• 运行一个最小 pgbench 工作负载，只验证执行链路，不偷渡性能结论；
• 完成一次可检查、可恢复、可验证的逻辑备份闭环；
• 把上述动作组合成带证据包的可重跑任务，并证明错误注入不会留下半成品。

开始之前

本章承接 1.7 实战 创建的 pg36_shop、shop 模式和三个角色。若环境尚未具备这些对象，先完成 ch01；若对象已经承载其他数据，不要用本章实验覆盖它们。

示例基线是 PostgreSQL 18.6 与 Pigsty v4.5。核心 psql 工作流适用于 PostgreSQL 14–18；COPY ... ON_ERROR、REJECT_LIMIT 等版本敏感能力会单独标出。实验使用 L1 的 Pigsty default 服务（默认端口 5436）执行管理任务，因为它跟随主库并直连 PostgreSQL；这不是 PostgreSQL 标准端口，也不代表所有平台都必须这样命名。

本章提供的可下载资产位于：

• 连接服务文件示例
• psqlrc 示例
• 上下文保护脚本
• 确定性夹具脚本
• 状态验证脚本
• 最小 pgbench 工作负载
• 故障注入脚本
• 安全重置脚本
• 综合任务入口

本章主线

flowchart LR
  A["连接参数<br/>目标与凭据"] --> B["上下文快照<br/>确认落点"]
  B --> C["探索与取证<br/>人读 + 机读"]
  C --> D["可靠脚本<br/>失败即停"]
  D --> E["确定性数据<br/>校验摘要"]
  E --> F["最小负载<br/>验证链路"]
  F --> G["逻辑备份<br/>恢复验证"]
  G --> H["综合任务<br/>证据 + 复位"]

这条路径有意先解决“正确地执行”，再碰性能与备份。一个无法可靠停止、重跑和复位的实验，即使偶然得到漂亮结果，也没有教学或工程价值。

本章目录

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 日志、清单与机器可读结果

本节建立本书的脚本协议：-X、ON_ERROR_STOP、安全变量引用、显式事务边界、标准输出与错误输出分离。

2.4 输入、输出与确定性数据

• 2.4.1 COPY 与 \copy 的权限和执行边界
• 2.4.2 CSV、文本与错误隔离
• 2.4.3 固定随机种子、规模档位与校验和

本节让实验输入可重建、输出可比较，并解释 PostgreSQL 18 容错导入与早期版本工作流的差异。

2.5 最小 pgbench 工作负载

• 2.5.1 初始化自定义脚本与参数
• 2.5.2 区分吞吐、延迟、错误与环境噪声
• 2.5.3 本节只建立可复现基线，不做性能结论

本节只证明工作负载能以确定输入完成预期事务数。硬件比较、容量模型与正式压测留给 ch26《胸有成竹：容量规划与压测基线》。

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 注入脚本错误，验证停止、修复与复位

本节把前六节组合成 task.sh all：创建夹具、验证状态、运行最小负载、注入语法错误并检查回滚。重置从不包含在默认路径中。

本章产物与验收

完成综合实验后，证据目录至少应包含：

章节验收不是背命令，而是能解释以下问题：

1. 为什么 service file 适合保存端点，却不应默认保存密码？
2. 为什么漂亮的提示符不能替代执行前的 SQL 上下文断言？
3. 为什么没有设置 ON_ERROR_STOP 的 psql -f 可能在 SQL 报错后仍继续？
4. 幂等为什么不等于“所有语句前都加 IF EXISTS”？
5. 为什么固定随机种子仍不能固定延迟和 TPS？
6. 为什么 dump 文件只有在恢复并验证后才构成一次有效演练？

下一章会把本章生成的确定性夹具当作输入样本，但不会把它误当成业务模型。我们将从业务语言提取实体、事件与不变量，开始 ch03《正本清源：从业务规则到关系模型》。

参考资料

• PostgreSQL 18：psql
• PostgreSQL 18：libpq 环境变量
• PostgreSQL 18：连接服务文件
• PostgreSQL 18：COPY
• PostgreSQL 18：pgbench
• PostgreSQL 18：pg_dump
• PostgreSQL 18：pg_restore
• Pigsty v4.5：PostgreSQL 服务与接入

上一章：盲人摸象：PostgreSQL 与 Pigsty 全局地图 · 返回上卷导读 · 下一章：正本清源：从业务规则到关系模型 · 查看全书目录 · 查看索引中心

2.1 可靠连接与上下文保护

第 1 章已经说明：连接串表达客户端意图，SQL 快照才是服务端证据。本节把这条原则固化成一个可重复入口。连接参数负责“去哪里”，凭据负责“我是谁”，上下文保护负责“这里是否允许执行这项任务”；三者不能因为都出现在一次连接里就混成一件事。

2.1.1 连接 URI、服务文件与环境变量

libpq 客户端——包括 psql、pg_dump、pg_restore 和 pgbench——共享一套连接参数。参数可以来自命令行、连接 URI、service file、环境变量与内置默认值。工程上的关键不是选出唯一写法，而是让覆盖关系和秘密边界可见。

给端点命名

下载连接服务文件示例，复制到当前用户的私有路径并替换 <L1_HOST>：

[pg36-admin]
host=<L1_HOST>
port=5436
dbname=pg36_shop
user=dbuser_dba
application_name=pg36-ch02
connect_timeout=5
options=-c statement_timeout=30s -c lock_timeout=5s

然后设置：

chmod 600 "$PWD/pg_service.conf"
export PGSERVICEFILE="$PWD/pg_service.conf"
psql -X "service=pg36-admin"

pg36-admin 是 libpq service 名称，不是 Pigsty 服务名。这里把它映射到 Pigsty default 服务的默认端口 5436：HAProxy 跟随当前主库，并把连接直接交给 PostgreSQL。若平台修改过服务定义，以实际配置和 ch01 的端点快照为准。

service file 使用 INI 语法。用户级默认路径是 ~/.pg_service.conf；PGSERVICEFILE 可以指定另一文件。显式连接参数会覆盖 service file 中的同名参数，service file 的值又会覆盖相应环境变量。例如：

PGPORT=9999 psql -X \
  "service=pg36-admin port=5436 application_name=pg36-override"

最终端口是 URI 中显式给出的 5436，而不是环境变量的 9999。不要靠记忆猜覆盖结果；连接后用 \conninfo 和 SQL 快照验证。

把秘密留在秘密载体

不要把密码写入本书配置、Git、命令行 URI 或 PGPASSWORD。Unix 上的 passfile 默认是 ~/.pgpass，也可由 PGPASSFILE 指定；每行格式是：

hostname:port:database:username:password

文件权限必须限制为 0600 或更严格，否则 libpq 会忽略它。匹配按从上到下的第一条记录决定，过早出现的 * 通配行可能把错误凭据应用到意外目标。密码中的 : 与 \ 还要按 passfile 规则转义。

自动化任务使用 -w（--no-password）：

psql -X -w "service=pg36-admin" -c 'SELECT current_database();'

它不会弹出交互式密码提示；若非交互凭据缺失，任务会立即失败。这比 CI 卡在不可见的密码提示上更可靠。交互探索时可以去掉 -w，让客户端主动询问。

service file 与 passfile 解决的是客户端配置管理，不是权限设计。角色授权、SCRAM、 证书和 pg_hba.conf 会在 ch23《固若金汤：认证、授权与数据安全》 系统展开。

2.1.2 application_name、提示符与上下文快照

可靠连接需要同时照顾人、服务器和证据系统：

• application_name 让服务端活动视图与日志知道“这条连接自称在做什么”；
• 提示符让操作者持续看见用户、主机、端口、数据库和事务状态；
• 上下文快照用服务端 SQL 证明实际数据库、角色、后端和读写状态。

三者互补，但都不是安全身份。客户端可以伪造 application_name，提示符可以被本地配置改坏，快照也只证明采集时刻的会话状态。

可观察的连接标签

service file 已经设置 application_name=pg36-ch02。也可按任务覆盖：

psql -X \
  "service=pg36-admin application_name=pg36-ch02-inspect"

在另一条有权查看活动会话的连接中验证：

SELECT
    pid,
    usename,
    datname,
    application_name,
    client_addr,
    backend_start,
    state
FROM pg_catalog.pg_stat_activity
WHERE application_name LIKE 'pg36-ch02%'
ORDER BY backend_start, pid;

标签应包含系统或任务名，而不是工单中的秘密、客户数据或完整 SQL。后续监控会使用它聚合会话，但不会把它当作授权条件。

让提示符暴露危险上下文

下载psqlrc 示例，其中核心设置是：

\set PROMPT1 '%n@%m:%>/%/%R%x%# '
\set PROMPT2 '%n@%m:%>/%/%R%x%# '

常用转义含义如下：

本书的可复现实验仍统一使用 psql -X，因为 -X 会跳过用户与系统 psqlrc，避免个人格式、变量或自动 SQL 改变脚本行为。交互会话可以使用提示符增强，人读体验与机器复现不应争用同一隐含配置。

进入会话后的标准快照

连接后先执行：

\conninfo

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(false)                AS effective_path,
    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,
    current_setting('transaction_read_only')::boolean
                                             AS transaction_read_only,
    current_setting('application_name')   AS application_name;

\conninfo 展示客户端已知的连接信息；SQL 列来自当前 PostgreSQL 后端。经过 Pigsty 5436 进入后，\conninfo 会保留客户端访问的服务入口，而 inet_server_port() 通常显示最后一跳 PostgreSQL 的 5432。把两侧一起保存，才能重建连接路径。

同一快照不要只拍一次。任务开始前用于阻断错误目标，任务结束后用于证明结果属于哪个会话；长任务还应在证据中记录开始和结束时间。

2.1.3 防止连错库、用错角色和改错模式

颜色鲜艳的提示符只能提醒人，不能保护无人值守任务。真正的保护必须在第一条有副作用的 SQL 之前验证数据库、恢复状态、有效角色与搜索路径，并在不符合预期时产生可信的非零退出码。

本章的上下文保护脚本按以下顺序执行：

1. 默认期望数据库为 pg36_shop、对象所有者为 pg36_owner；
2. 验证当前数据库正确且实例不在恢复；
3. 才执行 SET ROLE pg36_owner；
4. 设置并验证 search_path = pg_catalog, shop；
5. 输出一行可保存的上下文摘要。

关键片段是：

\set ON_ERROR_STOP on

SELECT
    current_database() = :'expected_db' AS database_ok,
    NOT pg_is_in_recovery()             AS writable_instance
\gset

\if :database_ok
\else
  \warn '[context] refused: unexpected database'
  DO $guard$
  BEGIN
      RAISE EXCEPTION 'context guard rejected the current database';
  END
  $guard$;
\endif

\if 是 psql 的客户端条件，不是 PL/pgSQL。查询通过 \gset 把一行结果写入 psql 变量；不满足条件时，固定的 DO 块抛出服务端异常，ON_ERROR_STOP 再让脚本以退出码 3 停止。

这里特意不写 \quit 3：PostgreSQL 18 的 psql 中 \quit 不接受自定义状态码，多余参数会使该元命令被忽略。保护脚本若只打印警告而没有可靠失败机制，最危险的结果就是“看起来拒绝，实际上继续”。

为什么先验数据库，再切换角色

如果先以高权限角色执行 SET ROLE，再发现连接到了错误数据库，权限提升动作已经发生。当前示例先执行两个只读断言，确认目标可写且数据库名称正确，之后才切换到无登录对象所有者。任何一步失败都由 ON_ERROR_STOP 截断。

角色验证不能只看 session_user：

SELECT session_user, current_user;

session_user 证明谁完成认证，current_user 证明此刻权限检查采用谁。对象迁移通常要求前者是受控管理员、后者是专用 owner；运行时查询则不应随意成为 owner。

搜索路径要验证有效结果

脚本显式设置：

SET search_path = pg_catalog, shop;

然后比较：

SELECT current_schemas(false)
       = ARRAY['pg_catalog', 'shop']::name[] AS path_ok;

因为 pg_catalog 被显式写入路径，即使 current_schemas(false) 的参数表示不额外包含隐式模式，结果仍会保留它。不要根据函数参数名称想当然地断言结果；在目标版本上观察实际数组。

安全敏感或机器生成的 SQL 仍应显式限定对象名。受控 search_path 降低误解析风险，但不把 shop.orders 写成 orders 的所有上下文都变得安全。

负向验证

保护脚本必须证明“错误目标会失败”。先对正确连接运行：

psql -X -w "service=pg36-admin" \
  -v ON_ERROR_STOP=1 \
  -f context.sql

预期看到类似：

[context] database=pg36_shop session_user=dbuser_dba \
current_user=pg36_owner search_path=pg_catalog, shop

再显式覆盖到 postgres 数据库：

set +e
psql -X -w \
  "service=pg36-admin dbname=postgres" \
  -v ON_ERROR_STOP=1 \
  -f context.sql
status=$?
set -e
test "$status" -eq 3

第二次运行应在任何写操作之前返回 3。若返回 0，不要继续后续章节；先修复保护脚本或调用方式。

本节验收

• service file 不含密码，passfile 权限符合要求；
• psql -X -w "service=pg36-admin" -c '\conninfo' 可以非交互完成；
• 能同时保存客户端入口与服务端后端快照；
• 错误数据库测试返回 3；
• 能解释为什么 application_name、提示符和 SQL 断言都不能互相替代。

参考资料

• PostgreSQL 18：数据库连接控制
• PostgreSQL 18：环境变量
• PostgreSQL 18：密码文件
• PostgreSQL 18：连接服务文件
• PostgreSQL 18：psql 提示符
• Pigsty v4.5：服务与接入

返回本章目录 · 下一节：用 psql 探索与取证 · 查看全书目录 · 查看索引中心

2.2 用 psql 探索与取证

psql 同时服务两种不同任务：人在终端里快速理解数据库，以及脚本稳定采集证据。交互探索可以接受对齐表格、分页与版本相关的展示；自动化取证则需要明确字段、排序、格式和退出码。把两种输出混用，是很多脆弱运维脚本的起点。

本节操作均为 R0·观察。先完成 2.1 的上下文检查，再在 pg36_shop 中执行。

2.2.1 对象、权限和会话元命令

psql 元命令以反斜杠开头，由客户端解释，不会作为 SQL 发给服务器。它们最适合回答“这里大概有什么”和“下一步该查哪个目录”，不应被误认为独立于 PostgreSQL 的另一套元数据。

一张够用的探索表

命令中的 shop.* 是 psql 模式匹配，不是 shell glob。仍建议放在交互会话内输入，或在 shell 中用单引号保护：

psql -X "service=pg36-admin" \
  -c '\dt+ shop.*'

若对象名包含大写字母、空格或特殊字符，模式规则与 SQL 标识符引用会变得更难读。这是本书坚持小写 snake_case 标识符的一个工程原因，而不是 PostgreSQL 的强制限制。

权限需要从三个角度看

以 shop.ch02_fixture 为例：

\d+ shop.ch02_fixture
\dp shop.ch02_fixture
\du+ pg36_app

它们分别展示对象定义、对象 ACL 与角色属性。实际能否执行某项操作，还可能受对象所有权、角色成员关系、模式 USAGE、行级安全和列级权限影响。最终判断应使用权限函数验证具体动作：

SELECT
    has_schema_privilege('pg36_app', 'shop', 'USAGE') AS schema_usage,
    has_table_privilege(
        'pg36_app',
        'shop.ch02_fixture',
        'SELECT'
    ) AS can_select,
    has_table_privilege(
        'pg36_ro',
        'shop.ch02_fixture',
        'UPDATE'
    ) AS ro_can_update;

期望前两项为 true，最后一项为 false。这仍不是“模拟一次完整 SQL”的万能授权检查，但比肉眼解释 ACL 字符串更适合验收。

2.2.2 扩展显示、分页、计时与查询缓冲区

探索效率常常取决于“怎样看”，而不是“还能背多少元命令”。以下设置只影响当前 psql 客户端：

\x auto
\pset null '∅'
\pset pager on
\timing on

• \x auto 在结果太宽时自动切换为逐字段显示；
• 自定义空值标记能区分 SQL NULL 与空字符串；
• pager 便于人在终端阅读长结果；
• \timing 显示客户端观察到的每条语句耗时。

这些设置不适合原样带进自动化。分页器可能等待键盘输入，装饰性空值会污染机器解析，客户端计时还包含网络传输与结果渲染。脚本应显式使用：

\pset pager off
\pset tuples_only on
\pset format unaligned

或者直接采用命令行 --no-align --tuples-only。

查询缓冲区是交互式安全带

psql 会把尚未发送的 SQL 保存在查询缓冲区。常用动作是：

例如先写一个查询但不输入分号：

SELECT fixture_id, sku, amount
FROM shop.ch02_fixture
ORDER BY fixture_id
LIMIT 5

随后依次输入：

\p
\gdesc
\gx

\gdesc 可以检查结果列的名称和类型；它不是通用 SQL 干运行工具，更不能证明一个写语句没有副作用。不要把“描述结果形状”扩展成“可以安全预演任何 SQL”。

\gexec 会把查询结果逐单元格当作 SQL 执行，后续章节偶尔用它创建可计算的 DDL。它的默认风险很高：执行顺序取决于结果排序，生成内容按字面发送，单条失败后是否继续又受 ON_ERROR_STOP 控制。使用前必须先把同一生成查询以普通 \g 输出审查，再在受控事务或隔离环境执行。

计时、重复观察与取消

\timing on
SELECT count(*) FROM shop.ch02_fixture;
\watch 2

\watch 2 每两秒重复当前查询，适合短时间观察计数或活动状态；按 Ctrl-C 取消当前查询或 watch 循环，而不是关闭整个终端。执行写语句前要先清空缓冲区，避免把它误交给 \watch。

\timing 是快速反馈，不是基准测试。第一次执行的缓存状态、返回行数、终端渲染、网络与并发噪声都会改变结果。第 2.5 节会建立最小负载协议，ch26 再讨论正式测量。

发生错误后可输入：

\errverbose

它会重新显示最近一个服务端错误的完整诊断，包括 SQLSTATE、DETAIL、HINT 和错误位置（若可用）。保存证据时应同时保留标准错误，而不是只截终端最后一行。

2.2.3 元命令与系统目录查询互相验证

元命令通常在内部查询 pg_catalog。用 psql -E 启动，或在会话中设置：

\set ECHO_HIDDEN on
\d+ shop.ch02_fixture

psql 会打印它为当前服务器版本生成的目录查询。这是学习系统目录的好入口，也揭示一个重要事实：\d 的展示和内部 SQL 都可能随 PostgreSQL 版本变化，不应被 shell 脚本按列位置解析。

用目录查询复核对象

下面的查询稳定地列出本章夹具的用户列：

SELECT
    a.attnum                                      AS ordinal,
    a.attname                                     AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod)
                                                   AS data_type,
    a.attnotnull                                  AS not_null
FROM pg_catalog.pg_attribute AS a
WHERE a.attrelid = 'shop.ch02_fixture'::regclass
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY a.attnum;

与 \d+ 相比，它的优势不是更“原生”，而是调用方明确选择了字段、含义和顺序。regclass 转换还能在对象不存在或解析错误时直接失败；如果希望“对象缺失返回 NULL”，则用 to_regclass('shop.ch02_fixture')。

再复核 owner 与关系类型：

SELECT
    n.nspname                                  AS schema_name,
    c.relname                                  AS relation_name,
    c.relkind,
    pg_catalog.pg_get_userbyid(c.relowner)     AS owner
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS n
  ON n.oid = c.relnamespace
WHERE n.nspname = 'shop'
  AND c.relname = 'ch02_fixture';

pg_catalog 暴露 PostgreSQL 的完整内部元数据，字段会随版本演进；information_schema 提供更标准化、通常受当前用户可见性过滤的视图，但不会覆盖全部 PostgreSQL 特性。跨数据库工具优先考虑后者，PostgreSQL 运维与深度取证通常需要前者。

人读输出与机器证据分开

交互探索：

psql -X "service=pg36-admin" \
  -c '\d+ shop.ch02_fixture'

机器采集：

mkdir -p evidence/ch02
psql -X -w "service=pg36-admin" \
  --set=ON_ERROR_STOP=1 \
  --csv \
  --command="
    SELECT a.attnum, a.attname,
           pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
           a.attnotnull
    FROM pg_catalog.pg_attribute AS a
    WHERE a.attrelid = 'shop.ch02_fixture'::regclass
      AND a.attnum > 0
      AND NOT a.attisdropped
    ORDER BY a.attnum;
  " > evidence/ch02/columns.csv

机器输出应有固定列、显式排序和失败即停；文件名、采集时间、连接上下文与版本则写入清单。CSV 解决字段引用，不会自动赋予字段长期兼容承诺。

本节验收

• 能用元命令找到 shop.ch02_fixture、owner 和 ACL；
• 能用 has_*_privilege 验证 pg36_app 与 pg36_ro 的实际权限；
• 能解释 \timing 为什么不是正式基准；
• 能用 -E 找到元命令背后的目录查询；
• 自动化证据不解析 \d 的人读表格，而是查询明确的目录字段。

参考资料

• PostgreSQL 18：psql 元命令
• PostgreSQL 18：psql 模式
• PostgreSQL 18：系统目录
• PostgreSQL 18：信息模式
• PostgreSQL 18：权限查询函数

上一节：可靠连接与上下文保护 · 返回本章目录 · 下一节：编写可靠 SQL 脚本 · 查看全书目录 · 查看索引中心

2.3 编写可靠 SQL 脚本

可靠脚本不是“把终端历史保存成 .sql”。它必须定义输入，验证上下文，遇错停止，选择事务边界，区分可重跑与可回退，并把结果传给调用者。这里建立的约定会贯穿全书实验。

2.3.1 ON_ERROR_STOP、退出码与失败即停

psql 默认面向交互使用：一条 SQL 失败后，它通常报告错误并继续读取后续输入。对人来说便于修正，对自动化来说却可能把“步骤二失败、步骤三成功”误报为任务完成。

所有本书脚本都在文件内设置：

\set ON_ERROR_STOP on

调用方仍显式传入：

psql -X -w \
  "service=pg36-admin" \
  --set=ON_ERROR_STOP=1 \
  --file=setup.sql

双重设置不是为了炫技：文件自带安全默认，调用方又表明自己依赖失败即停语义。-X 去掉隐含 psqlrc，-w 禁止无人值守任务等待密码。

四类退出状态

PostgreSQL 18 的 psql 约定：

状态码 3 依赖 ON_ERROR_STOP。没有它时，脚本可能在服务端报错后继续，最终甚至返回 0。因此不能用 grep ERROR 代替退出码，也不能只看退出码而省略状态验证。

Shell 中应保留原始状态：

set +e
psql -X -w \
  "service=pg36-admin" \
  -v ON_ERROR_STOP=1 \
  -f broken.sql \
  >broken.stdout \
  2>broken.stderr
status=$?
set -e

printf 'exit_code=%s\n' "$status"
test "$status" -eq 3

不要写成：

psql ... | tee task.log

若 shell 未启用 pipefail，管道状态可能来自成功的 tee，从而吞掉 psql 失败。可以启用 set -o pipefail，或像综合任务那样分别重定向标准输出与标准错误。

失败即停不等于原子回滚

ON_ERROR_STOP 只控制客户端是否继续发送后续命令，不会自动回滚之前已经提交的语句。若每条语句都处于自动提交模式，第一条 INSERT 成功、第二条语法错误时，第一条仍可能永久存在。

对可放进同一事务的脚本，使用：

psql -X -w \
  --single-transaction \
  "service=pg36-admin" \
  -v ON_ERROR_STOP=1 \
  -f broken.sql

--single-transaction（-1）会在所有 -c/-f 输入之前发送 BEGIN，成功后 COMMIT，失败且启用 ON_ERROR_STOP 时 ROLLBACK。本章故障注入正是用它证明标记行数量保持为零。

并非所有命令都允许在事务块内执行。CREATE DATABASE、VACUUM、CREATE INDEX CONCURRENTLY 等动作需要单独设计阶段、前置断言与补偿路径。遇到这类命令，不能为了追求“一个事务”而忽略 PostgreSQL 的语义。

2.3.2 变量、条件、包含文件与事务包装

psql 变量是客户端文本替换机制，不是服务端绑定参数。正确引用方式取决于变量代表“值”还是“标识符”：

psql -X "service=pg36-admin" \
  -v expected_db=pg36_shop \
  -v owner_role=pg36_owner \
  -f context.sql

脚本内：

SELECT current_database() = :'expected_db';
SET ROLE :"owner_role";

不要把用户输入拼进原样变量：

-- 危险：变量可改变 SQL 结构
SELECT * FROM shop.ch02_fixture WHERE fixture_id = :raw_input;

应用程序应使用驱动的绑定参数；psql 脚本至少用 :'value' 后再由服务端转换为目标类型：

SELECT *
FROM shop.ch02_fixture
WHERE fixture_id = :'fixture_id'::integer;

默认值与客户端条件

检测变量是否存在：

\if :{?expected_db}
\else
  \set expected_db pg36_shop
\endif

\if 接受可以解释为布尔值的结果，未执行分支中的 SQL 不会发送给服务器。它适合控制脚本装配，不应承担复杂业务逻辑。需要数据库事务、异常和类型系统时，使用 SQL 或 PL/pgSQL。

相对包含保证可搬迁

\ir context.sql

\ir（\include_relative）相对于当前脚本所在目录寻找文件；\i 通常相对于 psql 的当前工作目录。一个从任意目录调用的实验包，应优先用 \ir 组织内部依赖。

本章文件关系是：

setup.sql ─┐
verify.sql ├──> context.sql
broken.sql ┘

每个入口都独立设置 ON_ERROR_STOP，再包含同一上下文保护，避免复制三份逐渐漂移的断言。

两种事务包装

文件内部显式包装：

\set ON_ERROR_STOP on
BEGIN;
-- 一组允许在事务块内的变更
COMMIT;

调用方包装：

psql -X -1 -v ON_ERROR_STOP=1 -f task.sql "service=pg36-admin"

前者让事务意图跟随文件，后者便于对故障注入或多个 -f 输入统一包裹。不要混用嵌套 BEGIN 来制造虚假的双重保险；PostgreSQL 没有普通嵌套事务，只有保存点。若脚本本身控制事务，就不再额外传 -1。

一旦脚本主动执行 COMMIT、\connect 或事务块外命令，调用方就不能再假设 -1 提供全局原子性。事务边界必须是任务接口的一部分，而不是隐藏实现。

2.3.3 幂等、重入与执行前预览

三个常被混用的目标需要分开：

• 幂等：对同一起点重复执行，最终状态不因执行次数改变；
• 可重入：上次在某个中间点失败后，能识别现状并安全继续或重新开始；
• 可回退：有明确动作恢复到先前状态，且已经验证其适用范围。

一条 CREATE TABLE IF NOT EXISTS 只能避免“同名关系已经存在”的错误，并不证明现有表的列、类型、约束和 owner 正确。若错误对象占用了名称，它反而会掩盖漂移。

本章 setup.sql 采用“收敛 + 断言”：

CREATE TABLE IF NOT EXISTS shop.ch02_fixture (...);

DO $shape_guard$
BEGIN
    -- 从 pg_attribute 计算实际列形状；
    -- 若与期望数组不同，RAISE EXCEPTION。
END
$shape_guard$;

TRUNCATE TABLE shop.ch02_fixture;
INSERT INTO shop.ch02_fixture ...

这使脚本在形状正确时可以重复生成同一夹具，在形状漂移时失败，而不是偷偷接受未知对象。TRUNCATE 会删除本章夹具的现有行，因此整个 setup 是 R1·可逆变更，只允许作用于明确的教学表。

SQL 没有通用 dry-run

可靠预览应针对动作设计：

“先 BEGIN，最后 ROLLBACK”不是万能模拟器。序列值不会因事务回滚自动收回，通知可能在提交时发送，外部程序和远程系统更有自己的事务边界。正式变更应在预生产或可销毁克隆中演练，而不是在生产上借 ROLLBACK 试胆量。

让计划与应用分阶段

一个成熟任务通常分为：

1. inspect：只读采集现状；
2. plan：根据现状生成明确变更集合；
3. apply：再次检查前置条件后执行；
4. verify：独立查询目标状态；
5. reset 或 rollback：只处理任务拥有的对象。

本章的规模很小，setup 内部合并了 plan 与 apply，但仍保留形状断言；下卷涉及切换、备份和事故处理时会把阶段拆得更细。

2.3.4 日志、清单与机器可读结果

一个任务至少有四类输出：

不要把所有内容重定向到一个文件后再靠正则猜哪一行是结果。本章综合任务分别生成：

manifest.txt
setup.stdout
setup.stderr
verify.txt
verify.stderr
pgbench.txt
pgbench.stderr
broken.status
broken.stdout
broken.stderr

机器输出要主动收窄

最简单的单值：

row_count="$(
  psql -X -w "service=pg36-admin" \
    -v ON_ERROR_STOP=1 \
    --tuples-only \
    --no-align \
    -c 'SELECT count(*) FROM shop.ch02_fixture'
)"
test "$row_count" = "100"

多列结果使用：

psql -X -w "service=pg36-admin" \
  -v ON_ERROR_STOP=1 \
  --csv \
  -c '
    SELECT fixture_id, sku, amount
    FROM shop.ch02_fixture
    ORDER BY fixture_id
  ' >fixture.csv

无论哪种格式，都要显式 ORDER BY。关系结果没有默认顺序；一次输出“碰巧稳定”不能成为校验依据。

清单记录复现所需条件

至少保存：

date -u +%Y-%m-%dT%H:%M:%SZ
psql --version
pgbench --version
sha256sum context.sql setup.sql verify.sql workload.sql

再从服务端记录：

SELECT current_setting('server_version');
SELECT current_database(), session_user, pg_is_in_recovery();

只写 PostgreSQL 18 不够：客户端与服务器可以是不同版本，端点也可能经过 Pigsty 服务路由。清单应保存 service 名称和脱敏后的连接上下文，不保存密码或完整 passfile。

若 Shell 开启 set -x，展开后的连接 URI、变量和命令可能进入日志。处理秘密前应关闭跟踪，或者从设计上确保命令行根本不含秘密。

本节验收

• 任意 SQL 错误都会使脚本停止并返回非零；
• 能解释状态码 1、2、3 的差异；
• 值变量使用 :'name'，标识符变量使用 :"name"；
• 内部文件使用 \ir，不依赖调用者当前目录；
• setup 重跑得到相同状态，形状漂移则明确失败；
• 标准输出、标准错误、状态摘要与运行清单彼此分离。

参考资料

• PostgreSQL 18：psql 命令行选项与退出状态
• PostgreSQL 18：psql 变量
• PostgreSQL 18：psql 条件块
• PostgreSQL 18：事务隔离与原子提交

上一节：用 psql 探索与取证 · 返回本章目录 · 下一节：输入、输出与确定性数据 · 查看全书目录 · 查看索引中心

2.4 输入、输出与确定性数据

可复现实验需要确定的输入，也需要能被另一工具重新读取的输出。这里先解决小型数据交换和教学夹具；大规模装载、在线迁移、外部表和生产数据管道会在各自章节展开。

2.4.1 COPY 与 \copy 的权限和执行边界

COPY 是 PostgreSQL SQL 命令，\copy 是 psql 元命令。两者可以传输相同数据格式，但文件由哪台机器、哪个操作系统用户读写完全不同。

服务端文件版 COPY 需要超级用户，或 pg_read_server_files、pg_write_server_files、pg_execute_server_program 等高权限角色；路径从数据库服务器视角解析。不要为了方便给应用角色授予这些权限，它们可能读写数据库服务账号可访问的任意文件。

\copy 不需要服务端文件角色，因为 psql 自己打开本地文件，再通过标准输入/输出传输。导出本章夹具：

mkdir -p evidence/ch02
psql -X -w "service=pg36-admin" \
  -v ON_ERROR_STOP=1 <<'PSQL'
\copy (SELECT fixture_id, sku, label, amount, payload FROM shop.ch02_fixture ORDER BY fixture_id) TO 'evidence/ch02/fixture.csv' WITH (FORMAT csv, HEADER true, NULL '\N')
PSQL

这里的相对路径属于运行 psql 的客户端当前目录，不是 L1 数据库节点的 $PGDATA。\copy 对整行参数采用自己的解析规则，命令必须写在一条逻辑行内，也不进行普通 psql 变量替换；动态文件路径更适合由受控 Shell 生成完整命令，且必须正确处理空格与引号。

服务器侧 COPY PROGRAM 会以 PostgreSQL 服务账号启动命令。即使当前角色有权使用，也不能把不可信输入拼进命令字符串；Shell 元字符可能升级成服务器命令执行。第 2 章不使用它。

长时间 COPY 的进度可以从另一会话观察：

SELECT
    pid,
    datname,
    relid::regclass AS relation,
    command,
    type,
    bytes_processed,
    tuples_processed,
    tuples_excluded
FROM pg_catalog.pg_stat_progress_copy
ORDER BY pid;

视图中的计数是运行中证据，不替代完成后的行数、边界值和业务校验。

2.4.2 CSV、文本与错误隔离

PostgreSQL COPY 支持 text、CSV 和 binary。选择标准不是“哪个最快”：

CSV 默认用未加引号的空字段表示 NULL，用 "" 表示空字符串。这两个业务含义不同。实验显式写 NULL '\N'，让证据文件更容易肉眼审查；导入时必须使用同一约定。

默认策略：一错即停

\copy shop.ch02_fixture FROM 'fixture.csv'
  WITH (FORMAT csv, HEADER true, NULL '\N')

默认 ON_ERROR stop。任一输入转换错误会使整条 COPY 失败；若外层事务也失败，目标状态可以保持不变。错误发生前处理过的行虽然不可见，却可能暂时占用表空间，后续由 vacuum 回收，因此“大文件试错”仍应先在隔离 staging 中演练。

PostgreSQL 18 的受限容错导入

基线版本支持：

COPY shop.import_stage
FROM STDIN
WITH (
    FORMAT csv,
    HEADER true,
    ON_ERROR ignore,
    REJECT_LIMIT 3,
    LOG_VERBOSITY verbose
);

• ON_ERROR ignore 只忽略 text/CSV 输入转换错误，不是“忽略所有约束和触发器错误”；
• REJECT_LIMIT 3 表示第 4 个转换错误使命令失败；
• LOG_VERBOSITY verbose 为被丢弃行输出更详细的 NOTICE；
• 若不设置 reject limit，ignore 可能跳过任意数量错误，形成“任务成功、数据大面积消失”的假象。

ON_ERROR 在 PostgreSQL 17 引入，REJECT_LIMIT 属于 PostgreSQL 18 能力。面向 14–16 的可移植方案不是删掉验收，而是先导入全 text staging 表，再用显式验证查询区分：

1. 可转换且满足业务规则的行；
2. 原始内容与错误原因；
3. 无法识别或需要人工裁决的行。

最后在一个事务里把通过验证的数据转换进目标表。生产数据管道还要保存原始文件哈希、来源批次、拒绝行数量与处理决策。

一个错误隔离练习

在临时表中测试，不污染夹具：

CREATE TEMP TABLE amount_stage (
    source_line bigint GENERATED ALWAYS AS IDENTITY,
    sku text,
    amount_text text
);

INSERT INTO amount_stage (sku, amount_text)
VALUES
    ('SKU-0001', '1.23'),
    ('SKU-0002', 'not-a-number'),
    ('SKU-0003', '-4.00');

SELECT
    source_line,
    sku,
    amount_text,
    CASE
      WHEN amount_text ~ '^[0-9]+(\.[0-9]{1,2})?$'
      THEN amount_text::numeric(10,2)
    END AS parsed_amount,
    CASE
      WHEN amount_text !~ '^[0-9]+(\.[0-9]{1,2})?$'
      THEN 'invalid non-negative decimal'
    END AS rejection_reason
FROM amount_stage
ORDER BY source_line;

正则这里只服务受控教学格式，不是国际化金额解析器。重要的是保留原值与拒绝理由，再决定是否导入，而不是让 NULL 悄悄代表所有错误。

2.4.3 固定随机种子、规模档位与校验和

“重新生成 100 行”还不够；行内容、顺序和摘要也必须可解释。本章夹具不用真正随机数，而是从行号计算：

SELECT
    n AS fixture_id,
    'SKU-' || lpad(n::text, 4, '0') AS sku,
    'fixture-' || substr(md5('label:' || n), 1, 12) AS label,
    (((n * 37) % 10000)::numeric / 100)::numeric(10,2) AS amount,
    md5('pg36:' || n) AS payload
FROM generate_series(1, 100) AS g(n)
ORDER BY n;

相同 PostgreSQL 语义下，输入行号唯一决定输出。它比调用 random() 后希望种子“差不多一样”更容易审查。

随机种子固定什么

PostgreSQL 会话可以：

SELECT setseed(0.36);
SELECT random()
FROM generate_series(1, 5);

同一会话重新设置相同种子，会重启伪随机序列。pgbench 也支持 --random-seed=20260729。但种子只约束随机数流，不会固定：

• 多线程或多客户端的调度顺序；
• 并发事务的交错与锁等待；
• 缓存命中、CPU 频率、网络和后台任务；
• 不同主要版本对未承诺实现细节的变化；
• 没有显式 ORDER BY 的结果顺序。

因此，小型语义夹具优先用可计算哈希；需要随机分布时记录生成器、种子、线程数、版本和规模档位。

规模档位要有名字

本书后续使用：

本章 100 行是 tiny。它只让错误注入、COPY、dump 和 pgbench 快速完成。

校验和必须先定义序列化

verify.sql 采用：

SELECT md5(
         string_agg(
           fixture_id || '|' || sku || '|' || payload,
           E'\n'
           ORDER BY fixture_id
         )
       ) AS checksum
FROM shop.ch02_fixture;

在 PostgreSQL 18.6 实测期望值是：

00ed4599a6ed75e4441f5211909480fa

显式字段、分隔符与排序共同定义了序列化。若字段可以含 | 或换行，就要采用长度前缀、JSON、binary 或其他无歧义编码。对大表也不应把全部内容聚合成一个内存字符串；应按稳定键分块或使用面向数据管道的校验工具。

校验和证明“按这套序列化得到相同字节”，不证明业务正确。验收同时保留：

• 行数 100；
• 最小/最大 ID 为 1/100；
• 每行能由确定公式重新计算；
• 校验和匹配。

本节验收

• 能解释服务器文件 COPY 与客户端 \copy 的路径和权限差异；
• CSV 中 NULL 与空字符串有明确约定；
• 容错导入保存拒绝数量与原因，不静默跳过无限错误；
• 能说明 ON_ERROR/REJECT_LIMIT 的版本边界；
• 夹具重建后的行数、边界、逐行公式和校验和全部一致。

参考资料

• PostgreSQL 18：COPY
• PostgreSQL 18：COPY 进度
• PostgreSQL 18：随机函数
• PostgreSQL 18：字符串聚合

上一节：编写可靠 SQL 脚本 · 返回本章目录 · 下一节：最小 pgbench 工作负载 · 查看全书目录 · 查看索引中心

2.5 最小 pgbench 工作负载

pgbench 既能运行内置类 TPC-B 工作负载，也能执行自定义事务脚本。本节只用它验证“连接—变量—事务—查询—结果采集”链路；20 次 tiny 查询不足以评价 PostgreSQL、Pigsty、硬件或参数。

2.5.1 初始化自定义脚本与参数

pgbench -i 会创建它自己的 pgbench_accounts、pgbench_branches 等内置基准表。本章不使用这些对象；我们的“初始化”是先运行 setup.sql，得到 100 行确定性夹具，再运行自定义工作负载：

\set fixture_id random(1, 100)
BEGIN;
SET LOCAL ROLE pg36_owner;
SELECT payload
FROM shop.ch02_fixture
WHERE fixture_id = :fixture_id;
COMMIT;

pgbench 脚本的 \set 与 psql 元命令不是同一套完整语言。这里调用 pgbench 的 random(min, max) 表达式，把结果存为变量；SQL 中 :fixture_id 再被替换为整数。

自定义脚本不会自动包裹事务。显式 BEGIN/COMMIT 让“一次 pgbench transaction”对应一次数据库事务。SET LOCAL ROLE 只在该事务内切换为对象 owner，提交后自动恢复；它服务于本章管理员直连实验，不是应用运行时的推荐身份。

先确认夹具：

psql -X -w "service=pg36-admin" \
  -v ON_ERROR_STOP=1 \
  -f verify.sql

再运行：

pgbench \
  --random-seed=20260729 \
  --no-vacuum \
  --client=1 \
  --jobs=1 \
  --transactions=20 \
  --report-per-command \
  --file=workload.sql \
  "service=pg36-admin application_name=pg36-ch02-pgbench"

参数意图：

这里通过 Pigsty default 服务 5436，路径是 HAProxy 到当前主库 PostgreSQL，不经过 PgBouncer。若改用 primary 服务 5433，必须先确认登录角色已安全配置密码并进入 PgBouncer 用户清单。两次结果属于不同连接路径，不能直接混为一个基准。

pgbench 客户端版本应与清单一并保存。自定义脚本语法和输出字段会随 PostgreSQL 版本演进；不要从另一台机器拿一个未知版本客户端就假设完全等价。

2.5.2 区分吞吐、延迟、错误与环境噪声

一次正确运行的关键输出类似：

number of clients: 1
number of threads: 1
number of transactions per client: 20
number of transactions actually processed: 20/20
number of failed transactions: 0 (0.000%)
latency average = ...
initial connection time = ...
tps = ... (without initial connection time)

本章真正验收的是：

• 客户端、线程与事务数符合命令；
• 20/20 个事务实际完成；
• failed transactions 为 0；
• 标准错误中没有连接、SQL 或变量错误；
• 运行前后夹具校验和一致，因为工作负载只读。

其余数字需要先理解口径：

固定事务数时，测试持续时间很短，任何一次调度抖动都可能大幅改变 TPS。平均值还会隐藏最慢请求；正式测试至少需要时长、延迟分布、错误分类、预热和重复运行。

噪声从哪里来

即使脚本与种子完全相同，结果仍可能受到：

• 客户端 CPU、时钟与 pgbench 版本；
• DNS、网络、HAProxy 与是否经过 PgBouncer；
• PostgreSQL 数据页和操作系统页缓存；
• autovacuum、检查点、日志、备份与其他会话；
• 虚拟机 steal、CPU 频率、NUMA 和磁盘队列；
• 监控采样与终端输出；
• 第一次连接和第一次执行的初始化成本。

“第二次更快”经常只是缓存变热；“经连接池更慢”可能只是路径、认证和测量窗口不同。没有对照、重复和环境清单时，不应把相关性写成因果。

错误是一级指标

不要为追求 TPS 把错误行藏起来。若使用 --latency-limit，晚于阈值的事务会单独计数；若启用可重试错误处理，还要区分原始失败、重试和最终失败。一个吞吐更高但超时或失败更多的结果通常更差。

本章没有注入并发错误，因此只要求零失败。ch10 会制造隔离与冲突，ch26 才建立完整性能报告。

2.5.3 本节只建立可复现基线，不做性能结论

这里的“基线”指可重放的工作负载定义，不是可外推的性能基线。我们冻结了：

• 数据集：100 行、确定公式、固定校验和；
• 查询：按 1–100 的 ID 读取一行 payload；
• 事务：显式 BEGIN、一次查询、COMMIT；
• 随机输入：单客户端、单线程、固定 seed；
• 运行量：20 个事务；
• 连接路径：清单中命名的 Pigsty service；
• 成功条件：20/20、零失败、数据摘要不变。

我们没有冻结操作系统调度、CPU、缓存、网络或后台活动，因此绝不写“应达到 N TPS”。读者在本地实测看到几百、几千或几万 TPS，都只能说明这个 tiny 任务在那个瞬间的观察值。

做一次反证

连续运行两次同一命令：

for run_id in 1 2; do
  pgbench \
    --random-seed=20260729 \
    -n -c 1 -j 1 -t 20 -r \
    -f workload.sql \
    "service=pg36-admin application_name=pg36-ch02-run-${run_id}" \
    >"evidence/ch02/pgbench-${run_id}.txt" \
    2>"evidence/ch02/pgbench-${run_id}.stderr"
done

两个文件应具有相同事务数与零失败，但 latency 和 TPS 通常不会完全相同。这正好证明“确定输入”与“确定耗时”是两件事。

若两次选择的 fixture ID 也需要逐项核对，可以让工作负载把 ID 写入单独的审计结果；但写日志本身会改变测量。任何观测都会有成本，测试设计要说明成本是否在比较双方中一致。

何时才允许谈性能

到 ch26，至少补齐：

1. 明确问题：容量、回归、极限还是组件对比；
2. 代表性数据量与事务比例；
3. 预热、持续时间、并发阶梯与重复次数；
4. 硬件、内核、容器/虚拟化和存储清单；
5. 客户端是否成为瓶颈；
6. 平均值、分位数、错误、饱和指标和置信边界；
7. 数据库、系统与 Pigsty 监控证据；
8. 测试后状态和可复位性。

本章的小负载只是让未来这些测试拥有一个已经验证的入口。

本节验收

• 能解释为什么自定义脚本仍需显式事务；
• 能说明 --random-seed 固定什么、不固定什么；
• 运行结果为 20/20 且零失败；
• 运行前后 verify.sql 校验和一致；
• 报告不把某次 TPS 当作 PostgreSQL 或 Pigsty 性能承诺。

参考资料

• PostgreSQL 18：pgbench
• PostgreSQL 18：pgbench 自定义脚本
• PostgreSQL 18：pgbench 随机种子
• Pigsty v4.5：服务端点

上一节：输入、输出与确定性数据 · 返回本章目录 · 下一节：最小逻辑备份闭环 · 查看全书目录 · 查看索引中心

2.6 最小逻辑备份闭环

生成一个 dump 文件只是开始。最小闭环必须回答：文件里有什么、由哪个版本生成、能否在隔离目标恢复、恢复后的状态是否符合预期、哪些生产恢复目标仍未覆盖。

2.6.1 pg_dump 的对象、模式与自定义格式

pg_dump 连接一个数据库，从一致快照读取逻辑对象定义与数据。它不会阻塞普通读写，但会持有访问共享锁来防止被导出的表在过程中遭到破坏性 DDL；长时间快照也可能影响 vacuum 回收。生产上不能因为“在线 dump”就忽略运行窗口和监控。

先验证夹具，再创建目录：

mkdir -p evidence/ch02/backup
psql -X -w "service=pg36-admin" \
  -v ON_ERROR_STOP=1 \
  -f verify.sql \
  >evidence/ch02/backup/source-verify.txt \
  2>evidence/ch02/backup/source-verify.stderr

使用自定义格式：

pg_dump -w \
  --format=custom \
  --verbose \
  --file=evidence/ch02/backup/pg36_shop.dump \
  "service=pg36-admin application_name=pg36-ch02-dump" \
  >evidence/ch02/backup/dump.stdout \
  2>evidence/ch02/backup/dump.stderr

-w 禁止密码提示；凭据来自 passfile。不要把 stdout 和 stderr 合并，因为 pg_dump --verbose 的进度与警告写入 stderr，警告必须进入验收。

为什么选择 custom

custom 默认压缩且是单文件，适合小型教学闭环。真正的大库可能选择 directory format 与并行 worker，但并行数必须结合服务器 CPU、存储、网络和锁评估。

dump 的边界

普通 pg_dump 只处理一个数据库。它不包含跨数据库共享的角色与表空间定义；这类全局对象可由：

pg_dumpall -w --globals-only \
  --database="service=pg36-admin dbname=postgres" \
  >evidence/ch02/backup/globals.sql

单独导出。globals 文件可能含有角色口令哈希与敏感 ACL，应按秘密材料保护。本章恢复演练使用 --no-owner --no-privileges，故不依赖它；这也意味着恢复结果有意不保留原 owner/ACL，不能冒充完整灾备演练。

pg_dump -t 或 --schema 只选择匹配对象，不会自动保证所有依赖都包含。一个“成功生成”的局部 dump 可能无法恢复到空数据库。除非任务明确处理依赖，本章先 dump 整个 pg36_shop。

客户端版本也是输入

先记录：

pg_dump --version
psql -X -w "service=pg36-admin" -Atc \
  "SELECT current_setting('server_version')"

pg_dump 不能导出主要版本高于自己的服务器；较新的 pg_dump 可以读取较老服务器，但输出通常面向较新工具链。逻辑 dump 常用于升级，却没有“向旧版本降级必然成功”的承诺。扩展、排序规则和 SQL 语义仍需单独验证。

最后为归档文件计算客户端哈希：

sha256sum evidence/ch02/backup/pg36_shop.dump \
  >evidence/ch02/backup/pg36_shop.dump.sha256

哈希证明文件字节未变化，不证明内容完整、可信或可恢复。

2.6.2 pg_restore 的清单、选择性恢复与验证

第一步不是恢复，而是检查：

pg_restore \
  --list \
  evidence/ch02/backup/pg36_shop.dump \
  >evidence/ch02/backup/pg36_shop.list

清单列出 pre-data、data、post-data 阶段的模式、表、数据、约束、索引和 ACL 等条目。可以复制清单，按行前加分号排除对象，再用 --use-list 恢复；但手工删除依赖条目可能得到不完整数据库。

还可以把归档展开为 SQL 供审查：

pg_restore \
  --file=evidence/ch02/backup/preview.sql \
  evidence/ch02/backup/pg36_shop.dump

这一步非常重要：恢复来自不可信服务器的 dump，会在目标执行源端超级用户能够植入的任意代码。局部过滤不会消除这一风险。未知来源归档必须先审查，并在严格隔离与最小权限环境处理。

恢复到隔离数据库

不要对源数据库使用 --clean 试验恢复。创建一个名称固定、用途明确的临时数据库：

psql -X -w \
  "service=pg36-admin dbname=postgres" \
  -v ON_ERROR_STOP=1 <<'PSQL'
SELECT NOT EXISTS (
  SELECT 1
  FROM pg_catalog.pg_database
  WHERE datname = 'pg36_restore'
) AS restore_name_available
\gset

\if :restore_name_available
  CREATE DATABASE pg36_restore TEMPLATE template0;
\else
  \warn '[restore] refused: database pg36_restore already exists'
  DO $restore_error$
  BEGIN
      RAISE EXCEPTION 'restore target name is already in use';
  END
  $restore_error$;
\endif
PSQL

本章要求从干净 template0 新建。若同名数据库已经存在，上面的保护会返回非零；先确认它是否属于早先演练，再选择单独清理或更换名称，绝不自动覆盖。

恢复：

pg_restore -w \
  --exit-on-error \
  --single-transaction \
  --no-owner \
  --no-privileges \
  --dbname="service=pg36-admin dbname=pg36_restore application_name=pg36-ch02-restore" \
  evidence/ch02/backup/pg36_shop.dump \
  >evidence/ch02/backup/restore.stdout \
  2>evidence/ch02/backup/restore.stderr

• --exit-on-error 避免默认“继续恢复、最后报告错误数量”的行为；
• --single-transaction 保证本次小型恢复要么全部提交、要么全部回滚，并隐含 exit-on-error；
• --no-owner --no-privileges 让实验不依赖源角色，把对象归当前恢复角色所有；
• 大型归档可能因锁数量、事务长度而不适合单事务，生产方案必须实测。

--jobs 能并行装载数据和创建部分对象，但不能与 --single-transaction 同用。并行恢复的成功条件仍是状态验证，不是“worker 都退出了”。

用语义摘要验证

分别在源与恢复库执行：

SELECT
    count(*) AS row_count,
    min(fixture_id) AS min_id,
    max(fixture_id) AS max_id,
    md5(
      string_agg(
        fixture_id || '|' || sku || '|' || payload,
        E'\n'
        ORDER BY fixture_id
      )
    ) AS checksum
FROM shop.ch02_fixture;

期望两边均为 100、1、100 和 00ed4599a6ed75e4441f5211909480fa。再验证列、约束和索引，而不是只查行数：

\d+ shop.ch02_fixture

因为本次使用 --no-owner --no-privileges，owner 与 ACL 应与源库不同；这不是失败，而是任务选择的恢复语义。验证报告必须明确哪些属性要求相同、哪些有意重映射。

完成后，删除 pg36_restore 是 R2·破坏性演练。必须先确认它只属于本实验、终止范围仅限这个数据库，再携带精确令牌执行：

psql -X -w \
  "service=pg36-admin dbname=postgres" \
  -v ON_ERROR_STOP=1 \
  -v confirm_drop=DROP_PG36_RESTORE <<'PSQL'
SELECT :'confirm_drop' = 'DROP_PG36_RESTORE' AS drop_confirmed
\gset

\if :drop_confirmed
  SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
  WHERE datname = 'pg36_restore'
    AND pid <> pg_backend_pid();
  DROP DATABASE pg36_restore;
\else
  DO $drop_error$
  BEGIN
      RAISE EXCEPTION 'drop confirmation is required';
  END
  $drop_error$;
\endif
PSQL

不要把清理动作附在默认备份命令后；保留恢复目标供人工验收，确认后再独立清理。

2.6.3 与 ch21《未雨绸缪：备份体系与恢复演练》的边界

本节证明的是“逻辑对象可以导出、检查、恢复和验证”，不是“生产数据已经安全”。两者之间至少还差：

Pigsty 的生产备份参考实现以 pgBackRest 物理备份与 WAL 归档为核心；逻辑 dump 更适合对象级迁移、审查和辅助恢复。两者不是互相替代的单选题。

一份文件不是备份结论

至少经历以下状态，才能把本节称为一次演练：

flowchart LR
  A["源状态已验证"] --> B["dump 成功"]
  B --> C["归档哈希已记录"]
  C --> D["清单与 SQL 已检查"]
  D --> E["隔离目标恢复成功"]
  E --> F["数据与结构验证通过"]
  F --> G["耗时、错误和差异已记录"]

链路中任一步失败都应保留证据，而不是删除文件重新跑到“看起来成功”为止。

本节验收

• dump 客户端与服务端版本都进入清单；
• custom 归档有 SHA-256，并能由 pg_restore --list 读取；
• 恢复前查看了清单或展开 SQL；
• 恢复目标与源数据库隔离；
• pg_restore 遇错即停，恢复后验证结构、行数和校验和；
• 报告明确 owner/ACL 是否保留；
• 能列出本节没有覆盖的 RPO、RTO、WAL、保留和异地问题。

参考资料

• PostgreSQL 18：pg_dump
• PostgreSQL 18：pg_restore
• PostgreSQL 18：pg_dumpall
• PostgreSQL 18：客户端程序版本兼容
• Pigsty v4.5：备份与恢复
• ch21《未雨绸缪：备份体系与恢复演练》

上一节：最小 pgbench 工作负载 · 返回本章目录 · 下一节：实战：把人工操作变成可重跑任务 · 查看全书目录 · 查看索引中心

2.7 实战：把人工操作变成可重跑任务

现在把连接保护、可靠脚本、确定性数据、最小负载和证据清单组合成一个任务。它会创建并覆盖 shop.ch02_fixture，因此只能在明确的 L1 教学数据库运行，不能把“表名前缀看起来安全”当作生产授权。

风险分级：

• setup：R1·可逆变更，创建或重建本章专属 100 行夹具；
• verify、baseline：R0·观察，其中 pgbench 只读；
• inject-error：R2·破坏性演练，故意制造语法错误，但由单事务回滚隔离；
• reset：R2·破坏性演练，只删除 shop.ch02_fixture，需要双重确认令牌。

2.7.1 生成 pg36_shop 初始数据与校验摘要

下载本章全部实验文件到同一目录，至少包括：

context.sql
setup.sql
verify.sql
workload.sql
broken.sql
reset.sql
task.sh

复制service file 示例，替换主机并设置私有权限：

chmod 600 "$PWD/pg_service.conf"
export PGSERVICEFILE="$PWD/pg_service.conf"
export PGSERVICE=pg36-admin

为 dbuser_dba 准备 passfile 或等价的非交互凭据。本书不提供真实密码，也不要求把密码写入 service file。先人工确认落点：

psql -X -w "service=$PGSERVICE" <<'PSQL'
\conninfo
SELECT current_database(), session_user, pg_is_in_recovery();
PSQL

必须是 pg36_shop、受控管理员且 pg_is_in_recovery() = false。

setup 怎样收敛

setup.sql先包含 context.sql，然后在事务内：

1. 创建 shop.ch02_fixture（若不存在）；
2. 从 pg_attribute 计算五列的名称、类型与非空形状；
3. 发现同名表形状漂移则抛出异常；
4. 截断本章专属表并按确定公式生成 100 行；
5. 给 pg36_app 写权限、给 pg36_ro 只读权限；
6. 提交事务。

夹具故意不是电商领域模型：

ch03 会从业务规则重新设计正式模型；本表只训练工作流，避免在建模之前偷渡随意业务约束。

运行：

chmod +x task.sh
export PG36_EVIDENCE_DIR="$PWD/evidence/ch02/setup-$(date -u +%Y%m%dT%H%M%SZ)"
./task.sh setup
./task.sh verify

也可直接运行 SQL：

psql -X -w "service=pg36-admin" \
  -v ON_ERROR_STOP=1 \
  -f setup.sql

psql -X -w "service=pg36-admin" \
  -v ON_ERROR_STOP=1 \
  -f verify.sql

基线版本实测摘要：

status=ok
database=pg36_shop
effective_role=pg36_owner
row_count=100
min_id=1
max_id=100
checksum=00ed4599a6ed75e4441f5211909480fa

再次执行 setup 与 verify，应得到相同状态。若校验和不同，先检查脚本版本哈希、服务端主要版本与本地是否修改过生成公式；不要更新“期望值”来迁就未知漂移。

形状漂移为什么要失败

假如已有 shop.ch02_fixture 只是同名、列却不同，CREATE TABLE IF NOT EXISTS 会发 NOTICE 后继续。形状保护会随后抛出异常，整个事务不再 TRUNCATE。这才是可重入：认识并拒绝未知中间状态，而不是把所有错误压成“对象已存在”。

2.7.2 从 Pigsty 服务端点执行并保存证据

综合入口是 task.sh。它采用 Linux Shell 的严格模式和 umask 077，检查 psql、pgbench 与 sha256sum，再把每类输出写入独立文件。

export PGSERVICEFILE="$PWD/pg_service.conf"
export PGSERVICE=pg36-admin
export PG36_EVIDENCE_DIR="$PWD/evidence/ch02/all-$(date -u +%Y%m%dT%H%M%SZ)"

./task.sh all

all 的顺序固定：

sequenceDiagram
  participant T as task.sh
  participant H as Pigsty 5436 / HAProxy
  participant P as PostgreSQL primary
  T->>H: service=pg36-admin
  H->>P: direct primary connection
  T->>P: capture manifest
  T->>P: setup deterministic fixture
  T->>P: verify state
  T->>P: pgbench 20 read-only transactions
  T->>P: run broken.sql in one transaction
  P-->>T: syntax error; rollback
  T->>P: verify fixture_id=999 is absent
  T-->>T: write exit code and evidence path

任务使用的是 Pigsty default 服务，而不是固定实例 5432。服务层提供“当前主库直连”意图，PostgreSQL 仍负责事务、角色、目录和数据。若 5436 在你的配置中含义不同，必须修改 service file 并在清单中记录，不要改书中预期输出来掩盖端点差异。

清单与证据

成功后目录应包含：

manifest.txt
setup.stdout
setup.stderr
verify.txt
verify.stderr
pgbench.txt
pgbench.stderr
broken.stdout
broken.stderr
broken.status

manifest.txt 记录 UTC 时间、任务动作、service 名、客户端版本、七个执行文件的 SHA-256，以及服务端版本、数据库、登录角色和恢复状态。它有意不打印 host、密码或 passfile 内容；如组织审计需要记录脱敏端点，可在外层清单增加。

验证重点：

sed -n '1,120p' "$PG36_EVIDENCE_DIR/verify.txt"
sed -n '1,160p' "$PG36_EVIDENCE_DIR/pgbench.txt"
sed -n '1,80p'  "$PG36_EVIDENCE_DIR/broken.status"

期望：

row_count=100
checksum=00ed4599a6ed75e4441f5211909480fa
number of transactions actually processed: 20/20
number of failed transactions: 0 (0.000%)
exit_code=3
rollback_marker_count=0

不验收具体 latency 或 TPS。它们会随环境变化，保留在证据中供观察，不作为通过条件。

分动作重跑

./task.sh setup
./task.sh verify
./task.sh baseline
./task.sh inject-error

每次最好给 PG36_EVIDENCE_DIR 一个新路径，防止覆盖上次失败证据。verify 和 baseline 假设夹具已经存在；inject-error 会先验证正常基线，再注入错误。

task 的目标是把协议做显式，并不替代通用工作流平台。生产上的 CI、Ansible、Kubernetes Job 或调度器仍应保留同样语义：输入、目标保护、超时、失败状态、证据、重试策略和回退边界。

2.7.3 注入脚本错误，验证停止、修复与复位

broken.sql先插入一行标记，再故意把 SELECT 写成 SELEC：

INSERT INTO shop.ch02_fixture
    (fixture_id, sku, label, amount, payload)
VALUES
    (999, 'SKU-0999', 'must-be-rolled-back', 9.99, md5('broken'));

SELEC 'intentional syntax error';

任务调用：

psql -X -w \
  --single-transaction \
  "service=pg36-admin" \
  -v ON_ERROR_STOP=1 \
  -f broken.sql

必须同时满足三项：

1. stderr 含明确语法错误与位置；
2. psql 返回状态 3；
3. 新连接查询 fixture_id = 999 得到 0 行。

只满足前两项不够。若忘记 --single-transaction，ON_ERROR_STOP 会停止后续发送，却无法撤销已经自动提交的 INSERT。错误退出与状态回滚是两个独立性质。

修复并不自动等于正确

把 broken.sql 复制成临时 repaired.sql，将 SELEC 改为 SELECT 后再次以单事务运行，标记行会成功提交。此时语法已修复，但 verify.sql 会因为行数变成 101、确定公式不匹配而失败。

这说明：

• 修复执行错误，只证明脚本能跑完；
• 状态验证才证明结果符合任务契约；
• 幂等 setup 可以把本章拥有的夹具重新收敛到 100 行；
• 未经定义的数据不能因为“是成功 SQL 写进去的”就留在基线。

运行：

./task.sh setup
./task.sh verify

确认校验和恢复。不要在有业务价值的表上用 TRUNCATE + 重建 套用这个教学复位模式。

显式 reset

默认 all 不删除夹具。若要回到 ch01 末尾状态，需要两个一致令牌：

export PG36_RESET_TOKEN=RESET_CH02_FIXTURE
./task.sh reset
unset PG36_RESET_TOKEN

Shell 先检查环境变量，SQL 文件再检查 confirm_reset。脚本只执行：

DROP TABLE IF EXISTS shop.ch02_fixture;

它不会删除 pg36_shop、shop 模式或 ch01 的角色。完成后：

SELECT to_regclass('shop.ch02_fixture') IS NULL AS removed;

应返回 true。若下一章继续使用案例，重新执行 ./task.sh setup，不要 reset。

本章最终验收

逐项打勾：

• service file 与 passfile 分离，Git 中没有密码；
• 正确目标通过 context，错误数据库返回状态 3；
• setup 连续执行两次仍得到 100 行和固定校验和；
• 人读探索使用元命令，机器证据查询明确目录字段；
• 所有脚本设置 ON_ERROR_STOP，调用方保存原始退出码；
• CSV 的 NULL、编码、顺序和错误策略明确；
• pgbench 完成 20/20、零失败，且未宣称固定 TPS；
• custom dump 能列清单、恢复到隔离数据库并验证；
• 故障注入返回 3，标记行回滚；
• reset 需要令牌且只删除本章表。

达到这些条件后，读者拥有的不只是几个命令，而是一套后续 34 章都能复用的执行语法：先验证上下文，再应用动作；用状态而不是屏幕感觉验收；把失败当作需要设计的正常路径。

下一章进入 ch03《正本清源：从业务规则到关系模型》。ch02_fixture 只作为确定性输入与反例，正式业务表将从业务不变量重新推导。

参考资料

• PostgreSQL 18：psql
• PostgreSQL 18：pgbench
• PostgreSQL 18：pg_dump
• PostgreSQL 18：pg_restore
• Pigsty v4.5：服务与接入

上一节：最小逻辑备份闭环 · 返回本章目录 · 下一章：正本清源：从业务规则到关系模型 · 查看全书目录 · 查看索引中心