PostgreSQLエンタープライズ・コンソーシアム 技術部会 WG#3

2016年度WG3活動報告書
異種DB間連携 連携方式調査編

PostgreSQL Enterprise Consortium

目次
目次
1. ライセンス2. はじめに2.1. PostgreSQLエンタープライズコンソーシアムとWG3について
2.2. 本資料の概要と目的
2.3. 本資料の構成
2.4. 想定読者
3. 異種 DBMS と PostgreSQL の連携について
3.1. 異種 DBMS と PostgreSQL を連携する目的
3.2. 異種 DBMS と PostgreSQL の連携を実現する際の課題
4. DB間連携の各方式
4.1. 今回の調査にあたって
4.2. 一覧比較表
5. ETLツールを用いた連携方式
5.1. ETLツールとTalendについて
5.1.1. ETLツールの活用
5.1.2. ETLツール Talendについて
5.2. Talendを利用した異種DB連携環境の構築手順
5.2.1. 実行環境と連携するデータのイメージ
5.2.2. Talend Open Studioのインストールと起動
5.2.3. DBMSとの接続
5.2.4. スキーマ情報の取得
5.2.5. ジョブの作成
5.2.6. 実行ファイルの生成
5.2.7. 実行ファイルの配置と実行
5.2.8. 補足事項
5.3. ETLツール Talendの使い時と有償版の付加機能
6. SymmetricDS
6.1. SymmetricDS について
6.2. SymmetricDSを利用した異種DB連携環境の構築手順
6.2.1. 事前準備
6.2.1.1. SELinuxとファイアウォールの設定
6.2.1.2. Javaのインストール
6.2.1.3. PL/pgSQLのインストール
6.2.1.4. ユーザ作成と権限付与
6.2.1.5. データベース作成
6.2.2. 環境構築
6.2.2.1. SymmetricDSの展開
6.2.2.2. プロパティファイルを作成、配置
6.2.2.3. SymmetricDS用のテーブルを作成
6.2.3. SymmetricDSの起動
6.2.3.1. RootNodeの起動
6.2.3.2. ClientNode側の起動
6.2.4. SymmetricDSによるデータ連携(動作確認)
6.2.4.1. 連携するテーブルを作成
6.2.4.2. データロード
6.3. SymmetricDSの連携時の挙動
6.3.1. 挙動の概要
6.3.1.1. データの連携機能
6.3.2. データの整合性
6.3.3. データ連携における注意点
6.3.3.1. 同時更新時の注意点
6.4. SymmetricDSの障害対応について
6.4.1. 障害対応の概要
6.4.2. 障害検知方法
6.4.2.1. 検知対象
6.4.2.2. 通知方法
Page 2 of 51

2
4
5
5
5
5
5
6
6
6
7
7
8
9
9
9
9
10
10
10
11
13
16
21
23
24
25
27
27
27
28
28
28
28
28
28
29
29
29
30
31
31
31
31
31
31
32
32
32
33
33
33
34
34
34
34
34

PostgreSQL Enterprise Consortium

6.4.3. 障害回復方法
34
6.4.3.1. ClientNode障害時の手順
34
6.4.3.2. RootNode障害時の手順
34
6.5. SymmetricDSの遅延度合の動作検証について
35
6.5.1. 動作検証の概要
35
6.5.1.1. 検証構成
35
6.5.1.2. 検証方法
35
6.5.1.3. 検証結果
36
6.6. SymmetricDSの利用シーンと留意点
36
7. Logical Decoding
37
7.1. ロジカルデコーディングについて
37
7.2. ロジカルデコーディングを利用した異種DB連携環境の構築手順
37
7.2.1. プラグインの実装例
37
7.2.2. 設定例
40
7.2.3. 実行例
40
7.3. ロジカルデコーディングの今後
45
8. 外部データラッパ(FDW)を用いた連携方式
46
8.1. 外部データラッパ(FDW)について
46
8.2. FDWを利用した異種DB連携環境の構築手順
46
8.2.1. 外部データへのアクセス手順
46
8.2.1.1. 外部サーバの作成(CREATE SERVER)
46
8.2.1.2. 外部サーバで利用するユーザ権限定義
47
8.2.1.3. PostgreSQLユーザと外部サーバのユーザの関連付け(CRTEATE USER MAPPING)
47
8.2.1.4. 外部テーブルの作成(CREATE FOREIGN TABLE)
47
8.2.2. 外部データへのアクセス
47
8.2.2.1. SQL文の記載方法
47
8.2.3. その他の機能
47
8.2.3.1. Oracleのスキーマ配下のテーブル定義を外部テーブルとしてインポートする(IMPORT FOREIGN SCHEMA) 47
8.3. FDWを利用する上での留意点
48
8.3.1. 外部データ参照の確認(実行計画)
48
8.3.2. プッシュダウン
48
8.3.3. ストアドプロシージャ、ユーザ定義ファンクションの実行
49
9. 著者
51

Page 3 of 51

PostgreSQL Enterprise Consortium

1. ライセンス
本作品はCC-B Yライセンスによって許諾されています。 ライセンスの内容を知りたい方は こちら でご確認ください。 文書の内容、表記に関する誤り、ご要望、感想等につきましては、PG ECo n s のサイト を通じてお寄せいただきますようお願いいたします。

Eclip se は、Eclip se Fo u n datio n In c の米国、およびその他の国における商標もしくは登録商標です。
IB M およびDB 2 は、世界の多くの国で登録された In tern atio n al B u sin ess Mach in es Co rp o ratio n の商標です。
In tel 、インテルおよびXeo n は、米国およびその他の国における In tel Co rp o ratio n の商標です。
Java は、O racle Co rp o ratio n 及びその子会社、関連会社の米国及びその他の国における登録商標です。 文中の社名、商品名等は各社の商標または登録商標である場合があります。
Lin u x は、Lin u s To rvalds 氏の日本およびその他の国における登録商標または商標です。
Red Hat およびSh ado w man lo go は、米国およびその他の国におけるRed Hat,In c. の商標または登録商標です。
Micro so ft 、W in do w s Server 、SQ L Server 、米国 Micro so ft Co rp o ratio n の米国及びその他の国における登録商標または商標です。
MySQ L は、O racle Co rp o ratio n 及びその子会社、関連会社の米国及びその他の国における登録商標です。 文中の社名、商品名等は各社の商標または登録商標である場合があります。
O racle は、O racle Co rp o ratio n 及びその子会社、関連会社の米国及びその他の国における登録商標です。 文中の社名、商品名等は各社の商標または登録商標である場合があります。
Po stgreSQ L は、Po stgreSQ L Co mmu n ity Asso ciatio n o f Can ada のカナダにおける登録商標およびその他の国における商標です。
W in do w s は米国 Micro so ft Co rp o ratio n の米国およびその他の国における登録商標です。
TPC, TPC B en ch mark,TPC-C, TPC-E, tp mC, TPC-H, Q p h H は米国Tran sactio n Pro cessin g Perfo rman ce Co u n cil の商標です
その他、本資料に記載されている社名及び商品名はそれぞれ各社が 商標または登録商標として使用している場合があります 。

Page 4 of 51

PostgreSQL Enterprise Consortium

2. はじめに2.1. PostgreSQLエンタープライズコンソーシアムとWG3について
Po stgreSQ L エンタープライズコンソーシアムは、Po stgreSQ L 本体および各種ツールの情報収集と提供、整備などの活動を通じて、ミッションクリティカル性の高いエンター
プライズ領域へのPo stgreSQ L の普及を推進することを目的として設立された団体です。
Po stgreSQ L エンタープライズコンソーシアム 技術部会ではPo stgreSQ L の普及に対する課題を活動テーマとし3 つのワーキンググループで具体的な活動を行っています。
W G 1 （性能ワーキンググループ）
W G 2 （移行ワーキンググループ）
W G 3 （課題検討ワーキンググループ）
W G 3 ではPo stgreSQ L の設計運用で想定される様々な課題に対し調査検証を行い、Po stgreSQ L が広く活用される事を推進しています。

2.2. 本資料の概要と目的
本資料はW G 3 の2016 年度の活動として Po stgreSQ L と他のRDB MS 間でデータ連携を検討するにあたって様々な連携方式と周辺ツールを評価、整理するとともに一部のツールについて簡単な動作確認を行ったものです。

2.3. 本資料の構成
2. はじめに3. 異種 DB MS と Po stgreSQ L の連携について
異種 DB MS と Po stgreSQ L 間でデータ連携する目的や課題について記載しています。

4. DB 間連携の各方式
データベース間のデータ連携方式について整理し、代表的なものを評価した比較表を掲載しています。

5. ETL ツールを用いた連携方式
ETL ツール Talen d を用いた連携方式について概要、簡単な動作手順を記載しています。
6. SymmetricDS
DB 同期ツール SymmetricDS を用いた連携方式について概要、簡単な動作手順や動作検証の結果を記載しています。
7. Lo gical Deco din g
Po stgreSQ L のロジカルデコーディングを用いた連携方式について概要、簡単な動作手順を記載しています。
8. 外部データラッパ(FDW )を用いた連携方式
Po stgreSQ L のFDW を用いた連携方式について概要、簡単な動作手順を記載しています。

2.4. 想定読者
本資料の読者は以下のような知識を有していることを想定しています。

DB MS を操作してデータベースの構築、保守、運用を行う DB A の知識
Po stgreSQ L を利用する上での基礎的な知識

Page 5 of 51

PostgreSQL Enterprise Consortium

3. 異種 DBMS と PostgreSQL の連携について
3.1. 異種 DBMS と PostgreSQL を連携する目的
ICTシステムは往々にして当初のシステム設計と異なる要件が発生し、システムの拡張やシステム間連携などが 新たに必要となる場合があります。 例えばビジネス状況の変化や事業の拡大に伴う新サービスの設立であったり、 事業部門間または企業間の連携/統合、部門や地域をまたがった横断的なデータ分析への要求などが ユースケースとして挙げられます。
しかし、ICTシステム全体を統合/拡張するにはアプリケーションの改修や、データベースをはじめとする各システムの統合、 運用面の検討が必要で多額のコストも必要とな
り、なかなか現実的ではありません。 このため、既存システムを活用したままサブシステムを新たに拡張するケースが多くあります。
新たに拡張するシステムのデータベースとしてコストを抑えるために Po stgreSQ L の利用シーンが増えており、 異種DB MS との連携を行うニーズが高まっています。
異種DB MS とPo stgreSQ L を連携させる目的や機会は様々ですが、例を挙げると以下のようなケースがあります。

1. マスタデータを活用するサブシステムへのデータ連携
基幹システムからサブシステムに最新情報のデータ配信を実施する。マスタデータを地方の支店や工場にデータを連携して、 最新の状態を現地のシステムで利用するなど。

