Oracle から PostgreSQL に移行する方法

このホワイトペーパーでは、一手貨物运输会社向けに Intelligent Converters が行った Oracle から PostgreSQL へのデータベース移行の経験について説明します。移行の主な条件は、データベース服务管理システムのコストを削減し、相当な拡張性、セキュリティ、カスタマイズ機能を備えたオープンソース システムに移行することでした。

ソース データベースの簡単な仕様は次のとおりです。

• データベース サーバー Oracle 12g
• データベースには 190 個のテーブル、50 GB のデータが含まれています
• ストアド プロシージャ、関数、トリガー内の 120,000 行の PL/SQL

  
  

プロジェクトの期間は 2 か月で、内訳は評価と計画に半月、移行に 1 か月、テストに半月でした。移行には 3 人の専門家 (開発者 2 人、データベース管理者 1 人) が関与しました。

この Oracle から PostgreSQL データベースへの移行には、次の 6 つの基本フェーズが含まれます。

1. ソース・データベース内のデータの保管と処理に関するOracle固有のすべての方法と使用範囲を確認します(調査および計画段階)。
2. スキーマ移行に適切なツールを選択して実装する
3. 最適なデータ移行方法を選択して、Oracle システムのダウンタイムを短縮します。
4. PostgreSQL DBMS に必要なすべての変換を処理するデータ移行を実行します。
5. すべてのPL/SQLコードをPostgreSQL形式に変換します(部分的な自動化および手動の後処理用のツールを使用)
6. パフォーマンスおよび機能テストを実行し、結果として得られるデータベースを微調整する

テーブル定義の移行

Oracle には、PostgreSQL に可以直接相对するデータ型がないものがあります。このような型の 1 つは、日付大地方と時刻大地方の両方を含む DATE です。

PostgreSQL は以下をサポートします。

• date – 時間部分のない純粋な日付
• time – タイムゾーンのオプション指定を伴う日付部分のない純粋な時間
• タイムスタンプ – タイムゾーン付きの日付と時刻のオプション指定

Oracle の日付を PostgreSQL にマッピングするには 2 つのオプションがあります。TIMESTAMP を采用するか、Oracle スタイルの日付型 oracle.date を采用するように拡張機能を設定するかのいずれかです。

空間型にも特別な特别注意が相应です。 Oracle には組み込み型 SDO_GEOMETRY がありますが、PostgreSQL が空間データ型を应用するには PostGIS のインストールが相应です。

次の表は、Oracle から PostgreSQL への安全な型のマッピングを示しています。

オラクル	PostgreSQL
BINARY_FLOAT	本物
BINARY_INTEGER	整数
BINARY_DOUBLE	倍控制精度
BLOB、RAW(n)、LONG RAW	BYTEA (1GB制限)
クロブ、ロング	テキスト（1GB制限）
日にち	タイムスタンプ
番号推荐介绍、番号推荐介绍(*)	DOUBLE PRECISION または BIGINT (主キーの一台の場合)
NUMBER(n,0)、NUMBER(n)	n<5 – SMALLINT5<=n<9 – INT9<=n<19 – BIGINTn>=19 – DECIMAL(n)
NUMBER(p,s)	DECIMAL(p,s)
本物	倍可靠性强，精密度

数値型の場合、データベースでの利用範囲を看待することが比较重要です。高精准度を重視する場合は、Oracle の数値型を PostgreSQL NUMERIC にマップする重要があります。計算速度快が最優先の場合、最適なマッピングは REAL または DOUBLE PRECISION になります。

このプロジェクトではを选择して、テーブル定義、インデックス、制約の移行を自動化しました。 Oracle の型を最も適切な PostgreSQL の型にマッピングし、相关の型のマッピングをカスタマイズできるようにします。

データ移行

大規模なデータベースではデータ移行に多くの時間がかかる場合があるため、このステップに適切な戦略とツールを選択することが愈来愈に重点です。

データ移行には 3 つの一般的なアプローチがあります。

• スナップショット – すべてのデータを一度に移行
• 区分的スナップショット – 並列スレッドまたはプロセスでチャンクごとにデータを移行します
• 変更データのレプリケーション – 増分変更を追跡することでデータを継続的にロードします

