# 实战：写入、只读与管理三类接入

LLMS 索引： [llms.txt](/llms.txt)

---

本节把前六节变成一个可重放验收：

```text
baseline gate
  -> declared identity + private service material
      -> four endpoint semantics
          -> two-slot queue
              -> transaction-session counterexample
                  -> prepared-statement matrix
                      -> async visibility sample
                          -> exact pool rollback
                              -> forward/restore planned switch
                                  -> role-aware pool refresh
                                      -> token reconciliation
                                          -> postflight + adversarial review
```

它在本地 Pigsty nonproduction sandbox 执行 L1/L2 动作。不要把 guard 改掉后
指向生产。

## 22.7.1 为 `pg36_shop` 配置端点和连接预算 {#item-22-7-1}

### 先写生产设计，后映射 sandbox

`pg36_shop` 的概念设计：

| logical service | 用途 | role/path | session | freshness |
|---|---|---|---|---|
| `pg36_shop_rw` | API/worker 短写事务 | primary pooled | transaction | primary |
| `pg36_shop_ro` | catalog/非因果读 | replica pooled | transaction | 声明 staleness |
| `pg36_shop_admin` | migration/诊断 | primary direct | full session | primary |
| `pg36_shop_olap` | 报表/ETL | offline direct/受控 pool | workload-specific | 可陈旧 |

本章不创建真实 `pg36_shop` database，而把它映射到保留沙箱：

```text
logical service    pg36_shop
sandbox database   test
declared user      test, pgbouncer=true
fixture schema     pg36_ch22
fixture table      route_probe
```

为什么不临时创建一个 LOGIN：

```text
PostgreSQL role exists
  != Pigsty/PgBouncer authentication surface delivered
```

正式 runner 从 private reviewed Pigsty inventory 读取既有 `test` credential，
写入 mode `0600` 的临时 libpq service file，结束后删除；credential 不打印、
不 hash 到报告、不进入 Git。

### 生产 identity 应如何声明

生产应在 reviewed Pigsty inventory/secret workflow 中声明：

```yaml
pg_databases:
  - name: pg36_shop

pg_users:
  - name: pg36_shop_app
    password: <approved secret material/reference>
    pgbouncer: true
```

字段与 secret 语法按当前 Pigsty 版本确认。还要设置：

- owner/group role 与 login role 分离；
- least privilege；
- connection limit；
- default privilege；
- role/database timeout；
- TLS/HBA；
- rotation；
- application_name；
- direct admin role；
- PgBouncer per-user/database budget。

不要把书中 placeholder 作为可用 secret。

### libpq service file

概念结构：

```ini
[pg36-shop-rw]
host=pg36-shop.example
port=5433
dbname=pg36_shop
user=pg36_shop_app
sslmode=verify-full
target_session_attrs=read-write
connect_timeout=2

[pg36-shop-ro]
host=pg36-shop.example
port=5434
dbname=pg36_shop
user=pg36_shop_app
sslmode=verify-full
target_session_attrs=read-only
connect_timeout=2

[pg36-shop-admin]
host=pg36-shop.example
port=5436
dbname=pg36_shop
user=pg36_shop_migrate
sslmode=verify-full
target_session_attrs=read-write
connect_timeout=2
```

密码应来自 `.pgpass`、secret manager 或受控 service material。文件权限：

```text
directory 0700
service/pgpass 0600
no symlink
no stdout/log
```

本章沙箱 PgBouncer client TLS 是 `disable`，使用 `sslmode=prefer` 只为匹配
事实，并保留 `EX20-CLIENT-PROXY-NO-TLS`；生产必须另做 TLS 验收。

### 预算草案

假设：

```text
API pods                  12
API client pool           12 each
worker pods                6
worker client pool         6 each
read pods                 12
read client pool           8 each
migration/admin            2
```

客户端上限：

```text
rw clients      12×12 + 6×6 = 180
ro clients      12×8         =  96
admin direct                   =   2
```

不是 278 个 backend。一个候选 server budget：

```text
rw app/worker server pool       40 + reserve 8
ro per eligible replica         20
admin direct                      2
monitor/platform/incident        separately reserved
```

