情報畑でつかまえてロゴ
本サイトは NTTテクノロスが旬の IT をキーワードに
IT 部門が今知っておきたい最新テクノロジーに関する情報をお届けするサイトです

ぬこのたのしいぽすぐれ教室 第2回

このora2pgがすごい!Oracleのストアドプロシージャ2種をPostgreSQLに移行してみた!

商用DBMSのOracleからPostgreSQLへの移行について紹介したいと思います。

はじめに

みなさんはご存知でしょうか? ここ数年、PostgreSQLが目覚しく進化していることを。

以前は商用DBMSでなければ利用できなかった同期レプリケーションや、CPUコア数の増加に伴う性能のスケールアップ、マテリアライズドビューやパラレルクエリなどの機能も使えるようになっています。

また、周辺ツールも充実しています。性能監視ツール pg_statsinfo や、SQLの実行計画を制御する pg_hint_plan 、日本語全文検索を行う pg_bigm ほか、PostgreSQLの利用用途を広めるさまざまなツールが存在します。

このような背景から、PostgreSQLを導入するシステムも多くなりました。今までは商用DBMSしか選択肢がなかったシステムでも、更改のタイミングでPostgreSQLに乗り換えを検討するところが増えています。

今回は、そんな状況を踏まえ、商用DBMSのOracleからPostgreSQLへの移行を試してみたいと思います。


OracleからPostgreSQLの移行にはどんな作業が必要?

OracleからPostgreSQLへの移行作業は、以下のように分類できます。

  • Oracleデータベースのオブジェクト(DDL)を、PostgreSQLに移行する。
  • Oracleデータベースに登録しているデータをPostgreSQLに移行する。
  • Oracle用のアプリケーションのSQLをPostgreSQL用に書き換える。

Oracle、PostgreSQL両方のDBMSに通じており、「Oracle用のDDL、SQLをPostgreSQL用に書き換えるなんて簡単!」というエンジニアであっても、全ての修正要否を目視で確認し、修正していくのは大変な労力を要するでしょう。

修正量自体も膨大です。DBテーブルが1000個あったとして、それを手作業で修正していくなんてのは・・・考えたくないですよね^^;

そんな問題を解決するために使用するのが「ora2pg」です!

ora2pgとは?

ora2pgはOracle/MySQLからPostgreSQLへの移行を支援するツールです。

Oracleデータベースからオブジェクト定義を読み出し、PostgreSQLで実行可能なDDLにコンバートします。 DBデータも同様に、PostgreSQLで実行可能なINSERT文の形式にコンバートすることが可能です。

コンバートした結果はファイルに出力されます。出力したファイルをPostgreSQLに対して実行するだけで、オブジェクト、データの移行が完了します。

ora2pgの主な機能

ora2pgの主な機能は以下の3つです。

  • OracleのオジェクトをPostgreSQLのDDLの書式にコンバートする
  • OracleのDBデータをPostgreSQLのINSERT文として出力する
  • アプリケーション(SQLファイル)をPostgreSQLで実行可能な形式にコンバートする

ツールの実行イメージは以下のとおり。

図:ora2pg→PostgreSQLへの移行の流れ

ora2pgでは、テーブルやインデックスといった一般的なオブジェクトの移行はもちろん、Oracle固有のオブジェクトも、PostgreSQLのDDLに置き換えてくれます。

たとえば、パーティションテーブル。

PostgreSQLでパーティションテーブルを作成するときは、テーブル、子テーブル、トリガなどの組み合わせを全て別々に定義し、パーティションテーブル構成を実現する必要がありますが、ora2pgではそれらの面倒な定義を一発変換してくれます。非常に楽です。

一方で、Oracleの「CREATE TABLE」文のPCTFREE句をPostgreSQLのFILLFACTOR句へ置き換えるといった、一見、文字列置換でできるような簡単な置き換えは行われません。こういったところは必要に応じて修正する必要があったりもします。

ora2pgで移行可能なOracleのオブジェクト種別

ora2pg.confに設定可能なオブジェクト種別は以下のとおり。 コンフィグファイルには明記されていませんが、「DBLINK」「SYNONYM」も指定可能です。

