PostgreSQL 逻辑备份与恢复 (pg_dump / pg_restore)

使用 pg_dump、pg_dumpall 和 pg_restore 在表、模式、数据库或整个集簇级别进行逻辑备份与恢复；仅结构 (DDL) 或仅数据、迁移到其他服务器/版本、从 dump 中恢复单个表，以及通过 systemd/cron 定时执行、带校验和保留期的备份。每条路径最后都会对比行数进行验证。在流复制和 Patroni 集群中，dump 从 replica（HAProxy 只读端口或 standby）获取，恢复只在 primary/leader 上执行。

逻辑备份只能回到 dump 时刻，不能做时间点恢复 (PITR)；生产环境的主备份必须是物理备份：pgBackRest（推荐），或不依赖额外工具的 pg_basebackup + WAL 归档。要以秒级停机把大型数据库迁移到新版本，使用 logical replication；在同一服务器上原地升级，使用 pg_upgrade。

架构: 1 台服务器、流复制 (primary + standby) 或 Patroni 集群；迁移时需要源端 + 目标端，远程定时备份另需一台备份服务器

场景

• 导出 / 导入单个（或多个）表 — 导出所选表的结构和数据（包括索引、约束、触发器），并加载到另一个数据库。
• 导出 / 导入一个模式 — 导出模式中的所有对象（表、视图、函数、序列）。
• 备份 / 恢复单个数据库 — 将整个数据库（结构 + 数据）导出为一个 dump，并恢复到同一个或另一个数据库。不包括角色 (globals)。
• 整个集簇（角色 + 表空间 + 每个数据库） — 用 pg_dumpall -g 导出 globals，每个数据库单独执行 pg_dump；恢复先在临时测试集簇上演练。
• 仅模式结构 (DDL) — 不含数据的表、索引、视图、函数定义；用于审查、版本控制或搭建空环境。
• 仅数据 — 目标端结构已就绪时，只传输行数据 (COPY)。
• 迁移到其他服务器 / 版本 — 在目标服务器上用新版本的 pg_dump 通过网络连接源端；通过管道或文件传输。
• 从 dump 中恢复单个表 / 对象 — 从现有的 custom/directory 格式 dump 中，借助列表文件 (-L) 只恢复一个表及其索引、约束和数据。
• 定时备份 (systemd timer / cron) — 每天备份 globals + 数据库，并做可读性校验、保留期管理、日志记录和失败通知。

开始之前

• …Patroni 集群正在运行；本服务器已安装 PostgreSQL … 客户端工具 (…)，并且可以访问 HAProxy/VIP (…) 的读写端口 (…)… 和只读端口 (…)…。你掌握 postgres 角色的口令（Patroni 中为 /etc/patroni/patroni.env 里的 PATRONI_SUPERUSER_PASSWORD）。…Primary 上运行着 PostgreSQL …，postgres 用户可以本地 (peer) 连接；standby … 处于 streaming 状态，并且从 primary 可以访问其 … 端口。dump 要通过网络连接 standby，因此 postgres 角色必须设置口令（没有的话在 primary 上执行 \password postgres；口令通过 WAL 同步到 standby）。…PostgreSQL … 正在运行，操作系统用户 postgres 可以本地 (peer) 连接。如果是按 DBAtlas PostgreSQL 单服务器安装指南安装的，路径完全一致。…
• …集群健康：一个 Leader，至少一个 replica 处于 streaming，延迟很小。在任一 Patroni 节点上检查。…Standby … 已连接到 primary，延迟很小（dump 获取的是 standby 当时的镜像）。…
• 备份目录 … 位于与数据盘分离的磁盘上，并且有足够的可用空间：压缩后的 custom/directory dump 通常为数据库大小的 10–30%（索引在 dump 中只保存定义）；plain 格式和不压缩的 dump 更大。
• pg_dump 的主版本与被备份服务器相同或更新（新版 pg_dump 可以读取旧服务器，反之不行）。pg_restore 必须与生成该 dump 的 pg_dump 版本相同或更新。
• 目标端的 pg_dump/pg_restore (…) 主版本与源服务器 (…) 相同或更新：迁移使用目标端的新版工具完成。
• 源端使用的扩展（postgis、pgvector、pg_partman…）的软件在目标服务器上也已安装。逻辑备份只携带 CREATE EXTENSION 语句，不包含扩展本身。
• 如果存在用户定义的表空间（下面的查询返回行），必须在目标端准备相同的目录，或者用 --no-tablespaces 恢复到默认表空间。
• 目标服务器 … 上已安装 PostgreSQL …，正在运行且为空（只有 postgres、template0、template1）。目标端可以访问源端的 … 端口。
• 备份服务器 … 可以访问… HAProxy/VIP 地址 … 的 … 端口… standby … 的 … 端口…数据库服务器的 … 端口…，并且可以访问 PGDG 仓库（或内部镜像）。
• 服务器可以发送邮件：本地 MTA (postfix) 已配置为通过公司 SMTP relay 转发。
• 已为大规模恢复或迁移安排维护窗口；如果要覆盖目标端，已对目标端做了备份/快照。