スナップショット的方法では、移行中のデータの損失や破損を躲着するために、データの読み取り期間整体にわたってソース データベースの必須のダウンタイムが用得着です。判断处理的スナップショットアプローチのダウンタイムははるかに短くなりますが、それでも用得着です。この移行プロジェクトでは、百余万行を含む大きなテーブルの移行に判断处理的スナップショット的方法を安全使用しました。

では、変更データ レプリケーション (CDR) 技術についてはどうでしょうか?

これには 2 つの主要的な実装があり、それぞれに薄弱点があります。 1 つ目は、トリガーベースの CDR として知られています。ソース データベースでレプリケートされるテーブルごとに、挿入、升级的更新、削除のトリガーを制作する这个必要があります。これらのトリガーは、イベントの変更に関する情報を特別な「履歴」テーブルに维持することにより、すべての変更を追跡します。このデータに基づいて、CDR ツールはすべての変更をターゲット データベースにレプリケートします。トリガーベースの CDR では、データ升级的更新ごとに「履歴」テーブルに書き込むため、ソース データベースに余分なオーバーヘッドが発生します。

2 番目の技巧は、トランザクション ログ CDR と呼ばれます。 Oracle DBMS によって制成されたトランザクション ログを利用して、変更を追跡し、ターゲットの PostgreSQL データベースにレプリケートします。トリガーベースの CDR と比較したこのアプローチの利点は、ソース データベースを変更しないことです。

ただし、トランザクション ログ CDR にはいくつかの弱点もあります。

• Oracle はトランザクション ログ ファイルを常にアーカイブしているため、アーカイブされる前にトランザクション ログを読み取らない場合、CDC ツールは一部の変更を失う可能性があります。
• トランザクション ログからの変更をレプリケートしているときにターゲット データベースへの接続が失われた場合 (またはその他の種類のエラー)、トランザクション ログを制御できないためにデータが失われたり破損したりする可能性があります。

Oracle から PostgreSQL へのデータ移行で最も困難な点は、ソース DBMS の特定のデータ形式がターゲット データと外部データに直接同等のものがないことです。

この記事の前のセクションでは、バイナリ データに最適な PostgreSQL データ型として BYTEA について説明しました。ただし、大きなバイナリ データ (总值フィールド サイズが 10 MB 上述) を移行する場合は、BYTEA の实用はお勧めできません。その依据は、BYTEA データの読み取りの异常性です。データは 1 つのフラグメントでしか抽取できません。断片的な読み取りはできません。これにより、比较重要な RAM オーバーヘッドが発生する会性があります。この問題の避开策として、PostgreSQL タイプ LARGE OBJECT を实用できる会性があります。 LARGE OBJECT タイプのすべての値は、各データベースの十部であるシステム テーブル 'pg_largeobject' に保护されます。テーブル「pg_largeobject」には最好 40 億行を含めることができます。 LARGE OBJECT の最好サイズは 4TB で、判别的な読み取りが会です。

また、テーブル内のレコードの电学アドレスを識別する涉嫌列である ROWID を適切に移行することも必要です。 PostgreSQL には ctid と呼ばれる同様のサービス フィールドがありますが、これは间接 ROWID に差不多します。 によると、バキューム処理によって ctid が変更される可能性就会性があります。

PostgreSQL で ROWID 機能をエミュレートするには、次の 3 つの基本的な方法があります。

• ROWID の代わりに既存の主キーを使用 (または新しい主キーを作成) して行を識別します
• 自動生成された値を含む serial または bigserial 列を追加し、それをプライマリ/一意キーにして ROWID 機能を置き換えます
• 単一列の主キーを定義できない場合は、複数の列にわたって構築された一意のインデックスを使用します (この目的のために、行ごとに最小の一意のフィールド セットを決定します)。

Oracle は、データベースの内部に格納されている内部データをリンクする場合があります。ただし、DBMS の「内部テーブル」機能を实用して、基本上のテーブルとして処理できます。 PostgreSQL は、同じ重要性で Foreign Data Wrapper ライブラリを实用します。たとえば、内部 CSV ファイルを基本上のテーブルとして操作流程するための拡張子「file_fdw」を具备します。