需要在生产规模压测后定稿。

### fixture 合同

`setup.sql` 创建：

```sql
CREATE SCHEMA pg36_ch22 AUTHORIZATION postgres;

CREATE TABLE pg36_ch22.route_probe (
    run_id         uuid        NOT NULL,
    worker_no      integer     NOT NULL,
    attempt_no     integer     NOT NULL,
    token          text        NOT NULL UNIQUE,
    client_sent_at timestamptz NOT NULL,
    committed_at   timestamptz NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (run_id, worker_no, attempt_no)
);
```

它验证：

- existing schema owner/comment；
- exact columns/type/nullability；
- primary/unique constraints；
- declared login safe attributes；
- grants only USAGE/SELECT/INSERT；
- role 未被 runner 创建、修改或接管。

fixture 是 synthetic data，drill 不自动删除它。

### 四端点预期

```text
primary  5433 -> writable pg-test-1 through PgBouncer
replica  5434 -> read-only pg-test-2/3 through PgBouncer
default  5436 -> writable pg-test-1 direct
offline  5438 -> read-only pg-test-3 direct
```

SQL 同时记录 postmaster start time，与直连三成员的基线映射，解决 PgBouncer
local Unix backend 下 `inet_server_addr()` 可能为空的问题。

## 22.7.2 验证会话状态、预备语句与只读一致性 {#item-22-7-2}

### 风险分级

| 动作 | 风险 | 改动 |
|---|---:|---|
| `capture` | L0 | 只读快照 |
| `verify/review/all` | L0 | 重验既有证据 |
| schema setup | L1 | synthetic schema/table |
| pool override | L1 | 一个 PgBouncer process runtime 值 |
| queue/session/prepare/visibility | L1 | synthetic connection/row |
| planned switch + restore | L2 | Patroni role/timeline |
| `reset:fixture` | L3 | 删除 synthetic schema |

`all` 从不：

```text
创建连接
SET pool config
RECONNECT
写行
切换
删除
```

### preflight

在任何 mutation 前，第 19 章 gate 验证：

```text
exact target pg36-l2-vagrant
Pigsty v4.5.0 declaration
PostgreSQL 18
four distinct hosts
pg-test-1 primary
pg-test-2/3 replicas
required exceptions accepted
production approval false
```

第 22 章 capture 再验证：

```text
timeline/member/lag
package versions
listeners
four rendered services
all three PgBouncer configs
all three PostgreSQL connection settings
```

任何 drift 先停。

### pool role-state baseline

在第一个应用 probe 前：

```sql
RECONNECT test;
```

在三台 PgBouncer 分别执行，清除上一轮角色周期遗留的服务端连接状态。

这一步来自真实失败发现：

```text
pg-test-2 direct PostgreSQL was read-only
but its pooled target_session_attrs check rejected
RECONNECT test restored repeated checks
```

它只影响 sandbox teaching database，且是 evidence-bearing action。

### 临时两槽 pool

先 snapshot：

```text
default_pool_size       50
reserve_pool_size       30
reserve_pool_timeout     1
query_wait_timeout     120
```

再 runtime SET：

```text
default_pool_size        2
reserve_pool_size        0
reserve_pool_timeout     1
query_wait_timeout       5
```

为什么 runtime：

- 让 12-client queue 低噪声可观察；
- 避免为实验 saturate 50+30；
- 不修改 rendered file；
- exact `finally` rollback。

生产 pool policy 必须回到 Pigsty declaration，不照抄 runtime SET。

### endpoint probe

每个 service 连接后：

```sql
SELECT pg_is_in_recovery(),
       current_setting('transaction_read_only')::boolean,
       current_setting('cluster_name'),
       current_setting('port')::integer,
       pg_backend_pid(),
       pg_postmaster_start_time();
```

同时三节点 `SHOW POOLS` 证明 `test/test` pooled path。

### saturation probe

12 个 client 同时：

```sql
SELECT pg_sleep(0.25),
       pg_backend_pid(),
       current_setting('transaction_read_only')::boolean;
```

管理 console 周期采样：