移行対象オブジェクト指定するキーワード説明
テーブル TABLE Export tables, constraints, indexes, ...
パッケージ PACKAGE Export packages
データ INSERT Export data from table as INSERT statement
データ COPY Export data from table as COPY statement
ビュー VIEW Export views
権限 GRANT Export grants
シーケンス SEQUENCE Export sequences
トリガ TRIGGER Export triggers
ユーザ関数 FUNCTION Export functions
プロシージャ PROCEDURE Export procedures
表領域 TABLESPACE Export tablespace (PostgreSQL >= 8 only)
データ型 TYPE Export user defined Oracle types
パーティション PARTITION Export range or list partition (PostgreSQL >= v8.4)
外部表 FDW Export table as foreign data wrapper tables
マテリアライズドビュー MVIEW Export materialized view as snapshot refresh view
SQL文 QUERY Convert Oracle SQL queries from a file.
XMLテンプレート KETTLE Generate XML ktr template files to be used by Kettle.
シノニム SYNONYM (説明なし)
データベースリンク DBLINK (説明なし)

この中から移行したいオブジェクト種別をひとつを選び、ora2pg.confに設定します。テーブルをOracleからPostgreSQLにコンバートする場合は以下のようにコンフィグファイルに設定します。

TYPE = TABLE

全てのオブジェクト、データを一度に全部コンバートすることはできません。

コンフィグファイルを設定してora2pgを実行すると、指定した1つのオブジェクト種別のみがPostgreSQLで実行可能な形式にコンバートされ、SQLファイルに出力されます。

移行したいオブジェクト種別が複数ある場合は、オブジェクト種別の数だけora2pgを実行し、オブジェクト種別の数だけSQLファイルを生成する必要があります。


ストアドプロシージャに注目!PL/SQLのオブジェクトが移行できるか試してみた!

ストアドプロシージャは、PL/SQLで記述された一連の処理をデータベースに格納した、Oracle固有のオブジェクトです。

オブジェクトではあるのですが、実態はプログラムであるため、文字列置換のような定型的な修正では対応できません。そのため、通常はPostgreSQLのPL/pgSQLの形式に手作業でコンバートする必要があります。

PL/SQLとは?

PL/SQLは、OracleのSQL言語を拡張した手続き型のプログラム言語です。アプリケーションを作成する際には、ビジネスロジックとデータベース操作(SQL)は別々に作成しますが、PL/SQLを使うことで、ビジネスロジックとデータベース操作をひとまとめにできます。

また、PL/SQLを使って作成したプログラムはオブジェクトとしてデータベース内に事前に登録でき、ユーザが必要なタイミングで利用することができます。

プログラムの構文解析や、プログラム内でアクセスしているテーブルなどのオブジェクト存在チェックはストアドプロシージャの登録時に行われます。これにより、実際にプログラムを実行する際には構文解析が不要となり、高速な処理が期待できます。

このようなオブジェクトをストアドプロシージャ、ストアドファンクション(ユーザ定義関数)と呼びます。

PL/pgSQLとは?

PL/pgSQLは、OracleのPL/SQLと同様、PostgreSQLのSQL言語を拡張した手続き型のプログラム言語になります。PostgreSQLではPL/pgSQLを使って、ユーザ定義関数やトリガを作成します。

PostgreSQLのマニュアルには、OracleのPL/SQLからPL/pgSQLへの移植例が記載されています。

https://www.postgresql.jp/document/9.6/html/plpgsql-porting.html

移植例を見ると分かるように、PostgreSQLのPL/pgSQLはOracleのPL/SQLと結構似ています。

とはいえ、PL/SQLで書かれたプログラムをPostgreSQL上でそのまま実行できるわけではないため、PL/pgSQLの構文規約や、PostgreSQLとOracleの差分を吸収する形で書き換える必要があります。


実際にストアドプロシージャの移行を試してみる

ストアドプロシージャはプログラムのため、移行の難易度は非常に高いといえます。であればこそ、ora2pgの実力を試すのに最適ということで、PL/SQLで作成したストアドプロシージャをOracleに登録し、実際に移行してみました!