2. 負荷分散による連携
参照負荷の高いシステム向けに Po stgreSQ L を複数台立て、 マスタ管理のデータベースの負荷を軽減させる。

3. DR バックアップサイトの構築
災害対策に備えて遠隔地にマスター DB の複製を確保する。

4. データベース移行による期間限定の連携
事業統合などに伴うデータの統合や、開発サーバへのデータ移行や同期の為に一時利用する。

3.2. 異種 DBMS と PostgreSQL の連携を実現する際の課題
異種DB MS とPo stgreSQ L 間でデータの連携を取る手段として様々な連携方式や各種ツール/ソフトウェアが存在していますが、 各方式を選定するにあたっては以下のよ
うな多くの要素を考量し、選定する必要があります。
データ変換の有無
データ同期のリアルタイム性
実行性能(反映にかかる時間や、反映元、反映先のDB に与える性能影響)
連携する単位の粒度(DB 単位、スキーマ単位、テーブル単位など)
データ連携の仕組みに向けた設計開発の難易度かけられるコスト/開発期間
どの方式が最適であるか判断する為には、各連携方式の特徴を網羅的に理解し、 比較判断するための観点を持っておくことが課題となります。
本書ではそのような課題を解決すべく、Po stgreSQ L でデータ連携を実現する代表的な方式に対して 検討すべき観点を列挙し、各観点への評価を記載しています。

Page 6 of 51

PostgreSQL Enterprise Consortium

4. DB間連携の各方式
4.1. 今回の調査にあたって
データベースで利用可能なデータ連携方式については、体系的に網羅していくと以下のような形で分類し、 列挙していくことができます。(分類の仕方や挙げているものは一例となります)ハードウェアレイヤ/ストレージレイヤ
ストレージコピー機能
物理的なディレクトリのバックアップ＆リストア(PG DATA ディレクトリなど)
データベースのdu mp 出力機能(p g_du mp など)
テキストファイル出力(csv など)
データベースレイヤ
ログシッピングレプリケーション(ストリーミングレプリケーションなど)
ロジカルデコーディング
外部データラッパ
ソフトウェアレイヤ

DB 同期ツール: ログ解析またはトリガベースのレプリケーション(SymmetricDS など)
ETL ツール(Talen d 、Po w erCen ter など)
仮想データ統合(JB o ss Data Virtu alizatio n など)
本書では以下の4 つに絞り、調査対象としています。

ETL ツール(Talen d をピックアップ)
DB 同期ツール(SymmetricDS をピックアップ)
ロジカルデコーディング
外部データラッパ(FDW )上記4 つの方式を対象に、調査/評価の観点として以下の要素を設定し調査しました。また、各方式の実施例についても以降の章で記載しています。
調査の観点評価するポイ ント

想定用途

どのようなユースケースで活用できることを想定されているか

異種DB との接続性

連携可能なDB 種類の豊富さと、汎用性の高さについて

データ同期： A ⇒B で同期をとることは可能かA ⇒B で連携データの同期はとれるか。差分反映は可能か

データ同期：リアルタイム性

バッチやジョブレベルでの定期的な反映なのか。クエリが発行されたらすぐ反映されるか、準同期か、それともクエ
リ単位で同期されるか

データ転送：データ転送/NW 部分について

データ転送性能やNW に関わる部分について。データ圧縮機能はあるか。情報の付加など、生データより多い
データ量の転送となるか

データ転送：データ連携処理コンポーネント本体の処
理性能

データ抽出およびデータ格納処理自体の性能について。性能面での劣化、追加コストなどはあるか

データ転送：連携処理時の、ソース側DB への性能影響についてA ⇒B (異種DB ⇒Po stgreSQ L)連携時、ソース側であるA の処理に影響が発生するか

データ転送：連携処理時の、反映側DB への性能影響について

A ⇒B (異種DB ⇒Po stgreSQ L)連携時、反映側であるB の処理に影響が発生するか

データ連携単位の粒度（インスタンス・ DB ・スキーマ・
テーブル・列・行）

どの単位で抽出、またどの単位で反映ができるか

セキュリティ

データベースの特権の分離が可能か。ユーザ管理が同一の基盤上で動くか、それとも独立管理が必要か。転送時の暗号化が可能か

可用性：障害時の継続性

ソース側A が落ちた場合、連携側B で処理が継続できるか

可用性：フェイルバック、リカバリ

ソース側A が落ちて復旧した後、フェイルバックやリカバリは可能か

データ連携の仕組みに向けた設計開発の難易度

データ連携機構を構築するにあたっての難易度(全体的なSI の難易度)

アプリケーション開発者に向けた難易度

連携用のAP を開発する際の難易度（テーブルの透過性、異種DB 間でのストアド・関数開発など）

データ変換（文字コードからコード体系統一、MDM的な観点など）文字コードが違う場合など自動で変換、対応してくれるか。その他の変換もカスタマイズができるか

上記観点を元に一覧比較表を作成し、以下の分類にて評価しました。

Page 7 of 51

PostgreSQL Enterprise Consortium
評価については調査担当者による絶対評価として記載しています。一例として参考にして下さい。
記号

意味

◎

非常に得意、売りの機能。

○

可能、問題なく使える。

△

使えるが制約がある、問題になるケースがある。

×

対応していない。事実上使えない。

－

評価観点として適切ではない

4.2. 一覧比較表
比較表を別途Excel のp df 形式で添付いたします。 一覧比較表(pdf形式)

Page 8 of 51

PostgreSQL Enterprise Consortium

5. ETLツールを用いた連携方式
5.1. ETLツールとTalendについて
5.1.1. ETL ツ ー ル の 活用
ETL とは異なるデータベース間でデータを転送する際の3 つの工程、 Extract(抽出)、Tran sfo rm(変換・加工)、Lo ad(ロード)の一連の処理を指す用語です。
ETL 処理の作成/実施を支援する為に、ITベンダ各社より様々なツールが展開されています。
異種DB とPo stgreSQ L 間でデータ連携を実施するにあたって ETL ツールが必要とされるケースは以下のような場合があります。

1. 連携元DB から抽出したデータが、そのままPo stgreSQ L に取り込めない形式の場合
2. データの抽出にあたりAP の作成が必要となる場合
3. データを反映する前にデータの加工/変換など中間処理が必要となる場合
1. のケースとしては、通常のRDB MS であればCSV に出力すればそのまま取り込める場合が多いです。 しかし、Excel ファイルや連携元DB 独自のデータ形式など
Po stgreSQ L が取り込める形式ではない場合、 Po stgreSQ L に合せてデータの加工が必要となります。
2. のケースでは特定のスキーマ/特定の範囲でデータを抽出したい場合などAP の作成が必要となり、 連携元のデータベースに対するプログラミングの知識も必要となります。
3. のケースではPo stgreSQ L への反映にあたって文字コードやデータ型の変換、データのフィルタリング/ソートなどを 実施したい場合には何らかの変換ツールの活用であった
り、AP の作成が必要となります。
ETL ツールはこれらの課題に対し大きな効果を期待することができます。 G U I と予め用意された部品(コンポーネント)の組み合わせでプログラミングなしに AP を作成できるため、 連携先のデータベースに対するプログラミングの知識がなくとも連携用のAP を構築できます。 また、連携における中間処理も部品とし豊富に用意されており、複雑な業務も容易かつ柔軟に組むことができます。

5.1.2. ETL ツ ー ル Talendについ て
ETL ツールは様々なベンダにより数多く展開されていますが、 本章では無償版が提供されているTalen d をピックアップし、紹介いたします。
Talen d とは、Talen d 社が提供するデータ統合・連携製品です。
機能追加された有償版と無償のO SS 版（ Talen d O p en Stu dio ）があり、 それぞれTalen d 社やベンダー各社によるサポートが提供されています。

DB とDB を連携するAP の開発・運用・維持管理をサポートするデータ統合連携基盤であり、 G U I を利用したジョブの作成と、JAR ファイルによる実行ファイルの出力まで実施できます。

Talen d の特徴として O SS 版でも多くのETL 機能、データファイルやDB への接続コンポーネントを利用することができます。
O SS 版と有償版の違いは以下のような点が挙げられますが、 メインとなるETL 開発に使えるコンポーネント(DB への接続、データの抽出、加工、反映)については 無償版でもほぼ差はありません。

Page 9 of 51

PostgreSQL Enterprise Consortium
項目

OSS 版

有償版

サポートの有無

ベンダによる提供有り

Talen d 社と、ベンダによる提供有り

作成した AP の管理と実行

手動で管理し、手動で配置/実行する

Talen d による構成管理、デプロイ、スケジュール実行が可能

開発形態

スタンドアロン環境で単独開発

クライアント・サーバ環境によるチーム開発が可能

5.2. Talendを利用した異種DB連携環境の構築手順
実際に Talen d O p en Stu dio を利用して異種DB と連携する手順を紹介いたします。

5.2.1. 実行環境と連携するデ ー タの イメー ジ
本節では簡単な例として、O racle Datab ase 11g のデータを Po stgreSQ L に取り込むケースを解説いたします。
実行環境
項目

利用 SW

想定用途

OS

反映元DB MSO racle Datab ase 11g Exp ress Editio n

RedHat En terp rise Lin u x 7.2

携帯電話のコンテンツ決済情報を格納(マスタ )反映先DB MSPo stgreSQ L 9.6.2

RedHat En terp rise Lin u x 7.2

コンテンツ決済情報の分析用(参照用レプリカ)

ETL ツール

Talen d O p en Stu dio fo r Data In tegratio n
V6.3.1

W in do w s7

連携用AP 開発

テーブル定義(テーブル名： mo b ile_p aymen t)

項番

内容

列名

データ 型 (Oracle)

データ 型 (PostgreSQL)

連携時の型変換の有無

1

決済者のID(キャリアと電話番号)

u ser_idch ar(16)ch ar(16)

－

2

決済額

p aymen t

n u mb er

in t

○

3

利用サービスのID

service_id

ch ar(8)ch ar(8)－4利用コンテンツのID

co n ten t_id

ch ar(8)ch ar(8)

－

5

決済時刻

p aymen t_date

datetimestamp○

主キーはu ser_id とp aymen t_date の複合キーとします。
リスト 5.1 投入データ例

SQL> select * from mobile_payment;
USER_IDPAYMENT SERVICE_ CONTENT_ PAYMENT_DATE---------------- ---------- -------- -------- ------------------0001-09012345678
500 00000021 00000036 2017/04/01 12:34:56
0001-08043218765
1000 00000015 00000021 2017/04/01 14:12:09
0002-09011223344
600 00000021 00000008 2017/04/01 14:18:05

