Во время миграции из 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;