第 14 章备份与恢复

学习目标：能给老板讲清楚「逻辑备份和物理备份的本质区别、各自适合什么场景」；能用 pg_dump -Fc -j 4 把生产库导出来；能跑通一次完整的 PITR 时光机 —— 从基础备份 + WAL 归档恢复到任意一秒；能列出生产备份策略的 5 道安全闸门，并知道什么时候该上 pgBackRest。

14.0 导读：备份的「三个灵魂问题」

每次数据库事故复盘，最折磨人的不是「能不能恢复」，而是这三个问题：

能恢复到什么时间点？——只能恢复到昨天 00:00 全量备份那一刻？还是能恢复到事故前 1 秒？
恢复要多久？——300 GB 的库，1 小时还是 8 小时能拉起来？老板等得起吗？
恢复出来的库是不是真的对？——数据完整吗？事务一致吗？外键还能 JOIN 上吗？

这三个问题对应业界三个术语：

术语	含义	决定权在
RPO（Recovery Point Objective）	最多能容忍丢多少数据（时间维度）	备份频率、WAL 归档间隔
RTO（Recovery Time Objective）	故障到完全恢复服务的时长	备份格式、并行度、网络
RA（Recovery Assurance）	备份能成功还原的可信度	是否有恢复演练

行业有句残酷名言：「没有演练过的备份不是备份，是占用空间的二进制垃圾」。本章末尾会给你一份「演练 SOP」。

本章会让你彻底理解 PG 的备份生态：

14.1 逻辑 vs 物理：先把概念分清；
14.2 逻辑备份：pg_dump 四种格式、并行 dump、pg_dumpall 全局对象；
14.3 逻辑恢复：pg_restore 选择性 / 并行 / 仅生成 SQL 不执行；
14.4 物理备份：pg_basebackup 在线热备原理；
14.5 PITR：基础备份 + WAL 归档 + restore_command 全流程；
14.6 延迟从库：「时光机」的另一种实现；
14.7 生态工具：pgBackRest / Barman / WAL-G 选型；
14.8 备份策略与生产清单；
14.9 常见踩坑。

14.1 逻辑备份 vs 物理备份：到底差在哪？

14.1.1 生活类比

把数据库想成一个图书馆：

逻辑备份：派一个人坐在借阅处，把每本书的内容朗读出来录音——你拿到的是「一段段可读的内容」，可以放到任何图书馆（不同 PG 大版本、不同 OS、甚至迁去另一台云）。但是 100 万本书要朗读很久。
物理备份：直接把整个图书馆的书架搬走——全部物理文件复制走，重建图书馆只需把书架放回去。速度快，但搬过去的书架尺寸必须一致（大版本一致、平台兼容、字节序一致）。

14.1.2 技术对照表

维度	逻辑备份（pg_dump）	物理备份（pg_basebackup / 拷文件）
备份内容	SQL 文本 / 自定义二进制（包含 CREATE + INSERT/COPY）	PGDATA 目录全部数据文件 + WAL
大小	通常只占数据库空间 30%~70%（无索引数据，索引由 SQL 重建）	接近 PGDATA 真实大小（含索引、冗余空间）
速度	慢（O(rows)）	快（O(bytes) 块复制）
恢复速度	慢（要 INSERT + 重建索引）	快（拷回去启库即可）
跨版本	✅ 支持（PG 9 → PG 17 都能 import）	❌ 必须同大版本（PG 16 ↔ PG 16）
跨平台 / 字节序	✅ 支持	❌ 必须一致
PITR 能力	❌ 只能到 dump 时刻	✅ 配合 WAL 归档可恢复到任意秒
是否需要停机	❌ 在线（一致性快照）	❌ 在线（START_BACKUP / STOP_BACKUP）
选择性	✅ 可单库 / 单表	❌ 整库整集群
适合场景	跨版本迁移、误删表恢复、抽样导出、开发库初始化	灾备、PITR、主从复制初始化、大库快速重建

📌 与 MySQL 的区别：
用途 MySQL PG
逻辑备份 mysqldump / mydumper pg_dump / pg_dumpall
物理备份 xtrabackup（Percona） pg_basebackup（自带）/ pgBackRest
增量备份 xtrabackup 原生支持 pg_basebackup 没有原生增量，靠 WAL 归档「全量+持续 WAL」实现
PITR 全量 + binlog 重放全量 + WAL 重放（思想完全一致）

14.2 逻辑备份 `pg_dump` 全面拆解

14.2.1 四种输出格式

bash

# (1) plain：纯 SQL 文本（默认），便于 vim 看、git diff
pg_dump -h 127.0.0.1 -U postgres -d learn_pg -Fp -f learn_pg.sql

# (2) custom：自定义二进制格式（推荐！），可并行恢复、可选择性恢复、自带压缩
pg_dump -h 127.0.0.1 -U postgres -d learn_pg -Fc -f learn_pg.dump

# (3) directory：目录格式，每张表一个文件，唯一支持「并行 dump」(-j)
pg_dump -h 127.0.0.1 -U postgres -d learn_pg -Fd -j 4 -f learn_pg_dir

# (4) tar：tar 打包，几乎不用了（不支持 zlib 压缩、不支持并行）
pg_dump -h 127.0.0.1 -U postgres -d learn_pg -Ft -f learn_pg.tar

怎么选？

格式	什么时候用	优点	缺点
`-Fp` plain	小库、需要看 SQL、做 diff	文本可读、可手改	不能并行恢复、文件大
`-Fc` custom	生产首选，单一文件好管理	单文件、自带压缩、`pg_restore -j` 并行	必须 pg_restore 还原
`-Fd` directory	大库、需要并行 dump 才能跑完	`pg_dump -j` 并行加速、还原也支持 `-j`	是个目录，要打 tar 才好 scp
`-Ft` tar	历史遗物	—	不支持压缩、不支持并行

一句话总结：「单库/单表常规备份用 -Fc；超大库要并行 dump 用 -Fd；只有非常特殊场景用 -Fp 或 -Ft」。

14.2.2 关键参数速查

bash

pg_dump \
  -h 127.0.0.1 -p 5432 -U postgres \
  -d learn_pg \                       # 库
  -Fc \                                # 格式
  -f /backup/learn_pg_$(date +%F).dump \
  -j 4 \                               # 并行（仅 -Fd 支持，写在 -F 之前）
  -Z 6 \                               # 压缩级别 0~9
  -t 'public.tickets' \                # 仅某表（可重复）
  -T 'public.audit_*' \                # 排除某些表
  -n 'public' \                        # 仅某 schema
  -N 'tmp_*' \                         # 排除 schema
  --schema-only \                      # 仅结构（无数据）
  --data-only \                        # 仅数据（无结构）
  --no-owner --no-privileges \         # 不导 OWNER/GRANT，便于跨环境恢复
  --serializable-deferrable \          # 用可串行化获取强一致快照（极端正确性）
  --verbose

⚠️ pg_dump 不会备份「全局对象」（角色、表空间、replication slot 等）。要导出角色/全局，用 pg_dumpall -g：
bash
pg_dumpall -h 127.0.0.1 -U postgres --globals-only > globals.sql
1

14.2.3 一致性原理：MVCC 快照

pg_dump 启动后会：

在第一个事务里 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ（或 SERIALIZABLE），拿到一个一致性快照；
之后所有 SELECT 都基于这个快照，相当于「冻结」在那个时刻；
即使备份过程中其他人改数据、删表，pg_dump 也看不见——拿到的是开始时刻的「时光定格」。

并行 dump (-Fd -j N) 还要导出快照到子进程：每个 worker 都用 SET TRANSACTION SNAPSHOT 'XXX' 加入同一个快照，避免不同 worker 看到的数据不一致。

14.2.4 实操：一段真实的 dump 输出

text

$ time pg_dump -h 127.0.0.1 -U postgres -d learn_pg -Fc -f /tmp/learn.dump --verbose
pg_dump: 上次内置的角色名列表的 OID 是 16384
pg_dump: 读取扩展
pg_dump: 读取模式
pg_dump: 读取用户定义的表
pg_dump: 读取用户定义的函数
pg_dump: 读取索引
pg_dump: 读取触发器
pg_dump: 转储数据
pg_dump: 转储 public.tickets 的内容
pg_dump: 转储 public.users 的内容
...
real    0m4.213s
user    0m0.512s
sys     0m0.183s
$ ls -lh /tmp/learn.dump
-rw-r--r-- 1 postgres postgres 12M Apr 17 14:02 /tmp/learn.dump

14.3 逻辑恢复 `pg_restore`

pg_restore 仅用于 custom (-Fc) / directory (-Fd) / tar (-Ft) 格式。 plain (-Fp) 是纯 SQL，直接用 psql -f 灌就行。

14.3.1 基本用法

bash

# 1) 全量恢复
pg_restore -h 127.0.0.1 -U postgres -d learn_pg /tmp/learn.dump

# 2) 并行恢复（推荐，大库可加速 5~10 倍）
pg_restore -h 127.0.0.1 -U postgres -d learn_pg -j 8 /tmp/learn.dump

