☰

PIP2.0 PG库数据迁移至OpenGauss库操作要点及说明

一、数据迁移

数据迁移当前解决方案依赖dbeaver表右键导出数据的方式执行。参考如下： Image

二、更新序列

开发提供以下脚本用于更新所有使用序列（nextval 作为默认值）的表的序列当前值。

DO $$
DECLARE tablename varchar;
declare columnname varchar;
declare exsql varchar;
declare updateidvar varchar;
DECLARE resultId int;
declare info RECORD;
BEGIN
for info in SELECT table_schema||'.'||table_name as tablename,column_name
FROM information_schema.columns
WHERE table_schema = 'pip_pass' and column_default like 'nextval%'
loop
RAISE NOTICE '%,%',info.column_name,info.tablename;
exsql:= 'select '||'max('||info.column_name||') FROM '||info.tablename||' ';

RAISE NOTICE '%',exsql;
EXECUTE exsql INTO resultId;
RAISE NOTICE '%',resultId;
IF resultId > 0
then
updateidvar:='select setval('''||info.tablename||'_'||info.column_name||'_seq'', %s) from '||info.tablename||' ;';
EXECUTE format(updateidvar,resultId+1);
END IF;
end loop;

END;
$$

优化代码：

DO $$
DECLARE 
    info RECORD;
    max_id bigint;
    seq_name text;
    found_sequence boolean := false;
        sql text;
BEGIN 
    RAISE NOTICE '开始处理 pip_im 模式下的序列...';

    -- 获取所有使用序列作为默认值的列
    FOR info IN 
        SELECT 
            n.nspname as schema_name,
            c.relname as table_name,
            a.attname as column_name,
            d.adsrc as default_value,
            pg_get_expr(d.adbin, d.adrelid) as default_expr
        FROM pg_attrdef d
        JOIN pg_attribute a ON a.attrelid = d.adrelid AND a.attnum = d.adnum
        JOIN pg_class c ON c.oid = a.attrelid
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE n.nspname in ('pip_cdc','pip_im','pip_imdo','pip_pa','pip_pass','pip_pr','pip_public','pip_temp','pip_vbp') 
                                --and c.relname='passim_history_message' 
                AND (d.adsrc LIKE 'nextval%' OR pg_get_expr(d.adbin, d.adrelid) LIKE 'nextval%')
        ORDER BY n.nspname,c.relname, a.attname
    LOOP
        RAISE NOTICE '表: %.%, 列: %', 
            info.schema_name, info.table_name, info.column_name;
        RAISE NOTICE '默认值: %', info.default_value;
        RAISE NOTICE '默认表达式: %', info.default_expr;

        -- 尝试方法1: 使用 pg_get_serial_sequence
        EXECUTE format('SELECT pg_get_serial_sequence(%L, %L)', 
                      info.schema_name || '.' || info.table_name, 
                      info.column_name) 
        INTO seq_name;

        IF seq_name IS NOT NULL THEN
            RAISE NOTICE '方法1找到序列: %', seq_name;
            found_sequence := true;
        ELSE
            -- 尝试方法2: 从默认值中提取序列名
            RAISE NOTICE '方法1未找到序列，尝试从默认值中提取...';

            -- 从默认表达式中提取序列名
            IF info.default_expr IS NOT NULL AND info.default_expr LIKE 'nextval%' THEN
                -- 提取序列名，处理不同的格式
                seq_name := regexp_replace(
                    info.default_expr, 
                    E'nextval\\s*\\(\\s*\'([^\']+)\'[^)]*\\)', 
                    E'\\1'
                );

                -- 如果提取成功且没有模式前缀，加上模式前缀
                IF seq_name IS NOT NULL AND seq_name <> info.default_expr THEN
                    -- 检查是否已经有模式前缀
                    IF position('.' in seq_name) = 0 THEN
                        seq_name := info.schema_name || '.' || seq_name;
                    END IF;
                    RAISE NOTICE '方法2提取序列: %', seq_name;
                    found_sequence := true;
                END IF;
            END IF;
        END IF;

        -- 如果找到了序列，进行处理
        IF found_sequence THEN
            -- 获取当前最大值
            EXECUTE format('SELECT COALESCE(MAX(%I), 0) FROM %I.%I',
                          info.column_name, info.schema_name, info.table_name)
            INTO max_id;
            RAISE NOTICE '当前最大值: %', max_id;

            IF max_id > 0 THEN
                BEGIN
                                        sql := format('SELECT setval(%L, %s, false)', seq_name, max_id + 1);
                                        RAISE NOTICE '生成的SQL: %', sql;
                    EXECUTE sql;
                    RAISE NOTICE '✅ 已更新序列 % 为 %', seq_name, max_id + 1;
                EXCEPTION WHEN OTHERS THEN
                    RAISE NOTICE '❌ 更新序列失败: %', SQLERRM;
                END;
            ELSE
                BEGIN
                                        sql := format('SELECT setval(%L, 1, false)', seq_name);
                                        RAISE NOTICE '生成的SQL: %', sql;
                    EXECUTE sql;
                    RAISE NOTICE '✅ 已重置序列 % 为 1', seq_name;
                EXCEPTION WHEN OTHERS THEN
                    RAISE NOTICE '❌ 重置序列失败: %', SQLERRM;
                END;
            END IF;
        ELSE
            RAISE NOTICE '⚠  未找到关联的序列';
        END IF;

        RAISE NOTICE '---';
        found_sequence := false;
    END LOOP;
