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

- такой же как Forbes, только лучше.

Во время миграции из 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
 '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 '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)
@Lob
@Type(type = "org.hibernate.type.TextType")
public String scriptText;

Вы можете помочь и перевести немного средств на развитие сайта



Комментарии (10):

  1. pan-alexey
    /#10484748

    Ещё одна причина пользоваться хэлперами для бд, как при создании, так и при запросах к таблице.

    • intet
      /#10485054

      Можете сказать, что вы подразумеваете под хэлперами и чем они помогут при переносе?

      • pan-alexey
        /#10486152

        Практически для каждого языка программирования существуют хэлперы для работы с БД. Они позволяют вместо написания 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")
        

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

        • intet
          /#10486418

          По текущему опыту переноса относительно крупного проекта могу сказать, что переписывать кучу запросов не пришлось, а в тех случаях где пришлось хэлпер бы не помог. Встретил лишь два типа проблем или отсутствующие функции (nvl, to_number) или работа с boolean.
          Есть еще некоторое число больших сложных отчетов, где могут возникнуть проблемы, но их писать в хэлпере не удобно. Sql на 200-500 в подобном виде практически не читаемы и их труднее отлаживать.

          • pan-alexey
            /#10486690

            sql на 200-500 в принципе не очень читабельны ) Но что поделать. Я после приведенного выше примера с переписыванием кучи sql придерживаюсь мнения что лучше писать запросы через хэлперы.

            • intet
              /#10486904

              Все зависит от того как писать, если использовать конструкцию with и бить на куски, то 500 строчек sql разбиваются на десяток относительно простых, понятных и легких запросов. Которые можно отлаживать последовательно друг за другом. Не самое приятное дело, но и ничего титанического в этом нет.
              Касательно лимита по числу строк, у нас в проекте используется hibernate + нативные запросы через него и именно этот тонкий вопрос различий БД удалось обойти, но можно ли считать его хэлпером )

  2. a-l-e-x
    /#10484836

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

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

    www.sqlines.com/home

    www.sqlines.com/oracle-to-postgresql

  3. maxood
    /#10484838

    Похоже, что переход с Oracle на Postgres становится массовым. Еще заметил, что не менее массово проекты переносят с Hadoop/Hive/Spark на тот же Postgres.

    • vlanko
      /#10484998

      Вы цены на Оракл Ентерпайз на число ядер современных серверов умножали? :)

      • intet
        /#10485026

        Цены на Оракл стали уж совсем неприличными и дешевле залить просевшую производительность железом. Дополнительно в связи с «импортозамещением» бюджетные организации не очень любят оплачивать стоимость этих лицензий и проще уговорить на более мощное железо.