データベースマイグレーション～OracleからPostgreSQLへ～　−第２回ー

1. 初めに

こんにちは。Technology Innovation Groupの岸田です。

前回に引き続き、データベースのマイグレーションがテーマです。
第1回目はデータベースマイグレーションについて流れやポイントとなる点について記載してきました。

データベースマイグレーション～OracleからPostgreSQLへ～　ー第１回ー

第2回目は以下3点について紹介します。

移行支援ツールの紹介
実際にシステムで稼働しているデータベース環境とアプリケーション（SQL）を使った評価
データベースマイグレーションの検討の方針

2. 移行支援ツール

データベースのマイグレーションに際しては、移行ツールがいくつか公開されているため、それを利用するのが良いでしょう。このようなツールを利用してマイグレーションの作業量を削減していくことになります。

#	ツール名称	説明	参照先
1	ora2pg	GPLにもとづくオープンソースフリーソフトウェア	ora2pgのHP
2	AWS Schema Conversion Tool	AWSにて公開のツール	AWS Schema Conversion Tool

簡単ではありますが、2つの移行ツール(ora2pg/AWS Schema Conversion Tool)について紹介します。
移行ツールは現利用環境のデータベース定義を別環境に再現（ライセンスに注意！）し、再現環境に接続してツールを実行します。
もちろん、現利用環境に直接接続できる場合は別環境を用意する必要はございません。

2-1 ora2pg

ora2pgはOracle/MySQLからPostgreSQLへの移行を支援するツールです。動作環境はLinuxとなります。

できること

ora2pgでは以下を行うことが可能です。

スキーマ定義のコンバージョン用DDLの作成
- 接続した環境からOracleデータベースにあるオブジェクト定義からPostgreSQLで実行可能なDDLの出力
スキーマコンバージョンのレポート出力
- PostgreSQLに変換する際の評価レポート
データ移行SQLの作成
- PostgreSQLで実行可能なINSERT文またはCOPY文の形式によるDMLの出力
SQLファイルのコンバージョン

ora2pgの具体的な利用方法はこちらを参照してください。
参考までに、本ブログではora2pgはバージョン18.2を利用して確認しております。

コンバージョンの実行は、confファイル内でTYPE句にて指定し、個々に確認していきます。インストール時に用意されているconfファイル内にTYPEで指定できるキーワードが記載されております。（SYNONYMとDBLINKは記載がありませんが、指定が可能です。）
TYPEで指定できるキーワードとしては大きく3つの種類に分けられます。

種別	キーワード	説明
オブジェクト	TABLE	テーブル、インデックス、制約等のテーブルに関連するオブジェクト
オブジェクト	PACKAGE	パッケージ ⇒ シノニム＋ファンクションに変換
オブジェクト	VIEW	ビュー
オブジェクト	GRANT	オブジェクトの権限
オブジェクト	SEQUENCE	順序
オブジェクト	TRIGGER	トリガー
オブジェクト	FUNCTION	ファンクション
オブジェクト	PROCEDURE	プロシージャ ⇒ ファンクションに変換
オブジェクト	TABLESPACE	表領域
オブジェクト	TYPE	タイプ
オブジェクト	PARTITION	パーティション表 ⇒ 親子型のパーティション表に変換
オブジェクト	FDW	外部表
オブジェクト	MVIEW	マテリアライズド・ビュー
オブジェクト	KETTLE	XMLテンプレート
オブジェクト	SYNONYM	シノニム
オブジェクト	DBLINK	データベース・リンク
データ	INSERT	データ登録DML（INSERT形式）
データ	COPY	データ登録DML（COPY形式）
SQL	QUERY	SQL文の変換

スキーマコンバージョンのレポートは以下のようなコマンドとなります。

/usr/local/bin/ora2pg -c /etc/ora2pg/ora2pg.conf -t SHOW_REPORT --estimate_cost --dump_as_html > report.html

レポートの出力結果例は以下です。

