AWS DMS迁移PostgreSQL后序列查询遗漏问题求助

你的查询仅能捕获与表列存在直接依赖关系的序列（比如由SERIAL/BIGSERIAL自动生成的序列，或显式绑定到列默认值的序列），剩下的10个未被捕获的序列大概率属于以下情况：

• 独立序列：从未被任何表列的默认值引用，仅通过手动调用nextval()使用
• 被视图、函数、触发器等对象引用，而非直接绑定到表列
• 手动创建的无关联序列，未与任何表列建立依赖关系

改进后的查询（覆盖所有序列） #

以下查询会列出public schema下的所有序列，同时针对有无关联列的情况生成对应的SETVAL语句：

WITH all_sequences AS (
    SELECT
        n.nspname AS seq_schema,
        c.relname AS seq_name,
        c.oid AS seq_oid
    FROM pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    WHERE c.relkind = 'S'
      AND n.nspname = 'public'
),
sequence_columns AS (
    SELECT
        n.nspname AS seq_schema,
        c.relname AS seq_name,
        c.oid AS seq_oid,
        tn.nspname AS table_schema,
        tc.relname AS table_name,
        a.attname AS column_name
    FROM pg_depend d
    JOIN pg_class c ON d.objid = c.oid AND c.relkind = 'S'
    JOIN pg_namespace n ON c.relnamespace = n.oid
    JOIN pg_class tc ON d.refobjid = tc.oid
    JOIN pg_namespace tn ON tc.relnamespace = tn.oid
    JOIN pg_attribute a ON a.attrelid = tc.oid AND d.refobjsubid = a.attnum
    WHERE n.nspname = 'public'
)
SELECT
    COALESCE(sc.seq_schema, asq.seq_schema) AS seq_schema,
    COALESCE(sc.seq_name, asq.seq_name) AS seq_name,
    sc.table_schema,
    sc.table_name,
    sc.column_name,
    CASE
        WHEN sc.column_name IS NOT NULL THEN
            'SELECT SETVAL(' || quote_literal(quote_ident(asq.seq_schema) || '.' || quote_ident(asq.seq_name)) ||
            ', COALESCE(MAX(' || quote_ident(sc.column_name) || '), 1) + 1000) FROM ' ||
            quote_ident(sc.table_schema) || '.' || quote_ident(sc.table_name) || ';'
        ELSE
            'SELECT SETVAL(' || quote_literal(quote_ident(asq.seq_schema) || '.' || quote_ident(asq.seq_name)) ||
            ', COALESCE(CURRVAL(' || quote_literal(quote_ident(asq.seq_schema) || '.' || quote_ident(asq.seq_name)) || '), 1) + 1000);'
    END AS setval_statement
FROM all_sequences asq
LEFT JOIN sequence_columns sc ON asq.seq_oid = sc.seq_oid
ORDER BY asq.seq_schema, asq.seq_name;

逻辑说明 #

1. all_sequences CTE：获取public schema下的所有序列，确保不会遗漏任何序列
2. sequence_columns CTE：关联序列与对应的表和列（仅针对有直接依赖的序列）
3. 最终查询：
   • 对有关联列的序列，继续使用原逻辑基于列最大值+1000设置序列值
   • 对无关联的独立序列，优先使用序列当前值（如果已被调用过），否则以1为基础+1000设置

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