# 数据库开发

术核 AI · 后端工程课件

## 01 准备 PostgreSQL 与 psql 练习环境

为 notes-service 建立独立练习库，区分数据库进程、数据库、角色与客户端。

### 本节目标

- 启动 PostgreSQL 16 并创建非超级用户
- 用 psql 明确连接地址、端口、角色和数据库
- 区分连接失败、认证失败和 SQL 错误

### 必要概念

#### 服务端与客户端

PostgreSQL 进程负责保存数据和执行 SQL；psql 是向它发送命令的终端工具。关闭 psql 不会停止数据库。一个 PostgreSQL 实例可以包含多个数据库，连接时必须确定要进入哪个库。

#### 角色与数据库分别创建

角色表示谁在连接，数据库表示在哪里读写。练习的 app_user 是 notes_lab 所有者，但不是超级用户。第 11 课再拆分迁移角色与运行时角色，避免 API 进程拥有修改表结构的权限。

#### 实验边界

本模块所有命令都是学员在独立 notes_lab 中执行的练习，不是让你操作线上数据库。需要 Python 3.11+ 和已安装、启动的 Docker。此处只用容器获得一致的 PostgreSQL 16 环境；镜像、卷与容器网络在 Docker 模块系统学习。

### 动手实现

#### 1. 建立项目与本机密码文件

以下为 Linux / macOS Bash 命令；Windows 可在 WSL 中执行。本节使用 127.0.0.1:5433，容器内部仍是 5432。密码只用于本机练习，不要提交到 Git。终端在 notes-service 中执行。

文件：终端 / 当前课时

````bash
mkdir -p notes-service
cd notes-service
umask 077
printf 'POSTGRES_PASSWORD=%s\n' "$(python3 -c 'import secrets; print(secrets.token_urlsafe(24))')" > .postgres.env
printf '.postgres.env\n.env\nbackups/\n.venv/\n' >> .gitignore
docker run --name notes-postgres --env-file .postgres.env \
  -p 127.0.0.1:5433:5432 \
  -v notes_pgdata:/var/lib/postgresql/data -d postgres:16
docker exec notes-postgres pg_isready -U postgres
````

#### 2. 创建练习角色和数据库

等待 pg_isready 显示 accepting connections。先执行 docker exec -it notes-postgres psql -U postgres -d postgres，然后在该 psql 中执行下列命令。\password 会交互要求输入两遍 app_user 密码，不在 SQL 历史中直接写出密码。下面建库命令只执行一次。

文件：终端 / 当前课时

````sql
CREATE ROLE app_user LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;
\password app_user
CREATE DATABASE notes_lab OWNER app_user;
\q
````

#### 3. 用业务身份重新连接

执行 docker exec -it notes-postgres psql -h 127.0.0.1 -U app_user -d notes_lab -W，输入刚设置的密码。该命令在容器内访问容器的 5432。若本机已安装 psql，则使用 psql -h 127.0.0.1 -p 5433 -U app_user -d notes_lab -W。后续未特别标明的 SQL 都在此连接运行。

文件：终端 / 当前课时

````sql
\conninfo
SELECT current_database(), current_user, version();
SHOW server_encoding;
SHOW TimeZone;
\set VERBOSITY verbose
\timing on
````

#### 4. 验证持久化与 SQL 命令边界

运行两条 SELECT，观察分号用于结束 SQL，反斜杠命令由 psql 处理。退出客户端后重新连接，数据库仍存在。数据库卷保留了实例数据；不要为了重新做某节练习删除整个卷。

文件：终端 / 当前课时

````sql
SELECT 6 * 7 AS answer;
-- answer = 42
SELECT current_database() = 'notes_lab' AS in_lab;
-- in_lab = t
\q
````

### 验收结果

- current_database() 为 notes_lab，current_user 为 app_user。
- version() 显示 PostgreSQL 16，server_encoding 为 UTF8。
- 退出并重新连接后 SELECT 6 * 7 仍能执行；数据库进程没有因客户端退出而停止。

### 生产约束与故障演练

新服务需要一个可重复创建、不会误连线上实例的开发数据库。

#### 实现决策

- 连接串明确包含数据库名与端口。
- 先用非超级用户练习，环境文件加入忽略规则。
- 生产环境选择受维护的 16.x 补丁版并锁定镜像摘要；postgres:16 是本课程环境入口。

#### 隔离环境演练

在独立练习环境中用错误端口尝试连接，依据 pg_isready 与实际端口定位原因。

#### 恢复验收

- 能从连接信息确认当前实例和身份。
- 数据库卷与容器用途可区分。
- 仓库没有密码文件。

### 常见错误

#### connection refused

原因：容器未启动、端口尚未就绪或混用了宿主机与容器端口。

处理：用 docker ps 与 pg_isready 检查状态；本机使用 5433，docker exec 内部使用 5432。

#### role app_user does not exist 或 password authentication failed

原因：创建角色步骤未执行，或输入的是 postgres 密码。

处理：使用管理连接检查 \du；通过 \password app_user 重设本机练习密码，再用业务身份重连。

### 扩展练习

分别在一个正确连接和一个故意写错数据库名的连接中观察结果，记录错误发生在哪一层。

提示：将 notes_lab 暂时写成 notes_missing；不要真的创建这个库。

参考答案：

正确连接能执行 SELECT；错误库名在连接阶段返回 database does not exist，SQL 尚未执行。连接地址错通常是网络层失败，密码错是认证失败，SELECT 1/0 则是连接成功后的 SQL 执行错误。

### 技术文档