```sql
SHOW POOLS;
```

验收：

```text
completed=12
max sv_active<=2
max cl_waiting>=1
unique backend PID<=2
all transactions read-write
```

正式：

```text
max sv_active=2
max cl_waiting=10
unique PID=2
fastest=254.451ms
slowest=1519.423ms
```

### session counterexample

为确定性分配：

1. `RECONNECT test`；
2. A 在 backend X `SET search_path=pg_catalog`，commit；
3. B 借到 X 并保持 transaction；
4. A 被迫借 backend Y；
5. 比较 PID 与 search_path；
6. 关闭 client，`RECONNECT test` 清理实验状态。

正式：

```text
A first       PID 65057, pg_catalog
B borrowed    PID 65057, pg_catalog
A reassigned  PID 65058, "$user", public
```

既证明 state leakage，也证明 state loss。

### protocol prepared

Psycopg：

```text
prepare_threshold=1
12 parameterized executions
hold first backend
force same client to second backend
```

正式：

```text
PIDs        65171, 65172
results     12/12 correct
```

接受范围：

```text
PgBouncer 1.25.2
max_prepared_statements=256
psycopg 3.2.9
transaction pooling
exact tested query
```

### SQL PREPARE negative

```sql
PREPARE pg36_ch22_sql(integer) AS SELECT $1 + 1;
COMMIT;
```

占住创建 backend，再：

```sql
EXECUTE pg36_ch22_sql(41);
```

正式：

```text
prepared PID  65285
execute PID   65286
SQLSTATE      26000
class         InvalidSqlStatementName
```

这是必须出现的失败。

### replica visibility

写端点插入 unique token，commit 后取得主库 LSN；只读端点轮询 exact token，
记录：

```text
selected member
recovery/read-only
replay LSN
elapsed
polls/connection rejections
```

正式：

```text
member        pg-test-2
visible       true
delay         11.092 ms
polls         1
rejections    0
```

验收只要求在 5 秒 sandbox window 内看见，不形成 freshness SLO。

### pool rollback gate

以上任一步成功或失败，`finally` 恢复：

```text
50 / 30 / 1 / 120
```

读取 `SHOW CONFIG` exact compare。只有：

```text
restored_before_switch=true
```

才允许 L2 切换。

## 22.7.3 注入切换与连接风暴，观察退避和恢复 {#item-22-7-3}

### 这里“注入”的边界

本节只执行：

```text
healthy planned switchover
pg-test-1 -> pg-test-2 -> pg-test-1
```

不注入 process/network/storage/DCS failure。标题中的“连接风暴”是小型 6-worker
重连探针，不是生产规模压力。

### exact guards

需要两份 private input：

```text
PG36_CH19_INVENTORY
  第 19 章 exact baseline gate 使用，mode 0600

PG36_CH22_CREDENTIAL_INVENTORY
  包含已声明 test/pgbouncer user credential，mode 0600
```

在普通环境两者可以来自同一 reviewed inventory 的安全副本。本书 local
sandbox 的已部署 v4.5 baseline 与当前工作目录声明版本不同，因此 formal
run 明确分离，避免用新声明冒充旧部署。

执行：

```bash
export PG36_EVIDENCE_DIR=/absolute/private/path/to/new-empty/ch22-run
export PG36_CH19_INVENTORY=/absolute/private/path/to/baseline.yml
export PG36_CH22_CREDENTIAL_INVENTORY=/absolute/private/path/to/credential.yml

export PG36_CH22_TARGET=pg36-l2-vagrant/pg-test
export PG36_CH22_NONPRODUCTION=true
export PG36_CH22_PRODUCTION_DATA=false
export PG36_CH22_PRODUCTION_TRAFFIC=false
export PG36_CH22_CONFIRM=POOL_ROUTE_SWITCH_AND_RESTORE_CH22

static/labs/ch22/task.sh drill:service
```

所有值 exact match。output 非空、inventory 缺失/权限错误、topology drift
都会拒绝。

### client workload

六个 worker，24 秒：

