Geekly Articles each Day

OracleからPostgreSQLに移行するときに役立つスクリプト

ora2pgを使用したOracleからPostgreSQLへの移行中に、異なるデータベース間でのデータ型の不一致の問題が発生しました。 デフォルトでは、すべての列が正しく変換されるわけではなく、Oracleブール値がないためにあいまいさが生じます。一部の列は数値として、一部は論理値として転送する必要があります。 同時に、Hibernateはデータ型についてすべてを知っており、リファレンス回路を作成できます。

最終的な転送プロセスは次のとおりでした：ora2pgによるテーブル構造の作成、参照スキームに従って構造の修正、データの転送、blobおよびBooleanの変換、PostgreSQLにない関数（nvl、nvl2、regexp_substr）の追加、インデックス、ビューなどの残りの構造の作成。

catの下では、sqlの変換中に蓄積された半自動移行用のsqlスクリプト。

準備する


データ変換のユーティリティとして、ora2pgが使用されました。 使用プロセスは、 記事で非常によく説明されています 。

ora2pgでプロジェクトを作成し、プロジェクトを設定して、スキームを生成します。

$./export_schema.sh

PostgreSQLの「./schema/tables/table.sql」ファイルに「ora_schema」スキーマとテーブルを作成します。
Hibernateを切り替えて作成モードにし、別の「hb_schema」参照回路を作成します。 プロジェクトでビューを使用する場合、異なるスキームのテーブルの数は収束しません。 Hibernateは、ビューの代わりに完全なテーブルを生成します。これを考慮する必要があります。

列タイプの修正


変更する列を調べます

 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）列にのみ型の不一致が残っていること、および数値を持つすべての列が正しい精度を持ち、偶発的な丸めを避けるための「倍精度」ではないことを確認します。

デフォルトでは、コピープロセスは1つのストリームに移動します。 構成を目的の番号に変更します

 JOBS 4 ORACLE_COPIES 4

データをコピーするプロセスを開始します

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

処理ブール


デフォルト値がある場合は削除する必要があり、タイプを変更してデフォルト値を返す

 --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（日付）

 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（日付、日付）

 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（整数、整数）

 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（数値、数値）

 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（テキスト、テキスト）

 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（タイムゾーン付きタイムスタンプ、タイムゾーン付きタイムスタンプ）

 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（タイムスタンプ、タイムスタンプ）

 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（日付、日付、日付）

 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（整数、整数、整数）

 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（数値、数値、数値）

 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（テキスト、テキスト、テキスト）

 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（タイムゾーン付きタイムスタンプ、タイムゾーン付きタイムスタンプ）

 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（タイムスタンプ、タイムスタンプ、タイムスタンプ）

 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（テキスト、テキスト）

 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（日付、varchar）

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

trunc（タイムゾーン付きのタイムスタンプ、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（タイムスタンプ、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には特別なタイプ-テキストがあります。 両方のベースを使用できるようにするには、テキストフィールドの注釈を次のようにする必要があります

 @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; } }


シーケンス


OracleとPostgressのシーケンスとは異なるnextval構文があります。
オラクル
 SELECT my_seq.nextval;

ポストグレス
 select nextval('my_seq');

一般的な見方をします。 これを行うには、Postgresと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;

ポストグレス
 create function seq_nextval(p_sequence_name text) returns bigint as $$ select nextval(p_sequence_name); $$ language sql;

使用する
 select seq_nextval('my_seq')

Oracleではこの関数を作成できますが、値を選択することはできないため、Postgres nextval（ 'my_seq'）関数を直接使用することはできません。

Transformers.ALIAS_TO_ENTITY_MAP


Transformers.ALIAS_TO_ENTITY_MAPを使用する場合、戻り値の型を指定する必要があります。 Oracleはデフォルトで大文字のキー、PotgresSqlは小文字、キーの不一致は手動でのみ修正されます。

シスデート


SysdateはPotgresSqlにはなく、current_timestampは両方のデータベースで機能します。 それと交換

部分文字列


Javaとは異なり、データベースでは、インデックスは1で始まり、サブストリング（ 'str'、0、2）ではなく最初の2文字を取得するためにサブストリング（ 'str'、1、2）を正しく指定します。 しかし、Oracleは2番目のオプションを認めています。 修正する必要があり、0インデックスを使用しません

一時テーブル


