不灭的焱

加密类型:SHA/AES/RSA下载Go
复合类型:切片(slice)、映射(map)、指针(pointer)、函数(function)、通道(channel)、接口(interface)、数组(array)、结构体(struct) Go类型+零值nil
引用类型:切片(slice)、映射(map)、指针(pointer)、函数(function)、通道(channel) Go引用

作者:AlbertWen  添加时间:2026-11-04 14:58:33  修改时间:2026-11-20 16:07:01  分类:03.MySQL/PgSQL/Redis  编辑

目录

PostgreSQL 迁移中的 Sequence 问题,核心解决办法是:先迁移表数据,在正式业务切换前停止旧库写入,再把旧库 Sequence 的最终状态同步到新库,并校验 Sequence 与表字段的绑定关系。

针对你们阿里云 PostgreSQL → 阿里云 PostgreSQL 的场景,如果使用的是 DTS 全量+增量迁移,这个步骤尤其重要。

阿里云官方明确说明:DTS 不会完整迁移 Sequence 的当前值(last_value)及 OWNED BY 绑定关系,需要在业务切换前手动处理。

一、为什么会出现主键冲突?

假设 SPS 订单表 orders 使用自增主键:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    order_no VARCHAR(50)
);

旧 PostgreSQL

100,000

订单最大 ID

Sequence 正常

新 PostgreSQL

1

Sequence 仍是初始值

下一次插入可能冲突

迁移完成后,若 Sequence 正确调整为 100000

新订单 ID = 100001

注意,Sequence 与表数据是独立管理的。表中存在 id = 100000,并不代表 Sequence 会自动跳到 100000。

二、单张表如何修复?

假设你的订单表叫 public.orders,对应的序列叫 public.orders_id_seq。

第一步:在旧库查询 Sequence 状态。

SELECT last_value, is_called
FROM public.orders_id_seq;

再查询业务表:

SELECT MAX(id) AS max_id
FROM public.orders;

假设查询结果:

项目 值
旧库最大订单 ID 100000
旧库 Sequence last_value 100050
Sequence increment 1

这里 Sequence 的值比最大 ID 大,可能是因为事务回滚、Sequence 缓存或者曾经取号但没有成功插入。

第二步:在新库中修正 Sequence。

先确保旧库已经停止写入、增量同步已经追平,新库还没有开始业务写入。

SELECT setval(
    'public.orders_id_seq'::regclass,
    GREATEST(
        COALESCE((SELECT MAX(id) FROM public.orders), 0),
        100050
    ),
    true
);

这里 100050 是从旧库读取的最终 Sequence 值,不能在实际执行时随便写死。

修正后,新库下一次获取序列值将是:

SELECT nextval('public.orders_id_seq');
-- 返回 100051

注意:上面的 SQL 适用于常见的正向递增、步长为 1 的 Sequence。自定义起始值、递减序列、循环序列或共享序列,需要分别处理。

第三步:检查绑定关系。

SELECT pg_get_serial_sequence(
    'public.orders',
    'id'
);

正常情况下会返回类似:

public.orders_id_seq

如果需要恢复普通 SERIAL 序列的所有权绑定,可执行:

ALTER SEQUENCE public.orders_id_seq
OWNED BY public.orders.id;

OWNED BY 是序列与字段之间的依赖关系;它与字段的 DEFAULT nextval(...) 不是一回事。对于 GENERATED ... AS IDENTITY 字段,不要直接套用普通 SERIAL 的修复脚本,应检查身份列定义和内部依赖。

另外,nextval() 会真实消耗一个序列号,所以生产验收时不要随意调用它进行测试。用只读方式检查序列状态即可。

三、如果有几百张表,如何批量处理 Sequence?

实际迁移时不可能每张表都手动查一遍。所以我建议使用 SQL 批量扫描旧库所有 Sequence,自动生成新库修复语句。

下面的脚本在旧库停止取号之后执行。它只读取 Sequence 信息并输出修复 SQL,不会修改旧库。

DO $$
DECLARE
    r RECORD;
    v_last BIGINT;
    v_called BOOLEAN;