ora2pgのSQLの変換については、ファイル単位にコンバートしていくため、各SQLファイルに対してora2pgを実行する必要があります。多少手間ではありますが、シェルを組んでディレクトリ内のファイル（例えば拡張子がsqlのものとか）に対してora2pgを実行することになるでしょう。また、コンバートできないものについてはエラーが出力されることなく、そのままの記載内容でアウトプットファイルが出力されますので、個々にコンバージョン結果の確認が必要となります。

2-2 AWS Schema Conversion Tool(AWS-SCT)

AWS-SCTは、AWSが提供しているツールです。オンプレのデータベースシステムをAWSクラウドへシフトする支援用のツールとなっていまして、変換可能なデータベースが多数存在します。
変換可能なデータベースの対応表は以下の通りです。

変換元データベース	変換先データベース
MS SQL Server	MySQL/PostgreSQL/Redshift
MySQL	PostgreSQL
Oracle	MySQL/PostgreSQL/Redshift
PostgreSQL	MySQL
Greenplum	Redshift
Netezza	Redshift
Vertica	Redshift

AWS-SCTを起動して、プロジェクト（作業領域）をオープンすると、変換元と変換先を選択することになります。
例えば、接続元がOracle(OLTP)を選択すると接続先で選べるのは、以下となっております。

Amazon RDS for MySQL
Amazon Aurora (MySQL compatible)
Amazon RDS for PostgreSQL
Amazon Aurora (PostgreSQL compatible)
Amazon RDS for Oracle

できること

AWS-SCTでは以下を行うことが可能です。

スキーマ定義のコンバージョン用DDLの作成
- 接続した環境からOracleデータベースのオブジェクト定義からPostgreSQLで実行可能なDDLの出力
スキーマコンバージョンのレポート出力
- PostgreSQLに変換する際の評価レポート
変換先データベースに対するスキーマの作成
- 上記DDLを実行して変換先のデータベースにスキーマ及びオブジェクトを作成
SQLファイルのコンバージョン

AWS-SCTはGUIでの操作なのでユーザフレンドリーですね。また、レポートについても画面で表示されるため、イメージしやすいです。
スキーマのコンバージョンレポートイメージは以下。

以下はオブジェクトの詳細イメージ。

緑は自動変換可能、グレーっぽいのは単純変換で対応可能、黄色は多少手を加える必要がある、赤は要見直しという感じに考えていただければよいです。

AWS-SCTの中でとても便利な機能はアプリケーションのコンバージョンです。
Windows上の指定したフォルダ配下に格納されているファイル（もちろんサブフォルダも対象）の中からSQLを抽出してコンバージョンを評価します。ファイルの形式はJAVA/C++/C#といったものが選択できます。もちろんSQLのみが記載されているファイルでもOKです。スキーマのコンバージョン同様に抽出したSQL単位に対応内容をグルーピングします。レポート結果イメージは以下です。

また、各SQL毎にGUI上でツールによって変換された内容を見ることができます。

下図の例では、4つのSQL文が記述されているファイルに対するコンバージョン結果を表示しております。上部の左上の欄にはソースファイルの内容が表示されます。その下の枠には、ソースファイルから抽出されたSQL文が表示されます。1つのファイルに複数のSQL文が記載されていた場合は、SQLを選択することで個別に表示されます。抽出されたSQLの右の枠にPostgreSQL用に変換されたSQLが表示されます。その枠の上部のApplyボタンで元ファイルへの反映ができ、右上の枠内に反映結果が表示されます。その枠の上部のSaveボタンで反映結果を元のファイルへ保存することも可能です。現時点では評価したファイルすべてに対して一括反映⇒一括保存ができるような機能は無いと思われますので、コンバージョン結果の反映は面倒ですね。（そのような機能があるのかもしれませんが見つけられてません。）

自動変換ができないものについては、各SQL単位にアドバイスが出力されます。そのアドバイスから、手動でメンテナンスを実施していくことになります。

