引言

最近笔者（山月）将BookWyrm应用从YunoHost迁移到了使用Docker Compose的独立服务器环境，然而在新的环境中遇到了严重的问题：数据库中的表无法正常新增内容，例如无法添加图书、作者，或更新阅读状态等。在调试过程中，每次进行数据迁移操作时，都会遇到数据库主键重复的报错。这篇博文记录了笔者解决这一问题的全过程，希望能够帮助遇到相似困境的朋友们。

问题背景

在将BookWyrm从YunoHost迁移到Docker Compose环境后，笔者（山月）发现新增数据的操作频繁失败，无论是添加图书、作者，还是更新阅读状态，都会遇到数据库报错的问题。具体表现为，当执行Django的migrate命令时，报错显示主键重复（例如 django.db.utils.IntegrityError: duplicate key value violates unique constraint）。

笔者最初的解决方案是手动调整有问题的数据库表的主键自增计数器。但由于涉及的表非常多，一个个手动调整非常低效。这个过程也让笔者意识到，数据库迁移中某些自增序列可能没有同步，导致插入新记录时主键重复。

解决方案：自动校准PostgreSQL自增序列

在多次尝试手动解决主键问题无果后，笔者（山月）决定寻找一种更加自动化的方法，来确保所有数据库表的自增序列都能够同步。笔者最终找到了一个有效的方法：编写一个函数自动校准数据库中所有表的自增序列。

这个函数通过遍历数据库中所有具有自增序列的表，查询每个表的最大主键值，并将自增序列设置为该最大值加一。这样，所有表的自增序列都能够与表中现有数据保持一致，避免了重复主键错误。

下面是这个自动校准函数的实现：

-- 避免重复创建函数
DROP FUNCTION IF EXISTS reset_sequences_for_tables();
-- 重新创建函数
CREATE OR REPLACE FUNCTION reset_sequences_for_tables() RETURNS VOID AS $$
DECLARE
    table_name_text TEXT;
    seq_name TEXT;
    max_id INT;
    default_value TEXT;
    pk_column_name TEXT;
BEGIN
    FOR table_name_text IN
        SELECT t.table_name
        FROM information_schema.tables AS t
        WHERE t.table_schema = 'public'
        AND EXISTS (
            SELECT 1
            FROM information_schema.columns AS c
            WHERE c.table_name = t.table_name
            AND c.column_default ILIKE 'nextval%'
        )
    LOOP
        SELECT column_name INTO pk_column_name
        FROM information_schema.columns AS c
        WHERE c.table_name = table_name_text
        AND c.column_default ILIKE 'nextval%';

        SELECT column_default INTO default_value
        FROM information_schema.columns AS c
        WHERE c.table_name = table_name_text
        AND c.column_name = pk_column_name;

        seq_name := substring(default_value from E'\'(\\w+)\'::regclass');

        IF EXISTS (SELECT 1 FROM pg_class WHERE relname = seq_name) THEN
            EXECUTE 'SELECT MAX(' || pk_column_name || ') FROM ' || table_name_text INTO max_id;

            IF max_id IS NULL THEN
                EXECUTE 'SELECT setval(' || quote_literal(seq_name) || ', 1, false)';
            ELSE
                EXECUTE 'SELECT setval(' || COALESCE(quote_literal(seq_name), 'null') || ', ' || max_id || ')';
            END IF;
        ELSE
            RAISE NOTICE 'Sequence not found for table: %', table_name_text;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

创建函数后，执行它来重置所有自增序列的值：

SELECT reset_sequences_for_tables();

这一步将自动校准所有有自增序列的表的主键序列，让它们与表中的最大ID对应，避免重复主键的情况。

执行结果

执行该函数后，所有表的自增序列都得到了自动校准，数据库主键重复的问题也顺利解决了。执行migrate命令不再报错，BookWyrm的功能恢复正常，现在可以正常添加图书、作者和更新阅读状态。

总结

在迁移BookWyrm应用时，数据库中的自增序列可能因为不同的环境和数据迁移方式而失去同步，导致主键重复的问题。这篇博客记录了如何通过编写一个函数，自动校准PostgreSQL数据库中所有表的自增序列，确保数据一致性并解决问题。

如果你也遇到类似的问题，希望这篇文章能够对你有所帮助。