```sql
INSERT INTO pg36_ch22.route_probe
  (run_id, worker_no, attempt_no, token, client_sent_at)
VALUES (...)
RETURNING committed_at, pg_backend_pid(), pg_postmaster_start_time();
```

每 attempt 短连接，service：

```ini
host=10.10.10.11
port=5433
target_session_attrs=read-write
connect_timeout=2
```

失败：

```text
same token is not blindly resubmitted
outcome recorded unknown
worker uses capped exponential backoff + jitter
```

### forward

exact executor：

```bash
patronictl -c /etc/patroni/patroni.yml \
  switchover pg-test \
  --leader pg-test-1 \
  --candidate pg-test-2 \
  --force
```

完成条件：

```text
pg-test-2 sole primary/running
pg-test-1/3 replica/streaming
all timeline 10
lag within gate
```

然后三节点：

```sql
RECONNECT test;
```

必须出现一次 refresh 之后的 acknowledged write，才能进入回切。

### restore

```bash
patronictl ... switchover pg-test \
  --leader pg-test-2 \
  --candidate pg-test-1 \
  --force
```

完成：

```text
pg-test-1 sole primary/running
pg-test-2/3 replica/streaming
all timeline 11
```

再次刷新三节点 pool，并要求首笔确认。

### pool refresh evidence

正向三成员 action：

```text
pg-test-1  172.318 ms
pg-test-2  276.016 ms
pg-test-3  175.812 ms
first acknowledged after final refresh action  140.283 ms
```

回切：

```text
pg-test-1  176.102 ms
pg-test-2  167.016 ms
pg-test-3  173.996 ms
first acknowledged after final refresh action  1697.326 ms
```

这些 action time 只是管理命令耗时；write gap 还包括 topology、health、
server login 和 client backoff。

### reconcile

结束后查询本 run 的所有 worker row：

```sql
SELECT worker_no, attempt_no, token, committed_at
FROM pg36_ch22.route_probe
WHERE run_id = $1
  AND worker_no > 0;
```

分类：

```text
acknowledged token -> must exist
unknown token      -> lookup says committed or absent
duplicate token    -> must be zero
```

正式：

```text
events                        387
acknowledged                  339
unknown                        48
persisted                     339
acknowledged missing            0
unknown committed               0
unknown absent                 48
duplicate                       0
unreconciled                    0
distinct postmaster generations 3
```

`unknown_absent=48` 不是失败；它们已被确定分类。若 unknown committed > 0，
也可以通过，只要 token lookup 明确且业务不重复执行。真正不允许的是
unreconciled。

### 时间口径

```text
forward command                    2.774 s
forward conservative write gap     6.995 s
restore command                    2.766 s
restore conservative write gap     8.510 s
maximum adjacent ack gap           7.653 s
```

conservative gap：

```text
last ack before action start
  -> first ack after stable topology and pool refresh
```

它包含 probe interval、connection attempt 和 backoff，不是纯数据库 promotion
时间，也不是 production RTO。

### postflight 和反例

第 19 章 postflight 再次通过。十五个 evidence mutation 必须被指定错误码拒绝：

```text
production claim
primary routed read-only
replica routed writable
offline wrong member
sticky session claim
broken protocol prepare
SQL PREPARE cross-backend success
pool server cap exceeded
no waiter observed
pool config not restored
acknowledged write missing
unknown unreconciled
write gap over objective
wrong final leader
degraded source
```

反例不是额外单元测试装饰，它防止 validator 只检查“文件存在”。

### evidence tree

```text
ch22-run/
├── preflight-ch19/
├── drill/
│   ├── before.json
│   ├── endpoint-observations.json
│   ├── fixture.json
│   ├── pool-settings.json
│   ├── pool-saturation.json
│   ├── session-semantics.json
│   ├── prepared-statements.json
│   ├── replica-visibility.json
│   ├── phases/
│   │   ├── pre-switch.json
│   │   ├── after-forward.json
│   │   └── restored.json
│   ├── switch-forward.json
│   ├── switch-restore.json
│   ├── pool-refresh-actions.json
│   ├── client-events.jsonl
│   ├── reconciliation.json
│   ├── after.json
│   ├── drill-manifest.json
│   ├── validation-report.json
│   └── negative-report.json
├── postflight-ch19/
└── review.txt
```