3. 移行支援ツール検証

実際にとあるシステムのDB環境及びアプリケーションに対して移行ツールを使ってみました。

3-1 テーブルのコンバージョン

実際のテーブルのデータ型についてはツールにより自動変換されます。ora2pgではconfファイル内で、変換ルールを定義することも可能です。
あくまでもルールに基づいた変換となっておりますので、システム内での標準化基準があるようでしたら、そちらに従うようにしてください。
それぞれのツールでのデフォルトの自動変換ルールは以下の表のようになっております。

属性	Oracleのデータ型	ora2pgでの変換後のデータ型	AWS-SCTでの変換後のデータ型	備考
文字列	CHAR(n)	char(n)	character(n)	PostgreSQLのnは文字数
	NCHAR(n)	char(n)	character(n)
	VARCHAR2(n)	varchar(n)	character varying(n)	PostgreSQLのnは文字数
	NVARCHAR2(n)	varchar(n)	character varying(n)
	CLOB	text	text
	LONG	text	text
数値	NUMBER	bigint	double
	NUMBER(n)	smallint	numeric(n,0)	n=1～4
	NUMBER(n)	integer	numeric(n,0)	n=5～9
	NUMBER(n)	bigint	numeric(n,0)	n=10～19
	NUMBER(n)	decimal	numeric(n,0)	n=20～38
	NUMBER(n,m)	real	numeric(n,m)	n=2～6
	NUMBER(n,m)	double precision	numeric(n,m)	n=7～15
	NUMBER(n,m)	decimal	numeric(n,m)	n=16～38
日付	DATE	timestamp	timestamp
	TIMESTAMP	timestamp	timestamp
バイナリ	BLOB	bytea	bytea
	RAW	bytea	bytea
その他	ROWID	oid	character(255)

特殊型については個別に御確認ください。

3-2 アプリケーションソースのコンバージョン

スキーマ定義のコンバージョンは一定ルールで変更できるのが確認できましたが、マイグレーション検討を行うにあたって一番の壁がアプリケーションソースのコンバージョンです。SQLは各製品で互換性がない部分があるためソース(SQL)を一つ一つ確認していく必要があります。そのためソースの全量を把握することがまず最初の第一歩となります。

今回利用したシステムのアプリケーション(java)はDBに対するクエリはjavaソース内に記載されているのではなく、SQLごとにファイルが作成される実装方式のためSQLファイルを1か所に纏めてコンバージョンの検証を実行することができました。SQLの本数は1,150本です。

ora2pgはアプリケーションコードのファイル単位に実行が必要であり、また実行結果も目視確認しなければならないことから、今回はAWS-SCTの実行事例をご紹介致します。

SQLのコンバージョン結果レポートを出力させるとSQLConversion Actionsタブにコンバージョン評価に対するアドバイスの一覧が表示されます。各ISSUEが発生しているSQL数がNumber of occurrencesとして記載されており、▶をクリックすることでそのISSUEが発生しているファイルの一覧を表示することができますので、どのSQLでどのISSUEが発生しているか一目でわかるようになっています。また、各ISSUEの部分を見てわかる通り、ISSUEに関連した記載のあるマニュアルのURLも表示されていますので、実際にどのように直すかの参考になります。

今回コンバージョン検証をでてきた中で見直さなくてはいけないものとしてはどのようなものがあったのでしょう。少し見ていきます。

SQL自動変換

前回のブログにて記載した内容（外部結合・DECODE関数・HINT句）については自動的に変換されます。
加えて、Oracleでは省略が可能な表や列の別名の定義の際に記載するASの補完等も実施されます。
注意が必要なのは、AWS-SCTでの変換は変換後のデータベースをRDSを想定しているため、RDSで用意されているOracle互換用のスキーマ（aws_oracle_ext）で代替が可能な記載については自動的に変換されます。例えば、SYSDATE、ADD_MONTHS、TO_DATE、TO_CHAR等があります。

