pg_dump 的数据恢复为何会“自己弄坏自己”

2026-08-28 38 预计阅读时间: 1 分钟
来源: postgr.es AI 摘要 Original link

Disclaimer: This article is an AI-assisted summary. Read it together with the original source when precision matters. The summary may omit context, version differences, or edge cases and is not official documentation.

预计阅读时间：12 分钟

把 PostgreSQL 的 data-only dump 恢复到一个刚创建、完全空白的同结构数据库，看起来应该是最安全的场景：没有旧数据，不会发生主键冲突，外键也有机会按依赖顺序加载。然而，恢复仍可能在某张表的 COPY 中途失败。

一个容易被忽略的原因是：pg_dump 会在数据真正开始加载前，把当前会话的 search_path 固定为空字符串。只要触发器函数内部使用了未限定的表名，触发器就可能找不到它本来存在的表。

触发器看得到，恢复会话却看不到

下面是一个典型的最小结构：

CREATE TABLE authors (
    id bigserial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE books (
    id bigserial PRIMARY KEY,
    author_id bigint NOT NULL REFERENCES authors(id),
    title text NOT NULL
);

CREATE TABLE book_audit (
    id bigserial PRIMARY KEY,
    book_id bigint,
    note text,
    logged_at timestamptz DEFAULT now()
);

CREATE FUNCTION log_book() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO book_audit(book_id, note)
    VALUES (NEW.id, 'insert seen');
    RETURN NEW;
END
$$;

CREATE TRIGGER trg_book_audit
AFTER INSERT ON books
FOR EACH ROW
EXECUTE FUNCTION log_book();

在源数据库中插入 5 本书时，触发器会额外写入 5 条 book_audit。pg_dump --data-only 只看到最终的表内容，并不会记录这些行是用户直接插入的，还是触发器产生的。

问题出在 dump 文件开头类似下面这一行：

SELECT pg_catalog.set_config('search_path', '', false);

随后加载 books 时，COPY 会触发 log_book()。函数里的代码是：

INSERT INTO book_audit(...)

它没有写成 public.book_audit，而当前会话又没有可搜索的 schema，于是得到：

ERROR: relation "book_audit" does not exist

这并不意味着目标数据库真的缺少 book_audit。它只是无法通过当前的名称解析规则找到这张表。

为什么 books 最终一行也没有

COPY 是一个语句。触发器在第一行数据上报错时，整个 COPY books ... FROM stdin 都会回滚，而不是只跳过第一行。因此恢复后可能看到：

  • authors 成功加载 3 行；
  • book_audit 保留 dump 中原本的 5 行；
  • books 变成 0 行；
  • 没有触发器的其他表仍然继续加载。

这会制造一个非常危险的“半成功”状态：恢复命令可能继续执行，但目标数据库既不是源数据库，也没有完全保持原样。

第一处修复：让触发器函数使用稳定的对象名

最值得优先修复的是触发器函数本身，而不是每次恢复都绕过它：

CREATE OR REPLACE FUNCTION public.log_book() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO public.book_audit(book_id, note)
    VALUES (NEW.id, 'insert seen');
    RETURN NEW;
END
$$;

函数名、表名、序列名等持久化代码中的对象引用，最好明确写出 schema。这样可以避免依赖调用会话的 search_path，也能降低应用连接池、迁移脚本和管理工具之间的环境差异。

不过，修复 schema 限定后，另一个问题会浮现：dump 中已经包含触发器写入的审计行，而恢复 books 时触发器又会再写一次。此时可能遇到：

ERROR: duplicate key value violates unique constraint "book_audit_pkey"

这正是常见的“恢复时触发器重复执行”问题。它和前面的 relation does not exist 是两个不同阶段的故障：前者是名称解析失败，后者是触发器成功执行后的数据冲突。

data-only dump 到底包含什么

一个数据专用的纯 SQL dump 通常包含三类内容：

  1. 会话级 SET 语句，包括空的 search_path；
  2. 每张表一个 COPY ... FROM stdin 数据块；
  3. 文件末尾针对序列的 setval 语句。

数据表的加载顺序不是简单的字母顺序。PostgreSQL 会根据外键等依赖关系安排顺序。例如：

CREATE TABLE zz_parents (
    id bigserial PRIMARY KEY,
    name text
);

CREATE TABLE aa_children (
    id bigserial PRIMARY KEY,
    parent_id bigint REFERENCES zz_parents(id)
);

尽管 aa_children 的名字排在前面，恢复时通常会先加载 zz_parents，再加载 aa_children。但序列的 setval 往往按照名称顺序输出，因为序列之间通常没有可以用于依赖排序的关系。

序列语句的形式大致如下：

SELECT pg_catalog.setval('public.books_id_seq', 5, true);

它不会检查对应表是否真的成功加载。如果 books 的 COPY 因触发器失败而回滚，文件末尾仍可能把 books_id_seq 设置到 5。此时表是空的，但下一次默认插入会拿到 ID 6。

可以这样检查并修正：

SELECT count(*) AS rows_in_books,
       max(id) AS max_id
FROM public.books;

SELECT last_value, is_called
FROM public.books_id_seq;

SELECT setval(
    'public.books_id_seq',
    COALESCE((SELECT max(id) FROM public.books), 1),
    COALESCE((SELECT max(id) FROM public.books), 0) > 0
);

生产环境中应根据实际 identity/serial 定义确认 setval 的第三个参数，不能机械复制示例。

三种恢复杠杆，以及它们的代价

1. --disable-triggers

如果使用 custom archive，可以在恢复时临时关闭触发器：

pg_dump -Fc --data-only \
  --file=/tmp/data.dump \
  source_db

pg_restore --data-only \
  --disable-triggers \
  --dbname=target_db \
  /tmp/data.dump

对于纯 SQL 文件，也可以在导出时加入：

pg_dump --data-only --disable-triggers source_db > /tmp/data.sql

但这要求恢复角色拥有表的所有权，通常还需要超级用户权限。仅有 INSERT 权限并不足以执行：

ALTER TABLE ... DISABLE TRIGGER ALL;

更重要的是，ALL 不只会关闭业务触发器，也会关闭外键背后的内部约束触发器。它可以让不满足外键关系的数据进入数据库，因此使用前必须确认 dump 本身是一致的，并在恢复后主动验证约束。

2. 延迟约束

SET CONSTRAINTS ALL DEFERRED 解决的是加载顺序问题，不会关闭普通触发器：

BEGIN;
SET CONSTRAINTS ALL DEFERRED;

-- 这里可以加载依赖表顺序相反的数据
-- COPY public.books ...
-- COPY public.authors ...

COMMIT;

这个方案只有在约束被声明为 DEFERRABLE 时才有意义，而且 AFTER INSERT 触发器仍会在语句结束时执行。如果触发器引用了未限定的 book_audit，空 search_path 的问题仍然存在；即使名称限定正确，审计行重复问题也仍然存在。

将整个恢复包在一个事务中还有另一面：任何一个触发器或约束错误，都可能回滚此前已经成功加载的所有表。

3. session_replication_role = replica

这是会话级开关，不是 pg_dump 命令行选项：

SET session_replication_role = replica;

-- 执行数据加载

SET session_replication_role = origin;

它可以关闭触发器和约束的执行，但通常需要超级用户或显式的参数设置权限：

ERROR: permission denied to set parameter "session_replication_role"

这个选项的破坏范围很大。除非恢复流程能够在加载后执行完整的数据一致性检查，否则不应把它当作普通的开发环境快捷键。

不要让 CI 把失败的恢复判定为成功

纯 SQL 文件由 psql -f 执行时，如果没有打开错误停止选项，遇到错误后可能继续执行，甚至以退出码 0 返回：

psql "$DATABASE_URL" \
  -f /tmp/data.sql

更适合自动化流程的写法是：

set -Eeuo pipefail

psql "$DATABASE_URL" \
  -v ON_ERROR_STOP=1 \
  --single-transaction \
  -f /tmp/data.sql

这里有两个独立决定：

  • ON_ERROR_STOP=1：遇到第一处 SQL 错误就停止，并返回失败状态；
  • --single-transaction：让整个脚本作为一个事务执行，失败时回滚已经加载的内容。

如果业务需要“尽可能加载未出错的表”，可以不使用 --single-transaction，但必须在恢复后逐表核对行数、约束和序列。对于必须原子替换的环境，宁可让恢复整体失败，也不要留下半成品。

custom archive 通常能更清晰地报告错误：

pg_restore --data-only \
  --dbname="$DATABASE_URL" \
  --disable-triggers \
  /tmp/data.dump

pg_restore 会返回非零退出码，并报告被忽略的错误数量；它还允许在已经生成 archive 之后再选择 --disable-triggers。纯 SQL 文件则不同：如果导出时没有写入禁用触发器的语句，恢复时无法凭空补上同等的结构化控制。

恢复前的检查清单

  • 触发器函数内部的表名是否全部 schema-qualified？
  • dump 是纯 SQL 还是 custom archive？是否需要在恢复时选择关闭触发器？
  • 恢复角色是否拥有目标表，而不只是拥有 INSERT 权限？
  • 外键是否存在循环依赖，是否声明为 DEFERRABLE？
  • 是否需要全量原子恢复？如果需要，使用 --single-transaction。
  • CI 是否设置了 ON_ERROR_STOP=1，并检查了 pg_restore 的退出码？
  • 恢复后是否核对每张表的行数、主键冲突、外键完整性和序列位置？
  • 是否理解 --disable-triggers 和 session_replication_role 会暂时绕过约束？

最稳妥的顺序通常是：先修复触发器函数中的 schema 引用，再决定是否在数据恢复期间暂停触发器；选择 custom archive 以保留恢复时的操作空间；最后用严格的退出码和恢复后校验阻止“命令成功、数据失败”的假象。

# AI # Go # PostgreSQL # 云原生 # 可观测性 # 后端 # 安全 # 数据库

相关推荐