PostgreSQL 逻辑迁移：pg_dump、pg_restore 与跨 major 校验
一套订单库从旧 major 迁到新 major，pg_restore 最后一行显示完成，表也都在。应用切过去后却连续出现三类问题：定时任务账号没有 sequence 权限，空间查询因为目标机没安装对应 extension 而失败，客户列表的唯一索引在新系统排序库下出现 collation version mismatch。更隐蔽的是，恢复日志中早就出现过 role does not exist 和 extension 创建失败，只是脚本继续执行，末尾退出状态又被流水线吞掉了。
pg_dump 生成的是数据库内对象与数据的逻辑描述，不是整个 PostgreSQL cluster 的镜像。角色和 tablespace 属于 cluster 级 global objects，extension 还依赖目标操作系统中的二进制与控制文件，collation 又依赖 libc 或 ICU。跨 major 迁移真正要交付的是：archive 中有什么、目标环境能否重建依赖、每个对象归谁、数据与 sequence 是否一致、排序语义是否改变，以及任何失败是否让流水线停止。
从“一个备份文件”转向对象依赖图
PostgreSQL 逻辑迁移可以分成四层。cluster 层有 role、role membership、tablespace 与参数授权；database 层有数据库属性和默认 collation；schema 层有 extension、type、function、table、sequence、view、constraint、index 与 ACL；data 层有 table data、large object 和 sequence value。pg_dump 一次只处理一个 database，pg_dumpall --globals-only 才负责常见 global objects。
custom 或 directory archive 不是一串固定顺序 SQL。它包含目录表 TOC，记录对象、section、owner 和依赖；pg_restore 根据这些依赖调度 pre-data、data、post-data。并行恢复能让多个无依赖工作同时进行，但无法越过依赖关系，也不会替目标安装缺失 extension。
这个图也解释了为什么“表数据导入成功”仍可能不可用：extension 是函数与类型的前置依赖，owner 决定后续 DDL 与默认权限，sequence value 决定下一次插入是否撞主键，post-data 中的唯一约束和索引才会暴露某些坏数据。逻辑 dump 适合迁移、升级和对象级恢复，但长期备份介质、WAL 归档和 PITR 仍由备份与恢复承担；一次 archive 不能替代持续保护。
先锁定新客户端、源服务端与目标服务端
示例以当前正式主版本 PostgreSQL 18 客户端和目标端为基线，开发版 19 不作为生产迁移基线；源端可以是官方当前 dump 工具仍支持读取的较旧 major。官方 升级手册建议跨 major dump/reload 使用较新版本的 pg_dump 与 pg_dumpall，因为新客户端包含面向新目标语法与缺陷修复；当前发布线的 dump 程序可读取服务端 9.2 及以后版本。反方向不成立：pg_dump 不能从比自身 major 更新的服务端可靠导出，会直接拒绝不支持的 server version。
pg_dump --version
pg_restore --version
pg_dumpall --version
psql --version
psql "host=localhost port=5432 dbname=postgres user=migration_reader sslmode=require" \
-X -v ON_ERROR_STOP=1 -c "SELECT version(), current_setting('server_version_num'), pg_encoding_to_char(encoding), datcollate, datctype FROM pg_database WHERE datname=current_database();"
psql "host=localhost port=5433 dbname=postgres user=migration_loader sslmode=require" \
-X -v ON_ERROR_STOP=1 -c "SELECT version(), current_setting('server_version_num'), pg_encoding_to_char(encoding), datcollate, datctype FROM pg_database WHERE datname=current_database();"-X 不读取个人 psqlrc，避免本机宏、变量或输出设置污染迁移脚本；ON_ERROR_STOP=1 让 SQL 遇错返回非零。客户端程序使用同一个发行线更容易解释 archive feature 与恢复行为，但服务端 minor 仍应安装该 major 的当前修复版本。PostgreSQL 的 版本策略说明 major 之间数据目录不保持兼容，跨 major 要使用 dump/reload 或 pg_upgrade；不能把旧 PGDATA 直接挂给新二进制。
PostgreSQL 使用宽松的 PostgreSQL License，但 extension、操作系统库、容器基础镜像和云服务条款各自独立。尤其含 C 代码的 extension 不能仅因核心数据库许可宽松就忽略其再分发义务；固定工具镜像时应同时保存核心包、extension 包、来源仓库、许可证清单和摘要。
尚未安装时，从 PostgreSQL 官方下载入口 选择 Linux、macOS、Windows、BSD 或容器路径；Linux 页面会继续指向 PGDG 的 Apt/Yum 仓库，Windows 与 macOS 页面提供官方认可的安装包入口。安装客户端后先运行上面的四个 --version，并用 Get-Command pg_dump（PowerShell）或 command -v pg_dump（POSIX shell）确认实际路径。容器中执行时要记录镜像 digest，把 .pgpass 只读挂载，确认宿主和容器时区、CA、DNS 与 archive 目录权限。不要让临时工具镜像顺便启动一个未知参数的目标数据库。
用合成订单库建立完整正向实验
合成库包含 schema、sequence、外键、函数、large object 引用和 extension。pgcrypto 用于验证 extension 依赖，large object 用来提醒读者：只看普通表并不完整。示例要求本机实验 cluster 已提供 pgcrypto，生产目标必须提前从受信仓库安装与源端兼容的 extension 包。
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_runtime LOGIN PASSWORD '<APP_PASSWORD>';
GRANT app_owner TO app_runtime;
CREATE DATABASE migration_lab OWNER app_owner
ENCODING 'UTF8' TEMPLATE template0;
\connect migration_lab
CREATE EXTENSION pgcrypto;
CREATE SCHEMA app AUTHORIZATION app_owner;
SET ROLE app_owner;
CREATE TABLE app.customer (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
external_id uuid NOT NULL DEFAULT gen_random_uuid(),
migration_token bytea NOT NULL DEFAULT gen_random_bytes(16),
display_name text NOT NULL,
display_name_sha256 bytea
GENERATED ALWAYS AS (digest(display_name, 'sha256')) STORED,
credit numeric(18,2) NOT NULL CHECK (credit >= 0),
UNIQUE (external_id)
);
CREATE TABLE app.purchase_order (
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES app.customer(id),
order_no text NOT NULL UNIQUE,
amount numeric(18,2) NOT NULL,
payload jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE FUNCTION app.customer_credit(p_customer_id bigint)
RETURNS numeric LANGUAGE sql STABLE
RETURN (SELECT credit FROM app.customer WHERE id=p_customer_id);
CREATE TABLE app.customer_document (
customer_id bigint PRIMARY KEY REFERENCES app.customer(id),
document_loid oid NOT NULL UNIQUE
);
INSERT INTO app.customer(display_name, credit)
VALUES ('示例客户甲', 100.00), ('Emoji🙂', 0.00);
INSERT INTO app.purchase_order(customer_id, order_no, amount)
VALUES (1, 'ORDER-A', 18.50), (1, 'ORDER-B', 20.00);
WITH created AS (
SELECT lo_from_bytea(
0,
convert_to('customer-1-contract', 'UTF8')
) AS loid
)
INSERT INTO app.customer_document(customer_id, document_loid)
SELECT 1, loid FROM created;
RESET ROLE;迁移读取账号不应是应用账号。pg_dump 运行时不会绕过权限：账号必须能连接数据库、使用 schema 并读取将导出的所有表、sequence 与对象定义；启用 row security 时还要明确是否允许只导出可见行，通常完整迁移应由受控账号取得所有数据并保留审计。目标加载账号需要创建 database/schema/object 或切换到预建 owner 的能力，但不必长期保留 superuser。
CREATE ROLE migration_reader LOGIN PASSWORD '<SOURCE_PASSWORD>';
GRANT CONNECT ON DATABASE migration_lab TO migration_reader;
GRANT USAGE ON SCHEMA app TO migration_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO migration_reader;
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA app TO migration_reader;
-- pg_dump 读取 large object 还需要对象级 SELECT；由 psql 执行 \gexec。
SELECT format(
'GRANT SELECT ON LARGE OBJECT %s TO migration_reader', oid
)
FROM pg_largeobject_metadata
WHERE lomowner = 'app_owner'::regrole
\gexec
CREATE ROLE migration_loader LOGIN PASSWORD '<TARGET_PASSWORD>' CREATEDB;
GRANT app_owner TO migration_loader;自动化用 .pgpass、passfile 或密码代理，文件权限为 owner-only；不要设置会被进程、崩溃转储或 CI 日志复制的明文 PGPASSWORD。证书校验优先 sslmode=verify-full 并配置受信 CA；本机实验可用 require，但不能把“加密但不验证身份”直接复制到跨网络生产迁移。
custom 与 directory 格式怎么选
plain 格式是 SQL 文本，只能用 psql 恢复，便于审阅和管道传输，却失去 pg_restore 的 TOC 选择与原生并行恢复。custom 格式 -Fc 是单个 archive，默认压缩、可用 pg_restore 重排和过滤，适合大多数单库迁移。directory 格式 -Fd 把 TOC 与数据项拆成目录文件，支持 pg_dump -j 并行导出，也支持 pg_restore -j 并行恢复，但目录包含大量文件，需要原子传输、完整性清单和 inode/对象数预算。tar 格式可被 pg_restore 读取，但不支持并行恢复。
官方 pg_dump 手册指出 custom 与 directory 是最灵活格式。先做 custom 正向实验：
mkdir -p ./artifacts
pg_dump "host=localhost port=5432 dbname=migration_lab user=migration_reader sslmode=require" \
-Fc --verbose --file=./artifacts/migration_lab.dump \
2> ./artifacts/migration_lab.dump.stderr
pg_restore --list ./artifacts/migration_lab.dump \
> ./artifacts/migration_lab.toc
sha256sum ./artifacts/migration_lab.dump \
> ./artifacts/migration_lab.dump.sha256pg_dump 会建立一致性快照，普通读写可继续；它不会阻塞常规 DML，但需要对表取得 ACCESS SHARE 锁，因此会与请求 ACCESS EXCLUSIVE 的 DDL 竞争。迁移窗口内若有人排队执行 ALTER TABLE，后续普通查询也可能排在锁队列之后，形成意外阻塞。可设置合适的 --lock-wait-timeout 让 dump 在拿不到共享锁时失败，而不是无限等待；失败就重新取得完整快照，不能拼接半个 archive。
directory 格式的并行导出如下。-j 4 会打开多个数据库连接；主进程先获取 synchronized snapshot，worker 在相同快照读取数据。官方要求服务端支持 synchronized snapshots，并行 dump 期间还会用额外锁防止对象被删除。若另一个会话在 worker 取得锁前请求独占锁，工具可检测潜在死锁并中止。
mkdir -p ./artifacts/migration_lab.dir
pg_dump "host=localhost port=5432 dbname=migration_lab user=migration_reader sslmode=require" \
-Fd -j 4 --verbose --file=./artifacts/migration_lab.dir \
2> ./artifacts/migration_lab.dir.stderr
pg_restore --list ./artifacts/migration_lab.dir \
> ./artifacts/migration_lab.dir.toc
find ./artifacts/migration_lab.dir -type f -print0 | sort -z | xargs -0 sha256sum \
> ./artifacts/migration_lab.dir.sha256并行数不是 CPU 数的机械复制。dump worker 增加源端连接、并发顺序扫描、缓存冲刷和网络读；restore worker 增加目标连接、WAL、checkpoint、临时空间和索引 I/O。需要以固定数据集逐级增加 -j，记录吞吐、数据库连接、磁盘延迟、WAL 生成、checkpoint、CPU 与复制槽/WAL 保留影响，在边际收益消失前停止。
锁竞争要在演练中主动暴露。会话 A 先对目标表请求 ACCESS EXCLUSIVE 并保持事务，会话 B 用很短的 --lock-wait-timeout 执行 dump；预期 B 非零退出，stderr 指向无法取得锁，archive 不得进入恢复。反过来让 dump 先持有 ACCESS SHARE，再执行 ALTER TABLE，应在 pg_stat_activity.wait_event_type='Lock' 与 pg_locks 中看到 DDL 等待。这样才能证明变更冻结和失败门禁有效，而不是仅凭“常规 DML 不被阻塞”推断 DDL 也安全。
-- 会话 A：仅用于 migration_lab 合成实验。
BEGIN;
LOCK TABLE app.customer IN ACCESS EXCLUSIVE MODE;
SELECT pg_backend_pid() AS blocker_pid;
-- 完成反例取证后执行 ROLLBACK。pg_dump "host=localhost port=5432 dbname=migration_lab user=migration_reader sslmode=require" \
-Fc --lock-wait-timeout=2s --file=./artifacts/lock-failure.dump \
2> ./artifacts/lock-failure.stderr
test $? -ne 0
rm -f ./artifacts/lock-failure.dump清理顺序是先让会话 A ROLLBACK，确认等待队列消失，再重新取得完整快照；不要保留失败 archive，也不要在源端杀死无关业务事务来迁就 dump。生产 --lock-wait-timeout 由 DDL 窗口和业务 SLO 决定，2s 只是稳定触发实验分支的演示值。
快照与 LSN 必须来自同一个边界
单独执行 SELECT pg_current_wal_lsn() 再启动 pg_dump，只能得到相邻的观察值，不能把逻辑变更流和 dump 快照拼成无缝边界：在两个动作之间提交的事务、创建快照时仍在运行的事务，都可能被漏掉或重复。普通 pg_dump 自己保证 archive 内部一致；只有要在快照后接逻辑复制或 CDC 时，才需要外部 synchronized snapshot。
官方 pg_dump --snapshot 可以导入并行 dump 主进程、另一个事务或逻辑复制槽导出的 snapshot。若只是让多个独立 dump 读取同一时点，可在协调会话开启 REPEATABLE READ 只读事务，调用 pg_export_snapshot()，保持事务打开，所有 dump 成功后再提交。snapshot 名称只在导出它的事务存活期间有效，而且只能在同一 database 使用。
-- 协调会话：保持该事务打开，直到所有 pg_dump 都成功退出。
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT pg_export_snapshot() AS snapshot_name;pg_dump "host=localhost port=5432 dbname=migration_lab user=migration_reader sslmode=require" \
-Fc --snapshot='<snapshot_name>' \
--file=./artifacts/migration_lab.snapshot.dump这仍然没有提供 CDC 起始 LSN。真正的在线迁移应由逻辑复制客户端按官方 Streaming Replication Protocol 创建持久逻辑槽并选择 SNAPSHOT 'export'；服务端在同一响应中返回 consistent_point 与 snapshot_name。保持该 replication connection 不执行下一条命令也不关闭，把 snapshot_name 交给 pg_dump --snapshot；dump 成功并校验后，消费者从同一 slot 的 consistent_point 开始持续读取。直接调用 SQL 函数 pg_create_logical_replication_slot() 只返回 slot 和 LSN，不把可交给 pg_dump 的 snapshot 名称返回给调用者，因此不能替代这个协议步骤。
CREATE_REPLICATION_SLOT migration_seed LOGICAL pgoutput (SNAPSHOT 'export');
-- 响应必须共同保存：slot_name, consistent_point, snapshot_name, output_plugin
pg_dump ... --snapshot='<snapshot_name>' ...
START_REPLICATION SLOT migration_seed LOGICAL <consistent_point> (...)逻辑槽会保留 WAL 和 catalog 可见性。迁移期间持续监控官方 pg_replication_slots 视图中的 restart_lsn、confirmed_flush_lsn、wal_status、safe_wal_size、active 与 invalidation_reason；wal_status='lost'、safe_wal_size 接近容量保护线、slot database 不匹配或消费者确认位置倒退都应中止切换并重新取种子。最终冻结写后保存源端提交边界，等待消费者确认到该边界且积压归零，再做业务对账；“当前 WAL LSN 相等”不能替代 slot 确认位置和事务级应用证据。
SELECT slot_name, database, active, restart_lsn, confirmed_flush_lsn,
wal_status, safe_wal_size, invalidation_reason
FROM pg_replication_slots
WHERE slot_name='migration_seed';失败清理必须区分两个时点。archive 尚未成功时，先停止消费者，再由具备复制槽管理权限的账号确认 slot 未被其他任务复用后执行 pg_drop_replication_slot('migration_seed')；否则 WAL 会继续增长。目标已经接管并产生新写时，不能直接删 slot 或回源，必须先完成反向同步/补偿与数据 owner 决策。slot 名、consistent point、最终 confirmed LSN 和删除审批共同构成 LSN 边界证据。
TOC、section 与并行 restore 的真实行为
pg_restore --list 输出 archive TOC。每行包含 dump ID、catalog OID、对象类型、schema、名称与 owner；依赖信息保存在 archive 中。pre-data 通常创建 schema、type、function、table；data 装载 table data、large objects 与 sequence state；post-data 建 index、constraint、trigger 和规则。把 post-data 推迟使数据 COPY 更快，但也意味着数据阶段可能暂时没有外键和唯一约束保护。
先在目标创建干净数据库与必要 extension package，再做 dry inspection。pg_restore 没有通用 --dry-run；可用 --list 检查 TOC，或用 --file 生成 SQL 审阅，但真正的兼容性仍要在隔离数据库恢复。
createdb --host=localhost --port=5433 --username=migration_loader \
--owner=app_owner --template=template0 migration_lab_restore
pg_restore --list ./artifacts/migration_lab.dump | sed -n '1,80p'
pg_restore --file=./artifacts/migration_lab.preview.sql \
--no-owner --no-privileges ./artifacts/migration_lab.dump正式恢复 custom archive 可以并行。每个 job 使用独立连接；-j 只支持 custom 与 directory archive，输入必须是普通文件或目录，不能从 stdin 管道读取。--single-transaction 与多 job 不能同时使用，因为多个连接无法被一个本地事务包住。这是明确取舍：单事务恢复便于整体回滚，但牺牲并行；并行恢复缩短窗口，却可能留下部分成功目标，所以应始终加载到可丢弃的空数据库。PostgreSQL 18 还提供 --transaction-size=N，把最多 N 个 archive 对象组成一批事务并隐含 --exit-on-error，可限制单个大事务占用的锁表空间；它同样不是并行恢复的原子回滚，已经提交的批次仍会留下。
pg_restore --dbname="host=localhost port=5433 dbname=migration_lab_restore user=migration_loader sslmode=require" \
-j 4 --verbose --exit-on-error \
--no-owner --no-privileges \
./artifacts/migration_lab.dump \
2> ./artifacts/migration_lab.restore.stderr--exit-on-error 让任务在 SQL 错误时停止；默认行为是继续并在结束时报告 error count，这正是很多“末尾完成、途中已坏”事故的来源。并行时已经提交的其他 job 不会因为某个 job 失败而整体回滚，因此失败目标要隔离并重建。--clean --if-exists 适合明确覆盖已知对象，但不能保证清掉 archive 外的漂移对象；最可信的迁移落点仍是新建空 database。
需要选择对象时，不要只写 -t app.customer 就相信依赖会自动带齐。官方说明 -t 不保证恢复所选对象的所有依赖，干净数据库中的单表恢复可能失败。更可控的方法是导出 TOC、评审并生成 --use-list 文件，或在源端创建自包含的专用 dump。删改 TOC 行会改变恢复集合和顺序，应像代码一样评审并留摘要。
反向实验：让恢复继续并留下半成品
第一组反例在目标故意不安装 pgcrypto，再不加 --exit-on-error 恢复。extension 创建可能因缺少 control file 失败，依赖 gen_random_bytes() 的默认值、digest() 生成列或后续对象随之失败，而无关表数据继续装载。
pg_restore --dbname="host=localhost port=5433 dbname=migration_lab_restore user=migration_loader sslmode=require" \
--verbose --no-owner --no-privileges \
./artifacts/migration_lab.dump \
2> ./artifacts/extension-missing.stderr
grep -nE "ERROR|WARNING|errors ignored" ./artifacts/extension-missing.stderr预期故障证据包含“extension 不可用”或其依赖对象创建失败，并可能在末尾出现 ignored errors 计数；具体文字随版本和安装状态变化。流水线必须同时检查进程退出码、stderr 与恢复后的对象查询，不能只搜索最后一行。修复是在目标主机安装受信、兼容的 extension 二进制和控制文件，确认 pg_available_extension_versions 中存在所需版本，再重建空数据库恢复；不是手工创建一个同名空 schema 欺骗依赖。
第二组反例让 owner 缺失。保留 archive owner 恢复时，pg_restore 会发出 ALTER OWNER 或 SET SESSION AUTHORIZATION；加载账号不是 superuser、也不能成为该 owner 时会失败。先由目标端独立管理员重建实验数据库并明确删除目标 owner，避免前一次实验残留的角色或 membership 让反例意外成功。
dropdb --host=localhost --port=5433 --username=cluster_admin \
--if-exists migration_lab_restore
psql "host=localhost port=5433 dbname=postgres user=cluster_admin sslmode=require" \
-X -v ON_ERROR_STOP=1 -c "DROP ROLE IF EXISTS app_owner;"
createdb --host=localhost --port=5433 --username=cluster_admin \
--owner=migration_loader migration_lab_restore
pg_restore --dbname="host=localhost port=5433 dbname=migration_lab_restore user=migration_loader sslmode=require" \
--exit-on-error ./artifacts/migration_lab.dump \
2> ./artifacts/owner-missing.stderr第一轮的稳定证据是 role "app_owner" does not exist。还可以由 cluster_admin 创建 app_owner NOLOGIN，但不授予 migration_loader membership，再确认权限失败分支：
psql "host=localhost port=5433 dbname=postgres user=cluster_admin sslmode=require" \
-X -v ON_ERROR_STOP=1 -c "CREATE ROLE app_owner NOLOGIN; REVOKE app_owner FROM migration_loader;"
psql "host=localhost port=5433 dbname=postgres user=migration_loader sslmode=require" \
-X -v ON_ERROR_STOP=1 \
-c "SELECT pg_has_role(current_user, 'app_owner', 'SET') AS can_set_owner;"预期 can_set_owner=f；在再次清空数据库后保留 owner 恢复，会因不能切换或变更到 app_owner 而停止。修复选择有三种：由身份管理员预建同名 NOLOGIN owner，并仅在恢复窗口授予加载账号可 SET 的 membership；使用 --no-owner 后由独立管理员执行经过评审的 owner 映射；或让认证账号以 --role=app_owner 恢复，但前提仍是它有合法 membership。不能把所有对象永久交给高权限迁移账号，也不能把迁移账号设为长期 superuser。
globals、owner 与 ACL 的迁移策略
pg_dump 不保存 role 和 tablespace。官方 pg_dumpall 手册说明 --globals-only 导出 cluster 全局对象；读取完整角色信息通常需要高权限，恢复 roles 与 tablespaces 也可能需要 superuser。迁移前先导出到受控文件并审阅，不要自动把源 cluster 的所有登录账号、密码哈希和本地 tablespace 路径灌进目标。
pg_dumpall --database="host=localhost port=5432 dbname=postgres user=migration_admin sslmode=require" \
--globals-only --no-role-passwords \
--file=./artifacts/globals.sql
grep -E "^(CREATE ROLE|ALTER ROLE|CREATE TABLESPACE|GRANT)" \
./artifacts/globals.sql
sha256sum ./artifacts/globals.sql > ./artifacts/globals.sql.sha256--no-role-passwords 避免把密码 verifier 放进 artifact，但恢复后登录角色没有密码，需要由身份系统重新签发。托管数据库常禁止创建 tablespace、superuser 或某些参数授权，此时 globals.sql 是映射输入，不是直接执行脚本。建立角色映射表：源 owner 对应目标 NOLOGIN owner，源登录角色对应 SSO/身份平台主体，废弃账号不迁，tablespace 映射到目标允许的存储策略。
ACL 同样需要决策。--no-privileges 会跳过 GRANT/REVOKE，适合由目标 IaC 统一配置授权；若保留 ACL，就必须先存在被授权角色。默认权限 ALTER DEFAULT PRIVILEGES 与对象 owner 绑定，迁完当前对象但漏掉默认权限，会让下一次建表悄悄回到错误授权。恢复后要检查 table、sequence、function、schema 和 database privilege，应用写入尤其常因 sequence USAGE 缺失而失败。
SELECT n.nspname, c.relname, c.relkind,
pg_get_userbyid(c.relowner) AS owner,
c.relacl
FROM pg_class c
JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname='app'
ORDER BY c.relkind, c.relname;
SELECT defaclrole::regrole, defaclnamespace::regnamespace,
defaclobjtype, defaclacl
FROM pg_default_acl
ORDER BY 1,2,3;extension 是代码供应链，不只是 catalog 行
CREATE EXTENSION 把一组对象登记为一个可管理单元，dump 通常记录 extension 创建与 extension 配置表的数据，而不会把所有成员当作普通对象重复导出。目标恢复时需要匹配的 .control、SQL 脚本，含 C 代码的 extension 还需要与目标 PostgreSQL major、CPU 与系统 ABI 兼容的动态库。官方 extension packaging也提醒 extension 安装脚本由数据库服务端执行，来自不可信源的脚本等同于代码执行风险。
源端先记录名称、默认版本、已安装版本、schema 与 relocatable 属性；目标端比较可用版本。版本相同不自动证明二进制来源相同，因此还要保存包名、仓库、制品摘要与 SBOM。
SELECT e.extname, e.extversion, n.nspname AS schema,
e.extrelocatable
FROM pg_extension e
JOIN pg_namespace n ON n.oid=e.extnamespace
ORDER BY e.extname;
SELECT name, default_version, installed_version
FROM pg_available_extensions
ORDER BY name;
SELECT name, version, installed, superuser, trusted, relocatable
FROM pg_available_extension_versions
ORDER BY name, version;跨 major 前阅读每个 extension 的升级矩阵。若目标只提供更高 extension 版本，可以先在与源一致的版本恢复再 ALTER EXTENSION ... UPDATE，也可以按厂商支持路径在源端升级后重新 dump；两条路都要在副本演练。PostGIS 等大型 extension 还可能带辅助表、外部数据文件和操作系统库，恢复后要运行 extension 自带版本函数与代表性查询，不能以 pg_extension 有一行作为通过。
本实验不能再用 gen_random_uuid() 证明 pgcrypto 存在：PostgreSQL 18 的扩展手册已把该包装函数标为 obsolete，并说明它调用核心同名函数。真正的依赖是 customer.migration_token 的 gen_random_bytes(16) 与生成列中的 digest(..., 'sha256')；pgcrypto 依赖 OpenSSL，构建时未启用 OpenSSL 就不会安装。恢复后用代表性调用和 catalog 依赖同时验证：
SELECT encode(digest('migration-probe', 'sha256'), 'hex') AS probe_digest,
octet_length(gen_random_bytes(16)) AS random_bytes;
SELECT display_name,
octet_length(migration_token) AS token_bytes,
encode(display_name_sha256, 'hex') AS name_digest
FROM app.customer
ORDER BY id;random_bytes 与每行 token_bytes 应为 16，digest 应为 64 位十六进制文本。函数缺失、返回长度不符或生成列不可用都应阻断接管；随机字节本身每次不同，不能把具体值写成固定断言。
collation、encoding 与跨 major 的隐蔽变化
数据库 encoding 决定字节如何解释，collation 决定比较与排序。逻辑 dump 会重建对象和数据，但目标 libc/ICU 的排序规则实现可能与源端不同。PostgreSQL 会在 catalog 保存 provider-specific collation version；当前实现发现记录版本与实际库版本不一致时发出 warning。官方 ALTER COLLATION说明，排序定义变化可能让已有索引顺序失真，通常需要重建受影响对象，再 REFRESH VERSION。
先盘点数据库默认值、每个 collation provider 和版本，再查依赖对象。跨操作系统、基础镜像或 ICU major 时，名称相同不能证明语义相同。
SELECT datname, pg_encoding_to_char(encoding) AS encoding,
datcollate, datctype, datlocprovider,
daticurules, datcollversion,
pg_database_collation_actual_version(oid) AS actual_version
FROM pg_database
WHERE datname='migration_lab';
SELECT collname, collprovider, collisdeterministic, collversion,
pg_collation_actual_version(oid) AS actual_version
FROM pg_collation
WHERE collversion IS DISTINCT FROM pg_collation_actual_version(oid)
ORDER BY collname;反向实验可在使用不同 ICU/libc 的隔离目标恢复 archive，然后执行排序与唯一性探针。不要在生产为了消除 warning 直接 REFRESH VERSION：该命令只更新 catalog 记录，不会检查或重建受影响索引。正确路径是找出依赖，备份证据，REINDEX 对应 index/table/database，验证唯一约束与排序结果，再 refresh database 或 collation version。
SELECT display_name, encode(convert_to(display_name, 'UTF8'), 'hex') AS utf8_hex
FROM app.customer
ORDER BY display_name COLLATE "C";
SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS collation,
pg_describe_object(classid, objid, objsubid) AS dependent_object
FROM pg_depend d
JOIN pg_collation c
ON refclassid='pg_collation'::regclass AND refobjid=c.oid
WHERE c.collversion IS DISTINCT FROM pg_collation_actual_version(c.oid)
ORDER BY 1,2;应用还要验证大小写折叠、模糊匹配、唯一键和分页游标。排序变化会让基于 ORDER BY name LIMIT/OFFSET 的页面边界变化，也可能让过去允许共存的字符串在新规则下冲突。字节摘要相同只能证明数据没变，不能证明比较语义没变。
数据、sequence、LOB 与业务不变量校验
恢复后先执行 ANALYZE，否则跨 major 的 planner 和空统计信息会使性能验证失真。然后比较对象清单、行数、主键分桶、不变量与应用查询。不要直接比较整库物理文件，也不要把 pg_stat_* 估算行数当确定性对账。
ANALYZE VERBOSE app.customer;
ANALYZE VERBOSE app.purchase_order;
SELECT COUNT(*) AS customers,
SUM(credit) AS total_credit,
MIN(id) AS min_id, MAX(id) AS max_id
FROM app.customer;
SELECT COUNT(*) AS orphan_orders
FROM app.purchase_order o
LEFT JOIN app.customer c ON c.id=o.customer_id
WHERE c.id IS NULL;
SELECT customer_id, SUM(amount) AS amount_sum
FROM app.purchase_order
GROUP BY customer_id ORDER BY customer_id;identity 背后仍有 sequence。表最大 ID 与 sequence last value 不匹配时，下一次写入可能撞键。pg_dump 会保存 sequence state，但部分恢复、手工插入和失败重试会打破它。检查每个 owned sequence，并在应用写入前做事务内插入与回滚探针。
SELECT pg_get_serial_sequence('app.customer', 'id') AS customer_id_sequence;
SELECT last_value, is_called FROM app.customer_id_seq;
SELECT MAX(id) FROM app.customer;
BEGIN;
INSERT INTO app.customer(display_name, credit)
VALUES ('切流探针', 1.00)
RETURNING id, external_id;
ROLLBACK;large object 的元数据与 owner/ACL 位于 pg_largeobject_metadata，实际分页字节位于 pg_largeobject，业务表只保存 OID 引用。正向实验使用 lo_from_bytea(0, bytea) 创建真实对象并把返回 OID 写入 app.customer_document；它没有使用服务端文件系统，因此也不需要把高风险的 server-side lo_import 授给普通账号。pg_dump 在完整 dump 中会包含 large objects；指定 -n/--schema 时默认不包含这类非 schema 对象，必须显式加 --large-objects，而 --no-large-objects 会排除它们。
恢复前后都执行同一组摘要、授权与引用检查。下面的 refs 必须扩展为业务中所有 OID 引用列的并集；只列一张表时，“孤儿”仅表示不被这张表引用，不能直接删除。
WITH refs AS (
SELECT document_loid AS loid FROM app.customer_document
)
SELECT m.oid AS loid,
pg_get_userbyid(m.lomowner) AS owner,
m.lomacl,
has_large_object_privilege(
'migration_reader', m.oid, 'SELECT'
) AS reader_can_select,
encode(digest(lo_get(m.oid), 'sha256'), 'hex') AS content_sha256
FROM pg_largeobject_metadata m
JOIN refs r ON r.loid=m.oid
ORDER BY m.oid;
WITH refs AS (
SELECT document_loid AS loid FROM app.customer_document
)
SELECT r.loid AS missing_large_object
FROM refs r
LEFT JOIN pg_largeobject_metadata m ON m.oid=r.loid
WHERE m.oid IS NULL;
WITH refs AS (
SELECT document_loid AS loid FROM app.customer_document
)
SELECT m.oid AS unreferenced_large_object
FROM pg_largeobject_metadata m
LEFT JOIN refs r ON r.loid=m.oid
WHERE m.lomowner='app_owner'::regrole
AND r.loid IS NULL;正向结果应有一条 owner 为 app_owner 的引用，reader_can_select=t，源端与目标端 content_sha256 相同，missing 与 unreferenced 查询均为空。若应用运行时需要读取或修改 LOB，还要分别授予目标应用角色 SELECT 或 UPDATE；表上的 SELECT 不会隐式授予 large object 权限。普通 bytea 不属于 large object，两者不能混称。
跨 major 还要核对 generated columns、partition、foreign data wrapper、publication/subscription、row-level security、security label 和 untrusted language。部分对象依赖外部 endpoint、凭证或目标政策，即使 archive 可表达，也不该在隔离恢复环境自动激活。
PostgreSQL 18 的 pg_dump 对 subscription 有专门的安全语义：生成的 CREATE SUBSCRIPTION 会带 connect=false，恢复时不会连接 publisher、创建 slot 或做初始复制。这个选项还会强制 create_slot=false、enabled=false、copy_data=false，并且因为从未连接 publisher，尚没有任何表进入订阅集合。它不是“已创建但暂停一下”的完整订阅。接管时要由复制管理员核对 conninfo 和 publication，手工创建或确认 slot，按需要设置 failover，然后设置 slot、refresh 表集合，最后启用；若希望 refresh 做初始复制，还要明确 copy_data=true 及表清空策略。
-- 隔离恢复后先确认订阅仍禁用且没有远程动作。
SELECT subname, subenabled, subslotname, subpublications
FROM pg_subscription;
-- slot 已由复制管理员在 publisher 创建并核对后，才在 subscriber 执行：
ALTER SUBSCRIPTION app_sub CONNECTION
'host=publisher.example dbname=app user=replicator sslmode=verify-full';
ALTER SUBSCRIPTION app_sub SET (slot_name='app_sub_slot');
ALTER SUBSCRIPTION app_sub REFRESH PUBLICATION WITH (copy_data=false);
ALTER SUBSCRIPTION app_sub ENABLE;这里的 copy_data=false 适合目标表已由 dump 装满、只从既定 slot 水位追增量的迁移；如果 slot 水位与 dump 快照没有被同一方案绑定，直接启用会丢事务或重复应用。完全不迁 subscription 时，在 dump 或 restore 使用 --no-subscriptions，比删改生成 SQL 更清楚。
项目接入、切流与回切窗口
目标数据库恢复完成后，先用真实应用 role 连接，不用 migration_loader 冒充应用。检查 search_path、database/role settings、timezone、statement timeout、RLS、默认权限和连接池初始化 SQL。影子实例必须禁止向邮件、支付和外部队列写副作用；验证查询可以回放，验证写操作使用合成记录并在事务中回滚或打到隔离下游。
纯 dump/reload 的停机迁移要冻结写入，等待活跃事务结束，取得最终 archive，恢复、校验后切换。业务无法承担这个窗口时，需要逻辑复制或 CDC 在初始快照后追平 WAL，并定义最终 LSN、DDL 同步与 sequence 处理；不能让一次旧快照直接接管持续写入的源库。使用逻辑复制时，extension DDL、large object 和 sequence 等边界还要逐项核对。
切换时把目标设为唯一写入方，逐步切应用连接并通过 inet_server_addr()、inet_server_port()、current_database() 与审计指标确认实际落点。连接池会保留旧连接，DNS TTL 结束也不代表连接已重建。源端先改为受控只读或阻断应用 role 写入，保留回切能力；不要立刻 drop database。
一旦目标接受新写，简单改回源端会丢失这些提交。回切前必须有反向逻辑复制/CDC 并完成对账，或由业务明确接受恢复到切流点并补偿新写。双写若没有全局版本、幂等键与冲突 owner，会制造无法自动合并的分叉，不能把它当作低成本保险。
清理、失败恢复与敏感数据
并行 restore 失败后，最容易解释的恢复动作是删除隔离目标并重新创建，而不是在半成品上重复执行。--clean --if-exists 只清理 archive 中将恢复的对象，目标上额外漂移不会被发现；--create --clean 也要求连接到可用于 drop/create 的维护数据库，并涉及数据库级配置与 owner。自动化要打印目标的 host、port、database 与 server version，避免误删源库。
临时加载角色不能在自己的会话里直接删掉，也常常仍拥有对象、database 或残留 ACL。应由不待删除的独立 cluster_admin 登录，在角色可能留下依赖的每个 database 中依次执行 REASSIGN OWNED、DROP OWNED，最后回到维护库删除角色。官方要求这个顺序，因为 REASSIGN OWNED 不会撤销授予旧角色的 privileges，而 DROP OWNED 会清掉这些 ACL；两条命令都只覆盖当前 database，所以不能只跑一次。
: "仅用于 localhost 合成实验，cluster_admin 与 migration_loader 必须是不同角色"
psql "host=localhost port=5433 dbname=migration_lab_restore user=cluster_admin sslmode=require" \
-X -v ON_ERROR_STOP=1 -c "
SELECT inet_server_addr(), inet_server_port(), current_database(), version();
REASSIGN OWNED BY migration_loader TO app_owner;
DROP OWNED BY migration_loader;"
psql "host=localhost port=5433 dbname=postgres user=cluster_admin sslmode=require" \
-X -v ON_ERROR_STOP=1 -c "
REASSIGN OWNED BY migration_loader TO app_owner;
DROP OWNED BY migration_loader;"
dropdb --host=localhost --port=5433 --username=cluster_admin \
--if-exists migration_lab_restore
psql "host=localhost port=5433 dbname=postgres user=cluster_admin sslmode=require" \
-X -v ON_ERROR_STOP=1 -c "DROP ROLE migration_loader;"若该角色访问过更多 database，要先对每个 database 重复前两条 SQL；若 DROP ROLE 仍报告依赖，按错误列出的 database、tablespace、database ownership 或参数授权继续清理，不能加 CASCADE 猜测性删除业务对象。执行 REASSIGN OWNED 的普通管理员需要同时拥有源角色与目标角色 membership；生产通常由具备相应职责的集群管理员完成。
archive、TOC、stderr 和 globals.sql 都可能含敏感信息。custom/directory dump 包含完整行数据；TOC 暴露 schema、表名和 owner；错误日志可能打印 SQL、函数体、内部路径与客户值；globals.sql 若不加 --no-role-passwords 还可能包含密码 verifier。制品应静态加密、传输加密、最小访问、不可公开下载，并设置与数据分类一致的保留和销毁策略。生产数据恢复到测试环境前先获得授权并脱敏，不能因为格式是二进制 archive 就降低分类。
迁移完成后撤销临时 role、网络规则、CA 例外与对象存储访问，清除 CI workspace 和 runner cache 中的副本。源库在观察期继续保留既有备份和 WAL 策略，确认没有客户端、任务或外部集成访问后再按 owner 审批退役。删除源库与销毁 dump 是两个独立动作，都要能证明责任人与保留政策。
架构选型、容量成本与长期治理
custom archive 是默认稳妥选择：单文件易传输，TOC 可选择，restore 可并行。directory 适合源端导出本身就需要并行、单文件过大或希望数据项独立处理的场景，代价是文件数量、目录原子性和传输完整性。plain SQL 适合小库、强审阅需求或必须经过 SQL 网关的环境，但恢复可调度性与速度较弱。只迁少量自包含对象可以过滤 dump；复杂依赖、extension 与 ACL 较多时，完整数据库到空目标更可证明。
跨 major 停机升级还可以选择 pg_upgrade，它避免逻辑重写全部数据，通常缩短数据搬运时间，却要求文件系统与二进制/extension 条件，且仍要处理 collation 和升级后统计信息。业务不能停机时考虑逻辑复制/CDC；它把停机压缩到切换窗口，但增加 WAL 保留、DDL 协调、复制边界和回切复杂度。选型按允许停写时长、数据量、跨平台需求、extension 兼容、目标托管限制与团队排障能力决定，不按“哪个命令最快”决定。
容量预算包含源端并行扫描与缓存扰动、长快照导致的 vacuum 影响、网络、archive 压缩 CPU、目标 WAL、checkpoint、索引构建临时空间、ANALYZE、collation REINDEX、校验查询和双环境观察期。directory archive 的制品空间之外，还要给目标表、索引、WAL 与临时文件同时留峰值空间。并行恢复连接数与应用预热连接相加，必须低于 max_connections 和连接池预算。
团队治理应把每次迁移变成可重复 runbook，但不能把 runbook 简化为命令清单。数据库 owner 负责版本、extension、collation、容量和恢复策略；应用 owner 提供业务不变量、冻结写与回切决策；身份 owner 审核 role/ACL 映射；平台团队维护固定版本工具镜像、加密 artifact 与可观察流水线；安全团队审核生产数据流向和临时高权限。恢复人不能单独批准差异、切流并销毁源端。
最终接管证据应能回答：新客户端与两端版本是否记录；archive 摘要与 TOC 是否完整；globals 是否经过角色与 tablespace 映射；owner、ACL 与默认权限是否符合目标模型；extension 包、版本和代表性函数是否可用；database encoding、collation provider/version 与依赖索引是否核对；table、sequence、large object、约束和业务不变量是否一致；恢复日志是否零 error 且流水线能在反例中失败；应用是否以真实 role 完成读写；唯一写入方、回切数据路径和源端退役条件是否明确。只有这些问题都有证据，pg_restore 的“完成”才等于业务可以接管。