UPDATE文

UPDATE文については2種類のISSUEとして挙がってきています。5065と5608です。改修ポイントとして以下に纏めます。それぞれ、最新のPostgreSQLでは修正点は少なくて済みます。

ISSUE番号	メッセージ	詳細内容
5065	PostgreSQL doesn’t support the UPDATE statement for a subquery	UPDATEの対象テーブルにサブクエリ(VIEW)を指定することができない　⇒　9.1以降であればCTE等を使って改修してください。
5608	Unable to convert the UPDATE statement with multiple-column subquery in SET clause	SET句に複数行を指定し、更新後の値をサブクエリで指定する方法はサポートされていない → 9.5以降で記載できるようになったため、そのままで実行可能となります。

結果相違

OracleとPostgreSQLでの実行で検索結果が異なる可能性があります。
ここで挙がったSQLについては改修後のテストで重点的に新旧結果比較をすることをお奨め致します。

今回の検証で挙がった事象としては3種類ありました。

まずは、関数のGREATESTとLEASTです。検証結果としては5271と5272となります。
PostgreSQLだと関数で指定した列内にNULLが含まれるとNULLが返ります。そのため、COALESCE関数でNULLを変換する必要がでてきます。

次に、正規表現になります。検証結果としては5617となります。Oracleでは、REGEXP_LIKE関数となりますが、PostgreSQLにはそのような関数が無く、AWS-SCTでは~に自動変換されます。正規表現の判定がOracleとPostgreSQLで異なる可能性があり警告として出力されているものと考えております。

変換不可

移行支援ツールで自動変換が困難なものが、検証結果の5340、5621と9996にあたります。

MERGE文、PostgreSQLでは実装されていない事前定義関数で代替が困難なもの（今回はSUBSTRB関数）や複雑なSQL文のようなものとなります。コンバージョン結果としては何も変換されずにISSUEで挙がっている文言が記載されます。
参考までに、MERGE文については前回のブログを参照していただければ修正ポイントが分かります。

変換結果まとめ

AWS-SCTのSQLのコンバージョン検証結果を纏めると以下のようになります。

AWS-SCTによる分類のうちSUCCESSとLOWがそこまで労力をかけない修正（変換）でPostgreSQLで実行可能となります。また、上記で記載したようにSET句でのサブクエリの指定はPostgreSQL 9.5以降では可能となっているため、そちらを加えると**88.2%**となります。HIGHと分類されている、行ロックについては第1回にて説明しておりますので、そちらを参考にしてセッションパラメータの設定にて対処可能ですので、大きな修正となるのはMERGE文、サブクエリの更新とSUBSTRB関数の実装のみとなり、全体の6%程度に過ぎません。

このように、ソース全体のうち修正が必要なSQL量をざっと確認することができ、かつ修正難易度の目安についても確認できることが分かりました。コンバージョン計画立案時のコスト、スケジュール算出にとしても有用なものとなりそうです。

参考までに

ora2pgについても、外部結合やDECODE等の変換ができていることは確認しております。驚いたのは、ora2pgの外部結合の変換はすべてLEFT OUTER JOINに統一されているところです。（AWS-SCTは(+)の記載位置によりRIGHT OUTER JOINの変換もあります。）すべてを目視確認する場合はora2pgのSQL変換でも良いかもしれません。

3. データベースマイグレーション検討の方針

データベースマイグレーションを検討する際には、作業項目毎マイグレーションの難易度をに評価して実現性を検討していくことになります。どのようなシステムがマイグレーションがし易いのか考えてみます。

データベースオブジェクト

データベースオブジェクトについての変換は第1回でも触れましたが、圧倒的にストアド・サブプログラムが難易度が高いです。その他についてはPostgreSQLで実装されていないOracle固有のオブジェクトを利用している場合は検討が必要となってきます。

アプリケーションソース