# 3) 仅恢复某些表
pg_restore -d learn_pg -t tickets -t users /tmp/learn.dump

# 4) 仅恢复结构 / 仅恢复数据
pg_restore -d learn_pg --schema-only /tmp/learn.dump
pg_restore -d learn_pg --data-only   /tmp/learn.dump

# 5) 看 dump 里都有什么（不执行）
pg_restore -l /tmp/learn.dump
# 1234; 1259 16389 TABLE public tickets postgres
# 1235; 0 16389 TABLE DATA public tickets postgres
# 1236; 1259 16400 INDEX public idx_tickets_tenant postgres

# 6) 仅生成 SQL 而不执行（diff 用、人工 review）
pg_restore -f - /tmp/learn.dump > restore.sql

# 7) 用 list 文件做精细化恢复
pg_restore -l /tmp/learn.dump > toc.list
# 编辑 toc.list，把不想恢复的行前面加 ;
pg_restore -L toc.list -d learn_pg /tmp/learn.dump

14.3.2 误删一张表如何救？两套方案

方案 A：从 dump 选择性恢复（要求 dump 时间在事故前）

bash

# 假设有昨晚的备份 yesterday.dump
# 在另一个临时库恢复，再选择 tickets 表
createdb -U postgres tmp_restore
pg_restore -d tmp_restore -t tickets yesterday.dump

# 用 \copy 或 INSERT INTO ... SELECT 把数据搬回生产
psql -d tmp_restore -c "\copy tickets TO '/tmp/tickets.csv' CSV"
psql -d learn_pg    -c "\copy tickets FROM '/tmp/tickets.csv' CSV"

方案 B：PITR 恢复到事故前一秒（见 14.5）

— 这是最优方案，丢数据最少。

14.3.3 一段真实恢复

text

$ pg_restore -h 127.0.0.1 -U postgres -d learn_pg -j 4 /tmp/learn.dump --verbose
pg_restore: 连接到数据库以恢复
pg_restore: 创建 EXTENSION "plpgsql"
pg_restore: 创建 SCHEMA "public"
pg_restore: 创建 TABLE "public.tickets"
pg_restore: 处理 public.tickets 的数据
pg_restore: 创建 INDEX "public.idx_tickets_tenant"  -- 索引最后建（性能更好）
...

14.4 物理备份 `pg_basebackup`

14.4.1 它做了什么

pg_basebackup 通过复制协议连到主库，在线把整个 PGDATA 目录拷一份下来，期间不阻塞业务。它内部用了 pg_backup_start() + pg_backup_stop()（PG 15+，老版本叫 pg_start_backup / pg_stop_backup）做「一致性窗口」。

核心保证：恢复时数据库会从 backup_label 里读到 LSN_start，然后从这个 LSN 开始重放 WAL，直到一致状态——所以备份过程中数据文件被改了也无所谓，WAL 会兜底。

14.4.2 完整命令

bash

# 在备份机上，连主库 10.0.0.10
pg_basebackup \
  -h 10.0.0.10 -p 5432 -U replicator -W \
  -D /backup/base_$(date +%F) \      # 输出目录
  -Ft \                               # tar 格式（更紧凑）；不加就是 plain（目录）
  -X stream \                         # 同步流式拉 WAL（推荐）
  -P \                                # 进度
  -c fast \                           # 立即 CHECKPOINT 而不是等下一次
  -Z 6 \                              # 压缩
  --label='daily_full_$(date +%F)'    # 标签便于识别

-X 三个值：

none：不拉 WAL（备份不完整，几乎不用）；
fetch：备份完后一次性拉 WAL（如果备份过程中产生 WAL 太多，可能在备份完成前 WAL 就被回收，有风险）；
stream ✅：边备份边拉 WAL，推荐。

14.4.3 备份产物

text

/backup/base_2026-04-17/
├── base.tar           # PGDATA 数据文件
├── pg_wal.tar         # 备份期间产生的 WAL
└── backup_manifest    # PG 13+ 自带，校验完整性用

校验完整性：

bash

pg_verifybackup /backup/base_2026-04-17
# backup successfully verified

14.4.4 物理备份的「增量」怎么办？

坏消息：pg_basebackup 不支持原生增量。 好消息：业界两种解法——

「全量 + 持续 WAL 归档」自己搭（PG 内核能力）：每周一次 base backup，期间 WAL 归档存好；恢复时用 base + WAL 重放。这就是 PITR（下一节）。
用 pgBackRest（最广泛使用的第三方工具）：原生支持 --type=full / diff / incr，块级增量。

14.5 PITR：Point-In-Time Recovery 时光机

PITR 是 PG 备份的「最高境界」。学会了这一节，你就能跟老板拍胸脯：「事故前任意一秒都能给你恢复出来」。

14.5.1 生活类比

想象你写日记本：

每周一拍照存档整本日记 →「基础备份 base backup」；
每写一页都同时把这一页的复印件塞进抽屉 →「WAL 归档 archive」；
周三误把日记本撕了 → 拿出周一的存档（base backup），再把抽屉里周一到周三的复印件按顺序一页页加回去（WAL 重放），就能恢复到撕之前的样子。
想恢复到「周三下午 3:14:30」？重放到那个时刻就停止 →「recovery_target_time」。

14.5.2 PITR 全流程图

14.5.3 配置 WAL 归档（前置）

postgresql.conf：

ini

# 一、WAL 配置
wal_level = replica          # 至少 replica；逻辑复制需要 logical
archive_mode = on            # 开启归档
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
# %p = WAL 完整路径（pg_wal/0000000100000000000000A1）
# %f = 文件名（0000000100000000000000A1）
# test ! -f：避免覆盖已存在文件
archive_timeout = 60         # 即使 WAL 没写满，每 60s 强制切一段并归档

# 二、备份保留 WAL
wal_keep_size = 1GB          # 主库至少保留 1GB WAL（防从库追不上）

改完 pg_ctl restart（archive_mode 必须重启才生效）。

自检：

sql

SELECT name, setting FROM pg_settings
 WHERE name IN ('archive_mode','archive_command','wal_level');

SELECT * FROM pg_stat_archiver;
-- archived_count | last_archived_wal | last_archived_time | failed_count | last_failed_wal

failed_count 如果不为 0，说明归档脚本失败过（生产事故源——pg_wal 可能爆盘）。

14.5.4 制作基础备份

bash

pg_basebackup -h 127.0.0.1 -U replicator -D /backup/base_2026-04-17 \
  -Ft -X stream -P -c fast --label='base_2026-04-17'

14.5.5 PITR 恢复演练

假设我们要恢复到 2026-04-17 13:59:59 UTC+8：

bash

# 1. 停掉旧实例（如果还在）
pg_ctl stop -D $PGDATA -m fast

# 2. 备份原 PGDATA（保险）
mv $PGDATA ${PGDATA}.broken

# 3. 解压基础备份到新 PGDATA
mkdir -p $PGDATA && cd $PGDATA
tar -xf /backup/base_2026-04-17/base.tar
mkdir -p pg_wal && tar -xf /backup/base_2026-04-17/pg_wal.tar -C pg_wal/

# 4. 配置恢复（PG 12+，写到 postgresql.auto.conf）
cat >> $PGDATA/postgresql.auto.conf <<'EOF'
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-04-17 13:59:59+08'
recovery_target_action = 'promote'
EOF

# 5. 创建 recovery.signal 文件（PG 12+ 用它代替 recovery.conf）
touch $PGDATA/recovery.signal

# 6. 启动数据库
pg_ctl start -D $PGDATA