対象としたオブジェクトは以下の2つです。

  • ストアドプロシージャ
  • パッケージプロシージャ

パッケージプロシージャは、複数のストアドプロシージャやストアドファンクションをさらにひとまとめにしてパッケージ化したもので、Oracle固有のオブジェクト種別となります。

環境条件

今回、移行の確認のために使用した環境は以下のとおりです。

  • 移行元:Oracleバージョン:12c
  • 移行先:PostgreSQLバージョン 9.6
  • OS:CentOS7.3

ストアドプロシージャの移行例

サンプルとして、t_emp テーブルから、給料の平均を取得するプロシージャを作成しました。入力には「給料(SAL)」「部署(DEPTNO)」を使います。入力した「部署」が同じで、かつ入力した「給料」よりも多い社員の「給料」の平均を返却します。

テーブルは以下の状態とします。なお、t_emp テーブルは「パッケージプロシージャ」の移行例でも使用します。

SQL> select * from t_emp

EMPNO ENAME SAL DEPTNO ---------- ---------- ---------- ---------- 1 KUMA 150000 10 2 NEKO 200000 10 3 KIJI 300000 20 4 TORA 250000 20

OracleのDDL

CREATE OR REPLACE PROCEDURE test_proc ( sal_in IN NUMBER, deptno_in IN NUMBER, sal_avg_out OUT NUMBER )
AS
empno_out	NUMBER;
ename_out	VARCHAR2(100);
BEGIN

SELECT empno, ename INTO empno_out, ename_out FROM ( SELECT empno, ename FROM t_emp WHERE sal >= sal_in AND deptno = deptno_in ORDER BY empno ) WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE( 'EMPNO : ' || empno_out ); DBMS_OUTPUT.PUT_LINE( 'ENAME : ' || ename_out );
SELECT AVG(sal) INTO sal_avg_out FROM t_emp WHERE sal >= sal_in AND deptno = deptno_in;
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR : '|| SQLCODE || ' : ' || SQLERRM );
END; /

ストアドプロシージャというオブジェクト種別はPostgreSQLにはありません。また、PostgreSQLにはないROWNUMや、Oracle固有のDBMS_OUTPUTパッケージプロシージャのほか、エラー処理がどのようににコンバートされるかを確認しましょう。

PostgreSQL変換後のDDL

プロシージャは関数へコンバートされました。 コンバートしたDDLをそのままPostgreSQL上で実行したところ、いくつかのエラーが発生したため、修正しています。(後述)

CREATE OR REPLACE FUNCTION test_proc ( sal_in bigint, deptno_in bigint, sal_avg_out OUT bigint ) AS $body$
DECLARE

empno_out bigint; ename_out varchar(100); BEGIN
SELECT empno, ename INTO empno_out, ename_out FROM ( SELECT empno, ename FROM t_emp WHERE sal >= sal_in AND deptno = deptno_in ORDER BY empno ) TMP_TBL LIMIT 1 OFFSET 0;
RAISE NOTICE 'EMPNO : %', empno_out; RAISE NOTICE 'ENAME : %', ename_out;
SELECT AVG(sal) INTO sal_avg_out FROM t_emp WHERE sal >= sal_in AND deptno = deptno_in;
EXCEPTION WHEN OTHERS THEN RAISE NOTICE 'ERROR : % : %', SQLSTATE, SQLERRM;
END; $body$ LANGUAGE PLPGSQL SECURITY DEFINER ;

文字列を表示するDBMS_OUTPUT.PUT_LINEは、RAISE NOTICE にコンバートされています。ROWNUMはLIMITに置き換えられています。 エラー処理は同じですね。

大体予想通りになっていると思います。

OracleとPostgreSQLの実行例比較

各プロシージャを実行した結果も比較してみましょう。エラー処理もあるので、正常に動いたとき、エラーを発生させたとき、それぞれの結果を見てみます。

大体予想通り・・・おっと!「データがないとき」の結果が違いますね・・・!