- [PostgreSQL 16：psql](https://www.postgresql.org/docs/16/app-psql.html)
- [PostgreSQL 16：创建数据库](https://www.postgresql.org/docs/16/sql-createdatabase.html)
- [Docker：PostgreSQL 官方镜像](https://hub.docker.com/_/postgres)

---

## 02 定义笔记表、数据类型与约束

把 Python 对象转成有明确边界的数据表，让非法数据在数据库入口被拒绝。

### 本节目标

- 建立 notes 表并理解主键
- 选择 text、boolean 和 timestamptz
- 通过 NOT NULL、CHECK 与默认值维护数据规则

### 必要概念

#### 列类型约束存储形式

bigint 存储整数编号，varchar(120) 限制标题字符数，text 存储正文。boolean 使用 true / false，不能用随意约定的字符串。时间使用 timestamptz 表示一个时间点，显示格式受会话时区影响。

#### 默认值与非空是两条规则

DEFAULT 只在插入时省略列或使用 DEFAULT 时生效；显式传 NULL 仍然是 NULL。NOT NULL 才拒绝空值，CHECK 再约束有效内容。应用校验改善错误提示，数据库约束保护所有写入入口。

#### 主键和生成编号

identity 自动提供编号，PRIMARY KEY 保证编号唯一且非空。编号用于定位，不保证连续，也不代表严格的业务提交顺序。updated_at 的默认值只在插入时生效，更新时需要显式修改或使用统一触发器。

### 动手实现

#### 1. 确认连接后创建表

在第 01 课的 app_user / notes_lab 连接中执行。保存为 sql/001_notes.sql，首次运行一次；后续变更使用单独迁移文件，不要反复删表重建。

文件：sql/001_notes.sql

````sql
CREATE TABLE notes (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title varchar(120) NOT NULL CHECK (char_length(btrim(title)) > 0),
  content text NOT NULL DEFAULT '',
  pinned boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
\d notes
````

#### 2. 插入合法数据并看默认值

本节写入的标题用 db02- 前缀，避免与后续实验混淆。RETURNING 在同一次写入中返回实际保存的行。记录生成的编号，但后续练习不依赖它必须从 1 开始。

文件：终端 / 当前课时

````sql
INSERT INTO notes (title) VALUES ('db02-first')
RETURNING id, title, content, pinned, created_at, updated_at;
-- content 为空串，pinned 为 f，两列时间均非空
SELECT count(*) FROM notes WHERE title = 'db02-first';
````

#### 3. 让数据库拒绝非法数据

以下两条语句在 psql 默认自动提交状态逐条执行；预期分别失败，不要包进同一个 BEGIN 后继续运行。随后 count(*) 仍为 1。

文件：终端 / 当前课时

````sql
INSERT INTO notes (title) VALUES (NULL);
-- SQLSTATE 23502: not_null_violation
INSERT INTO notes (title) VALUES ('   ');
-- SQLSTATE 23514: check_violation
SELECT count(*) FROM notes WHERE title = 'db02-first';
````

#### 4. 观察同一时间点的时区显示

这里改的是当前会话显示方式。UTC 与上海时间显示相差 8 小时，epoch 秒数相同。业务对外使用带时区 ISO 8601，不能在 Python 中去掉 tzinfo 后再猜测时区。

文件：终端 / 当前课时

````sql
SET TIME ZONE 'UTC';
SELECT created_at, extract(epoch FROM created_at) FROM notes WHERE title='db02-first';
SET TIME ZONE 'Asia/Shanghai';
SELECT created_at, extract(epoch FROM created_at) FROM notes WHERE title='db02-first';
SET TIME ZONE 'UTC';
````

### 验收结果

- \d notes 显示主键、identity、非空与标题 CHECK。
- 省略 content 和 pinned 时得到默认值；显式 NULL 标题被拒绝。
- 切换时区后显示不同，epoch 值保持一致。

### 生产约束与故障演练

同一张表将被 API、导入脚本和后台任务共同写入。

#### 实现决策

- 关键合法性规则落到数据库。
- 编号只当标识符，不用于推算数据量。
- 统一存时间点、在展示层选择时区。

#### 隔离环境演练

向 notes_lab 提交空标题、NULL 和超长标题，逐条记录 SQLSTATE。

#### 恢复验收

- 非法行没有落库。
- 合法写入能返回真实生成值。
- 能说明 updated_at 的维护位置。

### 常见错误

#### UPDATE 后 updated_at 没变化

原因：DEFAULT now() 只参与插入。

处理：后续每次更新同时设置 updated_at=now()；多人维护时把规则收敛到统一写入层或触发器。

#### 空标题进入数据库

原因：只使用了 NOT NULL，空串不是 NULL。

处理：加入 btrim 与长度 CHECK，并在应用侧做一致校验；已有不合格数据需先清理。

### 扩展练习

验证 121 个字符的标题被拒绝，再插入带单引号的合法标题。

提示：SQL 字面量中的单引号用两个单引号表示；Python 动态值在下一课使用参数化。

参考答案：

INSERT INTO notes(title) VALUES(repeat('a',121)); 会触发长度错误。INSERT INTO notes(title) VALUES('db02-O''Reilly') RETURNING title; 返回 db02-O'Reilly。SQL 中的引号转义不替代 Python 驱动的参数化接口。

### 技术文档

- [PostgreSQL 16：约束](https://www.postgresql.org/docs/16/ddl-constraints.html)
- [PostgreSQL 16：日期时间类型](https://www.postgresql.org/docs/16/datatype-datetime.html)
- [PostgreSQL 16：生成列编号](https://www.postgresql.org/docs/16/sql-createtable.html)

---

## 03 用参数化 SQL 完成增删改查

从 Python 读写笔记，处理不存在的数据，并验证用户输入不会变成 SQL。

### 本节目标

- 使用 psycopg 3 传递参数
- 通过 RETURNING 判断实际写入结果
- 用明确列名、过滤与排序控制查询

### 必要概念

#### SQL 结构与参数分开传递

execute 的 SQL 字符串描述操作结构，第二个参数提供数据。值占位符统一写 %s，不自行加引号，也不用 f-string 拼接输入。表名、列名不能作为普通值参数传入，动态排序应使用固定白名单。

#### 写入结果要检查

UPDATE 或 DELETE 匹配不到数据通常不是 SQL 错误。RETURNING 返回零行能表示资源不存在；不要在未检查结果时声称修改成功。

#### 读取结果必须有边界

查询显式列名能稳定输出结构。列表查询需要 LIMIT 和确定排序，否则数据库不承诺返回顺序。id DESC 可作为当前课程按编号倒序的简单规则。

### 动手实现

#### 1. 安装驱动与设置连接

在 notes-service 创建并激活 Python 3.11+ 虚拟环境，执行 python -m pip install "psycopg[binary]>=3.2,<4"。把实际解析版本写入 requirements-lock.txt。Linux 使用下面的 PG 环境变量，PGPASSWORD 在当前 shell 中交互输入，不写进脚本。正式环境使用密钥注入并避免输出连接串。

文件：终端 / 当前课时

````bash
export PGHOST=127.0.0.1 PGPORT=5433 PGDATABASE=notes_lab PGUSER=app_user
read -rsp 'Database password: ' PGPASSWORD
printf '\n'
export PGPASSWORD
python -m pip freeze > requirements-lock.txt
````

#### 2. 保存并运行完整 CRUD 脚本

保存为 db_crud.py，再执行 python db_crud.py。connect("") 读取 PG 环境变量；上下文正常退出提交，异常退出回滚。单元素参数必须带逗号。此脚本先创建自己的数据，最后删除同一行，可以重复执行。

文件：db_crud.py

````python
import psycopg
from psycopg.rows import dict_row

with psycopg.connect("", row_factory=dict_row) as conn:
    created = conn.execute(
        "INSERT INTO notes(title, content) VALUES (%s, %s) RETURNING id, title",
        ("db03-O'Reilly", "parameterized insert"),
    ).fetchone()
    note_id = created["id"]
    print("created", created)
    print("read", conn.execute(
        "SELECT id, title, pinned FROM notes WHERE id = %s", (note_id,)
    ).fetchone())
    print("updated", conn.execute(
        "UPDATE notes SET pinned = %s, updated_at = now() WHERE id = %s RETURNING id, pinned",
        (True, note_id),
    ).fetchone())
    print("deleted", conn.execute(
        "DELETE FROM notes WHERE id = %s RETURNING id", (note_id,)
    ).fetchone())
    print("missing", conn.execute(
        "SELECT id FROM notes WHERE id = %s", (note_id,)
    ).fetchone())
````

#### 3. 提交一段看起来像 SQL 的标题

单独保存为 db_input.py 并运行。预期只插入一条普通笔记，输入里的 DROP TABLE 不会执行。finally 外层连接上下文会正常提交这个插入和删除的组合；最后 notes 表仍能查询。

文件：db_input.py

````python
import psycopg

payload = "db03-x'); DROP TABLE notes; --"
with psycopg.connect("") as conn:
    row = conn.execute(
        "INSERT INTO notes(title) VALUES (%s) RETURNING id, title", (payload,)
    ).fetchone()
    assert row[1] == payload
    print(row)
    conn.execute("DELETE FROM notes WHERE id = %s", (row[0],))
    print("table exists", conn.execute("SELECT count(*) FROM notes").fetchone())
````

#### 4. 练习有边界的列表查询

回到 psql 执行，记录结果列与顺序。修改 LIMIT 观察数量变化。对照 Python 中的写法：LIMIT 也可用 %s 传值，但应用应限制 1 到 100，不能让客户端请求任意数量。

文件：终端 / 当前课时

````sql
SELECT id, title, pinned
FROM notes
WHERE pinned = false
ORDER BY id DESC
LIMIT 10;
UPDATE notes SET pinned = true WHERE id = -1 RETURNING id;
-- 0 rows / UPDATE 0，不是 SQL 异常
````

### 验收结果

- CRUD 输出依次出现同一编号，最后 missing 为 None。
- 含单引号和 SQL 片段的标题保持原样，notes 表仍存在。
- 不存在编号的 UPDATE 返回零行；列表最多 10 行且编号递减。

### 生产约束与故障演练

笔记标题来自浏览器，数据库不能把任何一段用户输入当成可执行语法。

#### 实现决策

- 参数化所有动态值。
- 列表显式限制数量和排序。
- 以 RETURNING 判断变更结果。

#### 隔离环境演练

在 notes_lab 用包含单引号与 SQL 片段的标题创建笔记，核验原值与表完整性。

#### 恢复验收

- 没有 SQL 字符串插值。
- 空结果有明确处理。
- 连接与事务在脚本结束后释放。

### 常见错误

#### syntax error 或用户输入改变了 SQL 含义

原因：把输入放进 f-string 或字符串拼接。

处理：恢复固定 SQL 和独立参数元组；不要手写通用转义函数。

#### 脚本输出成功但另一个连接查不到新增行

原因：事务尚未提交，或者最后主动删除了练习行。

处理：确认连接上下文已经退出；若只验证插入，暂时移除删除步骤后在独立连接核查。

### 扩展练习

写一个只更新标题的函数，返回更新后的 id/title；不存在时返回 None。

提示：WHERE id = %s，并在同一 UPDATE 中 RETURNING id, title。

参考答案：

def rename_note(conn, note_id, title):
    return conn.execute("UPDATE notes SET title=%s, updated_at=now() WHERE id=%s RETURNING id,title", (title,note_id)).fetchone()
调用方决定事务提交边界；函数不私自创建连接或提交。

### 技术文档

- [Psycopg 3：参数传递](https://www.psycopg.org/psycopg3/docs/basic/params.html)
- [Psycopg 3：基本使用](https://www.psycopg.org/psycopg3/docs/basic/usage.html)
- [PostgreSQL 16：RETURNING](https://www.postgresql.org/docs/16/dml-returning.html)

---

## 04 用关联表、JOIN 与聚合表达关系

为笔记添加标签，使用外键保证关联有效，再统计每条笔记的标签数量。

### 本节目标

- 建立多对多关系和联合主键
- 区别 INNER JOIN 与 LEFT JOIN
- 避免 JOIN 重复行导致聚合错误

### 必要概念

#### 多对多关系单独建表

一条笔记可有多个标签，一个标签可属于多条笔记。note_tags 的每一行代表一组关联，联合主键阻止重复关联。将标签塞进逗号字符串会让检索、唯一性与改名都变得困难。

#### 外键检查关联完整性

note_id 必须对应真实笔记，tag_id 必须对应真实标签。此例删除笔记时级联清理关联行，删除仍被使用的标签则拒绝；删除策略来自业务，而不是所有外键统一加 CASCADE。

#### 聚合前先辨认行数

LEFT JOIN 会保留没有标签的笔记，并补一个右侧为空的连接结果。COUNT(*) 会把这一行算进去，COUNT(nt.tag_id) 只统计非空标签。聚合查询返回的是分组后的结果。

### 动手实现

#### 1. 建立标签和关联表

在 notes_lab 首次执行一次；保存为 sql/002_tags.sql。引用 notes 主键，不复制标题。外键列并不会自动获得适合所有反向查询的索引，后续根据查询创建。

文件：sql/002_tags.sql

````sql
CREATE TABLE tags (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name varchar(40) NOT NULL UNIQUE CHECK (char_length(btrim(name)) > 0)
);
CREATE TABLE note_tags (
  note_id bigint NOT NULL REFERENCES notes(id) ON DELETE CASCADE,
  tag_id bigint NOT NULL REFERENCES tags(id) ON DELETE RESTRICT,
  PRIMARY KEY (note_id, tag_id)
);
````

#### 2. 准备两条笔记和两个标签

以下是 psql 脚本；\gset 将 RETURNING 值写进本会话变量。创建笔记返回语句末尾不加分号，由 \gset 执行。标签可以重复准备，笔记每次执行都会产生两条独立样本。

文件：终端 / 当前课时

````sql
INSERT INTO notes(title) VALUES ('db04-with-tags') RETURNING id AS tagged_id \gset
INSERT INTO notes(title) VALUES ('db04-no-tags') RETURNING id AS empty_id \gset
INSERT INTO tags(name) VALUES ('db04-python'), ('db04-api') ON CONFLICT (name) DO NOTHING;
INSERT INTO note_tags(note_id,tag_id)
SELECT :tagged_id, id FROM tags WHERE name IN ('db04-python','db04-api');
````

#### 3. 分别查看关联明细和数量

第一条查询得到 2 行标签明细。第二条查询保留无标签笔记，数量分别为 2 与 0。注意按 id 筛选的是上一步新建的两行，不受此前重复运行影响。

文件：终端 / 当前课时

````sql
SELECT n.id,n.title,t.name
FROM notes n JOIN note_tags nt ON nt.note_id=n.id
JOIN tags t ON t.id=nt.tag_id
WHERE n.id=:tagged_id ORDER BY t.name;

SELECT n.id,n.title,count(nt.tag_id) AS tag_count
FROM notes n LEFT JOIN note_tags nt ON nt.note_id=n.id
WHERE n.id IN (:tagged_id,:empty_id)
GROUP BY n.id,n.title ORDER BY n.id;
````

#### 4. 验证重复关联和删除策略

先单独执行重复 INSERT，预期 23505 唯一性错误；再单独执行 DELETE tags，预期 23503 外键错误。最后删除本课带标签笔记，对应关联行自动清理。只删除本会话创建的样本。

文件：终端 / 当前课时

````sql
INSERT INTO note_tags(note_id,tag_id)
SELECT :tagged_id,id FROM tags WHERE name='db04-python';
-- 23505：重复关联
DELETE FROM tags WHERE name='db04-python';
-- 23503：仍被笔记引用
DELETE FROM notes WHERE id=:tagged_id;
SELECT count(*) FROM note_tags WHERE note_id=:tagged_id;
-- 0
````

### 验收结果

- 关联明细返回两个标签，聚合结果分别为 2 与 0。
- 重复添加同一关联失败，不存在编号的外键写入也会失败。
- 删除练习笔记后仅其关联行消失，标签本身仍存在。

### 生产约束与故障演练

生产列表页需要展示标签数，同时允许笔记暂时没有标签。

#### 实现决策

- 通过关联表表达多对多。
- 用唯一约束阻止重复关系。
- 明确父记录删除行为并验证空关系。

#### 隔离环境演练

在 notes_lab 重复插入关联、删除被引用标签，记录数据库拒绝原因。

#### 恢复验收

- 零标签结果正确。
- 重复关系无法落库。
- 统计没有因连接放大而重复计算。

### 常见错误

#### 没有标签的笔记消失了

原因：使用 INNER JOIN，或在 WHERE 中过滤右表导致 LEFT JOIN 效果被破坏。

处理：需要保留左表时使用 LEFT JOIN；右表匹配条件按语义放到 ON，并检查空值结果。

#### 无标签笔记显示数量 1

原因：COUNT(*) 统计了 LEFT JOIN 生成的空关联行。

处理：改用 COUNT(nt.tag_id)，并检查连接多个一对多表时是否又放大行数。

### 扩展练习

查询拥有 db04-python 标签的笔记，要求每条笔记最多出现一次。

提示：可使用 EXISTS 子查询表达是否存在，不需要把标签列放进输出。

参考答案：

SELECT n.id,n.title FROM notes n WHERE EXISTS (SELECT 1 FROM note_tags nt JOIN tags t ON t.id=nt.tag_id WHERE nt.note_id=n.id AND t.name='db04-python') ORDER BY n.id; 第 04 步已删除本课关联时结果为空；重新执行第 02 步后应出现新笔记。

### 技术文档

- [PostgreSQL 16：表表达式与 JOIN](https://www.postgresql.org/docs/16/queries-table-expressions.html)
- [PostgreSQL 16：聚合函数](https://www.postgresql.org/docs/16/functions-aggregate.html)
- [PostgreSQL 16：外键](https://www.postgresql.org/docs/16/ddl-constraints.html)

---

## 05 把多次写入放进同一个事务

一次保存笔记并关联标签，主动制造第二步失败，确认第一步也回滚。

### 本节目标

- 划定需要同时成功的写入边界
- 识别失败事务状态并执行回滚
- 使用保存点隔离可选步骤

### 必要概念

#### 事务覆盖一个业务动作

创建笔记和保存必需标签属于同一次提交。只有两步都成功才向调用者返回成功。数据库事务不能自动回滚已经发送的邮件或外部 HTTP 请求，跨系统一致性在 API + 数据库和异步任务模块继续处理。

#### 错误后的事务需要结束

BEGIN 后发生约束错误，事务进入失败状态。继续 SELECT 通常得到 25P02；应 ROLLBACK 整个事务，或者在预先建立的 SAVEPOINT 上回滚到保存点。

#### 事务越短越容易控制

事务持有连接与可能的锁。不要在 BEGIN 与 COMMIT 之间等用户输入、调用模型或上传文件。identity 消耗的编号即使回滚也可能不复用，所以不能用编号是否连续判断事务正确性。

### 动手实现

#### 1. 用成功路径保存笔记和标签

先完成第 04 课表结构。此脚本在 psql 中执行；标签使用已有 db04-api，若无该标签先执行 INSERT INTO tags(name) VALUES ('db04-api') ON CONFLICT DO NOTHING。事务末尾应 COMMIT。

文件：终端 / 当前课时

````sql
BEGIN;
INSERT INTO notes(title) VALUES ('db05-atomic-ok') RETURNING id AS ok_id \gset
INSERT INTO note_tags(note_id,tag_id)
SELECT :ok_id,id FROM tags WHERE name='db04-api';
COMMIT;
SELECT n.id,n.title,t.name FROM notes n
JOIN note_tags nt ON nt.note_id=n.id JOIN tags t ON t.id=nt.tag_id
WHERE n.id=:ok_id;
````

#### 2. 制造第二步失败

本例用不存在的负标签编号触发外键错误。逐条执行，观察提示符从 notes_lab=* 改为失败事务状态。不要把预期错误脚本用 ON_ERROR_STOP 直接执行后遗漏回滚。

文件：终端 / 当前课时

````sql
BEGIN;
INSERT INTO notes(title) VALUES ('db05-must-rollback') RETURNING id AS failed_id \gset
INSERT INTO note_tags(note_id,tag_id) VALUES (:failed_id,-999);
-- 23503：外键错误
SELECT 1;
-- 25P02：当前事务已失败
ROLLBACK;
SELECT count(*) FROM notes WHERE id=:failed_id;
-- 0
````

#### 3. 用保存点处理可选标签

只有当业务允许“笔记成功、可选标签失败”时才采用本例。保存点前的笔记保留，保存点之后失败的关联被回滚。若标签是业务必填，则应沿用上一步整笔回滚。

文件：终端 / 当前课时

````sql
BEGIN;
INSERT INTO notes(title) VALUES ('db05-optional-tag') RETURNING id AS optional_id \gset
SAVEPOINT optional_tag;
INSERT INTO note_tags(note_id,tag_id) VALUES (:optional_id,-999);
ROLLBACK TO SAVEPOINT optional_tag;
COMMIT;
SELECT id,title FROM notes WHERE id=:optional_id;
-- 1 row
````

#### 4. 在独立会话核对提交边界

打开第二个 psql，执行相同 SELECT。成功笔记和可选标签笔记可见，must-rollback 不可见。再在会话 A BEGIN 后插入一条新笔记，暂不提交；会话 B 查询该标题为零行，A COMMIT 后 B 再查才能看到。最后结束两个会话中所有未结束事务。

文件：终端 / 当前课时

````sql
SELECT title,count(*) FROM notes
WHERE title LIKE 'db05-%' GROUP BY title ORDER BY title;
-- 包含 db05-atomic-ok、db05-optional-tag
-- 不包含 db05-must-rollback
````

### 验收结果

- 第二步外键失败后，回滚事务中的笔记不存在。
- 失败状态执行 SELECT 返回 25P02，ROLLBACK 后恢复。
- 另一会话只能读取已提交数据；保存点例子符合明确的可选业务规则。

### 生产约束与故障演练

保存按钮触发多张表写入，任何必需步骤失败都不能产生半成品记录。

#### 实现决策

- 事务以业务动作划分。
- 可选步骤才允许保存点局部恢复。
- 外部调用在数据库事务之外安排。

#### 隔离环境演练

在 notes_lab 用无效外键中断保存，重新连接检查是否留下半条业务记录。

#### 恢复验收

- 必需写入要么全有要么全无。
- 失败后连接能够继续服务。
- 日志能定位原始约束错误而不是只有后续 25P02。

### 常见错误

#### 笔记创建了但标签没保存

原因：每条 SQL 独立提交，缺少完整事务边界。

处理：将必需写入放入同一事务，仅在成功提交后返回业务成功。

#### 重试 SQL 一直提示 current transaction is aborted

原因：在失败事务中继续执行，没有先回滚。

处理：退出事务上下文或显式 ROLLBACK，再决定是否重试整个业务动作。

### 扩展练习

把可选标签例子改成标签必填，说明应该删除哪些语句。

提示：必填意味着不能在失败后只回滚局部。

参考答案：

移除 SAVEPOINT 和 ROLLBACK TO SAVEPOINT。标签插入异常时执行整个事务 ROLLBACK，笔记和关联都不保留；应用把错误转成合适的响应，而不是返回创建成功。

### 技术文档

- [PostgreSQL 16：事务入门](https://www.postgresql.org/docs/16/tutorial-transactions.html)
- [PostgreSQL 16：SAVEPOINT](https://www.postgresql.org/docs/16/sql-savepoint.html)
- [Psycopg 3：事务管理](https://www.psycopg.org/psycopg3/docs/basic/transactions.html)

---

## 06 用两个会话观察隔离级别与 MVCC

按固定顺序执行读写，比较 Read Committed 和 Repeatable Read 的可见结果。

### 本节目标

- 用两个 psql 会话复现读取差异
- 解释语句快照与事务快照
- 识别需要重试的序列化失败

### 必要概念

#### 快照决定读者能看见什么

Read Committed 下，每条查询读取该语句开始时已经提交的数据。Repeatable Read 通常固定在事务第一条实际查询建立的快照。下面的实验用同一行、同一个读取动作对照，避免只背隔离级别名字。

#### 普通读与写锁不是同一机制

MVCC 使普通 SELECT 可以读取已提交版本，不必因为另一会话正在更新同一行而等待其提交。SELECT FOR UPDATE 是带锁读取，行为不同。长事务会保留旧快照，增加旧版本清理压力。

#### 更强隔离伴随重试责任

Repeatable Read 或 Serializable 在并发冲突下可能返回 SQLSTATE 40001。重试应从事务开始重新读取和计算，设置次数上限与退避；不能在已经失败的事务里重发最后一条 SQL。

### 动手实现

#### 1. 准备专用行并打开会话 A 与 B

在任意会话执行一次第一组语句，记录返回的编号。A、B 均使用 app_user / notes_lab。每个会话执行 \set iso_id 实际编号，示例中的 123 必须替换。每轮实验开始前确保双方已 COMMIT 或 ROLLBACK。

文件：终端 / 当前课时

````sql
INSERT INTO notes(title,content) VALUES ('db06-isolation','v1') RETURNING id;
-- 假设返回 123，在 A 和 B 各自设置：
\set iso_id 123
SHOW default_transaction_isolation;
-- read committed
````

#### 2. 执行 Read Committed 时序

按注释切换窗口，不要一次性粘贴到同一会话。A 第一次读 v1；B 更新但未提交时 A 仍读 v1；B 提交后 A 再读变为 v2。A 的事务没有重开，但下一条查询已经取得新快照。

文件：终端 / 当前课时

````sql
-- A：先执行
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT content FROM notes WHERE id=:iso_id;
-- B：执行，先不提交
BEGIN;
UPDATE notes SET content='v2',updated_at=now() WHERE id=:iso_id;
-- A：此时仍为 v1
SELECT content FROM notes WHERE id=:iso_id;
-- B：现在提交
COMMIT;
-- A：现在为 v2，然后结束
SELECT content FROM notes WHERE id=:iso_id;
COMMIT;
````

#### 3. 执行 Repeatable Read 时序

延续上一步的 v2。A 建立快照后，B 写入 v3 并提交；A 在原事务内仍读 v2。A 提交并开始新查询后读到 v3。记录四次读取结果与相应窗口。

文件：终端 / 当前课时

````sql
-- A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT content FROM notes WHERE id=:iso_id;
-- B：自动提交状态
UPDATE notes SET content='v3',updated_at=now() WHERE id=:iso_id;
-- A：原快照仍为 v2
SELECT content FROM notes WHERE id=:iso_id;
COMMIT;
SELECT content FROM notes WHERE id=:iso_id;
-- v3
````

#### 4. 复现并发更新失败并恢复

A 用 Repeatable Read 读取 v3，B 改成 v4 并提交，A 再更新同一行会遇到 40001。最后 A ROLLBACK 并重新读取，确认新事务看到 v4。这里只演示失败机制，不对包含外部副作用的动作进行盲目重试。

文件：终端 / 当前课时

````sql
-- A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT content FROM notes WHERE id=:iso_id;
-- B
UPDATE notes SET content='v4',updated_at=now() WHERE id=:iso_id;
-- A
UPDATE notes SET content='v5' WHERE id=:iso_id;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;
SELECT content FROM notes WHERE id=:iso_id;
-- v4
````

### 验收结果

- Read Committed 在 B 提交前后读到 v1、v2。
- Repeatable Read 在事务中保持 v2，事务结束后读到 v3。
- 并发更新实验出现 40001，ROLLBACK 后读取恢复。

### 生产约束与故障演练

后台导出需要一致读取，编辑接口同时持续更新笔记。

#### 实现决策

- 根据读取范围选择隔离级别。
- 限制长事务，避免把导出网络传输放在读事务内。
- 40001 重试覆盖完整事务。

#### 隔离环境演练

在 notes_lab 双会话执行快照与并发更新实验，保留实际输出顺序。

#### 恢复验收

- 每个结果可对应到一次提交。
- 实验后无遗留事务。
- 能区分数据可见性与行锁等待。

### 常见错误

#### 两种隔离级别看起来没有区别

原因：A 没有显式 BEGIN，或者 B 在 A 建立快照之前已提交。

处理：严格按窗口与注释顺序执行，记录每次 BEGIN、SELECT 和 COMMIT 的时点。

#### 实验结束后连接一直 idle in transaction

原因：只观察结果没有结束事务。

处理：两个窗口都执行 ROLLBACK；生产应用用上下文管理事务并限制事务内工作量。

### 扩展练习

说明报表需要同一时点多次读取，而普通单条查询通常不用提高到 Repeatable Read 的原因。

提示：先确定需要保证的是一条 SQL，还是多条 SQL 共享快照。

参考答案：

单条 SELECT 本身在一个语句快照内执行。需要多条 SELECT 共享一致快照时，可选择短的只读 Repeatable Read 事务；若能用一条 SQL 表达汇总，通常可减少事务持续时间。提高隔离级别不能代替业务约束与正确的重试实现。

### 技术文档

- [PostgreSQL 16：事务隔离](https://www.postgresql.org/docs/16/transaction-iso.html)
- [PostgreSQL 16：MVCC 简介](https://www.postgresql.org/docs/16/mvcc-intro.html)
- [PostgreSQL 16：序列化失败处理](https://www.postgresql.org/docs/16/mvcc-serialization-failure-handling.html)

---

## 07 定位锁等待、复现死锁并加入版本检查

观察同一行写入的等待关系，再分别用固定加锁顺序和乐观锁解决不同冲突。

### 本节目标

- 用 pg_blocking_pids 找到锁等待源头
- 复现并处理死锁回滚
- 用 version 条件阻止覆盖其他人的编辑

### 必要概念

#### 行锁持续到事务结束

UPDATE 和 SELECT FOR UPDATE 可阻止另一个事务更新同一行。等待不一定是坏事，但必须有时限与可观测性；持锁时调用外部服务会让数据库承担外部延迟。

#### 死锁来自循环等待

A 持有行一等待行二，B 持有行二等待行一，形成闭环。数据库会中止其中一个事务，不能预言一定中止谁。固定顺序获取多行锁可减少死锁，应用仍需识别 40P01 并有限重试整个事务。

#### 版本检查表达编辑冲突

客户端读取 version=1，更新时要求 WHERE version=1 并同时 version=version+1。若别人已提交，更新返回零行，调用者应重新读取并解决冲突。版本检查防止旧内容覆盖新内容，不用于替代所有数据库锁。

### 动手实现

#### 1. 建立两个实验行并添加版本列

完成前面表结构后执行一次 ADD COLUMN；若已存在 version 则只运行后两条。把返回编号记为 first_id 和 second_id，在 A、B 两个窗口各设置相同变量。

文件：终端 / 当前课时

````sql
ALTER TABLE notes ADD COLUMN version integer NOT NULL DEFAULT 1 CHECK (version > 0);
INSERT INTO notes(title) VALUES ('db07-first') RETURNING id;
INSERT INTO notes(title) VALUES ('db07-second') RETURNING id;
-- 在两个窗口替换为实际返回值
\set first_id 124
\set second_id 125
````

#### 2. 观察一个有上限的锁等待

先 A 加锁，再 B 执行 UPDATE。B 等待期间切回 A 查询阻塞信息；如果 5 秒不够观察，可重做一次。B 约 5 秒后收到 lock timeout，之后必须 ROLLBACK；A 也 ROLLBACK 释放锁。

文件：终端 / 当前课时

````sql
-- A
BEGIN;
SELECT id FROM notes WHERE id=:first_id FOR UPDATE;
-- B
BEGIN;
SET LOCAL lock_timeout='5s';
UPDATE notes SET content='waiting' WHERE id=:first_id;
-- A：在 B 等待期间执行
SELECT pid,wait_event_type,wait_event,pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity WHERE datname='notes_lab';
-- B 超时后，以及 A 观察完后，各自执行
ROLLBACK;
````

#### 3. 按顺序制造死锁

先确认双方没有遗留事务。先 A 锁第一行，再 B 锁第二行，然后 A 请求第二行进入等待，最后 B 请求第一行。一个窗口返回 deadlock detected，另一个窗口可继续获得锁。无论哪个失败，两个窗口最后都执行 ROLLBACK。

文件：终端 / 当前课时

````sql
-- A
BEGIN;
SELECT id FROM notes WHERE id=:first_id FOR UPDATE;
-- B
BEGIN;
SELECT id FROM notes WHERE id=:second_id FOR UPDATE;
-- A：此处会等待
SELECT id FROM notes WHERE id=:second_id FOR UPDATE;
-- B：触发循环等待
SELECT id FROM notes WHERE id=:first_id FOR UPDATE;
-- SQLSTATE 40P01 出现在其中一个会话
-- A 和 B 各执行 ROLLBACK;
````

#### 4. 改为相同顺序锁定多行

在 A 执行下面代码并保持事务，B 执行同样代码会等待 A。A COMMIT 后 B 能继续，然后 B COMMIT。双方都按 id 升序取得目标锁，避免本例的相反顺序。

文件：终端 / 当前课时

````sql
BEGIN;
SELECT id FROM notes WHERE id IN (:first_id,:second_id) ORDER BY id FOR UPDATE;
-- A 先 COMMIT，B 等待结束后再 COMMIT
COMMIT;
````

#### 5. 用旧版本号重放编辑

在同一个窗口按顺序执行即可模拟两个客户端都读到版本 1。先把专用行重置到 version=1；第一次更新得到 2，第二次带旧版本条件返回 0 行。只有实际更新成功才向客户端返回成功。

文件：终端 / 当前课时

````sql
UPDATE notes SET version=1,content='base' WHERE id=:first_id;
UPDATE notes SET content='editor A',version=version+1,updated_at=now()
WHERE id=:first_id AND version=1 RETURNING id,content,version;
-- version=2
UPDATE notes SET content='editor B',version=version+1,updated_at=now()
WHERE id=:first_id AND version=1 RETURNING id,content,version;
-- 0 rows
SELECT content,version FROM notes WHERE id=:first_id;
-- editor A, 2
````

### 验收结果

- 锁等待能查到非空 blockers，超时后事务可回滚。
- 死锁仅保证一个参与事务被中止，两个窗口最后均无活动事务。
- 旧版本更新返回零行，editor A 的内容没有被覆盖。

### 生产约束与故障演练

两名用户同时编辑笔记，后台任务又可能批量修改相同记录。

#### 实现决策

- 短事务与锁超时控制等待。
- 多行写入约定一致的锁顺序。
- 普通编辑用版本检查向用户呈现冲突。

#### 隔离环境演练

仅在 notes_lab 双会话制造行锁与死锁，记录等待链、SQLSTATE 和回滚结果。

#### 恢复验收

- 没有无限等待。
- 所有写入入口维护 version。
- 冲突时保留已提交内容并报告更新失败。

### 常见错误

#### 设置 statement_timeout 后还是不清楚在等什么

原因：语句超时能中断执行，但没有分析锁等待来源。

处理：在等待期间查看 wait_event 与 pg_blocking_pids，结合持锁会话事务时长定位。

#### 版本检查存在但更新仍覆盖内容

原因：SELECT 后在 Python 检查版本，UPDATE 没带 version 条件。

处理：把版本比较和递增放在同一条 UPDATE 中，依据 RETURNING 判定成功。

### 扩展练习

将批量修改改为按 id 升序锁定，并说明收到 40P01 后重试范围。

提示：不要只重试死锁报错的最后一条 SELECT。

参考答案：

先确定全部目标编号，在同一事务按升序 SELECT ... FOR UPDATE，再执行修改。收到 40P01 后回滚并重新读取整个业务动作的输入，有限退避重试；涉及外部副作用时先使用幂等机制。

### 技术文档

- [PostgreSQL 16：显式锁与死锁](https://www.postgresql.org/docs/16/explicit-locking.html)
- [PostgreSQL 16：统计活动](https://www.postgresql.org/docs/16/monitoring-stats.html)
- [PostgreSQL 16：错误码](https://www.postgresql.org/docs/16/errcodes-appendix.html)

---

## 08 围绕查询设计单列、组合与部分索引

准备足够的数据，对照查询条件与排序建立索引，再核算索引带来的写入成本。

### 本节目标

- 从 WHERE 与 ORDER BY 推导组合索引
- 用部分索引服务低占比条件
- 知道主键、唯一约束与外键索引的区别

### 必要概念

#### 索引缩小查找范围

B-tree 索引按键组织数据，使等值、范围与有序读取可以少访问一些数据页。索引占用磁盘，写入和更新时也要维护。是否使用索引由优化器估计成本决定，小表顺序扫描常常更合适。

#### 组合键的顺序来自查询

WHERE pinned=true ORDER BY created_at DESC,id DESC LIMIT 20 可用 pinned、created_at、id 的组合索引。前面的等值条件固定范围，后面提供稳定排序。只对 pinned 这个低基数字段建索引不一定有收益。

#### 部分索引只保存需要的行

如果置顶笔记仅占少数，可只索引 WHERE pinned=true 的记录。查询条件需能匹配该谓词；不要假设参数化的任意布尔条件总能使用该索引。主键和唯一约束会创建索引，外键引用列不自动创建索引。

### 动手实现

#### 1. 在独立练习库生成性能样本

仅 notes_lab 执行，写入 50,000 条带 db08-bench- 前缀的笔记。此脚本只运行一次，重复执行会额外产生同量数据。固定时间与稀疏 pinned 分布便于对照，但不能代表真实业务分布。

文件：终端 / 当前课时

````sql
INSERT INTO notes(title,content,pinned,created_at,updated_at)
SELECT 'db08-bench-' || g, repeat('x',200), g % 100 = 0,
       timestamptz '2026-01-01 00:00:00+00' + g * interval '1 second',
       timestamptz '2026-01-01 00:00:00+00' + g * interval '1 second'
FROM generate_series(1,50000) AS g;
ANALYZE notes;
SELECT count(*),count(*) FILTER(WHERE pinned) FROM notes;
````

#### 2. 保存无专用索引时的计划

先只用 EXPLAIN 观察估计计划，下一课运行带 ANALYZE 的实际计划。记录扫描方式、Sort 是否存在及估计行数，不把某个固定 cost 数值作为正确答案。

文件：终端 / 当前课时

````sql
EXPLAIN
SELECT id,title,created_at FROM notes
WHERE pinned=true ORDER BY created_at DESC,id DESC LIMIT 20;
SELECT indexname,indexdef FROM pg_indexes WHERE tablename='notes';
````

#### 3. 建立组合索引并对比

建索引后重新执行相同 EXPLAIN。数据量足够时通常能减少扫描和排序工作，但输出由版本、统计和数据决定。索引包含 id 用于同一时间点下稳定排序。

文件：终端 / 当前课时

````sql
CREATE INDEX notes_pinned_created_id_idx ON notes(pinned,created_at DESC,id DESC);
ANALYZE notes;
EXPLAIN
SELECT id,title,created_at FROM notes
WHERE pinned=true ORDER BY created_at DESC,id DESC LIMIT 20;
SELECT pg_size_pretty(pg_relation_size('notes_pinned_created_id_idx')) AS index_size;
````

#### 4. 将置顶查询改为部分索引实验

为能明确对照，只在 notes_lab 删除刚创建的实验索引，改建部分索引，再执行同样查询。生产建索引可评估 CREATE INDEX CONCURRENTLY 以减少阻塞，但它不能放在事务块中，失败后还需检查无效索引。

文件：终端 / 当前课时

````sql
DROP INDEX notes_pinned_created_id_idx;
CREATE INDEX notes_pinned_recent_idx ON notes(created_at DESC,id DESC) WHERE pinned=true;
EXPLAIN
SELECT id,title,created_at FROM notes
WHERE pinned=true ORDER BY created_at DESC,id DESC LIMIT 20;
SELECT pg_size_pretty(pg_relation_size('notes_pinned_recent_idx')) AS index_size;
CREATE INDEX note_tags_tag_id_idx ON note_tags(tag_id);
````

### 验收结果

- 记录至少两个版本计划和各自索引大小。
- 能说明 pinned=true 查询与部分索引谓词的关系。
- note_tags 的联合主键覆盖 note_id 开头查找，反向 tag_id 查询有单独索引。

### 生产约束与故障演练

首页需要快速显示置顶笔记，写入仍持续发生。

#### 实现决策

- 索引对应一条明确查询。
- 用接近真实分布的数据评估读写成本。
- 生产在线建索引单独规划事务与失败恢复。

#### 隔离环境演练

在 notes_lab 比较无索引、组合索引与部分索引的计划与大小。

#### 恢复验收

- 查询顺序确定。
- 有性能证据支持索引选择。
- 没有把练习库 DROP INDEX 直接当成生产变更方案。

### 常见错误

#### 建了索引仍然 Seq Scan

原因：表很小、命中行过多、统计过旧或查询不匹配索引。

处理：先 ANALYZE 并检查实际 SQL、类型与选择率；不要通过关闭顺序扫描作为生产修复。

#### 索引越加越多，写入越来越慢

原因：每个索引都增加存储与维护，部分索引可能重复覆盖同一查询。

处理：保留使用证据与目标查询，对重复索引评估后再清理，结合写入延迟一起验收。

### 扩展练习

为 WHERE tag_id=? 查询关联的 note_id 设计索引，并判断现有主键是否足够。

提示：联合主键是 (note_id,tag_id)，注意最左列。

参考答案：

现有主键擅长 note_id 或 note_id+tag_id 条件，不应默认满足按 tag_id 反查的性能需求。可使用 CREATE INDEX ON note_tags(tag_id,note_id)，并在实际数据上对比；若采用此组合索引，应评估是否还需要单列 tag_id 索引。

### 技术文档

- [PostgreSQL 16：多列索引](https://www.postgresql.org/docs/16/indexes-multicolumn.html)
- [PostgreSQL 16：部分索引](https://www.postgresql.org/docs/16/indexes-partial.html)
- [PostgreSQL 16：CREATE INDEX](https://www.postgresql.org/docs/16/sql-createindex.html)

---

## 09 读取 EXPLAIN ANALYZE 并改造深分页

用执行计划验证实际工作量，再把大 OFFSET 改成稳定的游标条件。

### 本节目标

- 读懂实际行数、loops 与 buffers
- 比较浅分页和深分页的扫描量
- 使用 created_at 与 id 实现键集分页

### 必要概念

#### ANALYZE 会真的执行 SQL

EXPLAIN 只生成计划，EXPLAIN ANALYZE 会执行语句并收集结果。此课仅分析 SELECT；不要直接拿它分析线上 DELETE 或 UPDATE。实际耗时受缓存与负载影响，需要同时看扫描量和缓冲访问。

#### 深 OFFSET 仍需跳过前面的记录

LIMIT 20 OFFSET 40000 返回 20 行，但执行器通常仍需走过被跳过的记录。键集分页使用上一页最后一行的排序键，直接定位下一段范围。

#### 游标必须与排序完全一致

按 created_at DESC,id DESC 排序，就用 (created_at,id)<(游标时间,游标编号)。id 处理相同时间的并列。游标不是快照；并发删除、排序字段修改仍可能影响跨页视图，导出要另行定义一致性。

### 动手实现

#### 1. 创建列表索引并收集实际计划

继续使用第 08 课的 50,000 条样本。首次建索引一次，运行 ANALYZE。记录节点名称、actual rows、loops、Buffers 和 Execution Time。不要要求每台机器出现完全相同的执行时间。

文件：终端 / 当前课时

````sql
CREATE INDEX notes_created_id_idx ON notes(created_at DESC,id DESC);
ANALYZE notes;
EXPLAIN (ANALYZE,BUFFERS)
SELECT id,title,created_at FROM notes ORDER BY created_at DESC,id DESC LIMIT 20;
````

#### 2. 比较深分页的额外工作

运行相同排序的深 OFFSET。即使最后只返回 20 行，下层扫描节点通常处理约 40,020 行。若计划不同，按真实节点追踪返回与丢弃的行，不强行匹配示例计划。

文件：终端 / 当前课时

````sql
EXPLAIN (ANALYZE,BUFFERS)
SELECT id,title,created_at FROM notes
ORDER BY created_at DESC,id DESC LIMIT 20 OFFSET 40000;
````

#### 3. 从第一页取得下一页游标

以下 psql 查询取第一页最后一行的时间和编号，\gset 保存为 cursor_created、cursor_id。实际 API 应从已返回的第 20 行取值，不再单独 OFFSET 查询。使用 :'cursor_created' 让 psql 正确引用时间文本。

文件：终端 / 当前课时

````sql
SELECT created_at AS cursor_created,id AS cursor_id
FROM notes ORDER BY created_at DESC,id DESC LIMIT 1 OFFSET 19 \gset
SELECT id,title,created_at FROM notes
WHERE (created_at,id) < (:'cursor_created'::timestamptz,:cursor_id)
ORDER BY created_at DESC,id DESC LIMIT 20;
EXPLAIN (ANALYZE,BUFFERS)
SELECT id,title,created_at FROM notes
WHERE (created_at,id) < (:'cursor_created'::timestamptz,:cursor_id)
ORDER BY created_at DESC,id DESC LIMIT 20;
````

#### 4. 在相近深度公平对照

为了教学比较，先花一次 OFFSET 成本拿到深处游标，再测游标查询本身。实际连续翻页只传前页游标。连续运行两次，说明缓存预热影响，不用单次毫秒值证明普遍结论。

文件：终端 / 当前课时

````sql
SELECT created_at AS deep_created,id AS deep_id
FROM notes ORDER BY created_at DESC,id DESC LIMIT 1 OFFSET 39999 \gset
EXPLAIN (ANALYZE,BUFFERS)
SELECT id,title,created_at FROM notes
WHERE (created_at,id) < (:'deep_created'::timestamptz,:deep_id)
ORDER BY created_at DESC,id DESC LIMIT 20;
````

### 验收结果

- 记录浅 OFFSET、深 OFFSET 与深游标的实际扫描量。
- 下一页与前页没有重复编号，排序键与过滤条件完全一致。
- 能区分计划估计值与实际执行值，不把 cost 当成毫秒。

### 生产约束与故障演练

笔记数量增长后，第几百页响应变慢，而第一页正常。

#### 实现决策

- 以实际扫描量解释深分页成本。
- 连续滚动用键集分页，任意跳页另做产品和性能取舍。
- 游标字段选择稳定值，分页不默认承诺全程快照。

#### 隔离环境演练

在 notes_lab 对比相同深度的 OFFSET 与游标查询，记录执行计划。

#### 恢复验收

- 深页工作量明显下降或有证据解释未下降原因。
- 同时间记录不重复。
- 测试记录注明样本规模与缓存状态。

### 常见错误

#### 分页重复或漏掉相同时间的记录

原因：只把 created_at 放进排序或游标，缺少唯一的并列键。

处理：排序和比较同时包含 id，并让排序方向与比较符号一致。

#### 游标查询变慢且出现 Sort

原因：缺少匹配排序的索引，或添加了新筛选条件改变访问方式。

处理：对实际查询重新 EXPLAIN ANALYZE，检查复合索引与筛选顺序，而不是只调 LIMIT。

### 扩展练习

给置顶列表加入键集分页，并说明使用哪个已有索引。

提示：保留 pinned=true，同时添加二元组范围条件。

参考答案：

SELECT id,title,created_at FROM notes WHERE pinned=true AND (created_at,id)<($1,$2) ORDER BY created_at DESC,id DESC LIMIT 20; 这是数据库参数形式示意，psycopg 中使用 %s。第 08 课的 notes_pinned_recent_idx 对该固定谓词和排序有用，仍需实际执行计划验证。

### 技术文档

- [PostgreSQL 16：EXPLAIN](https://www.postgresql.org/docs/16/using-explain.html)
- [PostgreSQL 16：LIMIT 与 OFFSET](https://www.postgresql.org/docs/16/queries-limit.html)
- [PostgreSQL 16：行比较](https://www.postgresql.org/docs/16/functions-comparisons.html)

---

## 10 用 psycopg 连接池管理连接与事务

让并发代码借用有限连接，复现池耗尽，确保异常后连接能够归还。

### 本节目标

- 建立一个可显式开启和关闭的连接池
- 把事务包含在借还连接的作用域内
- 通过超时和统计观察池拥塞

### 必要概念

#### 连接池是有限资源预算

每个连接对应数据库后端资源。池在 min_size 与 max_size 范围复用连接，借不到时等待而非无限新建。进程数乘每进程最大池容量，再加任务、运维与其他服务连接，必须落在数据库预算内。

#### 连接不等于业务并发数

多个请求可以排队等待少量连接。持有连接的时间越长，可服务吞吐越低。一个连接上的查询串行执行；不同线程不要把同一连接当作各自独立事务。同步 psycopg 应运行在同步代码或受控线程中。

#### 借还边界包含提交与回滚

pool.connection() 的上下文负责正常结束时提交、异常退出时回滚并归还连接。应用启动创建池、退出关闭池；业务函数借用连接，不在每次调用时重新创建整个池。异步 API 对应 AsyncConnectionPool，在 API + 数据库模块接入。

### 动手实现

#### 1. 安装池依赖

沿用第 03 课 PG 环境变量。在虚拟环境运行 python -m pip install "psycopg[binary,pool]>=3.2,<4"，然后更新 requirements-lock.txt。psycopg_pool 是单独分发的依赖，安装 psycopg 本体不代表已安装连接池。

文件：终端 / 当前课时

````bash
python -m pip install "psycopg[binary,pool]>=3.2,<4"
python -m pip freeze > requirements-lock.txt
````

#### 2. 运行并发读的完整示例

保存为 db_pool.py 并运行。4 个工作线程共用最大 2 条连接；每次函数调用独立借还。open=False 加 open(wait=True) 让启动阶段就确认连接可用，退出通过 finally 关闭。应用不应把密码或整条连接信息打印到日志。

文件：db_pool.py

````python
from concurrent.futures import ThreadPoolExecutor
from psycopg_pool import ConnectionPool

pool = ConnectionPool("", min_size=1, max_size=2, timeout=2, open=False,
                      kwargs={"application_name": "notes-service-db-lab"})

def count_notes(_):
    with pool.connection() as conn:
        with conn.cursor() as cur:
            cur.execute("SET LOCAL statement_timeout='2s'")
            cur.execute("SELECT count(*) FROM notes")
            return cur.fetchone()[0]

try:
    pool.open(wait=True, timeout=10)
    with ThreadPoolExecutor(max_workers=4) as workers:
        print(list(workers.map(count_notes, range(8))))
    print(pool.get_stats())
finally:
    pool.close()
````

#### 3. 验证异常后事务回滚

保存为 db_pool_rollback.py。故意抛出 Python 异常，异常必须离开 connection 上下文后再捕获，否则上下文可能把事务当作正常结束而提交。第二次借用连接后查询结果应为 0。

文件：db_pool_rollback.py

````python
from psycopg_pool import ConnectionPool

with ConnectionPool("", min_size=1, max_size=1) as pool:
    pool.wait(timeout=10)
    note_id = None
    try:
        with pool.connection() as conn:
            note_id = conn.execute(
                "INSERT INTO notes(title) VALUES (%s) RETURNING id",
                ("db10-rollback",),
            ).fetchone()[0]
            raise RuntimeError("simulated failure after insert")
    except RuntimeError:
        pass
    with pool.connection() as conn:
        count = conn.execute("SELECT count(*) FROM notes WHERE id=%s", (note_id,)).fetchone()[0]
        assert count == 0
        print("rolled back", count)
````

#### 4. 可控复现池耗尽

保存为 db_pool_timeout.py，只在本机练习运行。max_size=1 时外层已经占用唯一连接，内层借用 0.2 秒后超时；捕获异常后退出外层归还连接，再借用应成功。生产代码应消除嵌套借还并缩短持有时间，不能只扩大池。

文件：db_pool_timeout.py

````python
from psycopg_pool import ConnectionPool, PoolTimeout

with ConnectionPool("", min_size=1, max_size=1) as pool:
    pool.wait(timeout=10)
    with pool.connection():
        try:
            with pool.connection(timeout=0.2):
                pass
        except PoolTimeout:
            print("expected pool timeout")
    with pool.connection() as conn:
        print("recovered", conn.execute("SELECT 1").fetchone()[0])
    print(pool.get_stats())
````

### 验收结果

- 并发读取成功，连接数量受 max_size=2 限制。
- 异常插入的笔记在下一次借用中查不到。
- 池耗尽产生 PoolTimeout，归还后 SELECT 1 恢复。

### 生产约束与故障演练

API 与任务进程同时访问 PostgreSQL，突发请求不应耗尽全部数据库连接。

#### 实现决策

- 按进程数计算总连接预算。
- 连接等待和 SQL 执行分别设时限。
- 业务异常离开事务边界后再转为响应。

#### 隔离环境演练

在 notes_lab 用单连接池制造超时和事务异常，再确认连接能归还复用。

#### 恢复验收

- 没有连接泄漏。
- 等待有时限与可记录指标。
- 连接池在应用生命周期内创建和关闭。

### 常见错误

#### 高并发时不断出现 PoolTimeout

原因：连接持有时间过长、嵌套借用或总并发超过容量。

处理：检查等待数和事务时长，先缩短查询与持有范围，再按数据库预算调整池。

#### async 路由中数据库调用阻塞其他请求

原因：在事件循环中直接执行同步 psycopg 查询。

处理：用 AsyncConnectionPool 与 await，或在受控线程执行同步代码；不要只把函数名改成 async。

### 扩展练习

4 个 API 进程各 max_size=8，两个任务进程各 max_size=5，预留运维 10 条连接，至少需要规划多少连接？

提示：进程之间不共享一个 Python 连接池。

参考答案：

仅这些进程峰值预算是 4×8+2×5+10=52 条，还应核查迁移、监控、其他服务和数据库保留连接。52 是这组假设下的预算，不是直接把 max_connections 设为 52 的建议。

### 技术文档

- [Psycopg 3：连接池](https://www.psycopg.org/psycopg3/docs/advanced/pool.html)
- [Psycopg 3：池 API](https://www.psycopg.org/psycopg3/docs/api/pool.html)
- [Psycopg 3：并发与异步](https://www.psycopg.org/psycopg3/docs/advanced/async.html)

---

## 11 拆分数据库权限并管理连接配置

让运行时账号只做业务读写，限制危险语句，并检查日志和连接配置中的敏感信息。

### 本节目标

- 区分迁移账号与 API 运行账号
- 配置现有对象和未来对象的授权
- 验证最小权限以及超时配置

### 必要概念

#### 权限应按工作职责分配

app_user 保留为练习的对象所有者和迁移身份；新增 notes_runtime 供 API 运行。运行时需要 SELECT、INSERT、UPDATE、DELETE，不需要 CREATE ROLE、DROP TABLE 或 ALTER TABLE。数据库 CONNECT、schema USAGE、表权限是不同层。

#### 未来对象不会自动继承手工授权

GRANT ON ALL TABLES 只覆盖当时存在的表。ALTER DEFAULT PRIVILEGES 针对特定创建者未来创建的对象生效，必须由正确身份设置。生产服务若只需要部分表，应显式授权，不能照搬练习的全部业务表授权。

#### 连接加密与秘密存储

生产连接优先使用 TLS 且校验证书和主机名，例如 sslmode=verify-full 配合可信根证书。本机官方镜像没有自动配置可校验 TLS，不应伪称本课容器已提供 TLS。密码通过环境或秘密文件注入，SQL 参数和业务正文也可能包含敏感数据。

### 动手实现

#### 1. 管理身份创建运行角色

使用第 01 课 docker exec -it notes-postgres psql -U postgres -d notes_lab 进入管理连接。密码通过 \password 交互设置。PUBLIC 是所有角色的隐式集合，撤销公开连接只限此独立练习库，不在共享线上库直接执行。

文件：终端 / 当前课时

````sql
CREATE ROLE notes_runtime LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;
\password notes_runtime
REVOKE CONNECT ON DATABASE notes_lab FROM PUBLIC;
GRANT CONNECT ON DATABASE notes_lab TO app_user,notes_runtime;
ALTER ROLE notes_runtime IN DATABASE notes_lab SET statement_timeout='3s';
ALTER ROLE notes_runtime IN DATABASE notes_lab SET lock_timeout='1s';
ALTER ROLE notes_runtime IN DATABASE notes_lab SET idle_in_transaction_session_timeout='30s';
````

#### 2. 所有者身份授权现有与未来对象

重新用 app_user 连接 notes_lab 执行。当前课程所有业务表都位于 public，生产可改成独立 schema 并按表授权。identity 之外若使用显式序列默认值，需要相应 sequence 权限；本例将业务序列使用权限一并授予。

文件：终端 / 当前课时

````sql
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO notes_runtime;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO notes_runtime;
GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA public TO notes_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE app_user IN SCHEMA public
GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO notes_runtime;
ALTER DEFAULT PRIVILEGES FOR ROLE app_user IN SCHEMA public
GRANT USAGE,SELECT ON SEQUENCES TO notes_runtime;
````

#### 3. 运行身份验证允许与拒绝操作

通过 docker exec -it notes-postgres psql -h 127.0.0.1 -U notes_runtime -d notes_lab -W 连接。插入成功后删除本次编号；尝试 ALTER TABLE 会因不是所有者失败，不应产生新列。

文件：终端 / 当前课时

````sql
SELECT current_user;
INSERT INTO notes(title) VALUES ('db11-runtime') RETURNING id AS runtime_id \gset
DELETE FROM notes WHERE id=:runtime_id;
ALTER TABLE notes ADD COLUMN should_not_exist text;
-- ERROR: must be owner of table notes
SHOW statement_timeout;
SHOW lock_timeout;
SELECT pg_sleep(4);
-- 约 3 秒取消，SQLSTATE 57014
````

#### 4. 验证未来表权限并调整应用配置

以 app_user 创建临时验收表，再以 notes_runtime SELECT 应成功。验收后 app_user 删除该专用表。把应用 PGUSER 改为 notes_runtime 并注入对应密码，迁移工具继续单独使用 app_user。检查日志只记录操作名、耗时和 SQLSTATE，不打印密码或完整正文。

文件：终端 / 当前课时

````sql
-- app_user
CREATE TABLE permission_probe(id integer PRIMARY KEY);
INSERT INTO permission_probe VALUES (1);
-- notes_runtime：应该读到 1
SELECT id FROM permission_probe;
-- app_user：验收结束清理本课专用表
DROP TABLE permission_probe;
````

### 验收结果

- notes_runtime 可以执行业务 CRUD，不能修改表结构。
- 新连接显示 3 秒语句超时和 1 秒锁超时。
- app_user 创建的未来表遵循默认权限，密码没有写进仓库和示例日志。

### 生产约束与故障演练

API 连接配置泄漏或代码出现错误时，需要限制其能影响的数据库对象。

#### 实现决策

- 运行角色与迁移角色使用不同秘密。
- 超时配置需重连生效并实测。
- 审计日志避免包含原始敏感正文。

#### 隔离环境演练

在 notes_lab 用运行角色尝试 ALTER TABLE 并执行超时查询，验证拒绝和恢复。

#### 恢复验收

- 运行角色不是所有者或超级用户。
- 权限随未来迁移保持可用。
- 生产 TLS、凭据注入和轮换方式有明确配置责任。

### 常见错误

#### 现有表能访问，新迁移建的表报 permission denied

原因：只给现有表 GRANT，或默认权限针对了错误创建角色。

处理：检查实际迁移角色，给该角色设置 ALTER DEFAULT PRIVILEGES，并补授权已经创建的对象。

#### sslmode=require 就被当成完成服务器身份校验

原因：只要求加密不等于完整证书与主机名校验。

处理：生产按基础设施证书配置 verify-full 与根证书；本机未配 TLS 的容器使用明确的开发配置。

### 扩展练习

给只读报表账号设计最小权限，说明为什么不应复用 app_user。

提示：需要 CONNECT、schema USAGE 和必要表的 SELECT；不授予写入或对象所有权。

参考答案：

创建独立 LOGIN 角色，授予 notes_lab 的 CONNECT、public 的 USAGE、指定业务表的 SELECT。为其设置合适超时与只读事务默认值作为附加限制；最终权限仍由 GRANT 决定。app_user 是对象所有者，权限范围远超报表需求。

### 技术文档

- [PostgreSQL 16：权限](https://www.postgresql.org/docs/16/ddl-priv.html)
- [PostgreSQL 16：默认权限](https://www.postgresql.org/docs/16/sql-alterdefaultprivileges.html)
- [PostgreSQL 16：SSL 客户端验证](https://www.postgresql.org/docs/16/libpq-ssl.html)
- [PostgreSQL 16：客户端默认设置](https://www.postgresql.org/docs/16/runtime-config-client.html)

---

## 12 备份恢复与可回退的结构变更

导出练习库并恢复到另一个数据库，通过数据核对和迁移演练完成生产验收。

### 本节目标

- 用 pg_dump 创建逻辑备份并校验文件
- 在独立恢复库验证结构、数据和序列
- 使用扩展、回填、验证的步骤变更表结构

### 必要概念

#### 备份文件可生成不代表能恢复

逻辑备份包含数据库对象与数据，恢复需要兼容的工具、角色与目标环境。单库 pg_dump 不包含整个实例的角色定义，也不能独自提供任意时间点恢复；严格 RPO 需要基础备份与 WAL 归档等方案。

#### RPO 和 RTO 是验收条件

RPO 是可接受的数据丢失时间范围，RTO 是恢复服务需要的时间上限。备份频率、保留、异地副本与恢复速度必须与业务目标一致。本课实际测量恢复步骤，而不把本机几秒恢复推断为线上保证。

#### 结构变更考虑新旧代码并存

增加可空列通常比直接删除或改名更容易兼容。先加字段，再部署兼容代码，分批回填并验证，最后收紧约束。回滚代码不意味着可以随意 DROP 已有用户数据的新列，破坏性变更需要单独恢复设计。

### 动手实现

#### 1. 生成备份与源库验收数据

以下 Linux Bash 在 notes-service 执行，需要 PostgreSQL 16 客户端 pg_dump、psql、pg_restore；沿用 PGHOST/PGPORT/PGDATABASE，PGUSER 使用 app_user 且注入其密码。备份目录限制权限。命令失败必须停止，不继续上传空文件。

文件：终端 / 当前课时

````bash
set -e
mkdir -p backups
chmod 700 backups
pg_dump --version
pg_dump -Fc --no-owner --no-acl -f backups/notes_lab.dump
sha256sum backups/notes_lab.dump > backups/notes_lab.dump.sha256
pg_restore --list backups/notes_lab.dump > backups/notes_lab.contents.txt
psql -At -v ON_ERROR_STOP=1 -c "SELECT count(*),min(id),max(id),count(*) FILTER(WHERE pinned) FROM notes" > backups/source-notes.txt
sha256sum -c backups/notes_lab.dump.sha256
````

#### 2. 创建隔离恢复目标并恢复

管理连接执行 CREATE DATABASE notes_restore OWNER app_user;，只在本机首次创建该专用恢复库。不要向原 notes_lab 恢复。下面 --no-owner --no-acl 使本课恢复对象由 app_user 持有，权限在恢复后按第 11 课脚本重建；这不是自动恢复所有生产角色。

文件：终端 / 当前课时

````bash
# Linux Bash，PGUSER=app_user，使用相应密码
pg_restore --exit-on-error --single-transaction --no-owner --no-acl \
  -h 127.0.0.1 -p 5433 -U app_user -d notes_restore backups/notes_lab.dump
psql -d notes_restore -At -v ON_ERROR_STOP=1 \
  -c "SELECT count(*),min(id),max(id),count(*) FILTER(WHERE pinned) FROM notes" > backups/restored-notes.txt
diff backups/source-notes.txt backups/restored-notes.txt
````

#### 3. 核查恢复后的关系与新写入

使用 app_user 连 notes_restore 执行。反关联查询均应为 0。插入能生成合法新编号，ROLLBACK 避免保留验收笔记；序列可能前进一位，这是正常行为。若源库备份期间持续写入，外部另查的 count 不一定对应 pg_dump 快照；本课在停止练习写入后对照。

文件：终端 / 当前课时

````sql
SELECT count(*) FROM note_tags nt LEFT JOIN notes n ON n.id=nt.note_id WHERE n.id IS NULL;
SELECT count(*) FROM note_tags nt LEFT JOIN tags t ON t.id=nt.tag_id WHERE t.id IS NULL;
BEGIN;
INSERT INTO notes(title) VALUES ('db12-restore-probe') RETURNING id,title;
ROLLBACK;
\d notes
\d note_tags
````

#### 4. 在练习源库做一次兼容扩展迁移

重新连接 notes_lab / app_user。创建 archived_at 可空列表示未归档；保存为 sql/003_archived.sql，使用 psql -v ON_ERROR_STOP=1 -f sql/003_archived.sql 执行一次。短 lock_timeout 保证拿不到结构锁时尽快失败，再由人检查阻塞后择机重试。它不能保证 DDL 永远无阻塞。

文件：sql/003_archived.sql

````sql
BEGIN;
SET LOCAL lock_timeout='1s';
SET LOCAL statement_timeout='5s';
ALTER TABLE notes ADD COLUMN archived_at timestamptz;
COMMIT;
````

#### 5. 验证新旧访问兼容并记录恢复方案

旧查询只选择明确列名，不受新列影响。新查询把 archived_at IS NULL 当作活动笔记。故障演练是在恢复库模拟错误更新再从备份另建恢复目标，不覆盖源库。本课不实际给出删除全表语句；验收恢复耗时、权限重建与业务请求都必须列入恢复记录。

文件：终端 / 当前课时

````sql
SELECT id,title,pinned FROM notes ORDER BY id DESC LIMIT 3;
SELECT id,title FROM notes WHERE archived_at IS NULL ORDER BY id DESC LIMIT 3;
SELECT column_name,is_nullable,data_type
FROM information_schema.columns
WHERE table_name='notes' AND column_name='archived_at';
-- is_nullable=YES，data_type=timestamp with time zone
````

### 验收结果

- 备份校验和正确，pg_restore 清单包含业务表、约束和数据。
- 独立恢复库行数与静止源库一致，外键无孤儿，新插入成功。
- 新增字段后旧查询与新查询都可运行，迁移失败有停止和重试策略。

### 生产约束与故障演练

发布结构变更之前，需要证明数据库可恢复，旧版本服务仍能短暂并存。

#### 实现决策

- 恢复目标与源库严格分开。
- 备份验收覆盖数据、约束、权限与新写入。
- 新增字段先兼容，再部署和回填；迁移文件纳入版本管理。

#### 隔离环境演练

在独立 notes_restore 检查备份恢复，然后在 notes_lab 用短锁超时演练新增可空字段；所有操作保持目标库可辨认。

#### 恢复验收

- 记录实际恢复耗时与备份时间。
- 恢复库能完成业务 CRUD。
- 迁移前后访问兼容，生产 RPO/RTO 不由本机测试推定。

### 常见错误

#### 备份命令成功，但恢复时缺角色或权限

原因：把单库逻辑备份当成完整实例备份，未规划角色与授权。

处理：在恢复流程中显式重建角色和授权，或由受控配置管理维护；本课使用 no-owner/no-acl 后重新授权。

#### 上线加字段一直等待，API 也开始变慢

原因：DDL 正在等待强锁，可能与长事务形成阻塞队列。

处理：用短 lock_timeout 退出，查等待链并缩短长事务；在明确窗口重试，不无限等锁。

#### 恢复行数不等于备份后源库行数

原因：源库继续写入，两个统计不属于同一快照。

处理：练习对比时暂停写入；生产验收使用与备份快照一致的方法或业务不变量，不把后续变化误判为备份损坏。

### 扩展练习

为“每日备份、允许最多丢失 15 分钟数据”的需求判断现有方案是否合格，并写出补充项。

提示：先将备份间隔与 RPO 对照，再考虑恢复演练。

参考答案：

单独每日 pg_dump 无法满足 15 分钟 RPO。需要能够持续归档的 WAL 与基础备份或受管数据库时间点恢复，并验证归档延迟、保留期、异地副本、恢复步骤和监控；按目标时间点实际演练并测量 RTO。

### 技术文档

- [PostgreSQL 16：pg_dump](https://www.postgresql.org/docs/16/app-pgdump.html)
- [PostgreSQL 16：pg_restore](https://www.postgresql.org/docs/16/app-pgrestore.html)
- [PostgreSQL 16：持续归档与时间点恢复](https://www.postgresql.org/docs/16/continuous-archiving.html)
- [PostgreSQL 16：ALTER TABLE](https://www.postgresql.org/docs/16/sql-altertable.html)