5.2.2. Talend Open Studioの イン ストー ル と起動
Talen d 社のHP から Talen d O p en Stu dio fo r Data In tegratio n をダウンロードし、 手元の環境に展開、実行環境に合せて『 TO S_DI-w in 32-x86.exe 』または
『 TO S_DI-w in -x86_64.exe 』 を実行します。
実行には事前に Java のインストールが必要となります。 Talen d O p en Stu dio fo r Data In tegratio n V6.3.1 の推奨Ver は1.7 です。 その他、実行環境の要件はTalen d 社のHP から確認できます。
起動時にプロジェクトの選択を促されるため、 ここでは新規に Test_Pro ject を作成します。

Page 10 of 51

PostgreSQL Enterprise Consortium

起動して最初の画面です。 サンプルのジョブが開かれているので閉じておきます。

5.2.3. DBMSとの 接続
ジョブ関連のアイコンから、 『 DB 接続の作成』を選択します。

Page 11 of 51

PostgreSQL Enterprise Consortium

接続するDB MS に合せて DB タイプを選択し、必要な情報を入力します。 チェックボタンで接続の確認が可能です。
この時、必要なJDB C ドライバをTalen d が自動でダウンロードします。
実行環境が外部ネットワークに接続されていない場合は、 別途予め必要なJDB C を用意し、手動で配置する必要があります。
ログイン欄に指定するユーザー名/ロールには予め適切な権限を設定しておいて下さい。 反映元DB へ接続するユーザーには参照権限、反映先DB へ接続するユーザーには各種更新権限が必要となります。

Page 12 of 51

PostgreSQL Enterprise Consortium

5.2.4. スキ ー マ情報の 取得
DB MS との接続が設定できたら、データ連携の対象となるスキーマを選択し、読み込みます。
そうして取得したテーブルやビューがTalen d の操作対象となります。
作成した DB の接続を右クリックし、『スキーマ情報の取得』を選択します。

Page 13 of 51

PostgreSQL Enterprise Consortium

データ連携の対象とするテーブルを選択します。

Page 14 of 51

PostgreSQL Enterprise Consortium

読み込んだスキーマは各DB 接続のプルダウンメニューに表示されるようになります。

Page 15 of 51

PostgreSQL Enterprise Consortium

5.2.5. ジョ ブの 作成
連携するDB MS への接続と、スキーマ情報の取得を実施したら G U I 上でコンポーネントを配置しながら連携の処理内容を作成していきます。
ジョブ関連のアイコンから、 『ジョブの作成』を選択します。

Page 16 of 51

PostgreSQL Enterprise Consortium

中央のジョブ作成エリアに登録したスキーマ情報、 テーブル『 mo b ile_p aymen t 』をドラック＆ドロップすると コンポーネントとして配置することができます。
最初に反映元であるO racle のテーブルをコンポーネント配置します。

O racle がデータの抽出元である場合は、『 tO racleIn p u t 』を選択します。

次に反映先であるPo stgreSQ L のテーブルも同様にドラッグ＆ドロップで配置します。

Po stgreSQ L が出力先である為、『 tPo stgresqlO u tp u t 』を選択します。

Page 17 of 51

PostgreSQL Enterprise Consortium

O racle からの出力とPo stgreSQ L への反映がそれぞれ配置されました。 データの入/出力についてはコンポーネントのアイコンからも判別することができます。

tO racleIn p u t のアイコンを選択し、右クリック、 『ロウ(Main )』を選択し、tPo stgresqlO u tp u t までro w を繋げます。

O racle からの抽出と、Po stgreSQ L への反映が繋がりました。
これで単純な全件抽出、反映のジョブが完成となります。
データ型について暗黙的に変換が可能なものは、Talen d が自動で対応します。 (NU MB ER ⇒In t やDATE ⇒TimeStamp など)

Page 18 of 51

PostgreSQL Enterprise Consortium

O SS 版であるTalen d O p en Stu dio ではCDC(Ch an ge Data Cap tu re)の機能が使えない為、 差分反映が設定できません。
全件抽出、全件反映が基本となるため、今回の実施例ではPo stgreSQ L への反映時、 事前にテーブルをtru n cate する処理を設定しておきます。

tPo stgresqlO u tp u t のアイコンを選択し、Talen d の下部ウィンドウにある『コンポーネント』のタブを選択すると 選択したコンポーネントの詳細な設定が可能となります。

コンポーネント配置によるジョブの動作設定が完了したら、 下部ウィンドウの『実行』タブを選択し、ジョブの実行をテストします※実際に接続するDB MS への操作が行われます。

Page 19 of 51

PostgreSQL Enterprise Consortium

ジョブの実行が正常に終了できれば、ジョブの完成となります。 ジョブ作成エリアには実行時間なども表示されます。

Page 20 of 51

PostgreSQL Enterprise Consortium

5.2.6. 実行ファ イル の 生成
作成したジョブはJava のAP として実行ファイルに出力することが可能です。

Talen d 左側のエクスプローラーから、出力するジョブを選択し、右クリックで 『ジョブをエクスポート』を選択します。

Page 21 of 51

PostgreSQL Enterprise Consortium

ビルドタイプに『自律実行ジョブ』を選択し、出力先を指定して実行すると ジョブがビルドされ、jar ファイルと関連ファイルをまとめた zip ファイルが出力されます。

Page 22 of 51

PostgreSQL Enterprise Consortium

zip ファイルには、作成したジョブの実行ファイル(jar 形式)と、DB MS への接続に必要なJDB C 、 jar を実行するb at ファイルとスクリプトに加え、Java のソースコードも含まれています。

5.2.7. 実行ファ イル の 配置と実行出力した jar ファイルとb at ファイルまたは実行用スクリプトを任意のサーバに配置すれば、 データ連携AP を実施、O racle のデータをPo stgreSQ L に反映することが可能となります。
リスト 5.2 実行例

1.Oracle上のデータを確認
SQL> select * from mobile_payment;
USER_IDPAYMENT SERVICE_ CONTENT_ PAYMENT_DATE---------------- ---------- -------- -------- ------------------0001-09012345678
500 00000021 00000036 2017/04/01 12:34:56
0001-08043218765
1000 00000015 00000021 2017/04/01 14:12:09
0002-09011223344
600 00000021 00000008 2017/04/01 14:18:05

Page 23 of 51

PostgreSQL Enterprise Consortium

2.PostgreSQLのサーバで作成した jarファイルを実行[pgecons@host_postgres01 test]# ls
items src test_0_1.jar test_project test_run.bat test_run.sh[pgecons@host_postgres01]# ./test_run.sh[pgecons@host_postgres01]#
3.PostgreSQLへの反映を確認
test=# select * from mobile_payment;
user_id| payment | service_id | content_id |payment_date------------------+---------+------------+------------+--------------------0001-09012345678 |
500 | 00000021 | 00000036 | 2017-04-01 12:34:56
0001-08043218765 |
1000 | 00000015 | 00000021 | 2017-04-01 14:12:09
0002-09011223344 |
600 | 00000021 | 00000008 | 2017-04-01 14:18:05(3 行)
4.Oracle上でレコードを一件追加