SELECTの件数が0件の場合、Oracleでは「ORA-01403: no data found」のエラーが出ていますが、PostgreSQLでは特にエラーとして処理されないようです。

OracleのPL/SQLの中でSELECTをしている場合は、明示的にエラーハンドリングを追加する必要があるということですね。

■データがあるとき(Oracle)
SQL> variable vv number;
SQL> execute test_proc(200000,20,:vv);
EMPNO : 3
ENAME : KIJI

PL/SQL procedure successfully completed.
SQL> print vv
VV ---------- 275000
■データがあるとき(PostgreSQL)
postgres=# select test_proc(200000,20);
NOTICE:  EMPNO : 3
CONTEXT:  PL/pgSQL function test_proc(bigint,bigint) line 17 at RAISE
NOTICE:  ENAME : KIJI
CONTEXT:  PL/pgSQL function test_proc(bigint,bigint) line 18 at RAISE
test_proc 
-----------
275000
(1 行)
■データがないとき(エラー処理)Oracle

SELECTの結果が0件の場合、エラー処理が行われる。

SQL>set serveroutput on
SQL> execute test_proc(200000,10);
ERROR : 100 : ORA-01403: no data found

PL/SQL procedure successfully completed.
■データがないとき(エラー処理)PostgreSQL

SELECTの結果が0件であっても、明示的にハンドリングをしない限り、エラーにはならない。(SELECTの結果が0件の場合のハンドリング例は、「ストアドパッケージ」の移行例に記載)

postgres=# select test_proc(200000,50);
NOTICE:  EMPNO : <NULL>
CONTEXT:  PL/pgSQL function test_proc(bigint,bigint) line 17 at RAISE
NOTICE:  ENAME : <NULL>
CONTEXT:  PL/pgSQL function test_proc(bigint,bigint) line 18 at RAISE
test_proc 
-----------

(1 行)

ストアドパッケージ

次に、ストアドパッケージを移行してみましょう。

移行例のストアドパッケージには、ストアドプロシージャとストアドパッケージをひとつずつ持たせています。ストアドプロシージャはt_empテーブルへのデータ登録を、ストアドファンクションは入力した「社員名」の「給料」を返却します。

OracleのDDL

ストアドパッケージはパッケージに含むプロシージャ名などを定義する「仕様部」と、ストアドプロシージャ「本体部」で構成されます。

CREATE OR REPLACE PACKAGE test_pkg AS
PROCEDURE insert_emp ( empno_in IN NUMBER, ename_in IN VARCHAR2, sal_in IN NUMBER, deptno_in IN NUMBER );
FUNCTION get_emp_sal ( ename_in IN VARCHAR2 ) RETURN NUMBER;
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg AS
PROCEDURE insert_emp ( empno_in IN NUMBER, ename_in IN VARCHAR2, sal_in IN NUMBER, deptno_in IN NUMBER ) AS BEGIN INSERT INTO t_emp VALUES ( empno_in, ename_in, sal_in, deptno_in); COMMIT; END insert_emp;
FUNCTION get_emp_sal ( ename_in IN VARCHAR2 ) RETURN NUMBER as sal_out NUMBER := 0; BEGIN SELECT sal INTO sal_out FROM t_emp WHERE ename LIKE '%' || ename_in || '%'; RETURN sal_out; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('ENAME NOT FOUND.'); RETURN sal_out; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR : '|| SQLCODE || ' : ' || SQLERRM ); RETURN sal_out; END get_emp_sal; END test_pkg; /

こちらのポイントはパッケージがどのようにコンバートされるかです。

PostgreSQL変換後のDDL

パッケージはスキーマとして定義されました。SQL空呼び出す場合は「パッケージ名.関数名」から「スキーマ名.関数名」になるだけなので記述内容は同じです。

ただ、同一名称のスキーマがすでに使用されている場合は競合するので、その場合はスキーマ名を変える必要があります。

CREATE SCHEMA test_pkg;