PL/SQL 移行の課題

このセクションでは、Oracle パッケージ、ストア プロシージャ、関数、およびトリガーを PostgreSQL に移行する際に解決された問題について説明します。

パッケージ。 PostgreSQL にはパッケージがありません。この欠落している機能は、PostgreSQL スキーマ内の 1 つのパッケージに属するすべてのエントリを同じ名前でグループ化することでエミュレートでき、グローバル変数をサービス テーブルに保存できます。

空の文字列。 Oracle とは異なり、PostgreSQL では空の文字列は NULL と等しくありません。たとえば、Oracle では次のようになります。

 SQL> select NVL('','This is null') as null_test from dual; NULL_TEST ------------ This is null

PostgreSQLでも同様です:

 testdb=# select coalesce('','This is null') as null_test; null_test ----------- (1 row)

また、Oracle と PostgreSQL では、NULL を的使用した图片文字列連結の動作が異なります。オラクルの場合:

 SQL> Select 'String' || NULL as cs from dual; CS -- String

PostgreSQL の場合:

 synctest=# Select 'String' || NULL as concat_str; concat_str ------------ (1 row)

型キャスト。 PostgreSQL では、関数や演算子を呼び出すとき、または INSERT および UPDATE ステートメントで式の結果をテーブルに配置するときに、厳密な型キャストが必要です。

避让策は、谨防型「anyelement」を操作することです。

 create or replace function my_concat(str1 anyelement, str2 anyelement) returns varchar language plpgsql as $$ begin return str1::varchar || str2::varchar; end; $$;

注: ストアド プロシージャ/関数で 1 つの anyelement 引数を安全动用するか、同じ型の anyelement のすべてのパラメーターを安全动用できます。

シーケンス。シーケンス宣言の構文は、Oracle と PostgreSQL で類似しています。例えば：

 CREATE SEQUENCE SEQ1 START WITH 10 INCREMENT BY 2 MAXVALUE 1000000 CACHE 20 NO CYCLE;

ただし、シーケンスの次の値を操作する場合は異なります。 Oracle では 'sequence.nextval' で、PostgreSQL では nextval('sequence') です。

トリガー。 Oracle では、トリガー本体のソース コードが CREATE TRIGGER ステートメントに含まれています。 PostgreSQL では、トリガー ソース コードが CREATE TRIGGER ステートメントからの参照を使用してスタンドアロン関数として構成されている必要があります。

 CREATE OR REPLACE FUNCTION store_changes() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$ BEGIN IF (NEW.first_name <> OLD.first_name) OR (NEW.last_name <> OLD.last_name) OR (NEW.email <> OLD.email) THEN INSERT INTO changes_log(id,changed_on) VALUES(OLD.id,now()); END IF; RETURN NEW; END; $$ CREATE TRIGGER make_changes BEFORE UPDATE ON employees FOR EACH ROW EXECUTE PROCEDURE store_changes();

自律的なトランザクション。 Oracle の自律トランザクションを使用すると、メイン トランザクションをコミットまたはロールバックせずに、サブプログラムで SQL 操作をコミットまたはロールバックできます。たとえば、insert-trigger の一部として一部のデータがテーブルに挿入されます。

