はじめに

こんにちは！NTTテクノクロスでPostgreSQLの技術支援をやっています、中村です。

いきなりですが、皆さんは「データベースリンク」を利用されたことはありますでしょうか？ 「データベースリンク」とは、ローカルのデータベースに接続しながら、外部にある別のデータベース上のオブジェクト（テーブルなど）も ローカルデータベース上に存在するかのようにアクセスすることができる機能です。

PostgreSQLではいわゆる「データベースリンク」の実現手段がいくつか存在します。 そこで本記事では、Oracleでデータベースリンクを使用しているシステムを PostgreSQLに移行するケースを想定し、PostgreSQLで外部データベース上のテーブルを参照する場合の実現方法について記載します。

テーブル参照を目的としたデータベースリンクなら postgres_fdw への置き換えが最適

Oracleで外部データベース上のテーブル参照を目的としてデータベースリンクを用いていたのであれば、PostgreSQLでは postgres_fdw の利用が最適な置き換え方法となります。

まず、 Oracleのデータベースリンクは、外部データベースに対する接続情報を定義したオブジェクトのことを指します。
ローカルデータベース上には参照するテーブルの定義などは一切なく、すべては外部データベースに接続して取得します。
外部データベース上のテーブルにアクセスするイメージとしては以下のようになります。
図：Oracleのデータベースリンクの構成イメージ

一方、 postgres_fdw では、ローカルデータベース上に外部表というオブジェクトを作成し、 その中に外部データベースに対する接続情報も持っているイメージになります。
Oracleと異なり、参照する外部データベース上のテーブルと紐けるための外部表（実データはローカルに格納しないテーブル）をあらかじめ作成しておく必要があります。

図：postgres_fdwのデータベースリンクの構成イメージ
postgres_fdw は、あらかじめ外部表を定義しておけば、外部データベース上に存在しているテーブルをローカルデータベース上に存在しているテーブルと同じようにアクセスすることができます。
このため、Oracleでデータベースリンクを使っていたSQLにも修正がほとんど必要ありません。

・SQLの置き換え例は以下のようになります。
Oracleデータベースリンクを用いて使っていたSQL

SELECT id, username FROM tbl@DBLINK;

は、postgres_fdw を用いると以下のように置き換えることができます。

SELECT id, username FROM tbl;

ローカルデータベースに実体データが存在しない点は Oracle も PostgreSQL も同じです。
また、外部データベースが停止していたり、接続できない場合にはデータが参照できない点も同じです。

さて、最適な置き換え方法が postgres_fdw、ということはそれ以外の選択肢もあるわけです。
ここからは、利用するシステムの構成別に、データベースリンクの選択肢についておさらいしましょう。

データベースリンク構成別の移行手段の選択肢

移行前のシステムが参照元、参照先ともに Oracleの場合、移行後の構成は以下のパターンが想定されます。
全部をPostgreSQLに置き換えるのか、一部を置き換えるのかによって、利用可能なデータベースリンクの種類は異なります。

図：移行後のDB構成のパターン

移行後の構成パターンによって、PostgreSQLで利用可能なデータベースリンクの選択肢が決まります。

	移行前の接続構成	移行後の接続構成	利用可能なデータベースリンク機能
①	Oracle -> Oracle	PostgreSQL -> PostgreSQL	dblink, dblink_plus, postgres_fdw
②	Oracle -> Oracle	PostgreSQL -> Oracle	oracle_fdw, dblink_plus
③	Oracle -> Oracle	Oracle -> PostgreSQL	Oracle Database Gateway for ODBC

本記事では、データベースリンクの参照元も参照先もそろって PostgreSQLに移行した 場合を前提としているため、選択肢は、①のdblink, dblink_plus, postgres_fdw になりますね。

①で選択肢に挙げたデータベースリンク機能のそれぞれの特徴は以下の通りです。

名称	プッシュダウン	用途	トランザクション管理	接続可能なDB	機能の所在
dblink	不可	DDL/DML/プロシージャ	手動2相コミット	PostgreSQLのみ	本体拡張機能
dblink_plus	不可	DDL/DML/プロシージャ	自動2相コミット	PostgreSQL/その他	外部モジュール
postgres_fdw	可	DMLのみ※	2相コミット非対応	PostgreSQLのみ	本体拡張機能

postgres_fdw はプッシュダウン機能により外部データベース上でテーブルデータの絞り込みができます。
プッシュダウンとは、WHERE句の絞り込み条件や ORDER BYなどを外部データベース上に送り、外部データベース上で処理させる機能です。
プッシュダウン機能がない場合、外部データベース上のテーブルデータすべてをローカルデータベースに転送し、ローカルデータベース上で絞り込みを行いますが、プッシュダウン機能があることでネットワーク転送量を大幅に減らすことが可能になります。
※また、DMLの中に記述するユーザ定義関数も、postgres_fdwの extensionsオプションを用いることでプッシュダウンできるようになります。(ローカルデータベースと外部データベースの両方にユーザ定義関数を定義しておく必要があります)

ローカルデータベースのテーブルと、外部データベース上のテーブルを結合したり、WHERE句による外部データベース上の テーブルデータの絞り込みを行うことがわかっている場合には、性能面で圧倒的に有利な postgres_fdw の利用を検討するべきです。

