为何使用psql恢复PostgreSQL数据库比pg_restore更快？

我正尝试提升数据库备份与恢复的速度，目前使用命令：

pg_dump --no-privileges --no-owner -w source_db | psql target_db

（目标库已提前创建）。我了解到自定义格式更优，本以为二进制格式会比文本格式更快。

首次测试 #

执行以下hyperfine测试命令：

hyperfine --prepare './drop_db.sh target_db ; ./create_db.sh target_db' --runs=3  -i --export-markdown report.md \
'pg_dump --no-privileges --format=custom source_db | pg_restore --dbname=target_db' \
'pg_dump --no-privileges --no-owner -w source_db | psql target_db'

测试结果显示psql比pg_restore快20秒。

压缩测试 #

因听说压缩会拖慢速度，我测试了不同压缩级别，最优情况下两者性能相当。

备份与恢复分离测试 #

按建议将备份和恢复分开测试：

• 仅备份：psql对应的备份命令比自定义格式快7秒；
• 仅恢复：使用压缩级别1的自定义格式恢复最快，但psql恢复比它慢，且恢复比备份慢10倍，分离后恢复速度更慢。

请问有没有办法在不将转储写入磁盘（即不使用支持并行化的--format=directory）的情况下进一步提升速度？

1. 优化自定义格式的管道压缩逻辑 #

pg_dump内置压缩的CPU开销可能是瓶颈，试试关闭内置压缩，改用更快的外部轻量压缩工具（比如zstd，比gzip的速度/压缩比更优）做流处理：

pg_dump --no-privileges --format=custom --compress=0 source_db | zstd -1 | pg_restore --dbname=target_db --format=custom -d -

这种方式既保留自定义格式恢复的优势，又用更低开销的压缩工具平衡传输和CPU成本。

2. 临时调整目标库参数加速恢复 #

恢复阶段的写入和索引创建是主要瓶颈，临时修改以下参数（恢复完成后务必改回）：

• 增大maintenance_work_mem：给索引创建、约束校验分配更多内存，比如设为1GB（根据服务器内存调整）：

  ALTER SYSTEM SET maintenance_work_mem = '1GB';
  SELECT pg_reload_conf();
  

• 关闭fsync和synchronous_commit：暂时牺牲数据安全换取写入速度，恢复完成后立即恢复：

  ALTER SYSTEM SET fsync = off;
  ALTER SYSTEM SET synchronous_commit = off;
  SELECT pg_reload_conf();
  

3. 优化文本格式的psql恢复 #

如果继续用文本管道，试试这些优化：

• 添加--disable-triggers跳过触发器执行（无外键/触发器依赖时可用）：

  pg_dump --no-privileges --no-owner -w source_db | psql --disable-triggers target_db
  

• 增大work_mem提升批量数据处理效率：

  ALTER SYSTEM SET work_mem = '64MB';
  SELECT pg_reload_conf();
  

4. 用命名管道实现轻量并行（不落地磁盘） #

利用Linux命名管道（FIFO）模拟并行备份/恢复，不需要落地磁盘就能利用多核：

1. 创建命名管道：

   mkfifo pg_pipe1 pg_pipe2
   

2. 并行备份不同表到管道，同时恢复：

   # 后台备份表1到管道
   pg_dump --no-privileges --format=custom source_db -t large_table1 > pg_pipe1 &
   # 后台备份表2到管道
   pg_dump --no-privileges --format=custom source_db -t large_table2 > pg_pipe2 &
   # 同时恢复两个管道的内容
   pg_restore --dbname=target_db pg_pipe1 &
   pg_restore --dbname=target_db pg_pipe2 &
   wait
   

这种方式适合单库内有大表的场景，拆分后并行处理能大幅缩短总耗时。

5. 精简备份内容 #

确认--no-privileges、--no-owner是必要的，避免pg_dump额外处理权限相关逻辑；如果不需要备份某些大表或日志表，用-T参数排除，减少备份数据量。

内容的提问来源于stack exchange，提问作者Philippe