アプリケーションについては、ソースがファイルとして保持しているようであれば、変換ツールを施行することが可能となります。そのため、ソースファイルのような形でツールが利用できない場合は難易度が高くなります。動的にSQLを組んで実行する形やアプリケーション内にSQLが分散している場合などがそれにあたります。

また、ストアド・プログラムが存在する場合は、コンバージョン及びテストの難易度が上がります。
PostgreSQLのPL/pgSQLでファンクションとして作成することは可能ですが、コンバージョンのタイミングでアプリケーション側にロジックを寄せる検討をすることも視野に入れてください。
PL/SQLをPL/pgSQLにコンバージョンする際に気を付ける点としては、以下となります。

PL/pgSQL関数内部ではCOMMITが使用できない
エラーが発生した場合は内部処理はすべてロールバックされる
パッケージが存在しないため、関数を跨った変数を定義できない
変数はすべて宣言する必要がある

データベースへの接続方法としては、OracleもPostgreSQLも変わらないため特に意識する必要はありません。

難易度	アプリケーション実装方法	システム例
高	PL/SQLの実行／動的SQL	バックグラウンドでの集計処理メインのシステム、分析用の検索システム
低	実行されるSQLが外出しされている	定型フォームのオンライン検索システム

非機能要件

これまでは、アプリケーションについて触れてきましたが、PostgreSQLはシステム内ではデータベースサーバとして稼働していくことになりますので、非機能要件についても要件を満たすかどうかを検討していくことになります。

現行システムで可用性要件や性能要件のためにOracle RAC構成を取っている場合があると思います。
Oracle RACはShared Everythingのデータベース構成となっていて、複数台のサーバがアクティブ状態で1つのデータベースを共有して稼動しますので、単体のサーバ障害ではシステムが停止しないということが大きな特徴です。また、複数台がアクティブに稼働しておりますので、分散処理も可能となります。この要件が崩せない場合は、やはりOracleからのマイグレーションは難しくなります。

PostgreSQLでは、可用性を上げるために3rdベンダのクラスタウェアを利用したActive-Standby構成が考えられます。また、分散処理の一例としてレプリケーション機能を利用したMaster-Slave構成として参照処理をSlave側で実行させるといったことも考えられます。

その他、運用（バックアップ／監視／セキュリティ）等、非機能要件を確認したうえで、マイグレーションの評価をしていってください。OracleのEnterprise Editionの場合、PostgreSQLにはない機能が豊富にふくまれているため細かく確認が必要です。

クラウドサービスの場合、可用性（レプリケーション）、運用（バックアップ、監視、セキュリティ）がDB機能として提供されています（例えば、AWS RDS）のでこの部分のハードルはぐっと低くなると考えています。

データ移行

データベースのマイグレーションではなくても、システムの刷新時には検討されるのが、データの移行です。すいません、データ移行は非常に様々な検討項目があるため、書くとなると1回分くらいになってしまうかと思いますので、第2回のブログでも割愛させていただきます。
ただ、マイグレーションの難易度の評価は割愛せずに必ず実施してください。

4. 最後に

ここまでデータベースのマイグレーション（PostgreSQL）について確認してきましたが、ツールを利用したマイグレーションが有用であることが確認できました。また、実際のコンバージョンだけでなくプロジェクト計画を立てる際の一次評価としても有用と言えます。

第1回・第2回とはデータベースのマイグレーションに特化した内容で記載してきましたが、PostgreSQLへのマイグレーションの判断が下された後には、もちろんPostgreSQLとしてのデータベース設計は必要です。パラメータや配置などの物理設計や統計情報取得、VACUUM処理、バックアップ等の運用設計もしっかりとやっていきましょう。

フューチャーでは仮想的なDBチームが形成されており、各プロジェクトへの横断的に支援しております。OracleやPostgreSQLを中心としたノウハウの蓄積・共有なども活発に行っていますので、ご興味ある方は是非一緒に働いていきましょう！！

データベースマイグレーション～OracleからPostgreSQLへ～　ー第１回ーもぜひ参考ください