SQL> insert into mobile_payment values ('000109012345678',500,'00000021','00000038',to_date('2017-4-1 14:25:04','yyyy/mm/dd
hh24:mi:ss'));1行が作成されました。
SQL> select * from mobile_payment;
USER_IDPAYMENT SERVICE_ CONTENT_ PAYMENT_DATE---------------- ---------- -------- -------- ------------------0001-09012345678
500 00000021 00000036 2017/04/01 12:34:56
0001-08043218765
1000 00000015 00000021 2017/04/01 14:12:09
0002-09011223344
600 00000021 00000008 2017/04/01 14:18:05
0001-09012345678
500 00000021 00000038 2017/04/01 14:25:04
SQL> commit;
コミットが完了しました。

SQL>
5.PostgreSQLのサーバでjarファイルを再度実行[pgecons@host_postgres01]# ./test_run.sh[pgecons@host_postgres01]#
6.PostgreSQLへの反映を確認
test=# select * from mobile_payment;
user_id| payment | service_id | content_id |payment_date------------------+---------+------------+------------+--------------------0001-09012345678 |
500 | 00000021 | 00000036 | 2017-04-01 12:34:56
0001-08043218765 |
1000 | 00000015 | 00000021 | 2017-04-01 14:12:09
0002-09011223344 |
600 | 00000021 | 00000008 | 2017-04-01 14:18:05
0001-09012345678 |
500 | 00000021 | 00000038 | 2017-04-01 14:25:04(4 行)
test=#

5.2.8. 補足事項
本節で紹介した構築手順では単純な全件コピーを実施するジョブを作成しましたが、 Talen d O p en Stu dio には豊富な処理がコンポーネントとして部品で用意されておPage 24 of 51

PostgreSQL Enterprise Consortium
り、 複雑な処理もG U I で直感的に作成することが可能です。
例えば、データの反映にあたりマスタデータから特定の列(個人情報など)を削除したい場合など フィルタのコンポーネントを間に挟み込むことで設定できます。
様々なコンポーネントは右側のパレットから選択できます。

また、Talen d の実行環境と、出力する実行ファイルの配置先で ネットワークの設定などが異なる場合はDB への接続情報を個別に変更することも可能です。

tO racleIn p u t のアイコンを選択し、Talen d の下部ウィンドウにある『コンポーネント』タブから 接続の際に指定しているホスト名などを変更可能です。
その際、オプションで組み込みプロパティの変更を選択すれば、 本ジョブに関してのみ設定が変更されます。

5.3. ETLツール Talendの使い時と有償版の付加機能
Talen d(O SS 版)の使い時としては、対象DB でのシステム開発の経験が少ない
開発期間が短い、開発工数が少ない
AP の継続的な改修があり、処理の可視化などのメンテナンス性が必要などが想定されます。

Page 25 of 51

PostgreSQL Enterprise Consortium

Talen d の利点は無償でも使えるETL ツールであることと、数多くのDB に対応した コンポーネントが標準で用意されていることです。ビッグデータ /No SQ L に分類されるDB (mo n go DB など)やクラウド(AW S など)にも対応しています。
留意点としては、O SS 版ではCDC の機能が使えないため、差分反映を設定することができません。 時系列によってテーブルを分ける、反映先は都度tru n cate するなど、運用による工夫が必要です。
有償版ではCDC の他にも様々な機能拡張がされているため、より高度な開発管理、 運用を実施したい場合には有償版の採用も検討に値します。
有償版では開発AP のバージョン管理や共有リポジトリ
複数ジョブの並列実行
Talen d からの実行AP のデプロイや、スケジュール実行
ロードバランシングとフェールオーバー
連携処理エラー時のリカバリ機能など、開発管理、運用管理に関わる高度な機能が利用可能です。

Page 26 of 51

PostgreSQL Enterprise Consortium

6. SymmetricDS
6.1. SymmetricDS について
SymmetricDS は、異種DB 間で一方向レプリケーション、双方向マスターレプリケーションをサポートするツールで、レプリケーション時にフィルタリングや変換処理を行うことができます。レプリケーションは非同期でデータを複製します。

SymmetricDS は、Java 上で動作するため、Java をサポートする様々なデータベースに対応し、様々なプラットフォーム間で動作するように設計されています。

ライセンス体系は、O SS ライセンスと商用ライセンス(SymmetricDS Pro )の2 種類があります。 O SS のライセンスは、G NU G en eral Pu b lic Licen se (G PL) versio n 3.0 となっています。
実際の挙動を確認するため、O SS ライセンス版を使用して構築手順、挙動、性能影響について検証した結果をまとめます。 検証したバージョンは、SymmetricDS 3.8.0(O SS ライセンス版) です。
なお、 2012 年度のPG ECo n s の成果物 の 異種DB 間連携調査編(W G 2) では、商用製品の紹介していますので、併せてご参照ください。

6.2. SymmetricDSを利用した異種DB連携環境の構築手順
SymmetricDS を利用して異種DB 間の連携する手順は、以下の通りとなります。 ここでは、 SymmetricDS 3.8 Tu to rials のデモのシナリオを使って説明します。
構築結果イメージは、下図になります。
ノードは、それぞれ親となるノードをRo o tNo de 、クライアントとなるノードをClien tNo de と明記します。
Po stgreSQ L とMySQ L の連携による環境構築を行っていますが、説明はPo stgreSQ L のみとします。

連携方式には、Pu sh Jo b とPu ll Jo b の2 種類が存在します。違いは、Pu sh Jo b は更新データを連携元ノード(So u rce n o de)で監視し、Pu ll Jo b は連携先ノード(Target n o de)で監視します。

Page 27 of 51

PostgreSQL Enterprise Consortium

6.2.1. 事前準備
事前準備は、Ro o tNo de 、Clien tNo de の両ノードに対して行います。

6.2.1.1. SELinux と ファイ アウォールの設定
構築手順の検証で行った SELin u x とファイアウォールの設定は、以下の通りです。

●SELin u x の設定
SELINU X= p ermissive● ファイアウォールの設定# firewall-cmd --permanent --add-port=9090/tcp# firewall-cmd --reload

6.2.1.2. Java のイ ンスト ール
SymmetricDS は、JDK、または、JRE 7.0 以降がシステム要件となっています。
インストールしていない場合には、インストールが必要です。

6.2.1.3. PL/pgSQL のイ ンスト ール
必要に応じて、PL/p gSQ L のインストールを行います。
なお、Po stgreSQ L 9.0 以降はデフォルトでインストールされるため、改めてのインストールは不要です。

p o stgres= # CREATE FU NCTIO N p lp gsql_call_h an dler() RETU RNS lan gu age_h an dler AS '$lib dir/p lp gsql' LANG U AG E C;
p o stgres= # CREATE FU NCTIO N p lp gsql_validato r(o id) RETU RNS vo id AS '$lib dir/p lp gsql' LANG U AG E C;
p o stgres= # CREATE TRU STED PRO CEDU RAL LANG U AG E p lp gsql HANDLER p lp gsql_call_h an dler VALIDATO R p lp gsql_validato r;
6.2.1.4. ユーザ作成と 権限付与
SymmetricDS ユーザ"co rp "を作成します。
p o stgres= # create u ser co rp ;必要に応じて、p u b lic スキーマに対して SymmetricDS ユーザ"co rp "に権限(u sage 、create)を付与します。

p o stgres= # gran t u sage o n sch ema p u b lic to co rp ;p o stgres= # gran t create o n sch ema p u b lic to co rp ;6.2.1.5. データ ベース作成
SymmetricDS でDB 連携するデータベースを作成します。
例） samp le データベース

Page 28 of 51

PostgreSQL Enterprise Consortiump o stgres= # create datab ase samp le;

6.2.2. 環境構築
6.2.2.1. SymmetricDS の展開
SymmetricDS のダウンロードサイトからダウンロードした圧縮ファイルを展開します。
環境構築の検証では、SymmetricDS のホームディレクトリを/u sr/lo cal/symmetric-server として展開した例で説明します。
6.2.2.2. プロ パティファイ ルを作成、配置/u sr/lo cal/symmetric-server/en gin es 下にプロパティファイル(.p ro p erties)を配置します。
いずれもPo stgreSQ L の場合の設定を記載しています。
●Ro o tNo deco rp -000.p ro p erties
リスト 6.1 Ro o tNo de のプロパティ設定

engine.name=corp-000# The class name for the JDBC Driver
db.driver=org.postgresql.Driver# The JDBC URL used to connect to the databasedb.url=jdbc:postgresql://localhost:54320/sample?protocolVersion=2&stringtype=unspecified# The user to login as who can create and update tables
db.user=corp# The password for the user to login as
db.password=corp
registration.url=
sync.url=http://localhost:31415/sync/corp-000# Do not change these for running the demogroup.id=corp
external.id=000# Don't muddy the waters with purge logging
job.purge.period.time.ms=7200000# This is how often the routing job will be run in milliseconds
job.routing.period.time.ms=5000# This is how often the push job will be run.
job.push.period.time.ms=10000# This is how often the pull job will be run.
job.pull.period.time.ms=10000# Kick off initial load
initial.load.create.first=true●Clien tNo desto re-001.p ro p erties

Page 29 of 51

PostgreSQL Enterprise Consortium
リスト 6.2 Clien tNo de のプロパティ設定

engine.name=store-001# The class name for the JDBC Driver
db.driver=org.postgresql.Driver# The JDBC URL used to connect to the databasedb.url=jdbc:postgresql://localhost:54320/sample?protocolVersion=2&stringtype=unspecified# The user to login as who can create and update tables
db.user=store# The password for the user to login as
db.password=store# The HTTP URL of the root node to contact for registration
registration.url=http://192.168.1.101:31415/sync/corp-000# Do not change these for running the demogroup.id=store
external.id=001# This is how often the routing job will be run in milliseconds
job.routing.period.time.ms=5000# This is how often the push job will be run.
job.push.period.time.ms=10000# This is how often the pull job will be run.
job.pull.period.time.ms=10000
6.2.2.3. SymmetricDS 用のテーブルを作成
SymmetricDS 用のテーブルを作成します。
Ro o tNo de 側で作成しておけば、Clien tNo de 側にも反映されます（通常のテーブルの場合は、反映されない点に注意）。

$ /usr/local/symmetric-server/bin/symadmin --engine corp-000 create-sym-tables

作成される主なテーブルは、以下の通りです。
テーブル名

役割

sym_ch an n el

チャネルを管理するテーブル
チャネルは、任意のテーブル更新を１つのトランザクションでまとめる単位を定義します。

sym_n o de

ノードを管理するテーブル
一意識別子となるノードID を付与したノード情報や、どのノードグループに属するかを定義します。

sym_n o de_gro u p

ノードグループを管理するテーブル
ノードグループは、データを反映するノードのグルーピングを定義します。

sym_n o de_gro u p _lin k

ノードグループ間のデータ反映方式（ p u sh o r p u ll ）を管理するテーブル

sym_trigger

トリガを管理するテーブル
トリガは、監視対象のテーブルとチャネルの結びつけを定義します。

sym_ro u ter

ルータを管理するテーブル
監視対象のテーブルに変更があった場合のノードグループ間での反映の範囲(defau lt: テーブル全ての変更、co lu mn : 条件に従った変更のみ)を定義します。

sym_trigger_ro u ter

トリガとルータの結び付けを管理するテーブル
監視対象のテーブルに変更があった場合の反映の範囲を定義します。

sym_n o de_secu rity

ノードのセキュリティを管理するテーブル
ノードの認証に必要なパスワードを定義します。

sym_n o de_iden tity

Ro o tNo de を定義するテーブル ※SymmetricDS 起動時に必須。

Page 30 of 51

PostgreSQL Enterprise Consortium
ユーザテーブル（ Tab le ）と、Ch an n el 、No de 、No de G ro u p 、No de G ro u p Lin k 、Trigger 、Ro u ter との関係をイメージにした図を下図に示します。 図では、左のテーブルが更新された場合の連携を示しています。

6.2.3. SymmetricDSの 起動
6.2.3.1. RootN ode の起動
Ro o tNo de 側のSymmetricDS を起動し、Clien tNo de から待ち受けるモードにします。

$ /usr/local/symmetric-server/bin/sym --engine corp-000 --port 31415$ /usr/local/symmetric-server/bin/symadmin --engine corp-000 open-registration store 001

6.2.3.2. ClientN ode 側の起動

$ /usr/local/symmetric-server/bin/sym --engine store-001 --port 9090

6.2.4. SymmetricDSによるデ ー タ連携 (動作確認 )SymmetricDS で準備されているサンプルデータを用いて、データ連携の動作確認を行います。
6.2.4.1. 連携するテーブルを作成
Ro o tNo de 、Clien tNo de の両ノードで、create_samp le.xml を用いて、item テーブルなどのサンプルテーブルを作成します。

$ cd /usr/local/symmetric-server/samples/$ ../bin/dbimport --engine corp-000 --format XML create_sample.xml

6.2.4.2. データ ロ ード
Ro o tNo de で、in sert_samp le.sql を用いて、サンプルデータと連携の設定情報をインサートします。

$ cd /usr/local/symmetric-server/samples/$ ../bin/dbimport --engine corp-000 insert_sample.sql

実行後、Clien tNo de 側にインサートしたデータが連携していることを確認してください。 また、反対方向の連携（ Clien tNo de から Ro o tNo de ）は転送方式が異なるた

Page 31 of 51

PostgreSQL Enterprise Consortium
め、Clien tNo de 側に別のデータをインサートしてみて、連携することを確認してください。

6.3. SymmetricDSの連携時の挙動
6.3.1. 挙動の 概要
データ連携におけるデータの流れは、下記の通り元テーブルから宛先テーブルに伝搬します。
Pu sh Jo b の場合とPu ll Jo b の場合をそれぞれ図に示します。

● Pu sh Jo b による連携● Pu ll Jo b による連携

6.3.1.1. データ の連携機能
データの伝搬を行うにあたって、4 つの主要な機能により連携を実現しています。
① トリガ
元テーブルが更新されると、トリガがSYM_DATA テーブルに更新内容を記録します。
更新内容は、CSV 形式で格納します。
② Ro u te Jo b
Ro u te Jo b が、SYM_DATA テーブルの更新を検知し、バッチを作成し、SYM_O U TG O ING _B ATCH テーブルに格納します。

Page 32 of 51

PostgreSQL Enterprise Consortium
その際、同一チャネル(バッチの宛先、連携方式)であれば処理をまとめられるため、その処理をSYM_DATA_EVENTテーブルで決定し、どこまで終わったかの連携管理を
SYM_DATA_G AP テーブルで管理しています。
③ Pu sh Jo b

SYM_O U TG O ING _B ATCH テーブルから Stagin g Area の一時ファイルにデータを抽出し、HTTP PU Tで当該ファイルを送信先ノードのStagin g Area に送ります。
当該ファイルから SYM_INCO MING _B ATCH テーブルにデータをロードします。
ロードしたデータをもとに宛先テーブルを更新します。
④ Pu ll Jo b

SYM_O U TG O ING _B ATCH テーブルから Stagin g Area の一時ファイルにデータを抽出し、HTTP G ETで当該ファイルを送信先ノードのStagin g Area で受け取ります。
当該ファイルから SYM_INCO MING _B ATCH テーブルにデータをロードします。
ロードしたデータをもとに宛先テーブルを更新します。
(Pu ll Jo b は送信可能なバッチの有無に関わらずネットワークコネクションを貼る一方、Pu sh Jo b はバッチがあるときのみネットワークコネクションを貼ります。)

6.3.2. デ ー タの 整合性
連携におけるデータの整合性は、送信元はSYM_O U TG O ING _B ATCH テーブル、送信先はSYM_INCO MING _B ATCH テーブルのステータスを用いて保つようになっており、各テーブルで確認が可能です。
ステータスは以下の通りです。

- SYM_O U TG O ING _B ATCH テーブルのステータス
RT ルーティング中
RQ バッググラウンドで抽出要求されたNE バッチが新しく作られてレプリケーションの準備ができた
Q Y データベースからクエリーされ中SE ノードに送られた
LD ロード準備ができた
O K ロード成功のack があった
IG 無視されたER エラー- SYM_INCO MING _B ATCH テーブルのステータス
LD ロード中
O K ロードが成功した
ER エラーSK スキップされた

6.3.3. デ ー タ連携にお け る注意点
6.3.3.1. 同時更新時の注意点
データ連携の挙動を確認したところ、送信元ノードと送信先ノードで同時更新した場合には更新をそれぞれ反映されてしまったので、データの一貫性が担保できませんでした。
下図が検証結果になります。

このように、同時更新が発生すると更新が上書きされてしまうため、SymmetricDS をマルチマスタ用途として利用することは避けるべきであると考えられます。

Page 33 of 51

PostgreSQL Enterprise Consortium

6.4. SymmetricDSの障害対応について
6.4.1. 障害対応の 概要
本節では、SymmetricDS の障害検知方法と障害回復方法を紹介します。

6.4.2. 障害検知方法
6.4.2.1. 検知対象
障害の検知対象は下表の８種類で、検知対象の閾値設定やノードグループ、ノードID との対応付けや有効化などの設定を行います。
検知対象

概要

cp u

CPU 使用率

disk

ディスク使用率

memo ry

メモリ使用率b atch Erro r

エラーになったバッチの数

b atch U n sen t

送信待ちのバッチ数

dataU n ro u ted ルーティング待ちのバッチ数
dataG ap s

ルーティング中に確認したデータギャップの数

n o desO fflin e

オフラインノードの数

6.4.2.2. 通知方法
障害検知した場合の通知方法としては下記の2 種類があります。こちらも、通知方法とノードグループやノードID との対応付けや有効化などの設定を行います。
通知方法

概要

lo g

ログに出力する

email

メール通知する

6.4.3. 障害回復方法
6.4.3.1. ClientN ode 障害時の手順
Clien tNo de 障害時の回復手順とイメージを下記に示します。

1. Clien tNo de: データベースを作成
2. Clien tNo de: SymmetricDS 起動
3. Ro o tNo de: Ro o tNo de でClien tNo de を登録
4. Ro o tNo de: スキーマの反映
6.4.3.2. RootN ode 障害時の手順
Ro o tNo de 障害時の回復手順とイメージを下記に示します。

Page 34 of 51

PostgreSQL Enterprise Consortium

1. Clien tNo de: 反映側のPu sh /Pu ll Jo b をO fflin e にする
2. Ro o tNo de: データベースを作成
3. Ro o tNo de: テーブルを作成
4. Ro o tNo de: SymmetricDS 用テーブルの準備
5. Ro o tNo de: sym_ch an n el, sym_n o de_gro u p , sym_n o de_gro u p _lin k, sym_trigger, sym_ro u ter, sym_trigger_ro u ter, sym_n o de,
sym_n o de_secu rity, sym_n o de_iden tityの初期値を設定する
6. Ro o tNo de: SymmetricDS 起動
7. Clien tNo de: 1 のO fflin e モードを解除
8. Clien tNo de: データの反映
コマンド等、詳細は、 SymmetricDS U ser G u ide をご確認ください。

6.5. SymmetricDSの遅延度合の動作検証について
6.5.1. 動作検証の 概要
SymmetricDS は、非同期のデータ連携であるため、どの程度の遅延が発生するか気になります。
実際に動作検証を行いました。

6.5.1.1. 検証構成
検証環境のハードウェアおよびソフトウェアの主なスペックと構成は以下の通りです。 なお、検証を行うに当たって、その他の負荷はほとんど無い状態で実施しています。
ノード

Ro o tNo deClien tNo de

項目

仕様

CPU

Co re i7-610E 2.6G Hz (4 co re)

搭載メモリ

8G B

内蔵ストレージ

160G BOS

RedHat En terp rise Lin u x 7.2

DB MS

Po stgreSQ L 9.5.1

CPU

Co re i7-610E 2.6G Hz (4 co re)

搭載メモリ

8G B

内蔵ストレージ

160G BOS

RedHat En terp rise Lin u x 7.2

DB MS

MySQ L 5.7.17

6.5.1.2. 検証方法
SymmetricDS によるデータ連携環境を構築し、1 クエリ(in sert 文)を実行した際の遅延度合を計測しました。
計測方法は、SymmetricDS の実行ログと全クエリのレスポンス時間をログに出力・集計することで、SymmetricDS の内部処理にかかったタイムラグが確認できます。
この検証に当たって、以下の設定を行いました。
●p o stgresql.co n f の設定
全クエリのレスポンス時間をログに出力する設定しました。

lo g_min _du ratio n _statemen t = 0●SymmetricDS のプロパティファイルの設定
JO B の処理間隔を最小値に設定する設定にしました。

Page 35 of 51

PostgreSQL Enterprise Consortium

jo b .ro u tin g.p erio d.time.ms = 1
jo b .p u sh .p erio d.time.ms = 1
jo b .p u ll.p erio d.time.ms = 1
6.5.1.3. 検証結果
検証結果の前に、処理の流れをシーケンス図でまとめました。
in sert 文のトランザクション結果をt1 、SymmetricDS の処理結果がd1 ～d4 で表しており、トランザクション時間(t1)と連携先のテーブルに書き込まれるまでの時間(d1+ d2+ d3)のギャップが、遅延度合ということになります。

5 回検証を行った結果を下図にまとめました。
#

Phase

1 (ms)

2 (ms)

3 (ms)

4 (ms)

5 (ms)

ave. (ms)t1

In sertTran sactio n

19.0989.553

93.71219.66511.30430.666

d1

Trigger Jo b

21.098

11.55395.712

20.66512.30432.266d2

Ro u te Jo b

15.58223.51224.53916.55019.67719.972d3

Pu sh (In sert)
Jo b

91.71648.31276.55969.034325.288

122.182d4

Pu sh (After) Jo b

121.569

118.967

127.744155.292

420.023188.719

asyn c gap
100.61088.73144.637105.939((d1+ d2+ d3)-t1)
上記の検証環境下においては、遅延度合は1 秒以内にデータ連携が完了していることがわかります。
ただし、連携後の処理(d4)に時間がかかっているため、バッチが多くなる場合には遅延度合が増える可能性があります。

374.266

142.837

6.6. SymmetricDSの利用シーンと留意点● 利用シーン・他システムのデータを参照する必要があり、1 秒程度の遅延があっても大丈夫な場合
・データ連携したいDB が、FDW に未対応だがJDB C には対応している場合● 留意点
・連携方式を選択する指標として、接続先が多く更新頻度も高いデータの連携を行いたい場合には、コネクションプール的な役割を持つ Pu ll Jo b 方式を主方式として検討する。一方、更新頻度が高くない場合には、都度接続のPu sh Jo b 方式を検討することでリソースを無駄なく使うようにする。
・連携できるのはDML(in sert 、u p date 、delete)のみで、tru n cate やDDL はtrigger の対象外なので連携できません。
・ Nu llPo in terExcep tio n や強制終了など発生することが多く、品質にかなり難があると感じています。
・ユーザガイド通り設定してもできない箇所や、ユーザガイドのリンク先が誤っている箇所を10 個ほど見つけました。ドキュメント品質を上げる必要があると感じています。
・コミュニティはあるがあまり積極的ではなく、重要な問題もなかなか解決しないので、使用するにあたり注意が必要です。

Page 36 of 51

PostgreSQL Enterprise Consortium

7. Logical Decoding
7.1. ロジカルデコーディングについて
ロジカルデコーディングは、Po stgreSQ L9.4 から提供されている仕組みです。 実行された更新処理を他のリソースへと伝播することができます。 詳しくは Po stgreSQ L 文書
「ロジカルデコーディング」 も参照してください。
ロジカルデコーディングによる異種DB 連携は、Po stgreSQ L のトランザクションログ(W AL)を 独自のプラグインでデコードした更新処理をスロットから取得し、他のリソースへの反映することで実現します。

独自のプラグインなどを作成するので敷居は高いですが、他の連携方法よりも 自由度が高いことが特徴です。
たとえば、W AL の内容を見て、特定のテーブルのデータのみを連携させたり、 データの列名や値によるフィルタリングもプラグインの実装次第で可能です。

7.2. ロジカルデコーディングを利用した異種DB連携環境の構築手順
ロジカルデコーディングを用いる環境を構築するために必要な手順の流れを以下に示します。

(1)元となるPo stgreSQ L を用意する
ロジカルデコーディングの入力元となるのはW AL の情報です。 何はともあれ、Po stgreSQ L を用意する必要があります。

(2)プラグインを作成する
DB 連携する異種DB にあわせて、プラグインを作成します。 (プラグイン作成に必要なことを後述します)(3)Po stgreSQ L の設定を変更する
デフォルトの設定のままでは、ロジカルデコーディングを利用することはできません。 利用するための設定を施します。

(4)ロジカルデコーディングを実行する
SQ L 関数もしくはレプリケーション接続を介して、デコーディングを行います。

7.2.1. プラグイン の 実装例
前述の「 (2)プラグインを作成する」を行うにあたって必要な情報を、 co n trib に含まれるtest_deco din g モジュールを例に、プラグインの実装を見ていきます。 ロジカルデ
コーディングの仕組みを利用するためは、いくつかのコールバック関数を実装する必要があります。 また、初期化処理でこれらのコールバック関数を登録します。

(1)初期化関数_PG _o u tp u t_p lu gin _in it という名前で実装し、構造体に関数ポインタを登録します。 構造体および初期化関数の定義は以下の通りです。

Page 37 of 51

PostgreSQL Enterprise Consortium
リスト 7.1 ロジカルデコーディングで利用する構造体

typedef struct OutputPluginCallbacks{
LogicalDecodeStartupCB startup_cb;
LogicalDecodeBeginCB begin_cb;
LogicalDecodeChangeCB change_cb;
LogicalDecodeCommitCB commit_cb;
LogicalDecodeMessageCB message_cb;
LogicalDecodeFilterByOriginCB filter_by_origin_cb;
LogicalDecodeShutdownCB shutdown_cb;} OutputPluginCallbacks;
typedef void (*LogicalOutputPluginInit)(struct OutputPluginCallbacks *cb);
なお、test_deco din g での実装は、以下のようになっています。 サンプルということもあり、一通りのコールバック関数を実装/登録していますが、 必須なのはb egin _cb , ch an ge_cb , co mmit_cb です。
リスト 7.2 test_deco din g の初期化処理

78 /* specify output plugin callbacks */79 void
80 _PG_output_plugin_init(OutputPluginCallbacks *cb)
81 {82
AssertVariableIsOfType(&_PG_output_plugin_init, LogicalOutputPluginInit);
83
84
cb->startup_cb = pg_decode_startup;
85
cb->begin_cb = pg_decode_begin_txn;
86
cb->change_cb = pg_decode_change;87
cb->commit_cb = pg_decode_commit_txn;
88cb->filter_by_origin_cb = pg_decode_filter;89
cb->shutdown_cb = pg_decode_shutdown;90
cb->message_cb = pg_decode_message;
91 }(2)コールバック関数
初期化関数の中で登録されるコールバック関数の一覧を以下に示します。

(2-1) 開始コールバック
スロットの作成時及びデータ取得の要求の各処理があった際に呼び出されます。
リスト 7.3 開始コールバック関数のインターフェイス

94 /* initialize this plugin */95 static void
96 pg_decode_startup(LogicalDecodingContext *ctx, OutputPluginOptions *opt,
97bool is_init)
データのやりとりに利用されるLo gicalDeco din gCo n text 構造体(ctx)の変数にメモリを割り当てたり、 O u tp u tPlu gin O p tio n s.o u tp u t_typ e の変
更(O U TPU T_PLU G IN_TEXTU AL_O U TPU TかO U TPU T_PLU G IN_B INARY_O U TPU T)など各種オプションの設定を行います。

(2-2) 終了コールバック
各処理の最後に呼び出されます。
リスト 7.4 終了コールバック関数のインターフェイス

190 /* cleanup this plugin's resources */191 static void
192 pg_decode_shutdown(LogicalDecodingContext *ctx)
開始コールバック関数で割り当てた領域の削除などのクリーンアップ処理を実施します。

(2-3) トランザクション開始コールバック

Page 38 of 51

PostgreSQL Enterprise Consortium
トランザクションの開始(B EG IN)を検出した際に呼び出されます。
リスト 7.5 トランザクション開始コールバック関数のインターフェイス

200 /* BEGIN callback */201 static void
202 pg_decode_begin_txn(LogicalDecodingContext *ctx, ReorderBufferTXN *txn)
なお、トランザクション開始を検出した場合でも、そのトランザクションが破棄(AB O RT)される 場合には、トランザクション開始コールバック関数は呼び出されません。

(2-4) トランザクション終了コールバック
トランザクションの終了(CO MMIT)を検出した際に呼び出されます。
リスト 7.6 トランザクション終了コールバック関数のインターフェイス

224 /* COMMIT callback */225 static void
226 pg_decode_commit_txn(LogicalDecodingContext *ctx, ReorderBufferTXN *txn,
227
XLogRecPtr commit_lsn)(2-5) 更新コールバック
データの更新(INSERT, U PDATE, DELETE)を検出した際に呼び出されます。
リスト 7.7 更新コールバック関数のインターフェイス

391 /*
392 * callback for individual changed tuples
393 */
394 static void
395 pg_decode_change(LogicalDecodingContext *ctx, ReorderBufferTXN *txn,
396
Relation relation, ReorderBufferChange *change)
更新内容は、ch an ge-> actio n を見ることで判別できます。 また、新旧データの内容はch an ge-> data.tp を見ることで取得できます。
表 7.1 ロジカルデコーディングの更新処理判別
更新処理内容change->action

新し いデータ

古いデータ挿入処理(INSERT)ch an ge> data.tp .n ew tu p leー

REO RDER_B U FFER_CHANG E_U PDATE 更新処理(U PDATE)

ch an ge> data.tp .n ew tu p lech an ge-> data.tp .o ldtu p leーch an ge-> data.tp .o ldtu p le

REO RDER_B U FFER_CHANG E_INSERT

削除処理(DELETE)REO RDER_B U FFER_CHANG E_DELETE(2-6) オリジンフィルターコールバック

トランザクションの開始/終了及びデータの更新を検出した際、前述のコールバック関数に先駆けて 呼び出されます。
リスト 7.8 オリジンフィルターコールバック関数のインターフェイス

247 static bool
248 pg_decode_filter(LogicalDecodingContext *ctx,
249
RepOriginId origin_id)
オリジンの設定を行っておくことで、デコードすべきか否かを判断することが可能で、 双方向のレプリケーション時などにデータの伝播が循環し続けてしまう
ことを抑制できます。 オリジンの設定は、後述のSQ L 関数で設定できます。

(2-7) 汎用メッセージコールバック
汎用メッセージを検出した際に呼び出されます。

Page 39 of 51

PostgreSQL Enterprise Consortium
リスト 7.9 汎用メッセージコールバック関数のインターフェイス

479 static void
480 pg_decode_message(LogicalDecodingContext *ctx,
481
ReorderBufferTXN *txn, XLogRecPtr lsn, bool transactional,
482
const char *prefix, Size sz, const char *message)
汎用メッセージは、後述のSQ L 関数で発行することができます。

7.2.2. 設定例
ロジカルデコーディングの仕組みを利用するため、最低限以下の設定を変更する 必要があります。

(1)w al_levelw al_level パラメータでW AL に含まれる情報の量を変更します。 デフォルトの"min imal"から "lo gical"にします。
ex) w al_level = lo gical
なお、w al_level に指定できる値は、上記"min imal", "lo gical"のほか、"rep lica"があり、 "lo gical", "rep lica", "min imal"の順でW AL に含まれる情報の量は多くなります。 Po stgreSQ L9.5 以前の"arch ive"/"h o t_stan db y"は"rep lica"として扱われます。

(2)max_rep licatio n _slo t
max_rep licatio n _slo t パラメータでデータベースクラスタで作成できるスロットの最大数を変更します。 デフォルトの0 から 1 以上の値にします。
ex) max_rep licatio n _slo t = 1
また、ロジカルデコーディングの実行をレプリケーション接続を介して行う場合は、 上記に加えて、以下の設定を変更する必要があります。

