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 を使用するようにorafce拡張機能を設定するかのいずれかです。

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

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

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

このプロジェクトでは、Oracle から PostgreSQL へのコンバータを使用して、テーブル定義、インデックス、制約の移行を自動化しました。 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 に相当します。 PostgreSQL のドキュメントによると、バキューム処理によって 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 関数が含まれています。

*Oracle INSTR 関数の PostgreSQL への複雑な移植については、https: //www.postgresql.org/docs/9.0/static/plpgsql-porting.html#PLPGSQL-PORTING-APPENDIXで参照できます。

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

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

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

Oracle から移行する場合に最も重要な PostgreSQL モジュールの 1 つはorafceです。 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: //pgtap.org/documentation.htmlを参照してください。

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

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

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

PostgreSQL 拡張機能oracle_fdw は、 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 テーブルとして使用できます。