Полезные скрипты при миграции из Oracle в PostgreSQL

    Во время миграции из Oracle в PostgreSQL с помощью ora2pg встал вопрос с несоответствием типов данных между разными базами. По умолчанию не все колонки конвертируется правильно, а отсутствие в Oracle Boolean и вовсе порождает неоднозначность – часть колонок нужно перенести как числа, часть как логические значения. В тоже время hibernate знает все о типах данных и может создать эталонную схему.

    Итоговый процесс переноса выглядел следующим образом: создание структуры таблиц через ora2pg, исправление структуры по эталонной схеме, перенос данных, конвертация blob и Boolean, добавление отсутствующих в PostgreSQL функций (nvl, nvl2, regexp_substr), создания оставшейся структуры — индексов, view и прочего.

    Под катом накопившиеся за время конвертации sql скрипты для полуавтоматической миграции.

    Подготовка


    В качестве утилиты для конвертации данных использовалась ora2pg. Процесс использования очень хорошо описан в статье.

    Создаем проект в ora2pg, настраиваем проект, и генерируем схему.

    $./export_schema.sh

    Создаем в PostgreSQL схему 'ora_schema' и таблицы по файлу './schema/tables/table.sql'
    Переключаем Hibernate в режим create и создаем еще одну эталонную схему 'hb_schema'. В случае использования view в проекте, число таблиц в разных схемах не сойдется. Hibernate сгенерирует полноценные таблицы вместо view и это следует учитывать.

    Исправление типов колонок


    Смотрим в каких колонках надо произвести изменения

    select
      hb.table_name as table , hb.column_name as column, hb.data_type as hb_type, ora.data_type as ora_type
    from
      information_schema.columns hb
      join information_schema.columns ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name
    where
      hb.table_schema = 'hb_schema'
      and ora.table_schema= 'ora_schema'
      and ora.data_type !=  hb.data_type
    ORDER BY hb.table_name, hb.column_name;

    Замены для простых случаев

    select 'ALTER TABLE ora_schema.' || ora.table_name || ' ALTER COLUMN ' || ora.column_name || ' TYPE ' || hb.data_type
           || case when hb.data_type = 'numeric' then '('|| hb.numeric_precision ||','|| hb.numeric_scale||')' else '' end  || '; '
    from information_schema.columns as hb
      INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name
    where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
          and ora.data_type !=  hb.data_type
          and (hb.data_type = 'integer' or hb.data_type = 'numeric' or hb.data_type = 'bigint' or hb.data_type = 'date')
    ORDER BY hb.table_name, hb.column_name;

    Переносим данные


    Перед переносом данных проверяем, что не совпадения типов остались только в колонках boolean и bytea(oid), а все колонки с численными значениями имеют правильную точность и не являются ‘double precision’ во избежание случайного округления.

    По умолчанию процесс копирования идет в один поток. Меняем в конфигурации на нужное число

    JOBS 4
    ORACLE_COPIES 4
    

    Запускаем процесс копирования данных

    ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf

    Обрабатываем boolean


    Для них требуется удалить если есть значение по умолчанию, поменять тип и вернуть значение по умолчанию

    --ALTER TABLE mytabe ALTER COLUMN mycolumn DROP DEFAULT;
    --ALTER TABLE mytabe ALTER mycolumn TYPE bool USING CASE WHEN mycolumn is null then null when mycolumn=0 THEN FALSE ELSE TRUE END;
    --ALTER TABLE mytabe ALTER COLUMN mycolumn SET DEFAULT FALSE;
    
    select case when ora.column_default is not null then
      'ALTER TABLE ora_schema.'|| ora.table_name || ' ALTER COLUMN ' || ora.column_name || ' DROP DEFAULT; ' else '' end ||
      'ALTER TABLE ora_schema.'|| ora.table_name || ' ALTER ' || ora.column_name ||
           ' TYPE bool USING CASE WHEN ' || ora.column_name ||' is null then null WHEN ' || ora.column_name ||'=0 THEN FALSE ELSE TRUE END;'
          || case when cast(ora.column_default as NUMERIC) = 0 then
              'ALTER TABLE ora_schema.'|| ora.table_name || ' ALTER COLUMN ' || ora.column_name || ' SET DEFAULT FALSE'
             when cast(ora.column_default as NUMERIC) = 1 then
             'ALTER TABLE ora_schema.'|| ora.table_name || ' ALTER COLUMN ' || ora.column_name || ' SET DEFAULT TRUE'
           else '' end
    from information_schema.columns as hb
      INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name
    where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
          and hb.column_name = ora.column_name
          and hb.udt_name != ora.udt_name
          and hb.data_type = 'boolean'
    ORDER BY hb.table_name, hb.column_name;

    Обрабатываем oid(bytea)


    Создаем процедуру для конвертации bytea в oid

    CREATE OR REPLACE FUNCTION ora_schema.bytea_to_oid(p_blob bytea)
      RETURNS oid AS
    $BODY$
    declare
      v_oid oid;
      v_int integer;
    begin
      if octet_length(p_blob)=0 then
        v_oid:=null;
      else
        select lo_create(0) into v_oid;
        select lo_open(v_oid, x'20000'::int | x'40000'::int) into v_int;
        select lowrite (0, p_blob) into v_int;
      end if;
      return v_oid;
    END;$BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
    ALTER FUNCTION ora_schema.bytea_to_oid(bytea)
    OWNER TO postgres;

    Создаем временную колонку

    select
     'ALTER TABLE ora_schema.' || ora.table_name || ' ADD COLUMN ' || hb.column_name || '_oid oid; '
    from information_schema.columns as hb
       JOIN information_schema.columns as ora on hb.table_name = ora.table_name  and hb.column_name = ora.column_name
    where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
          and hb.udt_name != ora.udt_name
          and hb.data_type = 'oid'
    ORDER BY hb.table_name, hb.column_name;

    Переносим данные
    select
    'update ' || ora.table_name || ' set ' || hb.column_name || '_oid = bytea_to_oid(' || hb.column_name || ');'
    from information_schema.columns as hb
       JOIN information_schema.columns as ora on hb.table_name = ora.table_name  and hb.column_name = ora.column_name
    where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
          and hb.udt_name != ora.udt_name
          and hb.data_type = 'oid'
    ORDER BY hb.table_name, hb.column_name;

    Удаляем старую колонку

    select 'ALTER TABLE ora_schema.' || ora.table_name || ' DROP COLUMN ' || hb.column_name || '; '
    from information_schema.columns as hb
      INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name       and hb.column_name = ora.column_name
    where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
          and hb.udt_name != ora.udt_name
          and hb.data_type = 'oid'
    ORDER BY hb.table_name, hb.column_name;

    Переименовываем временную колонку

    select 'ALTER TABLE ora_schema.' || ora.table_name || ' RENAME COLUMN ' || hb.column_name ||'_oid to '|| hb.column_name||'; '
    from information_schema.columns as hb
      INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name ||'_oid' = ora.column_name
    where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
          and hb.data_type = 'oid'
    ORDER BY hb.table_name, hb.column_name;

    Добавление отсутствующих в PostgreSQL функций


    Чтобы не было необходимости в переписывании кода, просто создадим, отсутствующие в PostgreSQL, но которые есть в Oracle и используются в проекте.

    last_day(date)

    create function last_day(dt date) returns date
    LANGUAGE SQL
    AS $$
    select cast (date_trunc('month',dt)+interval '1 month' - interval '1 day' as date)
    $$;

    nvl(date, date)

    create function nvl(var1 date, var2 date) returns date
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl(integer, integer)

    create function nvl(var1 integer, var2 integer) returns integer
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl(numeric, numeric)

    create function nvl(var1 numeric, var2 numeric) returns numeric
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl(text, text)

    create function nvl(var1 text, var2 text) returns text
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl(timestamp with time zone, timestamp with time zone)

    create function nvl(var1 timestamp with time zone, var2 timestamp with time zone) returns timestamp with time zone
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl(timestamp, timestamp)

    create function nvl(var1 timestamp without time zone, var2 timestamp without time zone) returns timestamp without time zone
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl(varchar, varchar)

    create function nvl(var1 character varying, var2 character varying) returns character varying
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl2(date, date, date)

    create function nvl2(var1 date, var2 date, var3 date) returns date
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    nvl2(integer, integer, integer)

    create function nvl2(var1 integer, var2 integer, var3 integer) returns integer
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    nvl2(numeric, numeric, numeric)

    create function nvl2(var1 numeric, var2 numeric, var3 numeric) returns numeric
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    nvl2(text, text, text)

    create function nvl2(var1 text, var2 text, var3 text) returns text
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    nvl(timestamp with time zone, timestamp with time zone)

    create function nvl2(var1 timestamp with time zone, var2 timestamp with time zone, var3 timestamp with time zone) returns timestamp with time zone
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    nvl2(timestamp, timestamp, timestamp)

    
    create function nvl2(var1 timestamp without time zone, var2 timestamp without time zone, var3 timestamp without time zone) returns timestamp without time zone
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    nvl2(varchar, varchar, varchar)

    create function nvl2(var1 character varying, var2 character varying, var3 character varying) returns character varying
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    regexp_substr(text, text)

    create function regexp_substr(str text, pattern text) returns text
    LANGUAGE SQL
    AS $$
    SELECT (regexp_matches(str, pattern))[1]
    $$;

    regexp_substr(varchar, varchar)

    create function regexp_substr(str character varying, pattern character varying) returns text
    LANGUAGE SQL
    AS $$
    SELECT (regexp_matches(str, pattern))[1]
    $$;

    trunc(date, varchar)

    create function trunc(dt date, formatstr character varying) returns date
    LANGUAGE plpgsql
    AS $$
    begin
    return date_trunc(formatstr,dt);
    end;
    $$;

    trunc(timestamp with time zone, varchar)

    create function trunc(dt timestamp with time zone, formatstr character varying) returns date
    LANGUAGE plpgsql
    AS $$
    begin
    return date_trunc(formatstr,dt);
    end;
    $$;

    trunc(timestamp, varchar)

    create function trunc(dt timestamp without time zone, formatstr character varying) returns date
    LANGUAGE plpgsql
    AS $$
    begin
    return date_trunc(formatstr,dt);
    end;
    $$;

    Текстовые поля


    В Oracle нет неограниченного текстового поля, вместо этого используется Lob. В PostgreSQL есть специальный тип — text. Чтобы можно было использовать обе базы аннотации текстовых полей должны быть следующими

    @Column(name = "script", nullable = true)
    @Type(type = "org.hibernate.type.MaterializedClobType")
    public String scriptText;


    Для Postgres также используем самописный диалект, знающий о добавленных функциях
    public class PostgresDialect extends PostgreSQL9Dialect {
        public PostgresDialect() {
            super();
            this.registerFunction("nvl", new StandardSQLFunction("nvl"));
        }
    
        public SqlTypeDescriptor getSqlTypeDescriptorOverride(int sqlCode) {
            Object descriptor;
            switch (sqlCode) {
                case 2005:
                    descriptor = LongVarcharTypeDescriptor.INSTANCE;
                    break;
                default:
                    descriptor = super.getSqlTypeDescriptorOverride(sqlCode);
            }
            return (SqlTypeDescriptor) descriptor;
        }
    }


    Sequence


    Oracle и Postgress имеют разный синтаксис nextval от Sequence.
    Oracle
    SELECT my_seq.nextval;

    Postgres
    select nextval('my_seq');

    Приводим к общему виду. Для этого создаем функции в Postgres и Oracle и везде переписываем на использование этой функции.
    Oracle
    create or replace function seq_nextval(p_sequence_name varchar)
      return integer
    as
      l_statement varchar(4000);
      l_value     integer;
    begin 
      l_statement := 'select '||upper(p_sequence_name)||'.nextval from dual';
      execute immediate l_statement
        into l_value;
      return l_value;
    end;

    Postgres
    create function seq_nextval(p_sequence_name text)
      returns bigint
    as
    $$
       select nextval(p_sequence_name);
    $$
    language sql;

    Использование
    select seq_nextval('my_seq')

    Использовать напрямую функцию Postgres nextval('my_seq') не получиться, так как хотя Oracle и позволит создать данную функцию, но не позволит выбирать значения.

    Transformers.ALIAS_TO_ENTITY_MAP


    Если используется Transformers.ALIAS_TO_ENTITY_MAP то надо обязательно указывать возвращаемые типы. Oracle по умолчанию приведет ключи к верхнему регистру, PotgresSql к нижнему и несовпадение ключей исправляется только вручную.

    Sysdate


    Sysdate нет в PotgresSql, а current_timestamp работает в обоих бд. Заменяем на него

    substring


    В отличие от java, в базах данных индексы начинаются с единицы и правильно указывать substring('str', 1, 2) чтобы получить первые два символа, а не substring('str', 0, 2). Но Oracle допускает и второй вариант. Необходимо исправить и не использовать 0 индекс
    Поделиться публикацией
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама
    Комментарии 10
    • 0
      Ещё одна причина пользоваться хэлперами для бд, как при создании, так и при запросах к таблице.
      • 0
        Можете сказать, что вы подразумеваете под хэлперами и чем они помогут при переносе?
        • 0
          Практически для каждого языка программирования существуют хэлперы для работы с БД. Они позволяют вместо написания sql запросов писать на ЯП, при чем, в настройках хэлпера можно указать тип бд, а он в свою очередь уже будет генерировать запрос в зависимости от синтаксиса. Пример
          SELECT * FROM TABLE WHERE ATTR1>1 LIMIT 0, 10
          SELECT TOP 10 * FROM TABLE WHERE ATTR1>1 
          SELECT * FROM TABLE WHERE ARRT1>1 and ROWNUM<=10
          

          В случае хелпера это может выглядить следующим образом
          var sql = helper("mssql").select("*").from("table").where("attr1>1").limit("10")
          

          Вот что касается переноса, они ни чем не помогут, просто в случае переноса, не надо переписывать кучу запросов. Просто есть яркий пример миграции с одной бд на другую, и им пришлось переписывать кучу запросов.

          • 0
            По текущему опыту переноса относительно крупного проекта могу сказать, что переписывать кучу запросов не пришлось, а в тех случаях где пришлось хэлпер бы не помог. Встретил лишь два типа проблем или отсутствующие функции (nvl, to_number) или работа с boolean.
            Есть еще некоторое число больших сложных отчетов, где могут возникнуть проблемы, но их писать в хэлпере не удобно. Sql на 200-500 в подобном виде практически не читаемы и их труднее отлаживать.
            • 0
              sql на 200-500 в принципе не очень читабельны ) Но что поделать. Я после приведенного выше примера с переписыванием кучи sql придерживаюсь мнения что лучше писать запросы через хэлперы.
              • 0
                Все зависит от того как писать, если использовать конструкцию with и бить на куски, то 500 строчек sql разбиваются на десяток относительно простых, понятных и легких запросов. Которые можно отлаживать последовательно друг за другом. Не самое приятное дело, но и ничего титанического в этом нет.
                Касательно лимита по числу строк, у нас в проекте используется hibernate + нативные запросы через него и именно этот тонкий вопрос различий БД удалось обойти, но можно ли считать его хэлпером )

      • 0
        Спасибо, мне понравилось.

        Ещё, если интересно, можно вот здесь посмотреть (вдруг, что полезное найдётся):

        www.sqlines.com/home

        www.sqlines.com/oracle-to-postgresql
        • +1
          Похоже, что переход с Oracle на Postgres становится массовым. Еще заметил, что не менее массово проекты переносят с Hadoop/Hive/Spark на тот же Postgres.
          • 0
            Вы цены на Оракл Ентерпайз на число ядер современных серверов умножали? :)
            • 0
              Цены на Оракл стали уж совсем неприличными и дешевле залить просевшую производительность железом. Дополнительно в связи с «импортозамещением» бюджетные организации не очень любят оплачивать стоимость этих лицензий и проще уговорить на более мощное железо.

          Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.