Rails中从开发环境恢复PostgreSQL到生产环境后无法创建新记录

问题描述 #

使用以下命令从开发环境备份PostgreSQL数据库并恢复到生产环境后，创建新记录时触发报错：

ERROR: duplicate key value violates unique constraint "table_name_pkey"
Detail: Key (id)=(1) already exists.

备份命令：

PGPASSWORD=$DB_PASSWORD pg_dump \
    --host=$DB_HOST \
    --username=$DB_USERNAME \
    --dbname=$DB_NAME \
    --format=custom \
    --file=D:/output.dmp

恢复命令：

PGPASSWORD=$DB_PASSWORD pg_restore  \
    --host=$DB_HOST \
    --username=$DB_USERNAME \
    --dbname=$DB_NAME \
    D:/output.dmp

问题根源：恢复后表的主键序列未同步到现有数据的最大ID值，新增记录时序列仍从初始值（如1）开始，导致主键值与已存在的记录冲突。

解决方案 #

1. 手动重置单个表的主键序列 #

针对出现问题的表，执行以下SQL语句，将序列值设置为表中当前最大的ID：

SELECT setval(pg_get_serial_sequence('table_name', 'id'), max(id)) FROM table_name;

• 替换table_name为实际表名
• 如果主键字段不是id，替换为对应字段名

2. 批量重置所有表的主键序列 #

若多个表存在此问题，可执行以下SQL生成批量重置命令：

SELECT 'SELECT setval(pg_get_serial_sequence(''' || tablename || ''', ''id''), max(id)) FROM ' || tablename || ';'
FROM pg_tables
WHERE schemaname = 'public' AND tablename NOT LIKE 'pg_%';

执行后会输出每个表的重置语句，复制这些语句执行即可完成批量修复。

3. 备份时提前避免序列不一致问题 #

后续备份时，添加--serializable-deferrable参数，确保备份的数据与序列状态一致，恢复后无需手动调整：

PGPASSWORD=$DB_PASSWORD pg_dump \
    --host=$DB_HOST \
    --username=$DB_USERNAME \
    --dbname=$DB_NAME \
    --format=custom \
    --serializable-deferrable \
    --file=D:/output.dmp

该参数会让备份在可序列化事务中执行，保证数据和序列的一致性。

注意事项 #

• 生产环境操作前务必备份当前数据库，防止数据丢失
• 确认表的主键字段名，若不是id需对应修改SQL中的字段名
• 恢复时若生产环境已有数据，需合理使用pg_restore的--clean（清空现有表）或--create（创建新库）参数，避免数据冲突

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