(3)max_w al_sen ders
max_w al_sen ders パラメータでデータベースクラスタで起動できるw al sen der プロセスの最大数を変更します。 デフォルトの0 から 1 以上の値にします。
ex) max_w al_sen ders = 1(4)レプリケーション接続の許可
レプリケーション接続を許可するため、p g_h b a.co n f に rep licatio n のレコードを追加します。

ex) 認証方式の選択は適宜行ってください。
リスト 7.10 レプリケーション接続の許可

local replication postgres
trust
host replication postgres 127.0.0.1/32 trust
host replication postgres ::1/128
trust

7.2.3. 実行例
ロジカルデコーディングを実行する方法としては、以下の2 通りがあります。

(1)SQ L 関数を介して実行
SQ L 関数を実行することで、ロジカルデコーディングを実行します。
(2)レプリケーション接続を介して実行
同期レプリケーション機能とロジカルデコーディングを併用する場合には、 レプリケーション接続を介した実装、設定、実行を行う必要があります。 レプリ
ケーションプロトコルを利用したり、連携先の状態を連携元に伝えるなど の処理を実装する必要があります。

h ttp ://w w w .p o stgresql.jp /do cu men t/9.6/h tml/p ro to co l-rep licatio n .h tml
説明を簡単にするため、ここではSQ L 関数を用いた実行例を見ていきます。 設定後に実施する大まかな手順の流れは以下の通りです。