END;
$$;

三、解释说明

3.1 pg_sequences

pg_sequences 是 PostgreSQL 中的系统视图，用于集中展示数据库中所有序列（Sequence）的关键属性信息。其核心作用是简化序列的监控与管理，尤其在数据迁移、主键同步或性能优化场景中至关重要。

核心功能：序列元数据集中管理

pg_sequences 汇总了所有序列的配置和状态信息，主要字段包括：

  • schemaname 序列所属的模式（命名空间），如 public。

  • sequencename 序列的名称，例如 users_id_seq。

  • sequenceowner 序列的所有者（通常是创建该序列的数据库角色）。

  • start_value 序列的初始值（如 1）。

  • min_value / max_value 序列允许的最小值/最大值（如 1 和 9223372036854775807）。

  • increment_by 序列的步长（增量），通常为 1。

  • last_value 关键字段：序列最后一次持久化到磁盘的值（注意：若启用缓存，该值可能滞后于当前内存中的最新值）

3.2 应用场景

  1. 序列状态监控与修复 问题：批量导入数据后，序列的 last_value 可能小于表主键的最大值，导致主键冲突。 解决：通过 pg_sequences 快速定位需修复的序列，并执行：
SELECT setval('序列名', (SELECT MAX(id) FROM 表名) + 1);

  1. 跨数据库迁移验证 从 Oracle 迁移到 PostgreSQL 时，需确保序列属性（如起始值、步长）一致。 通过查询 pg_sequences 对比源库序列配置，避免因序列不同步引发主键冲突。

  2. 序列设计合理性检查 分析 min_value、max_value 和 last_value，预测序列剩余可用范围。 示例：若 last_value 接近 max_value，需及时扩展序列上限或重置，防止溢出错误。

  3. 权限与依赖关系审计 结合 pg_class 和 pg_depend 系统表，通过 sequencename 追溯序列关联的表、函数等对象，辅助安全审计。

3.3 脚本代码解析

目标定位

遍历 pip_pass 模式下所有包含 nextval 默认值的列（即使用序列作为主键或唯一标识的表）：

SELECT table_schema||'.'||table_name, column_name 
FROM information_schema.columns 
WHERE table_schema = 'pip_pass' AND column_default LIKE 'nextval%'

动态 SQL 执行

为每个表生成查询最大值的 SQL：

exsql := 'SELECT max(' || info.column_name || ') FROM ' || info.tablename;

执行该 SQL 并存储结果到 resultId：

EXECUTE exsql INTO resultId;

序列值更新 若最大值 resultId > 0，则生成更新序列的 SQL：

updateidvar := 'SELECT setval(''' || info.tablename || '_' || info.column_name || '_seq'', %s)';
EXECUTE format(updateidvar, resultId + 1);

逻辑：将序列的下一个值设置为 当前最大值 + 1，确保新插入数据的主键不冲突。

技术笔记
  • 全屏阅读F11
  • 打赏支持
  • 快速评论

评论

评论列表

暂无评论

文章目录

    查看评论