PostgreSQLでは、Oracleとは異なり、一時テーブルは毎回作成され、セッション内で有効になります。 Oracleでは、一時テーブルの内容のみが何らかのフレームワークに存在し、テーブル自体は常に作成されます。
これにより、次の問題が発生します。
1）各セッションを作成するとき、一時テーブルを作成する必要があります。 （この瞬間は、新しいセッションを作成するときに初期化sql-blockを実行するアプリケーションサーバーの機能を使用して簡略化できます（接続プール→詳細設定→初期化SQL）
2）休止状態のエンティティの検証は、構成されたスキームのフレームワーク内で実行されます（スキームを指定しない場合、使用可能なすべてのスキームで検証が実行されます）。 なぜなら Postgreの一時テーブルは別のスキームで作成されるため、検証は失敗します。
（同じ構造の実際のテーブルを作成することで回避できます。ネイティブクエリの場合、作業は通常のテーブルではなく一時テーブルで行われます）。
3）ネイティブクエリを介してのみ、このような一時テーブルを操作することができます。 Hibernateは、生成されたすべてのクエリにスキーマ名を追加します（ネイティブクエリを介して一時テーブルですべての作業を行うか、一時テーブルを破棄して、一意のキーでデータを区別する通常のテーブルを使用する必要があります。どちらの方法でも機能を書き換える必要があります）。

Oracleのように、Postgressで一時テーブルを引き続き使用するには、テーブル関数を使用し、テーブル関数に基づいてビューを作成します。 詳細はこちら

一時テーブルを作成する機能
create_permanent_temp_table
 create or replace function create_permanent_temp_table( p_table_name varchar, p_schema varchar default null) returns void as $$ declare -- https://github.com/yallie/pg_global_temp_tables v_table_name varchar := p_table_name || '$tmp'; v_trigger_name varchar := p_table_name || '$iud'; v_final_statement text; v_table_statement text; -- create temporary table... v_all_column_list text; -- id, name, ... v_new_column_list text; -- new.id, new.name, ... v_assignment_list text; -- id = new.id, name = new.name, ... v_cols_types_list text; -- id bigint, name varchar, ... v_old_column_list text; -- id = old.id, name = old.name, ... v_old_pkey_column text; -- id = old.id begin -- check if the temporary table exists if not exists(select 1 from pg_class where relname = p_table_name and relpersistence = 't') then raise exception 'Temporary table % does not exist. %', p_table_name, 'Create an ordinary temp ' || 'table first, then use create_permanent_temp_table function to convert it to a permanent one.' using errcode = 'UTMP1'; end if; -- make sure that the schema is defined if p_schema is null or p_schema = '' then p_schema := current_schema; end if; -- generate the temporary table statement with pkey as ( select cc.conrelid, format(E', constraint %I primary key(%s)', cc.conname, string_agg(a.attname, ', ' order by array_position(cc.conkey, a.attnum))) pkey from pg_catalog.pg_constraint cc join pg_catalog.pg_class c on c.oid = cc.conrelid join pg_catalog.pg_attribute a on a.attrelid = cc.conrelid and a.attnum = any(cc.conkey) where cc.contype = 'p' group by cc.conrelid, cc.conname ) select format(E'\tcreate temporary table if not exists %I\n\t(\n%s%s\n\t)\n\ton commit drop;', v_table_name, string_agg( format(E'\t\t%I %s%s', a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), case when a.attnotnull then ' not null' else '' end ), E',\n' order by a.attnum ), (select pkey from pkey where pkey.conrelid = c.oid)) as sql into v_table_statement from pg_catalog.pg_class c join pg_catalog.pg_attribute a on a.attrelid = c.oid and a.attnum > 0 join pg_catalog.pg_type t on a.atttypid = t.oid where c.relname = p_table_name and c.relpersistence = 't' group by c.oid, c.relname; -- generate the lists of columns select string_agg(a.attname, ', '), string_agg(format('new.%I', a.attname), ', '), string_agg(format('%I = new.%I', a.attname, a.attname), ', '), string_agg(format('%I %s', a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod)), ', '), string_agg(format('%I = old.%I', a.attname, a.attname), ' and ') into v_all_column_list, v_new_column_list, v_assignment_list, v_cols_types_list, v_old_column_list from pg_catalog.pg_class c join pg_catalog.pg_attribute a on a.attrelid = c.oid and a.attnum > 0 join pg_catalog.pg_type t on a.atttypid = t.oid where c.relname = p_table_name and c.relpersistence = 't'; -- generate the list of primary key columns select string_agg(format('%I = old.%I', a.attname, a.attname), ' and ' order by array_position(cc.conkey, a.attnum)) into v_old_pkey_column from pg_catalog.pg_constraint cc join pg_catalog.pg_class c on c.oid = cc.conrelid join pg_catalog.pg_attribute a on a.attrelid = cc.conrelid and a.attnum = any(cc.conkey) where cc.contype = 'p' and c.relname = p_table_name and c.relpersistence = 't' group by cc.conrelid, cc.conname; -- if primary key is defined, use the primary key columns if length(v_old_pkey_column) > 0 then v_old_column_list := v_old_pkey_column; end if; -- generate the view function v_final_statement := format(E'-- rename the original table to avoid the conflict alter table %I rename to %I; -- the function to select from the temporary table create or replace function %I.%I() returns table(%s) as $x$ begin -- generated by pg_global_temp_tables -- create table statement %s return query select * from %I; end; $x$ language plpgsql set client_min_messages to error;\n', p_table_name, v_table_name, p_schema, p_table_name, v_cols_types_list, v_table_statement, v_table_name); -- generate the view v_final_statement := v_final_statement || format(E' create or replace view %I.%I as select * from %I.%I();\n', p_schema, p_table_name, p_schema, p_table_name); -- generate the trigger function v_final_statement := v_final_statement || format(E' create or replace function %I.%I() returns trigger as $x$ begin -- generated by pg_global_temp_tables -- create temporary table %s -- handle the trigger operation if lower(tg_op) = \'insert\' then insert into %I(%s) values (%s); return new; elsif lower(tg_op) = \'update\' then update %I set %s where %s; return new; elsif lower(tg_op) = \'delete\' then delete from %I where %s; return old; end if; end; $x$ language plpgsql set client_min_messages to error;\n', p_schema, v_trigger_name, v_table_statement, -- function header v_table_name, v_all_column_list, v_new_column_list, -- insert v_table_name, v_assignment_list, v_old_column_list, -- update v_table_name, v_old_column_list); -- delete -- generate the view trigger v_final_statement := v_final_statement || format(E' drop trigger if exists %I on %I.%I; create trigger %I instead of insert or update or delete on %I.%I for each row execute procedure %I.%I();', v_trigger_name, p_schema, p_table_name, v_trigger_name, p_schema, p_table_name, p_schema, v_trigger_name); -- create all objects at once execute v_final_statement; end; $$ language plpgsql set client_min_messages to error;


