PostgreSQL使用pg_dump排除表数据时能否保留对应序列的设置？

解决方案 #

你的需求完全可以在PostgreSQL 13上实现，不需要修改表结构移除主键，有两种成熟的实现方式：

方案1：直接调整pg_dump参数（推荐） #

--exclude-table-data参数只会排除指定表的行数据，不会影响序列这类独立对象的导出，只需要显式指定包含审计表关联的序列即可：

1. 先查询审计表主键对应的序列名，执行SQL：

SELECT pg_get_serial_sequence('你的审计表名', '主键列名');

示例：审计表为audit_log、主键列为id，执行后会得到序列名如public.audit_log_id_seq。
2. 调整主备份命令，新增序列的包含规则：

pg_dump -F custom \
--exclude-table-data='public.audit_log' \
--include-table='public.audit_log_id_seq' \
-f 主备份文件.dmp 你的数据库名

如果有多张需要排除数据的表，重复添加--exclude-table-data和对应的--include-table（指定对应序列）参数即可。

方案2：批量导出序列值生成预置脚本 #

如果需要处理的表数量较多，可以提前导出所有目标表的序列当前值，生成SQL脚本在恢复主备份后执行：

1. 执行以下SQL生成设置序列值的脚本：

SELECT 'SELECT setval(' || quote_literal(seq.relname) || ', ' || nextval(seq.relname) - 1 || ', false);' 
FROM pg_class seq
JOIN pg_depend dep ON seq.oid = dep.objid
JOIN pg_class tbl ON dep.refobjid = tbl.oid
JOIN pg_attribute attr ON dep.refobjid = attr.attrelid AND dep.refobjsubid = attr.attnum
WHERE seq.relkind = 'S' 
AND tbl.relname IN ('audit_log', '其他待处理表1', '其他待处理表2');

2. 将生成的SQL语句保存为set_sequences.sql。

恢复流程验证 #

1. 恢复主转储文件：

pg_restore -d 目标数据库名 主备份文件.dmp

2. 若使用方案2，执行序列预置脚本：

psql -d 目标数据库名 -f set_sequences.sql

3. 启动应用对外提供服务，此时新写入的审计数据主键会从备份时的最大值+1开始生成，不会与历史数据冲突。
4. 后台导入审计表历史数据，建议单独备份审计表时添加--data-only --no-sequence-owned参数，避免恢复时覆盖已设置好的序列值：

# 单独备份审计表的命令参考
pg_dump -F custom --data-only --no-sequence-owned --table='public.audit_log' -f 审计表备份文件.dmp 原数据库名

# 恢复审计表历史数据命令
pg_restore -d 目标数据库名 --data-only --table='public.audit_log' 审计表备份文件.dmp

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