Django应用PostgreSQL跨版本备份恢复后ID序列默认值丢失问题

你当前遇到的场景属于跨大版本PostgreSQL备份恢复的兼容问题：

• 测试服务器使用PG12.5版本的pg_dump生成纯文本SQL备份，本地工作电脑是PG9.5环境，使用psql重定向导入备份时，哪怕备份文件中已经完整包含序列绑定、id字段默认值设置的正确SQL语句，恢复完成后所有id字段的默认值仍然为空，无法插入新数据。之前这套流程可以正常运行，基本是因为测试服务器原版本和本地9.5的PG版本差较小，导出的SQL没有高版本专属语法，近期测试服务器升级到12.5后才触发了兼容问题。
• 问题本质不是备份文件的语句有错误，而是恢复阶段的静默语句跳过机制：PG9.5的psql遇到自身不兼容的高版本专属SQL语法时，默认不会中断流程，只会打印报错信息继续执行，直到遇到下一个语句结束的分号前，会把所有后续的代码片段都判定为错误语句的残余内容直接跳过，刚好覆盖到了备份文件里的序列配置语句。

• 第一步：修改恢复命令，开启错误中断抓取真实报错
  替换掉原来的< mednut_bak.sql重定向导入方式，使用带错误停止参数的命令重新执行恢复，只要遇到任何SQL执行错误立刻中断流程，暴露真实的报错位置：
  psql -U chris -d mednutrition_db -v ON_ERROR_STOP=1 -f mednut_bak.sql
  绝大多数场景下，这里抓到的报错都是PG12导出时自动附带的系统权限回收、系统目录操作类语句，这类语句是PG9.5版本无法识别的，触发报错后就会直接跳过相邻的序列配置语句。
• 第二步：根据报错结果完成恢复
  • 如果能定位到兼容类报错语句，可以直接打开备份文件，删除这些和业务表、序列无关的高版本专属语句，再重新用上面带错误中断的命令执行恢复，保证所有序列配置语句能被正常执行。
  • 如果不想逐行修改备份文件，可以等全量导入流程完成后，执行一段批量修复SQL，自动匹配Django的主键序列命名规则，完成所有序列的绑定、默认值配置和当前值同步，避免手动逐表修改的工作量：

    DO $$
    DECLARE
        seq_info RECORD;
    BEGIN
        FOR seq_info IN
            SELECT 
                sequence_name, 
                REPLACE(sequence_name, '_id_seq', '') AS target_table
            FROM information_schema.sequences
            WHERE sequence_schema = 'public'
              AND sequence_name LIKE '%_id_seq'
        LOOP
            -- 将序列归属绑定到对应表的id字段
            EXECUTE format(
                'ALTER SEQUENCE public.%I OWNED BY public.%I.id',
                seq_info.sequence_name,
                seq_info.target_table
            );
            -- 设置id字段的自增默认值
            EXECUTE format(
                'ALTER TABLE public.%I ALTER COLUMN id SET DEFAULT nextval(''public.%I''::regclass)',
                seq_info.target_table,
                seq_info.sequence_name
            );
            -- 同步序列当前值，避免插入数据时出现主键冲突
            EXECUTE format(
                'SELECT setval(
                    ''public.%I'', 
                    COALESCE((SELECT MAX(id) FROM public.%I), 1), 
                    (SELECT MAX(id) IS NOT NULL FROM public.%I)
                )',
                seq_info.sequence_name,
                seq_info.target_table,
                seq_info.target_table
            );
        END LOOP;
    END $$;
    

• 长期优化建议
  后续做跨版本备份恢复时，尽量下载和本地PG9.5版本匹配的pg_dump工具，用和本地版本一致的工具连接测试服务器导出备份，从根源上避免高版本导出的SQL带兼容语法导致的执行跳过问题。

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