一時テーブルを削除する機能
drop_permanent_temp_table
 create or replace function drop_permanent_temp_table( p_table_name varchar, p_schema varchar default null) returns void as $$ declare -- https://github.com/yallie/pg_global_temp_tables v_table_name varchar := p_table_name || '$tmp'; v_trigger_name varchar := p_table_name || '$iud'; v_count int; v_drop_statements text; begin -- make sure that the schema is defined if p_schema is null or p_schema = '' then p_schema := current_schema; end if; -- check if the supporting functions exist select count(*) into v_count from pg_catalog.pg_proc p join pg_catalog.pg_namespace n on n.oid = p.pronamespace where p.proname in (p_table_name, v_trigger_name) and p.pronargs = 0 and n.nspname = p_schema and p.prosrc like '%pg_global_temp_tables%'; if v_count <> 2 then raise exception 'The table %.% does not seem to be persistent temporary table. %', p_schema, p_table_name, 'The function only supports tables created by pg_global_temp_tables library.' using errcode = 'UTMP2'; end if; -- generate the drop function statements v_drop_statements := format(E'-- drop the functions and cascade the view drop function %I.%I() cascade; drop function %I.%I() cascade;', p_schema, p_table_name, p_schema, v_trigger_name); -- drop the functions execute v_drop_statements; end; $$ language plpgsql set client_min_messages to error;


使用する
通常の一時テーブルを作成する
 CREATE temp TABLE filter_table ( id BIGINT NOT NULL, id_str VARCHAR(255), key VARCHAR(255) NOT NULL, fd DATE, id_long BIGINT, sd DATE, CONSTRAINT filter_table_pkey PRIMARY KEY (key, id) ) ON COMMIT PRESERVE ROWS;

生成関数を実行します：
 select create_permanent_temp_table('filter_table', 'schema_name');

Source: https://habr.com/ru/post/J340566/

More articles:


All Articles