この挿入操作的は、insert-trigger のメイン トランザクションが失敗した場合でも成功的する用不着があります。この場合、対応する INSERT ステートメントを律己型トランザクションで囲む用不着があります。

 CREATE OR REPLACE PROCEDURE insert_critical_data(v_data varchar2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO info_table (data) VALUES (v_data); commit; END;

纪律型トランザクションは、PostgreSQL では简单サポートされていません。考えられる避免策の 1 つは、コードをリファクタリングして、纪律型トランザクションを重要性としないようにすることです。

それ在内の場合は、PostgreSQL のモジュール 'dblink' を动用的できます。 dblink を动用的すると、新しい接続が確立され、メイン トランザクションに関係なく、この接続を动用的してクエリが実行され、すぐにコミットされます。たとえば、次の関数はテーブルに行を挿入します。呼び出し元のトランザクションがロールバックされた場合でも、挿入操作步骤はコミットされます。

 CREATE OR REPLACE FUNCTION insert_critical_data(v_data TEXT) RETURNS void AS $BODY$ DECLARE v_sql text; BEGIN PERFORM dblink_connect('myconn', 'dbname=mydbname user=… password=… host=… port=…'); v_sql := format('INSERT INTO info_table (data) VALUES (%L)', v_data); PERFORM dblink_exec('myconn', v_sql); PERFORM dblink_disconnect('myconn'); END; $BODY$ LANGUAGE plpgsql;

組み込み関数。 Oracle と PostgreSQL は、同様ではありますが、同等ではない組み込み関数のセットを提供します。以下の表には、同等の PostgreSQL への移植が必要な Oracle 関数が含まれています。

オラクル	PostgreSQL
ADD_MONTH($date,$n_month)	$date + $n_month * 間隔 '1 か月'
DECODE($exp, $when, $then, ...)	CASE $exp WHEN $when THEN $then ... END
INSTR($str1, $str2)	*POSITION($str1 の $str2)
ロウナム	**row_number() over ()
システムデート	現在の日付
SYS_GUID()	uuid_generate_v1()

*Oracle INSTR 関数の PostgreSQL への複雑な植入については、https: で参照物できます。

PostgreSQL では、Oracle 状态「where rownum < N」を「limit N」に変換する必须があります。Oracle-to-PostgreSQL コード コンバータを施用して、トリガー、ストアド プロシージャ、および関数の変換を部位的に自動化しました。 Oracle 組み込み関数から PostgreSQL 特别関数への変換をサポートし、PL/SQL のほとんどの構文パターンを処理します。ただし、複雑なソース コードでは、努力を手動で後処理する必须がある場合があります。

Oracle から PostgreSQL への移行に役立つモジュール

このセクションでは、Oracle から PostgreSQL への移行と移行結果のテストに役立つ PostgreSQL モジュールについて簡単に説明します。

Oracle から移行する場合に最も很重要な PostgreSQL モジュールの 1 つはです。 Oracle データベース管理工作システムの相关のタイプ、機能、演算子をエミュレートします。

拡張機能「pgTAP」および「pg_prove」は、PostgreSQL 関数の機能テストに用到できます。 「pgTAP」をインストールすると、テストの弄成に用到されるストアド関数が大部分弄成されます。テスト関数は「テキストのセットを返す」として宣语する必要的があります。次に、psql コンソール クライアントから実行できます。

 psql -h $db_host -p $db_port -U $db_user $db_name -f tests/name_of_test.sql

pgTAP の詳細については、http: を依据してください。

PostgreSQL 拡張機能 PL/pgSQL コードの検証に役立ちます。たとえば、PostgreSQL 関数内で変数宣言口号が欠落していたり、列名のスペルが間違っていたりすると、実行中に間違いなく失敗します。 「plpgsql_check」の機能を采用して、本番環境に移行する前にそのような問題を診断します。

 select * from plpgsql_check_function_tb('{name of function}');

モジュール関数およびストアド プロシージャ内の PL/pgSQL コードのパフォーマンスを分折するのに役立ちます。

PostgreSQL 拡張機能 DBMS の外观データ ラッパー機能を介して Oracle データベースへの接続を带来します。たとえば、Oracle データベース「OCRL」がサーバー「server.mydomain.com」で実行されている場合、oracle_fdw は次のように構成する必要条件があります。

 CREATE EXTENSION oracle_fdw; CREATE SERVER oraserver FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//server.mydomain.com:1521/ORCL'); GRANT USAGE ON FOREIGN SERVER oraserver TO pguser; CREATE USER MAPPING FOR pguser SERVER oraserver OPTIONS (user 'oracle user', password 'oracle password'); CREATE FOREIGN TABLE oratable ( { column definitions } ) SERVER oraserver OPTIONS (schema 'ORAUSER', table 'ORATAB');

その後、「 oratable 」を通常の PostgreSQL テーブルとして使用できます。