第二大脑

主题

2026-08-24
1726
6.7m

PostgreSQL 逻辑备份与恢复（pg_dump / pg_restore） ​

一句话机制：pg_dump 在一个 MVCC 快照里把数据库重新表达成一串创建语句 + 数据，pg_restore 再把这串语句重新执行一遍——所以它备份的是"逻辑内容"而非磁盘块，恢复等于重建数据库（建表、灌数据、重建索引），而不是拷贝还原。

不变量（必须成立的约束） ​

  1. 恢复 = 重放，不是还原。 pg_restore 执行的是 CREATE 脚本，不是 UPDATE 脚本。目标库必须先存在（或用 -C），且不能指望它"合并"进已有数据——这是原文小结里唯一最重要的一句话。
  2. 恢复耗时 ≫ 备份耗时。 灌数据后要重建全部索引和约束，这是恢复慢的主因，与磁盘拷贝不是一个量级。数据量越大，倍数差距越夸张。
  3. 版本单向兼容：pg_dump 版本必须 ≥ 服务器版本。 新版工具可以 dump 旧服务器；旧版工具连新服务器会直接 aborting because of server version mismatch。pg_restore 同理，读不了比自己新的归档（报 unsupported version (x.xx) in file header）。
  4. 只有 -F c（custom）/ -F d（directory）能被 pg_restore 读。 -F p（纯文本，默认）产出的是 psql 脚本，只能 psql -f 灌回，不支持选择性恢复、不支持并行。
  5. pg_dump 只备份"一个数据库"，不含全局对象。 角色/用户、密码、表空间定义、数据库级权限都不在里面，需要 pg_dumpall -g 单独导。换集群恢复后一堆 role "xxx" does not exist 就是这么来的。
  6. 逻辑备份没有"时间点"概念。 只能恢复到 dump 开始那一刻的快照，无法 PITR（恢复到故障前 1 分钟）。要 PITR 必须走物理备份 + WAL 归档。
  7. 不阻塞读写，但阻塞 DDL，且长事务有副作用。 pg_dump 持 ACCESS SHARE 锁，正常增删改查不受影响；但期间 DDL 会被卡住，且它开着一个长事务 → 抑制 autovacuum 回收、WAL 堆积。大库 dump 尤其要留意。

命令速查（经修正，可直接抄） ​

bash
# 全库备份：自定义压缩格式（唯一推荐的日常格式）
pg_dump -h <ip> -p 5432 -U postgres -F c -v -f /backup/mydb.dump mydb

# 目录格式 + 并行备份（大库首选，-j 只对 -F d 生效）
pg_dump -h <ip> -U postgres -F d -j 4 -f /backup/mydb_dir mydb

# 并行恢复（对 -F c / -F d 都生效，最有效的提速手段）
pg_restore -h <ip> -U postgres -d mydb -j 4 -v /backup/mydb.dump

# 排除大日志表的"数据"但保留表结构（比 -T 安全，见下方误解②）
pg_dump ... --exclude-table-data=op_log -F c -f /backup/mydb.dump mydb

# 只导表结构 / 只导数据
pg_dump -s -t tlb mydb > struct.sql     # -s 仅结构，-t 指定表
pg_dump -a -t tlb mydb > data.sql       # -a 仅数据

# 恢复前清库重建（覆盖式恢复）
pg_restore --clean --if-exists -d mydb -j 4 /backup/mydb.dump

# 全局对象（角色/权限/表空间）——务必单独备份，否则换集群必翻车
pg_dumpall -h <ip> -U postgres -g > /backup/globals.sql

# 重命名数据库（正确姿势，勿改系统表）
ALTER DATABASE old_name RENAME TO new_name;

# 删库/改名前踢掉残留会话
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';
-- PG13+ 可直接： DROP DATABASE mydb WITH (FORCE);

关键证据 / 例子 ​

  • 格式与压缩：装了 zlib 时 -F c 会自动压缩，体积接近 gzip，但保留了选择性恢复能力——这是它优于 pg_dump | gzip 纯文本的核心理由（原文这点说对了）。
  • 版本报错实例：server version: 10.8; pg_dump version: 9.2.24 → aborting；pg_restore: [archiver] unsupported version (1.13) in file header。两者都是"工具比服务器/归档旧"。
  • 体量边界：逻辑备份的额外磁盘需求 ≈ 数据量级（压缩后仍是同量级），恢复耗时由索引重建主导。因此百 GB 量级以上的生产库，逻辑备份只适合做单库/单表级的逻辑迁移与冷归档，不适合当日常备份手段，详见 PostgreSQL备份方案对比。

常见误解（含对原文的纠正） ​

① ❌ 原文把 pg_dump 软链到了错误版本。 文中服务器是 10.8，修复却是 ln -sfn /usr/pgsql-9.6/bin/pg_dump /usr/bin/pg_dump——9.6 < 10.8，照抄仍会 mismatch（它后面修 pg_restore 时链的是 /usr/pgsql-10/，可见是笔误）。 ✅ 正确：链到 ≥ 服务器版本的路径；且更稳妥的做法是用绝对路径或 alternatives，别覆盖 /usr/bin 全局软链——那会影响机器上所有其它 PG 客户端。

② ❌ 用 -T 排除大日志表 ≠ 安全。 原文为解决"日志表太大恢复很慢"用了 -T op_log，但 -T 是连表结构一起不导，恢复后该表根本不存在，应用写日志时直接报错。 ✅ 正确：用 --exclude-table-data=op_log——留结构、丢数据，应用照常跑。

③ ❌ "恢复太慢"的第一解法不是排除表，是并行。 原文全篇没提 -j。pg_restore -j 4（甚至 -j = CPU 核数）通常能把恢复时间压到 1/3 以下，因为索引重建可以并行。先加 -j，再考虑砍数据。

④ ❌ "Ident authentication failed 就别用 localhost"是治标。 根因是 pg_hba.conf 里 local / 127.0.0.1 那几行配的是 ident/peer 认证。换成真实 IP 只是恰好命中了另一条 md5 规则。 ✅ 正确：改 pg_hba.conf 对应行为 scram-sha-256（或 md5）后 pg_ctl reload。绕开它意味着你的本地连接认证配置始终是错的。

⑤ ❌ 绝不要 UPDATE pg_database SET datname = ... 改库名。 原文这条是危险操作：直接改系统目录表绕过了 PG 的内部一致性检查，可能留下失效的缓存与依赖。 ✅ 正确：ALTER DATABASE old RENAME TO new;（需先断开该库所有会话）。

⑥ ❌ "-b 必须加"是过时习惯。-b/--blobs 在全库备份时本来就是默认行为，只有配合 --schema / --table / --schema-only 时才需要显式加。原文命令里的 -b 是空操作，无害但没必要。

⑦ ❌ "备份成功 = 能恢复"。 最贵的误解。dump 文件能生成不代表能还原：版本、角色缺失、扩展（extension）缺失、表空间路径不存在，任何一条都能让恢复当场失败。 ✅ 不变量：没做过恢复演练的备份，等于没有备份。

待验证 / 待补 #待验证 ​

  • 原文基于 PostgreSQL 10 + CentOS 7（2019）。当前主流为 PG 14~17，需核对：-b 已更名 --large-objects、--no-sync、--strict-names 等参数变化。
  • 落地任一环境前，先确认目标库的 PG 大版本——它直接决定客户端工具版本与可用参数，缺这条信息就无法给出可执行命令。
  • 生产备份策略应以 PostgreSQL备份方案对比 的结论落地（物理备份 + WAL 归档），而非直接套用本文的 pg_dump。

关联 ​

最近更新