BEGIN
    FOR r IN
        SELECT
            sn.nspname AS seq_schema,
            s.relname AS seq_name,
            tn.nspname AS table_schema,
            t.relname AS table_name,
            a.attname AS column_name,
            d.deptype
        FROM pg_class s
        JOIN pg_namespace sn
            ON sn.oid = s.relnamespace
        LEFT JOIN pg_depend d
            ON d.objid = s.oid
            AND d.classid = 'pg_class'::regclass
            AND d.refclassid = 'pg_class'::regclass
            AND d.deptype IN ('a', 'i')
        LEFT JOIN pg_class t
            ON t.oid = d.refobjid
        LEFT JOIN pg_namespace tn
            ON tn.oid = t.relnamespace
        LEFT JOIN pg_attribute a
            ON a.attrelid = t.oid
            AND a.attnum = d.refobjsubid
            AND NOT a.attisdropped
        WHERE s.relkind = 'S'
          AND sn.nspname NOT IN (
              'pg_catalog', 'information_schema'
          )
          AND sn.nspname NOT LIKE 'pg_toast%'
    LOOP
        EXECUTE format(
            'SELECT last_value, is_called FROM %I.%I',
            r.seq_schema, r.seq_name
        ) INTO v_last, v_called;

        -- 普通 SERIAL 序列恢复 OWNED BY
        IF r.deptype = 'a' THEN
            RAISE NOTICE '%', format(
                'ALTER SEQUENCE %I.%I OWNED BY %I.%I.%I;',
                r.seq_schema, r.seq_name,
                r.table_schema, r.table_name,
                r.column_name
            );
        END IF;

        -- 输出 Sequence 当前值恢复语句
        RAISE NOTICE '%', format(
            'SELECT setval(%L::regclass, %s, %L);',
            format('%I.%I', r.seq_schema, r.seq_name),
            v_last, v_called
        );
    END LOOP;
END $$;

生成结果类似:

ALTER SEQUENCE public.orders_id_seq
OWNED BY public.orders.id;

SELECT setval(
    'public.orders_id_seq'::regclass,
    100050,
    true
);

ALTER SEQUENCE public.customers_id_seq
OWNED BY public.customers.id;

SELECT setval(
    'public.customers_id_seq'::regclass,
    5080,
    true
);

将生成的 SQL 保存下来,确认新库已经有对应 Sequence,并对照新库表中的最大 ID 检查后,再批量执行。

这份脚本是“恢复旧库状态”的脚本,不是任何场景都可以无条件执行的通用修复器。 特别是新库已经产生数据、Sequence 被多表共用、存在自定义步长或递减序列时,必须单独校验。deptype='i' 的 Identity 序列也需要核对,但不应按普通 SERIAL 序列直接修改 OWNED BY。

四、到底应该在什么时间修复?

  1. DTS 正在全量+增量迁移

    旧业务正常运行,暂时不需要最终修正 Sequence。

  2. 安排正式业务停写

    停止旧库所有写入来源,包括应用、定时任务、后台服务、人工 SQL 和独立的取号操作,确保未完成的事务及连接中的缓存取号得到处理。

  3. 等待 DTS 增量追平

    确认源库最终数据全部到达目标库,并按阿里云要求安全结束迁移任务。

  4. 导出源库 Sequence 最终状态

    运行上面的扫描脚本,保存修复 SQL。

  5. 修复新库 Sequence 并验收

    核对 last_value、is_called、关联字段、最大 ID 和 OWNED BY。

  6. 应用切换到新库,恢复业务

    用应用账号做真实新增订单测试,再检查实际生成的 ID 和相关日志。

还有三个容易忽略的点:

  • 不要在旧库还持续写入时,只修复一次新库 Sequence 就认为完成了。 后续旧库生成的新 ID 仍可能超过你提前设置的值。

  • setval() 的变更不会因为事务回滚而自动撤销。 不要认为把它放在 BEGIN/ROLLBACK 里就能安全试运行。

  • 如果使用完整的 pg_dump + pg_restore,情况不完全一样。 正常完整转储会包含 Sequence 值;但如果转储后旧库还在写入,仍需在最终切换时补齐后续变化。

五、我建议增加一项正式的迁移验收标准

检查内容 通过标准
Sequence 数量 新旧库业务 Sequence 数量一致
Sequence 定义 起始值、步长、上下限、缓存、循环属性符合预期
Sequence 当前值 不会重新生成已被使用的 ID
Sequence 绑定关系 SERIAL、Identity 对应关系正确
字段默认值 新增记录能正确调用 Sequence
业务新增验证 新增订单、客户等记录无主键冲突

对于你们这次迁移,我最推荐的做法是:迁移前提前完成 Sequence 清单和脚本演练;正式切换时只重新采集最终值、执行修复并验收。 这样可以把需要临时操作的内容尽量减少,降低正式切换的失误风险。

参考:阿里云官方:RDS PostgreSQL 间的数据迁移。