pg_dump/pg_restore 大表备份+三段式恢复

生成备份

  • 参数说明
    • -Fd：多线程备份，备份到目录下，每个表一个文件
      • 仅 -Fd 的情况下支持多线程备份
    • -d：要备份的数据库
    • -j：并发备份线程数
    • –verbose：日志记录详细信息
    • –compress：压缩级别，默认为0，不压缩，测试3压缩基别已经符合预期
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# 创建备份目录
mkdir 20260512

# 生成备份
pg_dump \
  -h 127.0.0.1 \
  -p 5432 \
  -U postgres \
  -d sourcedb \
  -Fd \
  -j 16 \
  --verbose \
  --compress=3 \
  -f 20260512
  • 打包
1

tar -xvf 20260512.tar 20260512
  • 传输至目标数据库
1

scp 20260512.tar  postgres@XX.XX.XXX.XXX:

导入数据

  • 三段式恢复
  • 解压
1

tar -xvf 20260512.tar

阶段1：恢复结构(pre-data)

  • 创建表、视图、序列、函数等，不包含数据。
  • 如果表内有其他脏数据需要清理的话可增加 –clean 参数，会在创建对象前调用 drop 清理对象
    • 需要清理原有数据的话，仅在 pre-data 阶段调用 –clean 即可
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

pg_restore \
  -h 127.0.0.1 \
  -p 5432 \
  -U postgres \
  -d targetdb \
  --section=pre-data \
  -j 16 \
  --verbose \
  --no-owner \
  --no-acl \
  20260512

阶段2：恢复核心数据(data)

  • 可增加参数 –disable-triggers 暂时禁用触发器和外键检查，提升速度，但恢复完成后必须手动验证数据完整性
1
2
3
4
5
6
7
8
9

pg_restore \
  -h 127.0.0.1 \
  -U postgres \
  -d targetdb \
  --section=data \
  -j 16 \
  --verbose \
  --no-owner \
  20260512

阶段3：恢复约束与索引(post-data)

  • 序列的 setval 自动在此阶段执行，无需手动处理序列问题
1
2
3
4
5
6
7
8
9

pg_restore \
  -h 127.0.0.1 \
  -U postgres \
  -d targetdb \
  --section=post-data \
  -j 16 \
  --verbose \
  --no-owner \
  20260512

更新表统计信息

1

vacuumdb -h 127.0.0.1 -U postgres -d targetdb --analyze-only -j 16

数据检查

自增序列

  • 查询自增序列
1

SELECT schemaname || '.' || sequencename AS seq, start_value, last_value FROM pg_sequences;