CREATE OR REPLACE FUNCTION test_pkg.insert_emp ( empno_in bigint, ename_in text, sal_in bigint, deptno_in bigint ) RETURNS VOID AS $body$ BEGIN BEGIN INSERT INTO t_emp VALUES ( empno_in, ename_in, sal_in, deptno_in); END; END; $body$ LANGUAGE PLPGSQL SECURITY DEFINER VOLATILE;
CREATE OR REPLACE FUNCTION test_pkg.get_emp_sal ( ename_in text ) RETURNS bigint AS $body$ DECLARE sal_out bigint := 0; BEGIN SELECT sal INTO STRICT sal_out FROM t_emp WHERE ename LIKE '%' || ename_in || '%'; RETURN sal_out; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE NOTICE 'ENAME NOT FOUND.'; RETURN sal_out; WHEN OTHERS THEN RAISE NOTICE 'ERROR : % : %', SQLSTATE, SQLERRM; RETURN sal_out; END; $body$ LANGUAGE PLPGSQL SECURITY DEFINER ;

ora2pgでコンバートしたDDLを結果をPostgreSQL上で実行したところ、いくつかエラーが出たので修正しました。

まず、「test_pkg.insert_emp」の最終行を「STABLE」→「VOLATILE」に変更しています。また、「INSERT」文の前後に「BEGIN」「END;」を付与して修正しています。

OracleとPostgreSQLの実行例比較

■データがあるとき(Oracle)
SQL> select test_pkg.get_emp_sal('NEKO') from dual;

TEST_PKG.GET_EMP_SAL('NEKO') ---------------------------- 200000
■データがあるとき(PostgreSQL)
postgres=# select test_pkg.get_emp_sal('NEKO');
get_emp_sal 
-------------
200000
(1 行)
■データが無いとき(Oracle)
SQL> select test_pkg.get_emp_sal('SARU') from dual;

TEST_PKG.GET_EMP_SAL('SARU') ---------------------------- 0
ENAME NOT FOUND.
■データが無いとき(PostgreSQL)
postgres=# select test_pkg.get_emp_sal('SARU');
NOTICE:  ENAME NOT FOUND.
get_emp_sal 
-------------
0
(1 行)
■データ登録(Oracle)
SQL> exec test_pkg.insert_emp(101,'TORI',180000,30);

PL/SQL procedure successfully completed.
SQL> select * from t_emp;
EMPNO ENAME SAL DEPTNO ---------- -------------------- ---------- ---------- 1 KUMA 150000 10 2 NEKO 200000 10 3 KIJI 300000 20 4 TORA 250000 20 101 TORI 180000 30
■データ登録(PostgreSQL)
postgres=# select test_pkg.insert_emp(101,'TORI',180000,30);
insert_emp 
------------

(1 行)
postgres=# select * from t_emp; empno | ename | sal | deptno -------+-------+--------+-------- 1 | KUMA | 150000 | 10 2 | NEKO | 200000 | 10 3 | KIJI | 300000 | 20 4 | TORA | 250000 | 20 101 | TORI | 180000 | 30 (5 行)

ストアドプロシージャ移行のまとめ

今回はOracleのストアドプロシージャ、ストアドパッケージの移行についてora2pgの実力を試してみました。

ストアドプロシージャの移行は難易度が高いですが、ora2pgを用いることで、移行作業を大幅に軽減できることが確認できました。

ただし、コンバート後のPostgreSQL用のDDLは完璧なものではないため、PL/SQLからPL/pgSQLに変換したあとに、デバッグ作業が必要です。

ストアドプロシージャの移行における、ora2pgの使いどころと注意点は以下です。

  • OracleとPostgreSQLの大枠の変換に用いる。
  • 変換は完全ではないので、人による修正が必要。
  • 全て関数にコンバートされるので、呼び出し元の修正が必要。

それでもPL/SQLの構文を一からPL/pgSQL構文へ手動で修正するよりは難易度は低くなるため、移行時には積極的に使って行くことが有効です。

NTTテクノクロスでは、OracleからPostgreSQLへの移行や、運用支援のサービスを提供しています。

PostgreSQL移行・運用支援サービスはこちら

著者プロフィール
中村 憲一

クラウド&セキュリティ事業部第一事業ユニット
中村 憲一(KENICHI NAKAMURA)

中村 憲一