PostgreSQL从dump文件恢复数据库报错 疑似版本不匹配问题求解

前置检查：确认dump文件格式 #

如果你生成dump时用了pg_dump -Fc参数生成自定义格式的备份，不能直接用psql命令导入，必须用pg_restore执行恢复：

pg_restore -v -d dbname -j 4 path-to-dump

加-j 4参数可以用4线程并行恢复，大幅提升20GB大文件的恢复速度。

第一步：校验dump文件完整性 #

• 对比源端备份文件和当前文件的哈希值（比如执行md5sum dump.sql），如果哈希不一致说明文件传输过程中损坏，直接重新传输备份文件即可。
• 如果哈希一致，提取报错SQL前后100行内容检查，你贴出的函数定义中存在单独的CREATE行和无意义的半截注释，属于备份内容异常，大概率是生成备份时任务中途中断导致的，需要重新生成备份文件。

第二步：修复语法错误后分步恢复 #

如果备份文件本身完整，只是部分SQL语句存在语法兼容问题，可以按如下步骤操作：

1. 先导入基础表结构和数据，跳过自定义函数、触发器等对象：

# 针对plain格式的SQL dump
grep -v -E 'CREATE FUNCTION|CREATE TRIGGER|CREATE RULE' dump.sql > base_data.sql
psql -v ON_ERROR_STOP=1 dbname < base_data.sql

2. 手动修正所有自定义函数、触发器的语法错误后单独执行，你给出的报错函数删除多余的CREATE行和半截注释后即可正常执行：

CREATE FUNCTION public.documents_search_trigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    new.tsv := setweight(to_tsvector(coalesce(new.name,'')), 'A');
    RETURN new;
END
$$;

第三步：解决批量invalid command \N报错 #

这个报错是前置SQL执行失败后，psql未终止，误将COPY语句中的空值标识\N识别为psql元命令导致的，只要你加了-v ON_ERROR_STOP=1参数，解决第一个出现的语法错误后，后续批量报错会自动消失。

兼容问题兜底方案 #

如果上述操作仍报错，直接在当前机器安装和pg_dump版本一致的PostgreSQL 13.x客户端，用对应版本的psql执行恢复操作，避免跨版本客户端的语法解析差异问题。

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