# 7. 看日志
tail -f $PGDATA/log/*.log
# LOG: starting point-in-time recovery to 2026-04-17 13:59:59+08
# LOG: restored log file "0000000100000000000000A1" from archive
# LOG: restored log file "0000000100000000000000A2" from archive
# LOG: recovery stopping before commit of transaction 12345, time 2026-04-17 13:59:59.123+08
# LOG: archive recovery complete
# LOG: database system is ready to accept connections

搞定！ 数据库已经恢复到事故前一秒，可以重新对外服务。

⚠️ PG 12 起的重大变化：废弃了 recovery.conf 文件，改成：
恢复参数（restore_command 等）写在 postgresql.conf / postgresql.auto.conf 里；
用 recovery.signal 空文件来「触发恢复模式」；
用 standby.signal 空文件来「触发 standby 模式」（对应原来的 standby_mode = on）。

14.5.6 `recovery_target_*` 五种目标

参数	含义	例子
`recovery_target_time`	恢复到指定时间	`'2026-04-17 13:59:59+08'`
`recovery_target_xid`	恢复到指定事务 ID	`'12345'`
`recovery_target_lsn`	恢复到指定 LSN	`'0/A12B340'`
`recovery_target_name`	恢复到指定 restore point（事先用 `pg_create_restore_point()` 打点）	`'before_release'`
`recovery_target = 'immediate'`	一致性后立即停止	—

recovery_target_action：到达目标后做什么，可选 pause（默认）/ promote/shutdown。生产建议先 pause 验证数据正确，再手动 pg_wal_replay_resume() + 提升。

14.5.7 PITR 时间轴 timeline

每次 PITR 完成 promote 后，PG 会生成一条新的时间线（timeline）。WAL 文件名前 8 个字符就是时间线编号：

text

0000000100000000000000A1   ← timeline 1
0000000200000000000000A1   ← timeline 2 （第一次 PITR 恢复后开启）
0000000300000000000000A1   ← timeline 3 （第二次 PITR 后）

作用：避免 PITR 后产生的 WAL 与原时间线 WAL 冲突。多时间线场景如下：

14.6 延迟从库：另一种「时光机」

如果你预算够，可以专门搭一个延迟 1 小时的从库（standby with delay），用做「反悔窗口」：

ini

# 从库的 postgresql.auto.conf
recovery_min_apply_delay = '1h'

效果：

主库 14:00 误 TRUNCATE，从库 15:00 才会执行这条命令；
这 1 小时窗口内，DBA 可以从从库 pg_dump 受影响的表，复制回主库；
比 PITR 快得多（不用拉基础备份 + 大量 WAL 重放）。

代价：多一台机器、从库始终落后 1h（不能拿来扛只读流量）。

14.7 备份策略与生态工具

14.7.1 三种典型策略

(1) 小库（< 100 GB）：纯 pg_dump 周方案

text

每天 02:00 全量 pg_dump -Fc -j 4 → S3
保留 30 天 → 旧文件转冷存储
RPO 24h, RTO ~30 分钟

(2) 中等库（100 GB~1 TB）：物理 + WAL

text

每周日 pg_basebackup → /backup/weekly/
每分钟 archive_command 把 WAL 推到 S3
保留：基础备份 4 份 + WAL 30 天
RPO ≈ 1 分钟, RTO 1~3 小时

(3) 大库（> 1 TB）：pgBackRest

text

pgBackRest，支持：
  - 块级增量备份（每天增量、每周差异、每月全量）
  - S3 / Azure / GCS 直存
  - 加密、压缩、并行
  - 多副本备份保留策略
RPO < 1 分钟, RTO < 1 小时

14.7.2 主流第三方工具对比

工具	增量	PITR	云存储	加密	复杂度	适合
自带 pg_basebackup + WAL	❌	✅	自己 ship	自己加	★★	中小规模
pgBackRest	✅ 块级	✅	✅ S3/GCS/Azure	✅	★★★	大库首选
Barman	✅ rsync	✅	❌（自家盘）	❌	★★	经典 EU 用得多
WAL-G	✅ delta	✅	✅ 云原生	✅	★★	云上首选
pg_probackup	✅ ptrack	✅	部分	✅	★★★	Postgres Pro 系

学习路径建议：先掌握自带的 pg_dump / pg_basebackup / WAL 归档，把 PITR 流程跑通；上生产再选 pgBackRest 或 WAL-G。

14.7.3 `pgBackRest` 一瞥

ini

# /etc/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=4
repo1-retention-diff=7
process-max=4

[demo]
pg1-path=/var/lib/postgresql/16/main

bash

pgbackrest --stanza=demo stanza-create
pgbackrest --stanza=demo --type=full backup
pgbackrest --stanza=demo --type=incr backup
pgbackrest --stanza=demo info
pgbackrest --stanza=demo --type=time --target='2026-04-17 14:00:00' restore

14.8 备份恢复演练 SOP（生产必做）

text

[每月一次]
□ 1. 拉一份当月最新基础备份 + 期间 WAL 到「演练机」
□ 2. 解压、配置 recovery.signal + restore_command
□ 3. 选一个事故前时间点，启动 PITR
□ 4. promote 后跑 SQL 校验：
       SELECT count(*) FROM critical_table;
       SELECT max(created_at) FROM events;
□ 5. 执行业务侧 smoke test（登录、下单 …）
□ 6. 记录：恢复耗时、成功率、踩坑记录到知识库
□ 7. 销毁演练实例

行业事故警示：某大厂误删生产库，备份天天跑，但从未恢复演练过——结果发现备份脚本三个月前就因为权限问题拷了空文件。没演练过的备份等于没备份。

14.9 常见踩坑

坑 1：归档脚本失败导致 pg_wal 爆盘

现象：pg_wal/ 目录占满磁盘，数据库写入挂起。原因：archive_command 失败时，PG 不会丢弃 WAL（保证可恢复），WAL 会无限堆积直到磁盘满。预防：

archive_command 必须返回 0（成功）/ 非 0（失败），写好 retry 与告警；
监控 pg_stat_archiver.failed_count、pg_wal/ 大小；
紧急时可临时把 archive_command 设为 /bin/true（会丢数据），只为救活实例。

坑 2：忘备角色和全局对象

bash

# 错误：只 dump 了库，恢复时报 ERROR: role "alice" does not exist
pg_dump -d learn_pg -Fc -f learn.dump

# 正确：单独 dump 全局
pg_dumpall --globals-only > globals.sql
# 恢复时先灌全局，再恢复库
psql -f globals.sql && pg_restore -d learn_pg learn.dump

坑 3：跨大版本恢复

pg_dump 兼容跨大版本（旧 dump → 新库 OK，新 dump → 旧库不一定）。物理备份完全不能跨大版本。要跨大版本升级用 pg_upgrade（in-place）或 pg_dumpall + psql 重灌。

坑 4：`pg_basebackup` 期间主库 WAL 被回收

现象：备份做到一半报 requested WAL segment has already been removed。原因：用了 -X fetch，但备份耗时太长，WAL 被回收。解决：用 -X stream（同步拉 WAL）或调高 wal_keep_size、配 replication slot。

坑 5：PITR 跨时间线

如果你之前已经 PITR 过一次（比如恢复到了 timeline 2），再做 PITR 想到 timeline 1 的某个时间点，需要 recovery_target_timeline = '1' 显式指定，否则默认走最新 timeline。

坑 6：`pg_dump` 时锁

pg_dump 在每张表上拿 ACCESS SHARE 锁——和 DDL 互斥。如果备份期间有人 ALTER TABLE，会互相等待。生产建议在低峰期做，并配合 lock_timeout。

14.10 与 MySQL 对比速查

主题	MySQL	PostgreSQL
逻辑备份	mysqldump / mydumper	pg_dump / pg_dumpall
全局对象	mysqldump --all-databases	pg_dumpall --globals-only
物理备份	xtrabackup（Percona）	pg_basebackup（自带）
增量备份	xtrabackup `--incremental` 块级	pg_basebackup ❌ 无原生；pgBackRest ✅
PITR	binlog 重放（mysqlbinlog）	WAL 重放（restore_command）
时间点指定	`--stop-datetime`	`recovery_target_time`
一致性	`--single-transaction`	默认 REPEATABLE READ 快照
跨大版本	dump 可，物理不行	同上

14.11 小结

逻辑备份轻便、可读、跨版本；物理备份快、整库一致、能 PITR。
pg_dump 首选 -Fc 格式，超大库加 -Fd -j N 并行 dump。
pg_dumpall -g 别忘，否则恢复时角色全没。
pg_basebackup -X stream -P -c fast 是物理备份标准姿势。
PITR = 基础备份 + 持续 WAL 归档 + restore_command + recovery_target_*。
PG 12 起，恢复用 recovery.signal + postgresql.auto.conf，不再有 recovery.conf。
生产推荐 pgBackRest，云上推荐 WAL-G。
备份必须演练；归档目录必须监控；磁盘必须有报警。

🎮 配套演示

用浏览器打开 ./14_backup_recovery/demo.html，跟着可视化动画再走一遍本章核心概念。
配套代码在 ./14_backup_recovery/code/，每个脚本都可以独立 bash xxx.sh（或 python xxx.py）运行，先跑 init.sql 准备数据。

14.12 面试高频题

Q1. `pg_dump` 和 `pg_basebackup` 的本质区别？分别适合什么场景？(⭐⭐⭐)

考察点：逻辑 vs 物理、PITR、跨版本。

答案：

pg_dump 是逻辑备份——它通过 SQL 协议读出每行数据，导出 CREATE TABLE + COPY / INSERT。pg_basebackup 是物理备份——它通过复制协议把整个 PGDATA 目录的二进制文件拷下来。

维度	pg_dump	pg_basebackup
备份内容	SQL 文本/二进制	数据文件 + WAL
大小	小（无索引数据）	大（含索引/冗余空间）
跨大版本	✅ 兼容	❌ 必须同版本
跨平台	✅	❌ 字节序、ABI 必须一致
选择性	✅ 单库/单表/单 schema	❌ 整集群
PITR	❌ 只能到 dump 那一刻	✅ + WAL 归档
速度	慢（O(行数)）	快（O(字节数)）
恢复速度	慢（INSERT + 重建索引）	快（拷回去启动）

场景对照：

跨版本迁移、误删表恢复、抽样导出 → pg_dump；
生产全库灾备、PITR、主从复制初始化、TB 级大库 → pg_basebackup（或 pgBackRest）。

易错点：很多人以为 pg_dump 是「热备」、pg_basebackup 是「冷备」——其实两者都是热备，pg_basebackup 通过 START_BACKUP/STOP_BACKUP 拿到一致性窗口，业务不停机。

与 MySQL 对比：mysqldump ↔ pg_dump，xtrabackup ↔ pg_basebackup。

Q2. `pg_dump` 的 -Fp / -Fc / -Fd / -Ft 四种格式的差异？(⭐⭐)

考察点：dump 格式、并行能力、压缩。

答案：

格式	全称	文件形式	压缩	并行 dump	并行 restore	选择性 restore
`-Fp` plain	纯 SQL 文本	单文件，可 vim	自己 gzip	❌	❌（直接 psql -f）	❌（手编辑文件）
`-Fc` custom	自定义二进制	单文件	✅ 内置 zlib	❌	✅ `-j N`	✅ `-t / -L`
`-Fd` directory	目录	一个目录，每表一个文件	✅	✅ `-j N`	✅ `-j N`	✅
`-Ft` tar	tar 包	单文件	❌	❌	❌	✅

怎么选：

单库常规备份 → -Fc（生产首选）；
TB 级大库 dump 太慢 → -Fd -j 8 并行；
想看 SQL / 做 git diff → -Fp；
-Ft 几乎不用。

关键细节：

-Fp 不能用 pg_restore 还原，要 psql -f；
并行 dump（-j）只支持 -Fd，因为只有目录格式每张表一个文件，可以并发写；
-Fc 也支持选择性恢复（-t、-L），不是 -Fd 独占。

Q3. PITR 是怎么实现的？需要哪些前置配置？(⭐⭐⭐⭐)

考察点：PITR 全套流程、archive_mode、restore_command、recovery_target。

答案：

PITR（Point-In-Time Recovery）= 基础备份 + 持续 WAL 归档 + 重放到指定时点。

前置配置：

ini

wal_level = replica           # WAL 级别
archive_mode = on             # 开启归档
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_timeout = 60          # 强制 60s 切一次

改完 pg_ctl restart（archive_mode 必须重启）。

流程：

text

1. 用 pg_basebackup 拉一次基础备份 → /backup/base/
2. 业务运行期间，PG 自动调用 archive_command 把 WAL 推到 /archive/
3. 事故发生（比如 14:00 被 DROP TABLE）
4. 准备一个空 PGDATA：
   - 把基础备份 base.tar 解压进去
   - 把基础备份的 pg_wal.tar 解压到 pg_wal/
5. 配置（PG 12+ 写到 postgresql.auto.conf）：
     restore_command = 'cp /archive/%f %p'
     recovery_target_time = '2026-04-17 13:59:59+08'
     recovery_target_action = 'promote'
6. touch recovery.signal
7. pg_ctl start
   PG 进入 recovery 模式：
   - 从 backup_label 读起始 LSN
   - 调 restore_command 拉 WAL 段
   - 一段段重放，直到 13:59:59
   - 触发 promote，写新 timeline，对外可写

关键点：

recovery.signal 是 PG 12+ 替代 recovery.conf 的方式（standby.signal 替代 standby_mode）；
recovery_target_* 五种目标：time/xid/lsn/name/immediate；建议先 recovery_target_action = pause 验证数据后再 promote；
promote 后会生成新 timeline（WAL 文件名前 8 位变化），避免与原 WAL 冲突；
备份过程中数据文件被改了无所谓，恢复时 WAL 会修复一致性。

与 MySQL 对比：MySQL PITR = mysqldump/xtrabackup 全量 + binlog 重放（mysqlbinlog --stop-datetime），思想完全一致；PG 用的是 WAL，更细粒度（物理日志），重放永远幂等。

Q4. 物理备份过程中，pg_basebackup 怎么保证备份一致性？(⭐⭐⭐⭐)

考察点：start_backup / stop_backup、backup_label、WAL 兜底。

答案：

pg_basebackup 通过下面四步保证一致性：

pg_backup_start('label')（PG 15+ 名字；老版本 pg_start_backup）：
- 强制触发一次 CHECKPOINT；
- 记录起始 LSN（LSN_start）；
- 写一个 backup_label 文件，里面有 LSN_start 和 backup_label 名。
复制数据文件：通过复制协议把 PGDATA 流式发给客户端。期间业务正常运行，任何块都可能被改。
-X stream 同步流式拉 WAL：客户端同时开第二个连接，把备份期间产生的 WAL 一并拉到 pg_wal/。这是「兜底」的关键。
pg_backup_stop()：返回 LSN_stop，把 backup_label 写完整。

恢复时：

PG 启动，看到 backup_label，知道这是个未完成一致性的 backup；
从 LSN_start 开始重放 pg_wal/ 里的 WAL；
重放到至少 LSN_stop 才达到一致性；
之后才允许查询/可写。

所以：「数据文件被改了」根本不重要——WAL 里记录了所有改动，重放时会把数据文件「修复」到一致状态。这就是物理备份能在线、不停机的核心原理。

易错点：

用 -X fetch（备份完后一次性拉 WAL）有风险——备份太久 WAL 被回收。生产必用 -X stream。
用 cp -r 自己拷 PGDATA + 不走 START_BACKUP 协议——绝对不行，备份的数据文件不一致，无法恢复。要么走 pg_basebackup，要么手工 SELECT pg_backup_start(...) + 拷文件 + SELECT pg_backup_stop()。

Q5. 误删一张大表如何恢复？给两种方案。(⭐⭐⭐)

考察点：选择性恢复 vs PITR、利弊权衡。

答案：

方案 A：从昨晚的 pg_dump 选择性恢复

bash

# 1. 在临时库恢复（不要直接对生产）
createdb -U postgres tmp_restore
pg_restore -d tmp_restore -t big_table /backup/yesterday.dump

# 2. 把数据搬回生产
psql -d tmp_restore -c "\copy big_table TO '/tmp/data.csv' CSV"
psql -d learn_pg    -c "TRUNCATE big_table; \copy big_table FROM '/tmp/data.csv' CSV"
# 或用 pg_dump --data-only + psql 直接 INSERT

优点：操作简单，不影响其它表；
缺点：丢数据 = 「dump 时刻 → 误删时刻」之间的所有变更（通常 24h）。

方案 B：PITR 恢复到误删前一秒

bash

# 1. 在演练机/新实例做完整 PITR（见 14.5.5）
# 2. recovery_target_time = '误删前 1s'
# 3. promote 后，从这个临时实例 pg_dump -t big_table，灌回生产

优点：丢数据最少（< 1 分钟）；
缺点：操作复杂，需要 30min~几小时；需要事前已开 archive_mode 并保留 WAL。

生产实战决策树：

text

事故发生
  │
  ├─ 误删的是「无关紧要的临时表」 → 方案 A（快）
  ├─ 关键业务表，丢 24h 数据扛得住 → 方案 A
  └─ 关键业务表，丢一秒都不行 → 方案 B（PITR）
                              ↓
                  事前没开 archive_mode？ → 凉凉，只能方案 A

第三种隐藏方案：如果你部署了延迟从库（recovery_min_apply_delay = '1h'），还可以从从库直接 dump 出 1 小时前的表，比 PITR 快得多。

与 MySQL 对比：MySQL 用 binlog 反向解析也能做类似的「精确恢复某一行」，但没有 PG 的 RLS/PITR 一气呵成方便。

Q6. 备份策略「全量 + WAL 归档」中，归档文件怎么管理才不爆盘？(⭐⭐⭐)

考察点：archive_cleanup、pg_archivecleanup、保留策略。

答案：

「全量 + WAL 归档」的痛点是 WAL 永远在产生，归档目录会无限增长。三种主流处理方式：

(1) pg_archivecleanup（PG 自带工具）

bash

# 删除 /archive 中比 0000000100000000000000A0 更早的 WAL
pg_archivecleanup /archive 0000000100000000000000A0

判断「更早」用的是文件名字典序，等价于 LSN 顺序。典型用法：每次做完新的基础备份后，把比这个备份起始 LSN 更早的 WAL 全删掉（之前的 WAL 已经没用了，再恢复也是基于新基础备份）。

(2) 备份保留策略 + 定时清理

bash

# crontab：每天清理 30 天前的归档
find /archive -mtime +30 -name '0*' -delete

# 同时保留最近 4 份基础备份
ls -t /backup/base_* | tail -n +5 | xargs rm -rf

(3) 用 pgBackRest / WAL-G 自动管理

ini

# pgBackRest
repo1-retention-full=4         # 保留 4 个全量
repo1-retention-archive=4      # 保留 4 个全量的 WAL（更早的自动清）
repo1-retention-archive-type=full

pgbackrest expire 会按策略删除过期备份和对应的 WAL。

重要原则：

WAL 必须能与至少一个有效基础备份配对。删 WAL 之前，必须确认有比它更新的基础备份；
删 WAL 时优先用 pg_archivecleanup，按 LSN 安全删除，不要用 mtime 暴力删（可能误删未配对的）；
监控 pg_stat_archiver.failed_count 和 /archive 占用率，配告警；
上 S3 / 对象存储可以用生命周期规则自动转冷存储/删除。

坑：

如果 archive_command 失败，PG 会无限堆积 WAL 在 pg_wal/ 里直到磁盘满（不是归档目录满）。所以 archive_command 必须严格返回正确退出码，并配监控；
紧急时可以临时把 archive_command='/bin/true' 救命，但会丢这段时间的 PITR 能力，事后必须立即做新基础备份。

Q7. `pg_dump --jobs` 并行 dump 的内部原理？为什么只支持 directory 格式？(⭐⭐⭐)

考察点：MVCC 快照同步、IO 模型、目录格式特性。

答案：

pg_dump -j N 的并行用法：

bash

pg_dump -h db -U postgres -d learn_pg -Fd -j 8 -f /backup/dir

实现原理：

主进程开第一个事务：

sql

BEGIN ISOLATION LEVEL REPEATABLE READ;  -- 拿一个一致性快照
SELECT pg_export_snapshot();             -- 导出快照 ID 给 worker

主进程 fork N 个 worker 子进程，每个 worker 开自己的连接和事务：
sql
```
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '<导出的快照 ID>';   -- 加入主进程的快照
```
1
2
这样所有 worker 看到完全相同的「时光定格」，备份出的多张表是同一时刻的一致性视图。
每个 worker 各自负责一组表，并发 COPY tableX TO STDOUT 把数据写到 directory 格式的不同文件里。

为什么只支持 -Fd？

-Fp、-Fc、-Ft 都是单一输出文件，多 worker 同时写一个文件需要复杂的字节锁/拼接，PG 没实现；
-Fd 是个目录，每张表一个文件，N 个 worker 写 N 个不同文件，IO 互不干扰，实现简单且快。

性能收益：

IO 瓶颈型场景（机械盘）收益有限，可能 1.5~2 倍；
SSD + 多核场景收益明显，可达 5~10 倍；
不要把 -j 设过大（如 32），因为每个 worker 会 fork 一个 backend 进程，对主库会施加显著 CPU 压力。

配套并行 restore：

bash

pg_restore -d learn_pg -j 8 /backup/learn.dump

恢复时也是多个 worker 并发跑 INSERT 和 CREATE INDEX，索引建立这一步收益最大。

易错点：

用 -j dump 时，所有 worker 共享一个导出快照，如果备份时间超过 idle_in_transaction_session_timeout，事务被踢，备份会失败。生产 dump 大库前要把这个超时调大或设 0；
-j 的 worker 数和 PG 主库 max_connections 互斥占用，要预留余量。

🔗 延伸阅读

第 10 章 WAL 与 checkpoint：WAL 是 PITR 的物理基石，搞懂 WAL 才能调好归档与回放。
第 15 章复制与高可用：物理基础备份既能还原也能用来快速搭建从库。
第 17 章性能调优：备份窗口对生产读写、checkpoint、I/O 抖动的影响及缓解方案。

本章完。下一章 15_replication.md：复制与高可用——流复制原理、同步 / 异步、Patroni 自动切主。

🎬 可视化演示

演示加载缓慢或样式异常？点此在新标签页打开 ↗

💻 示例代码

bash

#!/usr/bin/env bash
# ============================================================
# 第 14 章 · 演示 1：pg_dump 四种格式 + 仅 schema / 仅 data
# ------------------------------------------------------------
# 目的：把 learn_pg 库分别用 plain / custom / directory / tar
#       四种格式各导出一份，并对比文件大小、耗时；
#       再演示「仅结构」「仅数据」「按表过滤」用法。
#
# 前置：
#   - 已运行 ../init.sql 准备好 ch14_users / ch14_orders 等表
#   - psql / pg_dump 在 PATH 中
#   - 默认连接：host=127.0.0.1 port=5432 dbname=learn_pg user=postgres
#     如果用密码连接，可在 ~/.pgpass 写：127.0.0.1:5432:learn_pg:postgres:xxx
#     或 export PGPASSWORD=xxx
#
# 用法：
#   bash 01_pg_dump_examples.sh
# ============================================================
set -euo pipefail

PGHOST=${PGHOST:-127.0.0.1}
PGPORT=${PGPORT:-5432}
PGDATABASE=${PGDATABASE:-learn_pg}
PGUSER=${PGUSER:-postgres}
export PGHOST PGPORT PGDATABASE PGUSER

BACKUP_DIR=${BACKUP_DIR:-/tmp/pg_dump_demo}
mkdir -p "$BACKUP_DIR"
rm -rf "$BACKUP_DIR"/*

bar() { echo -e "\n============================================================\n$1\n============================================================"; }

bar "Step 0：基础信息"
psql -c "SELECT version();" -c "SELECT pg_size_pretty(pg_database_size('${PGDATABASE}')) AS db_size;"

bar "Step 1：plain (-Fp) → 纯 SQL 文本"
time pg_dump -Fp -f "$BACKUP_DIR/learn.sql"
ls -lh "$BACKUP_DIR/learn.sql"
echo "---- 前 30 行预览 ----"
head -n 30 "$BACKUP_DIR/learn.sql"

bar "Step 2：custom (-Fc) → 自定义二进制（生产首选）"
time pg_dump -Fc -Z 6 -f "$BACKUP_DIR/learn.dump"
ls -lh "$BACKUP_DIR/learn.dump"
echo "---- 用 pg_restore -l 看目录 ----"
pg_restore -l "$BACKUP_DIR/learn.dump" | head -n 20

bar "Step 3：directory (-Fd) + 并行 dump (-j 4)"
time pg_dump -Fd -j 4 -Z 6 -f "$BACKUP_DIR/learn_dir"
ls -lh "$BACKUP_DIR/learn_dir/" | head
du -sh "$BACKUP_DIR/learn_dir"

bar "Step 4：tar (-Ft) （历史遗物，仅作展示）"
time pg_dump -Ft -f "$BACKUP_DIR/learn.tar"
ls -lh "$BACKUP_DIR/learn.tar"

bar "Step 5：仅 schema（--schema-only）"
pg_dump -Fp --schema-only -f "$BACKUP_DIR/schema_only.sql"
echo "字符数:"; wc -c "$BACKUP_DIR/schema_only.sql"
grep -c "^CREATE TABLE" "$BACKUP_DIR/schema_only.sql" \
    | xargs -I{} echo "包含 CREATE TABLE 数量: {}"

bar "Step 6：仅数据（--data-only）"
pg_dump -Fp --data-only -f "$BACKUP_DIR/data_only.sql"
echo "字符数:"; wc -c "$BACKUP_DIR/data_only.sql"

bar "Step 7：按表过滤 (-t / -T) 与按 schema 过滤 (-n / -N)"
pg_dump -Fc -t 'public.ch14_users' -t 'public.ch14_orders' \
    -f "$BACKUP_DIR/users_orders.dump"
echo "users_orders.dump 内容:"
pg_restore -l "$BACKUP_DIR/users_orders.dump"

bar "Step 8：导出全局对象（角色/表空间）"
pg_dumpall --globals-only -f "$BACKUP_DIR/globals.sql"
echo "globals.sql 行数:"; wc -l "$BACKUP_DIR/globals.sql"
echo "前 10 行:"; head -n 10 "$BACKUP_DIR/globals.sql"

bar "Step 9：四种格式大小对比"
ls -lhS "$BACKUP_DIR"/learn.sql "$BACKUP_DIR"/learn.dump \
        "$BACKUP_DIR"/learn.tar "$BACKUP_DIR"/learn_dir/

bar "完成！备份文件位于 $BACKUP_DIR"
echo "下一个脚本 02_pg_restore_demo.sh 会用 learn.dump 来演示 pg_restore"

bash

#!/usr/bin/env bash
# ============================================================
# 第 14 章 · 演示 2：pg_restore 全场景
# ------------------------------------------------------------
# 演示：
#   ① 从 custom (-Fc) 备份并行恢复到一个临时库
#   ② 仅恢复部分表 (-t)
#   ③ 仅生成 SQL 而不执行 (-f -)
#   ④ 用 list 文件做精细化恢复 (-L toc.list)
#
# 前置：
#   - 先跑 01_pg_dump_examples.sh，会在 /tmp/pg_dump_demo 生成 learn.dump
#
# 用法：
#   bash 02_pg_restore_demo.sh
# ============================================================
set -euo pipefail

PGHOST=${PGHOST:-127.0.0.1}
PGPORT=${PGPORT:-5432}
PGUSER=${PGUSER:-postgres}
export PGHOST PGPORT PGUSER

DUMP_FILE=${DUMP_FILE:-/tmp/pg_dump_demo/learn.dump}
TARGET_DB=${TARGET_DB:-learn_pg_restore}

bar() { echo -e "\n============================================================\n$1\n============================================================"; }

if [[ ! -f "$DUMP_FILE" ]]; then
  echo "❌ 找不到 $DUMP_FILE，请先运行 01_pg_dump_examples.sh"
  exit 1
fi

bar "Step 0：准备一个全新的目标库 $TARGET_DB"
psql -d postgres -c "DROP DATABASE IF EXISTS ${TARGET_DB};"
psql -d postgres -c "CREATE DATABASE ${TARGET_DB};"

bar "Step 1：用 -j 4 并行恢复整个备份"
time pg_restore -d "$TARGET_DB" -j 4 --verbose "$DUMP_FILE" 2>&1 | tail -n 20
psql -d "$TARGET_DB" -c "
  SELECT 'users' tbl, count(*) FROM ch14_users
  UNION ALL SELECT 'products', count(*) FROM ch14_products
  UNION ALL SELECT 'orders',   count(*) FROM ch14_orders
  UNION ALL SELECT 'items',    count(*) FROM ch14_order_items;"

bar "Step 2：把 $TARGET_DB 重置后，仅恢复 ch14_users + ch14_orders 两张表"
psql -d postgres -c "DROP DATABASE ${TARGET_DB};"
psql -d postgres -c "CREATE DATABASE ${TARGET_DB};"
pg_restore -d "$TARGET_DB" -t ch14_users -t ch14_orders --verbose "$DUMP_FILE" 2>&1 | tail -n 10
psql -d "$TARGET_DB" -c "\dt"

bar "Step 3：仅生成 SQL 而不执行（-f - → 标准输出）"
pg_restore -f - "$DUMP_FILE" | head -n 25
echo "↑ 这里只是预览，可以重定向到文件做 review，例如："
echo "    pg_restore -f restore.sql $DUMP_FILE"

bar "Step 4：用 list 文件 (-L) 做精细化恢复"
TOC_FILE=/tmp/pg_dump_demo/toc.list
pg_restore -l "$DUMP_FILE" > "$TOC_FILE"
echo "原始 toc.list 内容（前 20 行）:"
head -n 20 "$TOC_FILE"

# 把 ch14_orders 那一行注释掉（前面加分号）
echo
echo "现在把所有包含 'TABLE DATA public ch14_orders' 的行注释掉，模拟「不恢复 orders 数据」"
sed -i.bak 's/^\(.*TABLE DATA public ch14_orders \)/;\1/' "$TOC_FILE"

psql -d postgres -c "DROP DATABASE ${TARGET_DB};"
psql -d postgres -c "CREATE DATABASE ${TARGET_DB};"
pg_restore -L "$TOC_FILE" -d "$TARGET_DB" --verbose "$DUMP_FILE" 2>&1 | tail -n 10

echo
echo "验证：ch14_users / ch14_products 应该有数据，ch14_orders 应该是空的（仅结构）"
psql -d "$TARGET_DB" -c "
  SELECT 'users' tbl, count(*) FROM ch14_users
  UNION ALL SELECT 'products', count(*) FROM ch14_products
  UNION ALL SELECT 'orders',   count(*) FROM ch14_orders
  UNION ALL SELECT 'items',    count(*) FROM ch14_order_items;"

bar "Step 5：清理"
psql -d postgres -c "DROP DATABASE ${TARGET_DB};"
echo "完成。"

bash

#!/usr/bin/env bash
# ============================================================
# 第 14 章 · 演示 3：完整 PITR 流程脚本
# ------------------------------------------------------------
# 这是一个「教学用」端到端 PITR 演练脚本，会在「同一台主机」上
# 启动一个独立 PG 实例，做：
#   1) 配置 archive_mode + archive_command
#   2) 跑业务（写入数据 → 模拟事故 DROP TABLE）
#   3) 用 pg_basebackup 拉一次基础备份
#   4) 继续业务，产生新 WAL
#   5) 模拟事故时间点 T_BAD
#   6) 用 PITR 恢复到 T_BAD - 1s
#
# ⚠️ 重要：脚本需要的环境
#   - 必须以 postgres OS 用户（或有写权限的用户）运行
#   - 需要 initdb / pg_ctl / pg_basebackup 在 PATH 中
#   - 需要写入临时目录的权限（默认 /tmp/pitr_demo）
#   - 不会影响你的主 PG 实例（用独立端口 5499）
#
# 用法：
#   bash 03_basebackup_pitr.sh
#
# 如果你不想真实跑（比如没有 root/initdb 权限），
# 可以只阅读注释，理解 PITR 全流程。
# ============================================================
set -euo pipefail

WORK_DIR=${WORK_DIR:-/tmp/pitr_demo}
PGDATA="$WORK_DIR/data"
ARCHIVE="$WORK_DIR/archive"
BACKUP="$WORK_DIR/backup"
PORT=${PITR_PORT:-5499}

bar() { echo -e "\n============================================================\n$1\n============================================================"; }

cleanup() {
  bar "清理：停掉演示实例并删除目录"
  pg_ctl stop -D "$PGDATA" -m immediate >/dev/null 2>&1 || true
  rm -rf "$WORK_DIR"
}

trap 'echo "[!] 出错，请查看 $PGDATA/log/ 下日志"' ERR

# ------------------------------------------------------------
# Step 0：彻底清理上一次留下的环境
# ------------------------------------------------------------
cleanup
mkdir -p "$ARCHIVE" "$BACKUP"

# ------------------------------------------------------------
# Step 1：initdb 创建一个新实例
# ------------------------------------------------------------
bar "Step 1：initdb 创建新实例（端口 $PORT）"
initdb -D "$PGDATA" --auth-local=trust --auth-host=trust -U postgres -E UTF8 --locale=C

# 改 postgresql.conf：开归档、用本地 socket、监听独立端口
cat >> "$PGDATA/postgresql.conf" <<EOF

# === PITR 演示配置 ===
port = $PORT
unix_socket_directories = '$WORK_DIR'
listen_addresses = ''

wal_level = replica
archive_mode = on
archive_command = 'test ! -f $ARCHIVE/%f && cp %p $ARCHIVE/%f'
archive_timeout = 30
wal_keep_size = 64MB

logging_collector = on
log_directory = 'log'
log_filename = 'postgres-%Y-%m-%d.log'
log_min_messages = info
EOF

mkdir -p "$PGDATA/log"

# ------------------------------------------------------------
# Step 2：启动实例，建库 + 跑些初始数据
# ------------------------------------------------------------
bar "Step 2：启动实例，建库写初始数据"
pg_ctl start -D "$PGDATA" -l "$PGDATA/log/start.log" -w

PSQL="psql -h $WORK_DIR -p $PORT -U postgres"
$PSQL -d postgres -c "CREATE DATABASE pitr_demo;"
$PSQL -d pitr_demo -c "
  CREATE TABLE money_log (
    id BIGSERIAL PRIMARY KEY,
    msg TEXT,
    amount NUMERIC,
    created_at TIMESTAMPTZ DEFAULT now()
  );
  INSERT INTO money_log(msg, amount)
  SELECT '初始数据 ' || g, 100*g
  FROM generate_series(1,5) g;
"
$PSQL -d pitr_demo -c "SELECT count(*) AS init_rows FROM money_log;"

# ------------------------------------------------------------
# Step 3：基础备份
# ------------------------------------------------------------
bar "Step 3：pg_basebackup 拉一次基础备份"
rm -rf "$BACKUP"/*
pg_basebackup -h "$WORK_DIR" -p "$PORT" -U postgres \
              -D "$BACKUP" -Ft -X stream -P -c fast --label='pitr_demo_base'
echo "备份产物："
ls -lh "$BACKUP"

# ------------------------------------------------------------
# Step 4：继续业务，写一些「重要数据」
# ------------------------------------------------------------
bar "Step 4：备份后继续写入「重要数据」（10 行）"
$PSQL -d pitr_demo -c "
  INSERT INTO money_log(msg, amount)
  SELECT '重要交易 ' || g, 1000*g
  FROM generate_series(1,10) g;
"
$PSQL -d pitr_demo -c "SELECT count(*) AS after_backup_rows FROM money_log;"

# 强制切一段 WAL 触发归档
$PSQL -d pitr_demo -c "SELECT pg_switch_wal();"
sleep 2

# ------------------------------------------------------------
# Step 5：记录「事故时间点」 T_BAD
# ------------------------------------------------------------
sleep 1
T_BAD=$($PSQL -d pitr_demo -tA -c "SELECT now()")
echo "📌 记录事故前时间 T_BAD = $T_BAD"
sleep 2

# ------------------------------------------------------------
# Step 6：模拟事故 —— DROP TABLE
# ------------------------------------------------------------
bar "Step 6：模拟事故：DROP TABLE money_log"
$PSQL -d pitr_demo -c "DROP TABLE money_log;"
$PSQL -d pitr_demo -c "\dt"

# 强切 WAL 把 DROP 这条记录归档
$PSQL -d pitr_demo -c "SELECT pg_switch_wal();"
sleep 2

# ------------------------------------------------------------
# Step 7：停库，准备 PITR
# ------------------------------------------------------------
bar "Step 7：停掉实例，开始 PITR 恢复到 $T_BAD"
pg_ctl stop -D "$PGDATA" -m fast -w

# 备份原数据目录便于对比
mv "$PGDATA" "${PGDATA}.broken"

# 解压基础备份到新 PGDATA
mkdir -p "$PGDATA/pg_wal"
tar -xf "$BACKUP/base.tar"   -C "$PGDATA"
tar -xf "$BACKUP/pg_wal.tar" -C "$PGDATA/pg_wal/"

# 配置恢复
cat >> "$PGDATA/postgresql.auto.conf" <<EOF

# === PITR 恢复配置 ===
restore_command          = 'cp $ARCHIVE/%f %p'
recovery_target_time     = '$T_BAD'
recovery_target_action   = 'promote'
EOF

# 创建 recovery.signal（PG 12+ 用法，替代 recovery.conf）
touch "$PGDATA/recovery.signal"

mkdir -p "$PGDATA/log"

# ------------------------------------------------------------
# Step 8：启动并观察 recovery
# ------------------------------------------------------------
bar "Step 8：启动 PG，自动重放 WAL 直到 T_BAD"
pg_ctl start -D "$PGDATA" -l "$PGDATA/log/start.log" -w

echo "---- 启动日志（最后 30 行） ----"
tail -n 30 "$PGDATA/log"/postgres-*.log || true

# 等 promote 完成
sleep 3

bar "Step 9：验证恢复结果"
echo "money_log 应该已经回来，行数应该是 5（初始）+ 10（重要） = 15："
$PSQL -d pitr_demo -c "SELECT count(*) AS recovered_rows FROM money_log;"
$PSQL -d pitr_demo -c "SELECT id, msg, amount FROM money_log ORDER BY id LIMIT 5;"

bar "🎉 PITR 演示完成"
echo "  - 事故前时间 T_BAD : $T_BAD"
echo "  - WAL 归档目录     : $ARCHIVE （内有 $(ls -1 $ARCHIVE | wc -l) 个 WAL）"
echo "  - 旧坏 PGDATA     : ${PGDATA}.broken （已保留供对比）"
echo
echo "若要清理本次演示："
echo "    pg_ctl stop -D $PGDATA -m fast"
echo "    rm -rf $WORK_DIR"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195

python

"""
第 14 章 · 演示 4：用 Python 调度 pg_dump 做定时备份
======================================================

场景：用 psycopg + subprocess 实现一个「生产级备份脚本」，特点：
  1) 自动按日期建子目录 /backup/2026-04-17/
  2) 用 -Fd -j N 并行 dump
  3) 校验 dump 完整性（pg_restore -l）
  4) 写入备份元信息到一张审计表 ch14_backup_history
  5) 自动清理 N 天前的旧备份
  6) 自动 dump 全局对象 (pg_dumpall -g)

依赖：
    pip install "psycopg[binary]>=3.1"

用法：
    python 04_dump_with_python.py --dest /tmp/pg_backup --jobs 4 --keep 7

可放进 cron：
    0 2 * * *  /usr/bin/python /path/to/04_dump_with_python.py --dest /backup --keep 30
"""

from __future__ import annotations

import argparse
import datetime as dt
import os
import shutil
import subprocess
import sys
from pathlib import Path

import psycopg

DEFAULT_DSN = "host=127.0.0.1 port=5432 dbname=learn_pg user=postgres"
HISTORY_TABLE_DDL = """
    CREATE TABLE IF NOT EXISTS ch14_backup_history (
        id          BIGSERIAL PRIMARY KEY,
        backup_name TEXT NOT NULL,
        backup_path TEXT NOT NULL,
        format      TEXT,
        size_bytes  BIGINT,
        started_at  TIMESTAMPTZ NOT NULL,
        finished_at TIMESTAMPTZ NOT NULL,
        ok          BOOLEAN     NOT NULL,
        error_msg   TEXT
    );
"""


def run(cmd: list[str], **kw) -> subprocess.CompletedProcess:
    """执行命令，stderr/stdout 实时打印；非 0 抛异常。"""
    print(f"  $ {' '.join(cmd)}")
    return subprocess.run(cmd, check=True, text=True, **kw)


def folder_size(path: Path) -> int:
    if path.is_file():
        return path.stat().st_size
    return sum(p.stat().st_size for p in path.rglob('*') if p.is_file())


def ensure_history_table(dsn: str) -> None:
    with psycopg.connect(dsn) as c, c.cursor() as cur:
        cur.execute(HISTORY_TABLE_DDL)
        c.commit()


def record_history(dsn: str, **kw) -> None:
    sql = """
        INSERT INTO ch14_backup_history
          (backup_name, backup_path, format, size_bytes, started_at, finished_at, ok, error_msg)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
    """
    with psycopg.connect(dsn) as c, c.cursor() as cur:
        cur.execute(sql, (
            kw["backup_name"], kw["backup_path"], kw["format"], kw["size_bytes"],
            kw["started_at"], kw["finished_at"], kw["ok"], kw.get("error_msg"),
        ))
        c.commit()


def backup_once(args: argparse.Namespace) -> Path:
    today = dt.datetime.now().strftime("%Y-%m-%d_%H%M%S")
    backup_root = Path(args.dest)
    backup_root.mkdir(parents=True, exist_ok=True)
    sub = backup_root / today
    sub.mkdir(exist_ok=True)

    print(f"\n=== 开始备份 → {sub} ===")
    started = dt.datetime.now(dt.timezone.utc)
    err: str | None = None
    try:
        # 1) 全局对象（角色、表空间）
        run([
            "pg_dumpall",
            "-h", args.host, "-p", str(args.port), "-U", args.user,
            "--globals-only",
            "-f", str(sub / "globals.sql"),
        ])

        # 2) 业务库 -Fd 并行 dump
        run([
            "pg_dump",
            "-h", args.host, "-p", str(args.port), "-U", args.user,
            "-d", args.dbname,
            "-Fd", "-j", str(args.jobs), "-Z", "6",
            "-f", str(sub / "main"),
            "--verbose",
        ])

        # 3) 校验完整性
        proc = subprocess.run(
            ["pg_restore", "-l", str(sub / "main")],
            check=True, text=True, capture_output=True,
        )
        n_items = sum(1 for ln in proc.stdout.splitlines()
                      if ln.strip() and not ln.startswith(';'))
        print(f"  ✅ pg_restore -l 校验通过，共 {n_items} 个对象")

        # 4) 计算大小
        size = folder_size(sub)
        print(f"  📦 备份总大小: {size/1024/1024:.2f} MB")

        finished = dt.datetime.now(dt.timezone.utc)
        record_history(
            args.dsn,
            backup_name=today, backup_path=str(sub),
            format="directory", size_bytes=size,
            started_at=started, finished_at=finished, ok=True,
        )
        print(f"  ⏱️ 耗时 {(finished-started).total_seconds():.1f}s")
        return sub
    except subprocess.CalledProcessError as e:
        err = f"{e.cmd} returned {e.returncode}"
        print(f"  ❌ 备份失败: {err}")
        finished = dt.datetime.now(dt.timezone.utc)
        record_history(
            args.dsn,
            backup_name=today, backup_path=str(sub),
            format="directory", size_bytes=0,
            started_at=started, finished_at=finished,
            ok=False, error_msg=err,
        )
        raise


def cleanup_old(dest: Path, keep_days: int) -> None:
    if keep_days <= 0:
        return
    cutoff = dt.datetime.now() - dt.timedelta(days=keep_days)
    print(f"\n=== 清理 {keep_days} 天前的旧备份（< {cutoff}） ===")
    for child in dest.iterdir():
        if not child.is_dir():
            continue
        try:
            d = dt.datetime.strptime(child.name.split("_")[0], "%Y-%m-%d")
        except ValueError:
            continue
        if d < cutoff:
            print(f"  🗑️ 删除 {child}")
            shutil.rmtree(child, ignore_errors=True)


def parse_args() -> argparse.Namespace:
    p = argparse.ArgumentParser(description="PG 定时备份 (Python 调度 pg_dump)")
    p.add_argument("--host", default="127.0.0.1")
    p.add_argument("--port", type=int, default=5432)
    p.add_argument("--user", default="postgres")
    p.add_argument("--dbname", default="learn_pg")
    p.add_argument("--dest", default="/tmp/pg_backup", help="备份根目录")
    p.add_argument("--jobs", type=int, default=4, help="pg_dump 并行 worker 数")
    p.add_argument("--keep", type=int, default=7, help="保留天数")
    p.add_argument("--dsn", default=DEFAULT_DSN,
                   help="psycopg 连接串（用于写 ch14_backup_history）")
    return p.parse_args()


def main() -> int:
    args = parse_args()
    ensure_history_table(args.dsn)
    backup_once(args)
    cleanup_old(Path(args.dest), args.keep)

    print("\n=== 最近 5 次备份历史 ===")
    with psycopg.connect(args.dsn) as c, c.cursor() as cur:
        cur.execute("""
            SELECT id, backup_name, format, pg_size_pretty(size_bytes::bigint), 
                   to_char(started_at, 'MM-DD HH24:MI:SS'),
                   round(extract(epoch from (finished_at - started_at))::numeric, 1) || 's',
                   ok
              FROM ch14_backup_history
             ORDER BY id DESC
             LIMIT 5
        """)
        rows = cur.fetchall()
    print(f"  {'ID':<5}{'Name':<22}{'Fmt':<12}{'Size':<10}{'Time':<18}{'Cost':<8}OK")
    for r in rows:
        print(f"  {r[0]:<5}{r[1]:<22}{r[2] or '':<12}{r[3] or '':<10}{r[4]:<18}{r[5] or '':<8}{r[6]}")
    return 0


if __name__ == "__main__":
    sys.exit(main())

markdown

# 第 14 章 配套代码 · 备份与恢复

## 准备工作

1. 跑 `psql -h 127.0.0.1 -U postgres -d learn_pg -f ../init.sql` 初始化 `ch14_users / ch14_orders` 等业务表
2. 确认 `pg_dump / pg_restore / pg_basebackup` 在 `PATH` 中（一般和 `psql` 同一个包）
3. （可选）安装依赖：`pip install "psycopg[binary]>=3.1"`（仅 `04_dump_with_python.py` 需要）
4. （可选）通过环境变量覆盖默认连接：`export PGHOST=... PGPORT=... PGUSER=... PGDATABASE=...`

## 脚本一览（推荐运行顺序）

| 脚本 | 一句话说明 | 关键 PG 特性 |
|------|------------|--------------|
| `01_pg_dump_examples.sh` | 同一个库分别用 plain / custom / dir / tar 四种格式各导出一份，对比体积 | `pg_dump -F{p,c,d,t}`、`-j`、`--schema-only / --data-only` |
| `02_pg_restore_demo.sh` | 把 `learn.dump` 恢复到临时库；演示按表恢复、`-L toc.list` 精细化恢复 | `pg_restore -j`、`-t`、`-l/-L` |
| `03_basebackup_pitr.sh` | 走一遍 `pg_basebackup` + WAL 归档 + `recovery_target_time` 的 PITR | `archive_command`、`pg_basebackup`、`recovery.signal` |
| `04_dump_with_python.py` | 「生产级备份脚本」：自动按日期分目录、并行 dump、写 `ch14_backup_history` 审计表、清理旧备份 | `subprocess` 调度、`pg_dumpall -g` |

## 运行方式

```bash
bash 01_pg_dump_examples.sh
bash 02_pg_restore_demo.sh
sudo bash 03_basebackup_pitr.sh                # 涉及 PGDATA 操作，需要 postgres 用户权限
python 04_dump_with_python.py --dest /tmp/pg_backup --jobs 4 --keep 7

预期输出

01_pg_dump_examples.sh 末尾会打印四种格式的体积对比：

-rw-r--r--  ... learn.tar
-rw-r--r--  ... learn.sql
-rw-r--r--  ... learn.dump
drwx------  ... learn_dir/

04_dump_with_python.py 每次运行后会在数据库里追加一行 ch14_backup_history，并打印最近 5 次备份历史。

常见报错与依赖

connection refused → PG 未启动，或 pg_hba.conf 没放行
relation "ch14_orders" does not exist → 没有先跑 init.sql
pg_basebackup: FATAL: number of requested standby connections exceeds max_wal_senders → 调高 max_wal_senders
recovery_target_time 不生效 → 检查归档目录是否被 PG 进程读到、restore_command 路径是否正确
03 / PITR 演示对 PGDATA 路径写入，必须以 OS 上的 postgres 用户运行，且备份目录要有写权限
04_dump_with_python.py 需要 psycopg[binary]，并且数据库账号有建表权限（用于 ch14_backup_history）

01_pg_dump_examples.sh ↗ · 02_pg_restore_demo.sh ↗ · 03_basebackup_pitr.sh ↗ · 04_dump_with_python.py ↗ · README.md ↗

第 14 章 备份与恢复 ​

14.0 导读：备份的「三个灵魂问题」 ​

14.1 逻辑备份 vs 物理备份：到底差在哪？ ​

14.1.1 生活类比 ​

14.1.2 技术对照表 ​

14.2 逻辑备份 pg_dump 全面拆解 ​

14.2.1 四种输出格式 ​

14.2.2 关键参数速查 ​

14.2.3 一致性原理：MVCC 快照 ​

14.2.4 实操：一段真实的 dump 输出 ​

14.3 逻辑恢复 pg_restore ​

14.3.1 基本用法 ​

14.3.2 误删一张表如何救？两套方案 ​

14.3.3 一段真实恢复 ​

14.4 物理备份 pg_basebackup ​

14.4.1 它做了什么 ​

14.4.2 完整命令 ​

14.4.3 备份产物 ​

14.4.4 物理备份的「增量」怎么办？ ​

14.5 PITR：Point-In-Time Recovery 时光机 ​

14.5.1 生活类比 ​

14.5.2 PITR 全流程图 ​

14.5.3 配置 WAL 归档（前置） ​

14.5.4 制作基础备份 ​

14.5.5 PITR 恢复演练 ​

14.5.6 recovery_target_* 五种目标 ​

14.5.7 PITR 时间轴 timeline ​

14.6 延迟从库：另一种「时光机」 ​

14.7 备份策略与生态工具 ​

14.7.1 三种典型策略 ​

14.7.2 主流第三方工具对比 ​

14.7.3 pgBackRest 一瞥 ​

14.8 备份恢复演练 SOP（生产必做） ​

14.9 常见踩坑 ​

坑 1：归档脚本失败导致 pg_wal 爆盘 ​

坑 2：忘备角色和全局对象 ​

坑 3：跨大版本恢复 ​

坑 4：pg_basebackup 期间主库 WAL 被回收 ​

坑 5：PITR 跨时间线 ​

坑 6：pg_dump 时锁 ​

14.10 与 MySQL 对比速查 ​

14.11 小结 ​

🎮 配套演示 ​

14.12 面试高频题 ​

Q1. pg_dump 和 pg_basebackup 的本质区别？分别适合什么场景？(⭐⭐⭐) ​

Q2. pg_dump 的 -Fp / -Fc / -Fd / -Ft 四种格式的差异？(⭐⭐) ​

Q3. PITR 是怎么实现的？需要哪些前置配置？(⭐⭐⭐⭐) ​

Q4. 物理备份过程中，pg_basebackup 怎么保证备份一致性？(⭐⭐⭐⭐) ​

Q5. 误删一张大表如何恢复？给两种方案。(⭐⭐⭐) ​

Q6. 备份策略「全量 + WAL 归档」中，归档文件怎么管理才不爆盘？(⭐⭐⭐) ​

Q7. pg_dump --jobs 并行 dump 的内部原理？为什么只支持 directory 格式？(⭐⭐⭐) ​

🔗 延伸阅读 ​

🎬 可视化演示 ​

💻 示例代码 ​

预期输出 ​

常见报错与依赖 ​

第 14 章备份与恢复

14.0 导读：备份的「三个灵魂问题」

14.1 逻辑备份 vs 物理备份：到底差在哪？

14.1.1 生活类比

14.1.2 技术对照表

14.2 逻辑备份 `pg_dump` 全面拆解

14.2.1 四种输出格式

14.2.2 关键参数速查

14.2.3 一致性原理：MVCC 快照

14.2.4 实操：一段真实的 dump 输出

14.3 逻辑恢复 `pg_restore`

14.3.1 基本用法

14.3.2 误删一张表如何救？两套方案

14.3.3 一段真实恢复

14.4 物理备份 `pg_basebackup`

14.4.1 它做了什么

14.4.2 完整命令

14.4.3 备份产物

14.4.4 物理备份的「增量」怎么办？

14.5 PITR：Point-In-Time Recovery 时光机

14.5.1 生活类比

14.5.2 PITR 全流程图

14.5.3 配置 WAL 归档（前置）

14.5.4 制作基础备份

14.5.5 PITR 恢复演练

14.5.6 `recovery_target_*` 五种目标

14.5.7 PITR 时间轴 timeline

14.6 延迟从库：另一种「时光机」

14.7 备份策略与生态工具

14.7.1 三种典型策略

14.7.2 主流第三方工具对比

14.7.3 `pgBackRest` 一瞥

14.8 备份恢复演练 SOP（生产必做）

14.9 常见踩坑

坑 1：归档脚本失败导致 pg_wal 爆盘

坑 2：忘备角色和全局对象

坑 3：跨大版本恢复

坑 4：`pg_basebackup` 期间主库 WAL 被回收

坑 5：PITR 跨时间线

坑 6：`pg_dump` 时锁

14.10 与 MySQL 对比速查

14.11 小结

🎮 配套演示

14.12 面试高频题

Q1. `pg_dump` 和 `pg_basebackup` 的本质区别？分别适合什么场景？(⭐⭐⭐)

Q2. `pg_dump` 的 -Fp / -Fc / -Fd / -Ft 四种格式的差异？(⭐⭐)

Q3. PITR 是怎么实现的？需要哪些前置配置？(⭐⭐⭐⭐)

Q4. 物理备份过程中，pg_basebackup 怎么保证备份一致性？(⭐⭐⭐⭐)

Q5. 误删一张大表如何恢复？给两种方案。(⭐⭐⭐)

Q6. 备份策略「全量 + WAL 归档」中，归档文件怎么管理才不爆盘？(⭐⭐⭐)

Q7. `pg_dump --jobs` 并行 dump 的内部原理？为什么只支持 directory 格式？(⭐⭐⭐)

🔗 延伸阅读

🎬 可视化演示

💻 示例代码

预期输出

常见报错与依赖