pg_dumpall备份恢复失败：内存不足且CPU满负载求解决方案

问题场景 #

用pg_dumpall生成了16GB的数据库备份，生成命令：

pg_dumpall -U username -p port --database "postgres"

恢复时执行：

psql -p port -U username -d postgres -f filename

过程中CPU占用率直接拉满到100%，最终因为内存耗尽，进程被终止，无法完成恢复。

解决方案 #

• 拆分大备份文件分批恢复
  16GB的单文件备份会让psql尝试一次性加载解析，很容易吃光内存。可以这样处理：

  • 如果备份里包含多个数据库，手动拆分备份文件，把每个数据库对应的CREATE DATABASE、\connect语句及后续SQL单独拆成一个文件，逐个恢复。
  • 用split命令按大小拆分，比如每1GB拆一个分片：

    split -b 1G backup_filename backup_part_
    

    然后按顺序恢复每个分片：

    for part in backup_part_*; do psql -p port -U username -d postgres -f $part; done
    

• 调整内存相关参数

  • 启动psql时指定work_mem，降低排序等操作的内存占用（根据服务器实际内存调整，比如32MB/64MB）：

    psql -p port -U username -d postgres --set=work_mem=64MB -f filename
    

  • 临时调大maintenance_work_mem（恢复完成后记得改回默认值）：
    先连接到postgres库执行：

    ALTER SYSTEM SET maintenance_work_mem = '128MB';
    SELECT pg_reload_conf();
    

    恢复完成后改回：

    ALTER SYSTEM SET maintenance_work_mem = '默认值';
    SELECT pg_reload_conf();
    

• 降低CPU占用

  • 恢复时限制并行操作，避免CPU跑满：

    psql -p port -U username -d postgres --set=max_parallel_workers_per_gather=0 -f filename
    

  • 恢复期间临时关闭自动统计收集，减少后台消耗：

    ALTER SYSTEM SET autovacuum = off;
    SELECT pg_reload_conf();
    

    恢复完成后重新开启：

    ALTER SYSTEM SET autovacuum = on;
    SELECT pg_reload_conf();
    

• 改用更高效的恢复方式（PostgreSQL 12+适用）
  如果能先把备份导入临时数据库，可转成目录格式用pg_restore并行恢复：

  # 先导入到临时库（如果直接导入失败，先用拆分法）
  psql -p port -U username -d temp_db -f filename
  # 导出为目录格式
  pg_dump -U username -p port -F d -f backup_dir temp_db
  # 并行恢复（-j指定并行数，根据CPU核心数选，比如4）
  pg_restore -U username -p port -d target_db -j 4 -F d backup_dir
  

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