完整证据含 token 和运行细节，应放 private evidence store，不提交 Git。
仓库只保留 secret-free 聚合 [`connection-run.json`](/labs/ch22/connection-run.json)。

### read-only 重验

```bash
export PG36_EVIDENCE_DIR=/absolute/path/to/ch22-run
static/labs/ch22/task.sh all
```

应输出：

```text
status=review-ok
endpoints=4
pool_active_max=2
waiters_max=10
acknowledged=339
unknown=48
missing=0
duplicates=0
unreconciled=0
counterexamples=15-rejected
production_ch22_gate=pending
mutation=none
```

### reset

reset 与 drill 完全分离：

```bash
export PG36_CH22_TARGET=pg36-l2-vagrant/pg-test
export PG36_CH22_NONPRODUCTION=true
export PG36_CH22_PRODUCTION_DATA=false
export PG36_CH22_PRODUCTION_TRAFFIC=false
export PG36_CH22_RESET_CONFIRM=DROP_CH22_SYNTHETIC_SCHEMA_AND_ROLE

static/labs/ch22/task.sh reset:fixture
```

确认 token 为兼容已发布的实验接口保留旧名称，但当前 reset 只：

```text
terminate application_name like pg36_ch22_% for user test
DROP SCHEMA pg36_ch22 CASCADE
preserve declared role test
```

它不回滚 timeline、不清理 evidence、不改 pool。删除前仍应阅读脚本并确认 exact
target。

### 失败时保守恢复

脚本：

- pool override 已开始就尝试恢复 baseline；
- switch 未开始则不触碰 topology；
- 若 `pg-test-2` 是唯一稳定 leader，允许计划切回；
- topology ambiguous/degraded 时不猜、不 force；
- 保留 failure manifest；
- 不自动 drop fixture。

`finally` 能降低风险，不能替代 operator inspection。

### 生产准入差距

本章 sandbox contract：

```text
accepted-with-exceptions
```

生产仍需：

1. 在 reviewed Pigsty inventory 声明 database/user/service/budget；
2. 验收 client/server TLS 与证书轮换；
3. 证明 VIP、DNS 或 multi-host entry failover；
4. 跑真实 driver/ORM/query-mode matrix；
5. 在 production-class 资源做容量和 reconnect load test；
6. 注入 unplanned failure、partial network 与 cancel；
7. 为每个 replica workload 定义 consistency contract；
8. 把 pool refresh 自动化、告警化并限定 blast radius；
9. 把结果纳入 SLO/SOP/change review；
10. 由业务 owner、安全与平台共同签署。

不要把本章 8.510 秒写进生产 SLO。

## 本章完成定义

读者应能独立解释并证明：

```text
为什么应用连服务而不是机器
四个端点选择什么角色/路径
异步副本为何无天然 read-your-writes
连接预算如何跨应用/pool/database 相乘
transaction pooling 会丢失/泄漏什么状态
两类 prepared statement 为什么结论不同
SHOW POOLS 如何证明排队和 backend cap
HAProxy health 为什么不等于 SQL 可用
切换后 pool state 为什么必须重验
write outcome unknown 如何 reconcile
何时只能说 sandbox accepted-with-exceptions
```

若只能背端口和 `RECONNECT` 命令，本章还没有完成。

## 参考资料

- [Pigsty：PostgreSQL Service](https://pigsty.io/docs/pgsql/service/)
- [PgBouncer：Feature map](https://www.pgbouncer.org/features.html)
- [PgBouncer：Configuration](https://www.pgbouncer.org/config.html)
- [PgBouncer：Administration console](https://www.pgbouncer.org/usage.html)
- [PostgreSQL 18：libpq connection parameters](https://www.postgresql.org/docs/18/libpq-connect.html)

---

[上一节：Pigsty 服务接入层](../06/) · [返回本章目录](../) · [下一章：固若金汤：认证、授权与数据安全](/authentication-authorization-security/) ·
[查看全书目录](/toc/) · [查看索引中心](/indexes/)