また、postgres_fdw は2相コミットが非対応ですが、これは明示的な PREPARE TRANSACTION に対応していないという意味で、 ローカルデータベース上のトランザクションがコミット、アボートした場合、外部データベース上のトランザクションもコミット、アボートするため、postgres_fdw を用いたローカルデータベース、外部データベース上のトランザクション整合性は確保されています。

では、dblink や dblink_plus はどんな時に使うのでしょうか？
テーブル以外のオブジェクト（プロシージャなど）を外部データベース上で参照する場合や、 DDLなどのコマンドを実行するようなケースは postgres_fdw では実現できないため、これらの用途で利用します。

dblink_plus が利用できるのであれば、トランザクション管理の利便性から、dblink ではなく、dblink_plus を利用することを推奨します。
ただし、dblink_plus は Amazon RDS for PostgreSQL や Amazon Aurora PostgreSQL などクラウド上のマネージドなデータベースでは使用できません。この場合は、dblinkが選択肢ということになります。

dblinkを利用する場合で留意するべきなのは、トランザクションの管理です。
dblinkを用いてデータベースリンクを使用する場合では、ローカルデータベースのトランザクションと 外部データベースでも個別にトランザクションを開始し、PREPARE TRANSACTION による 2相コミットを行う必要があります。

dblink で 2相コミットを行う例を以下に示します。

PostgreSQL本体拡張機能の dblink で 2相コミットを行う例

dblinkで 2相コミットを行う場合、以下のようにローカルデータベースと外部データベース上で PREPARE TRANSACTION / COMMIT PREPARED を行います。 ちょっと長いですが、手順例を記載しますので、参考になればと思います。

・ローカルデータベース上でトランザクション開始

postgres=# begin;
BEGIN

・dblink接続を開始

postgres=*# SELECT dblink_connect('con1', 'hostaddr=127.0.0.1 port=5433 dbname=postgres user=postgres password=nopassword');
 dblink_connect
----------------
 OK
(1 行)

・外部データベース上でトランザクションを開始

postgres=*# SELECT dblink_exec('con1', 'BEGIN');
 dblink_exec
-------------
 BEGIN
(1 行)

・外部データベース上でINSERT

postgres=*# SELECT dblink_exec('con1', 'INSERT INTO t_test VALUES (3,''CCCCCCCC'')');
 dblink_exec
-------------
 INSERT 0 1
(1 行)

・外部データベース上のトランザクションIDを取得

postgres=# SELECT * from dblink('con1', 'select txid_current()') as t1(txid_current xid);
 txid_current
--------------
          759
(1 行)

・ローカルデータベース上でINSERT

postgres=*# insert into t_test2 values (3,'USER3','0177556666');
INSERT 0 1
(1 行)

・外部データベース上でPREPARE TRANSACTION

postgres=*# SELECT dblink_exec('con1', 'PREPARE TRANSACTION ''remote_001''');
   dblink_exec
---------------------
 PREPARE TRANSACTION
(1 行)

・ローカルデータベース上でPREPARE TRANSACTION

postgres=*# prepare transaction 'prt001';
PREPARE TRANSACTION

・外部データベース上でCOMMIT PREPARED

postgres=# SELECT dblink_exec('con1', 'COMMIT PREPARED ''remote_001''');
  dblink_exec
-----------------
 COMMIT PREPARED
(1 行)

・外部データベース上のトランザクションが COMMITされていることを確認（念のため）

postgres=# SELECT * from dblink('con1', 'select txid_status(759)') as t1(txid_status text) ;
 txid_status
-------------
 committed
(1 行)

・ローカルデータベース上でCOMMIT PREPARED

postgres=# commit prepared 'prt001';
COMMIT PREPARED
※「・外部データベース上のトランザクションが COMMITされていることを確認（念のため）」の結果が「commited」ではなく
　「aborted」（ロールバック）となっていた場合は、
　「COMMIT PREPARED」の代わりに「ROLLBACK PREPARED」を実施してロールバックします。

・dblink接続を切断

postgres=# SELECT dblink_disconnect('con1');
 dblink_disconnect
-------------------
 OK
(1 行)

まとめ

PostgreSQLでデータベースリンクを使用するケースについて確認してきました。 最後にもう一度、どのデータベースリンク機能をもちいればよいか、選択肢についておさらいしていきましょう。

PostgreSQL -> PostgreSQL のデータベースリンクの利用を検討している場合は、下記の選択基準で選択すると良いですね。

名称	選択基準
postgres_fdw	テーブルアクセスだけしか使わない。
dblink_plus	テーブルアクセス以外に利用する場合。dblink_plus をインストール可能な環境で利用する場合。
dblink	テーブルアクセス以外に利用する場合。クラウドのマネージドなデータベースなど、dblink_plus が使えない場合。

postgres_fdw は事前に外部表の定義が必要なため、動的にテーブルが増えたり減ったりするような 環境で利用する場合は、その都度外部表を作成、削除するといった運用も一緒に検討する必要があります。 その代わり、スタンダードなSQLの記述が可能であることと、性能面で有利なため、積極的な利用を推奨します。

postgres_fdw では対応できない処理（外部データベース上でプロシージャなどを実行したりする）などは dblink や dblink_plus などと組み合わせ、最適な方法を選択してもらえればと思います。

それでは、本記事は以上となります！

PostgreSQLのことならNTTテクノクロスにおまかせください！

NTTテクノクロス株式会社ではPostgreSQLに関する各種のお問い合わせを受け付けています。
システムの導入、開発、維持管理の際にご活用ください。

定額制チケットサービスはこちら