(1)スロットの作成
まずp g_create_lo gical_rep licatio n _slo t 関数を実行して、スロットを作成します。

p g_create_lo gical_rep licatio n _slo t(slo t_n ame n ame, p lu gin n ame)
関数の引数として、スロット名(slo t_n ame)とプラグイン名(p lu gin )を指定します。 戻り値は、スロット名(slo t_n ame)とトランザクション位置(xlo g_p o sitio n )です。

Page 40 of 51

PostgreSQL Enterprise Consortium
リスト 7.11 スロットの作成

testdb=# select pg_create_logical_replication_slot('test_slot','test_decoding');
LOG: logical decoding found consistent point at 0/152E108
DETAIL: There are no running transactions.
STATEMENT: select pg_create_logical_replication_slot('test_slot','test_decoding');
pg_create_logical_replication_slot-----------------------------------(test_slot,0/152E140)(1 row)
なお、作成したスロットはp g_rep licatio n _slo ts ビューで確認できます。また、スロットの 削除はp g_dro p _rep licatio n _slo t 関数で行えます。
リスト 7.12 スロットの確認と削除

testdb=# select * from pg_replication_slots ;-[ RECORD 1 ]-------+-------------slot_name| test_slot
plugin| test_decoding
slot_type| logical
datoid| 16384
database| testdb
active| factive_pid|
xmin|
catalog_xmin| 880
restart_lsn| 0/152E108
confirmed_flush_lsn | 0/152E140

testdb=# select pg_drop_replication_slot('test_slot');
pg_drop_replication_slot-------------------------(1 row)(2)デコーディングの実行
スロットの作成が完了したら、そのスロットから W AL を読み込みデコーディングしていきます。 p g_lo gical_slo t_get_ch an ges 関数、
p g_lo gical_slo t_p eek_ch an ges 関数を実行することで、 デコーディングを実行できます。