指南阶段

• 准备 — 创建备份目录 · 将连接口令保存到 .pgpass · 在 standby 的 pg_hba.conf 中授权 · 确认连接到达的角色 · 检查数据库大小和长事务
• 恢复（导入） — 准备目标数据库 · 准备目标端：扩展、模式和触发器函数 · 恢复 · 覆盖目标进行恢复 · 收集统计信息 (ANALYZE) · 比较行数
• 仅模式结构 (DDL) — 执行 DDL dump · 创建目标数据库 · 将 DDL 应用到目标端 · 验证结构一致
• 仅数据 — 执行数据 dump · 创建目标数据库 · 在目标端创建表结构 · 清空目标表 · 加载数据 · 收集统计信息并比较行数
• 全集簇备份 — 备份 globals 和所有数据库 · 验证备份可读
• 恢复演练（临时测试集簇） — 准备测试目录 · 创建并启动临时集簇 · 加载 globals · 加载数据库 · ANALYZE 与行数比较 · 停止并删除临时集簇
• 源服务器上的访问授权 — 在 pg_hba.conf 中允许目标服务器 · 在 pg_hba 中允许目标服务器 (Patroni) · 在防火墙中向目标端开放端口 · 停止应用（切换时刻）
• 在目标服务器上传输 — 将源端连接口令保存到 .pgpass · 检查版本和扩展兼容性 · 迁移角色 (globals) · 传输数据库（通过文件，并行） · 传输数据库（通过管道） · 收集统计信息 (ANALYZE) …
• 切换后 — 移除源端的临时访问 · 移除源端的临时访问 (Patroni) · 将应用指向目标端
• 从 dump 中恢复单个表 — 检查 dump 内容 · 创建列表文件 (-L) · 准备目标数据库 · 恢复表 · 将表恢复到原数据库 · 比较行数 …
• 备份服务器与备份角色 — 创建备份角色 · 授予大对象的读取权限 · 为备份角色设置口令 · 在 pg_hba.conf 中允许备份服务器 · 在 pg_hba 中允许备份服务器 (Patroni) · 安装 PostgreSQL 客户端工具（备份服务器） …
• 备份脚本 — 确认工具的全路径 · 安装通知工具 · 编写备份脚本 · 赋予脚本执行权限 · 手工执行第一次备份
• 调度 (systemd timer) — 编写 systemd 服务 · 编写 systemd timer · 启用 timer 并手工触发服务
• 调度 (cron) — 准备 cron 服务 · 编写 cron 作业 · 设置 cron 文件权限并在 cron 环境中测试 · 确认 cron 执行了作业
• 验证与监控 — 将最新备份恢复到测试数据库 · 监控最新备份的时间
• 下一步：物理备份 — 弥补逻辑备份的不足