p g_lo gical_slo t_get_ch an ges(slo t_n ame n ame, u p to _lsn p g_lsn , u p to _n ch an ges in t, VARIADIC o p tio n s text[])
p g_lo gical_slo t_p eek_ch an ges(slo t_n ame n ame, u p to _lsn p g_lsn , u p to _n ch an ges in t, VARIADIC o p tio n stext[])
関数の引数として、スロット名(slo t_n ame)、トランザクション位置(u p to _lsn )、トランザクション数(u p to _n ch an ges)、 プラグインで利用されるオプション(o p tio n s)を指定します。 u p to _lsn , u p to _n ch an ge でトランザクション位置及びトランザクション数によるデコード対象の絞り込みが行えます。 両
方ともNU LL が指定されている場合は全ての結果が返されます。 p g_lo gical_slo t_get_ch an ges 関数は一度デコードされたデータは再度デコードされません。一方、 p g_lo gical_slo t_p eek_ch an ges 関数は一度デコードされたデータも再度デコードの対象となります。 戻り値は、トランザクション位
置(lo catio n )、トランザクションID(xid)、デコードしたデータ (data)です。

Page 41 of 51

PostgreSQL Enterprise Consortium
リスト 7.13 スロットの確認と削除

testdb=# select * from pg_logical_slot_peek_changes('test_slot', NULL,
NULL);
location | xid |data-----------+-----+---------------------------------------0/152E4F8 | 880 | BEGIN 880
0/152E4F8 | 880 | table public.tbl: INSERT: i[integer]:1
0/152E6B8 | 880 | COMMIT 880(3 rows)
testdb=# select * from pg_logical_slot_peek_changes('test_slot', NULL,
NULL);
location | xid |data-----------+-----+---------------------------------------0/152E4F8 | 880 | BEGIN 880
0/152E4F8 | 880 | table public.tbl: INSERT: i[integer]:1
0/152E6B8 | 880 | COMMIT 880(3 rows)
testdb=# select * from pg_logical_slot_get_changes('test_slot', NULL, NULL);
location | xid |data-----------+-----+---------------------------------------0/152E4F8 | 880 | BEGIN 880
0/152E4F8 | 880 | table public.tbl: INSERT: i[integer]:1
0/152E6B8 | 880 | COMMIT 880(3 rows)
testdb=# select * from pg_logical_slot_get_changes('test_slot', NULL, NULL);
location | xid | data----------+-----+-----(0 rows)
なお、処理結果をb ytea 型で出力する場合(O u tp u tPlu gin O p tio n s.o u tp u t_typ e をO U TPU T_PLU G IN_B INARY_O U TPU Tと したプラグインの場
合)には、p g_lo gical_slo t_get_b in ary_ch an ges 関数、p g_lo gical_slo t_p eek_ch an ges 関数で 実行できます。
基本的な利用方法は上記のみです。 以降はオリジンフィルター処理とメッセージ処理の実行方法について見ていきます。

(3)オリジンフィルター処理
オリジンの設定を行っておくと、W AL の中に含まれるオリジンに関する情報を利用できるようになります。 プラグインのオリジンフィルターコールバック関数は、
この情報を元にデコードを行うか否かを 判別できるようになります。 オリジンの作成はp g_rep licatio n _o rigin _create 関数で行います。

p g_rep licatio n _o rigin _create(n o de_n ame text)
関数の引数は、オリジン名(n o de_n ame)です。 戻り値は、そのオリジンを一意に特定するID(o id)です。
オリジンの設定はp g_rep licatio n _o rigin _sessio n _setu p 関数で行います。

p g_rep licatio n _o rigin _sessio n _setu p (n o de_n ame text)
関数の引数は、オリジン名(n o de_n ame)です。戻り値はありません(vo id)。
オリジンの削除はp g_rep licatio n _o rigin _dro p 関数で行います。

p g_rep licatio n _o rigin _dro p (n o de_n ame text)
関数の引数は、オリジン名(n o de_n ame)です。戻り値はありません(vo id)。

Page 42 of 51

PostgreSQL Enterprise Consortium
リスト 7.14 オリジンの作成、設定、削除

testdb=# select pg_replication_origin_create('test_origin');
pg_replication_origin_create-----------------------------1(1 row)

testdb=# select pg_replication_origin_session_setup('test_origin');
pg_replication_origin_session_setup------------------------------------(1 row)

testdb=# select pg_replication_origin_drop('test_origin');
pg_replication_origin_drop---------------------------(1 row)

test_deco din g では、デコーディングのオプションとして o n ly-lo cal オプションを指定できます。 このオプションを指定するとオリジンが設定されたセッションから実行された更新処理を デコードの対象から外すようになります。

Page 43 of 51

PostgreSQL Enterprise Consortium
リスト 7.15 オリジンフィルター処理

testdb=# begin;
BEGIN
testdb=# insert into tbl values (1000);
INSERT 0 1
testdb=# commit;
COMMIT
testdb=# select * from pg_logical_slot_peek_changes('test_slot', NULL,
NULL);
location | xid |data-----------+-----+------------------------------------------0/152F880 | 888 | BEGIN 888
0/152F880 | 888 | table public.tbl: INSERT: i[integer]:1000
0/152FAD8 | 888 | COMMIT 888(3 rows)
testdb=# select * from pg_logical_slot_peek_changes('test_slot', NULL, NULL,'only-local', 'on');location | xid | data----------+-----+-----(0 rows)-- IN ANOTHER SESSION --------------------------------------testdb=# begin;
BEGIN
testdb=# insert into tbl values (1001);
INSERT 0 1
testdb=# commit;
COMMIT-------------------------------------------------------------testdb=# select * from pg_logical_slot_peek_changes('test_slot', NULL, NULL,'only-local', 'on');location | xid |data-----------+-----+------------------------------------------0/152FBF0 | 889 | BEGIN 889
0/152FBF0 | 889 | table public.tbl: INSERT: i[integer]:1001
0/152FE88 | 889 | COMMIT 889(3 rows)(4)汎用メッセージ処理
汎用メッセージにより、W AL に独自の文字列を含めることができます。 プラグインの汎用メッセージコールバック関数は、この情報を元に独自の処理を行うことが できます。 汎用メッセージの発行はp g_lo gical_emit_message 関数で行います。

p g_lo gcial_emit_message(tran sactio n al b o o l, p refix text, co n ten t text) p g_lo gcial_emit_message(tran sactio n alb o o l, p refix text, co n ten t b ytea)
関数の引数は、トランザクションの一部か否かを表すフラグ(tran sactio n al)、判別に利用する接頭辞(p refix)、 メッセージ本体(co n ten t ： text もしくはb ytea)です。 戻り値はトランザクション位置(p g_lsn )です。

Page 44 of 51

PostgreSQL Enterprise Consortium
リスト 7.16 汎用メッセージ処理

testdb=# select pg_logical_emit_message(false, 'postgres', 'Hello,
LogicalDecoding');pg_logical_emit_message------------------------0/15301D0(1 row)
testdb=# select * from pg_logical_slot_peek_changes('test_slot', NULL,
NULL);
location | xid |data-----------+-----+---------------------------------------------------------------------------------0/15301D0 | 0 | message: transactional: 0 prefix: postgres, sz: 22
content:Hello, LogicalDecoding(1 row)

7.3. ロジカルデコーディングの今後
ロジカルデコーディングを用いた正式なソリューション、まだ Po stgreSQ L 本体には含まれていません。 このため、本機能を利用するためには、自らプラグインを作成するか、世の中に公開されている プラグインを適用する必要があります。 導入に際しては、十分に検証を行った上で判断してください。
なお、2017 年9 月にリリース予定のPo stgreSQ L10 では、ロジカルデコーディングの機構を ベースにした「ロジカルレプリケーション」機能が本体に追加される見込みです。 現
時点ではPo stgreSQ L から Po stgreSQ L へのデータ連携がメインの機能ですが、 この機能の追加により、様々な異種DB 間の連携ソリューションが加速することが期待されます。

Page 45 of 51

PostgreSQL Enterprise Consortium

8. 外部データラッパ(FDW)を用いた連携方式
本章では、Po stgreSQ L の外部データラッパを用いた異種データベース連携の方式に関して説明します。

8.1. 外部データラッパ(FDW)について
Po stgreSQ L はSQ L/MED 仕様を部分的に実装しており、Po stgreSQ L の外部にあるデータ（「外部データ」と呼びます）に対して SQ L 文を利用してアクセスすることが可能になります。
外部データには、外部データラッパ(以降、FDW )を通じてアクセスします。 外部データラッパは、外部データと通信できるライブラリであり、接続の確立やデータ取得の機能を抽象化します。
本章では、特に異種データベースのスキーマ、テーブルに格納されたデータを 「外部データ」の対象にし、異種データベースとの連携方法として FDW を評価することを目的としています。
なお、 2015 年度のPG ECo n s の成果物 DB 選定基準編(W G 2) にも、Po stgreSQ L の特長的な機能として、外部データラッパの調査結果が整理されていますので、併せてご参照ください。

8.2. FDWを利用した異種DB連携環境の構築手順
FDW を利用して異種DB と連携する手順は、大きく以下の通りです。 本節では、異種DB として O racle Datab ase 11g と、外部データラッパとして o racle_fdw を利用します。

1. O racle Datab ase 向けのクライアントのセットアップ
外部データラッパから利用するO racle クライアントの入手およびインストール
O racle クライアントが利用する環境変数の設定
O racle クライアントからのO racle Datab se への接続確認

2. 異種データベース向けの外部データラッパのセットアップ
異種データベース向けの外部データラッパの入手
コンパイルおよびインストール

3. 外部データへのアクセス手順
外部サーバの作成(CREATE SERVER)
外部サーバで利用するユーザ権限定義
Po stgreSQ L ユーザと外部サーバのユーザの関連付け(CRTEATE U SER MAPPING )外部テーブルの作成(CREATE FO REIG N TAB LE)

4. 外部データへのアクセス
SQ L 文の記載方法
5. その他の機能
O racle のスキーマ配下のテーブル定義を外部テーブルとしてインポートする(IMPO RT FO REIG N SCHEMA)
前出の2015 年度のPG ECo n s の成果物の FDW （外部データラッパ）のアセスメント に O racle クライアントのセットアップ手順、o racle_fdw のセットアップ手順が記載されています。 本節では、外部データラッパの利用に共通で必要な、Po stgreSQ L 内での構築手順(3.,4.,5.)を中心に記載します。

8.2.1. 外部デ ー タへ の アクセ ス手順
8.2.1.1. 外部サーバの作成 (CREATE SERVER)
以下のパラメータを利用して外部サーバの定義を実施します。
パラ メ ータ 名

値

外部サーバ名remo te_o racle

利用する外部データラッパ名o racle_fdw

接続先192.168.10.2

ポート番号

1521

データベース名XE

以下のSQ L 文を実行し、外部サーバを定義します。

Page 46 of 51

PostgreSQL Enterprise Consortium

testdb=# create server remote_oracle FOREIGN DATA WRAPPER oracle_fdw options (dbserver'192.168.10.2:1521/XE');

8.2.1.2. 外部サーバで利用するユーザ権限定義
必要に応じて、外部サーバに接続するユーザに権限を付与します。

testdb=# GRANT USAGE ON FOREIGN SERVER remote_oracle TO oracle_user;

8.2.1.3. PostgreSQL ユーザと 外部サーバのユーザの関連付け (CRTEATE USER M APPIN G)
Po stgreSQ L ユーザと外部サーバのユーザの関連付けます。

testdb=# create user mapping for oracle_user server server remote_oracle options(user 'scott',password 'tiger');

8.2.1.4. 外部テーブルの作成 (CREATE FOREIGN TABLE)
ここでは、O racle 上のo racle_fdw _test という名前のテーブルに対し、Po stgreSQ L 側ではo racle_test という名前で外部テーブルを作成することで対応付けます。

testdb=# create foreign table oracle_table(id int, name text) server remote_oracle options(table'oracle_fdw_test');8.2.2. 外部デ ー タへ の アクセ ス
8.2.2.1. SQL 文の記載方法
定義した外部テーブルに対し、ローカルのテーブルアクセスと同様に SQ L 文を記述します。 以下に例を示します。

postgres=# select id from oracle_table ;
id
---1
2
3
4
5
6
7
8
(8 rows)

8.2.3. そ の 他の 機能
8.2.3.1. Oracle のスキーマ配下のテーブル定義を外部テーブルと し て イ ンポート する (IM PORT FOREIGN SCHEM A)
IMPO RT FO REIG N SCHEMA は、外部サーバ上に存在するテーブルを指す外部テーブルを作成するための構文です。
Po stgreSQ L 文書のIMPO RT FO REIG N SCHEMA の記述 も参照してください。
以下は、O racle のスキーマ「 SYSTEM 」配下のテーブルを外部テーブル定義としてインポートする例です。 (スキーマ名SYSTEM は大文字ですので、二重引用符で囲む必要がある点に注意してください)

Page 47 of 51

PostgreSQL Enterprise Consortium

postgres=# create schema test_schema;
CREATE SCHEMA
postgres=# IMPORT FOREIGN SCHEMA "SYSTEM" FROM SERVER remote_oracle INTO test_schema;
IMPORT FOREIGN SCHEMA

システムカタログ「 p g_fo reign _tab le 」を利用して、外部テーブル定義が実施されていることを確認します。

postgres=# select * from pg_foreign_table order by ftoptions limit 10;
ftrelid | ftserver |ftoptions---------+----------+------------------------------------------36702 |
36065 | {schema=SYSTEM,table=AQ$DEF$_AQCALL}
36179 |
36065 | {schema=SYSTEM,table=AQ$DEF$_AQCALL}
36182 |
36065 | {schema=SYSTEM,table=AQ$DEF$_AQERROR}
36705 |
36065 | {schema=SYSTEM,table=AQ$DEF$_AQERROR}
36185 |
36065 | {schema=SYSTEM,table=AQ$_DEF$_AQCALL_F}
36708 |
36065 | {schema=SYSTEM,table=AQ$_DEF$_AQCALL_F}
36188 |
36065 | {schema=SYSTEM,table=AQ$_DEF$_AQERROR_F}
36711 |
36065 | {schema=SYSTEM,table=AQ$_DEF$_AQERROR_F}
36714 |
36065 | {schema=SYSTEM,table=AQ$_INTERNET_AGENTS}
36191 |
36065 | {schema=SYSTEM,table=AQ$_INTERNET_AGENTS}(10 rows)

8.3. FDWを利用する上での留意点
8.3.1. 外部デ ー タ参照の 確認 (実行計画 )外部テーブルへのアクセスの確認方法として、実行計画を参照する方法があります。 以下は、通常のテーブルへのアクセス時の実行計画です。

postgres=# explain analyze select count(*) from pgbench_accounts;
QUERY PLAN-------------------------------------------------------------------------------------------------------------------------------------------------------------------Aggregate (cost=28480.42..28480.43 rows=1 width=0) (actual time=652.621..652.621 rows=1 loops=1)-> Index Only Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.42..25980.42 rows=1000000 width=0) (actual time=0.063..394.224 rows=1000000 loops=1)
Heap Fetches: 0
Planning time: 0.520 ms
Execution time: 653.024 ms(5 rows)

以下は、外部テーブルへのアクセス時の実行計画です。 テーブルへのアクセス方法として「 Fo reign Scan 」が選択されています。

postgres=# explain analyze select count(*) from pgbench_accounts_remote;
QUERY PLAN-----------------------------------------------------------------------------------------------------------------------------------------Aggregate (cost=220.92..220.93 rows=1 width=0) (actual time=3768.933..3768.934 rows=1 loops=1)-> Foreign Scan on pgbench_accounts_remote (cost=100.00..212.39 rows=3413 width=0) (actual
time=1.761..3486.258 rows=1000000 loops=1)
Planning time: 0.260 ms
Execution time: 3769.992 ms(4 rows)

8.3.2. プッ シ ュ ダウ ン
対象にする異種DB に対応するFDW の実装によっては、SQ L 文のW HERE 句の条件に応じて 連携先データベース上で処理が実施されます(「プッシュダウン」と呼びます)。
これにより、連携先のデータベースのリソースを有効に利用できます。

Page 48 of 51

PostgreSQL Enterprise Consortium
例を以下に示します。

postgres=# explain(analyze,verbose) select * from pgbench_accounts_remote where aid < 10 order bybid;
QUERY PLAN-----------------------------------------------------------------------------------------------------------------------------------------Sort (cost=116.37..116.55 rows=72 width=352) (actual time=19.894..19.895 rows=9 loops=1)
Output: aid, bid, abalance, filler
Sort Key: pgbench_accounts_remote.bid
Sort Method: quicksort Memory: 26kB-> Foreign Scan on public.pgbench_accounts_remote (cost=100.00..114.15 rows=72 width=352)(actual time=19.869..19.872 rows=9 loops=1)
Output: aid, bid, abalance, filler
Remote SQL: SELECT aid, bid, abalance, filler FROM public.pgbench_accounts WHERE ((aid <10))Planning time: 7.645 ms
Execution time: 59.947 ms(9 rows)

同じ定義のテーブルをローカルに作成し、同様のSQ L 文を実行した場合の実行計画は、以下の通りです。

postgres=# explain(analyze,verbose) select * from pgbench_accounts where aid < 10 order by bid;
QUERY PLAN----------------------------------------------------------------------------------------------------------------------------------------------------Sort (cost=8.74..8.76 rows=9 width=97) (actual time=0.240..0.243 rows=9 loops=1)
Output: aid, bid, abalance, filler
Sort Key: pgbench_accounts.bid
Sort Method: quicksort Memory: 26kB-> Index Scan using pgbench_accounts_pkey on public.pgbench_accounts (cost=0.43..8.59 rows=9
width=97) (actual time=0.011..0.019 rows=9 loops=1)
Output: aid, bid, abalance, filler
Index Cond: (pgbench_accounts.aid < 10)
Planning time: 1.557 ms
Execution time: 0.472 ms(9 rows)

W HERE 句(W HERE aid < 10)の絞り込みが連携先のデータベースに対して投入されるため、 連携先のリソースを利用できます。 さらに、W HERE 句で絞り込んだ結果のみを連携元に返却しているため、 連携先、連携元を流通するデータ量が削減できています。

8.3.3. ストアドプロシ ー ジャ 、 ユ ー ザ定義ファ ン クシ ョ ン の 実行
異種DB 上に配置されているストアドプロシージャやユーザファンクションは、FDW を通じて直接実行することができません。 そのため、FDW を経由せず、連携先の異種DBに対して直接SQ L 文を実行します。 例を以下に示します。
連携先(O racle)での操作-- Oracle上でユーザ定義ファンクションを作成
SQL> CREATE FUNCTION test_function(dt IN NUMBER) RETURN NUMBER
2 IS
3 d NUMBER;
4 BEGIN
5 d := dt * 2;
6 RETURN d;
7 END;8 /連携元(Po stgreSQ L)での操作

Page 49 of 51

PostgreSQL Enterprise Consortium-- 連携元(PostgreSQL)から外部テーブルにアクセス
postgres=# select id from oracle_table ;
id
---1
2
3
4
5
6
7
8
(8 rows)-- 外部テーブル向けのアクセスになっていることを実行計画から確認
postgres=# explain analyze select id from oracle_table ;QUERY PLAN-------------------------------------------------------------------------------------------------------------------Foreign Scan on oracle_table (cost=10000.00..20000.00 rows=1000 width=4) (actual
time=0.510..0.548 rows=8 loops=1)
Oracle query: SELECT /*a973056b69582205b8a9e0cb679a7325*/ "ID" FROM "ORACLE_FDW_TEST"
Planning time: 0.723 ms
Execution time: 0.611 ms(4 rows)--Oracle上で作成したファンクションを実行
postgres=# select test_function(id) from oracle_table ;ERROR: function test_function(integer) does not exist
LINE 1: select test_function(id) from oracle_table ;^HINT: No function matches the given name and argument types. You might need to add explicit type
casts.
postgres=# select id from oracle_table ;連携先(O racle)での操作-- Oracle上で作成したファンクションを実行
SQL> select test_function(2) from dual;
TEST_FUNCTION(2)---------------4

Page 50 of 51

PostgreSQL Enterprise Consortium

9. 著者（企業・団体名順）
版
第1.0 版(2016 年度W G 3)

所属企業・ 団体名

部署名

氏名

NTTテクノクロス株式会社

クラウド＆セキュリティ事業部

勝俣 智成

NTTテクノクロス株式会社

クラウド＆セキュリティ事業部

原田 登志

日本電気株式会社

クラウドプラットフォーム事業部

川畠 輝聖

日本電気株式会社

クラウドプラットフォーム事業部

湯村 昇平

株式会社日立製作所

O SS ソリューションセンタ

稲垣 毅

株式会社日立製作所

O SS ソリューションセンタ

田畑 義之

富士通株式会社

ミドルウェア事業本部

山本 貢嗣

Page 51 of 51