更新日時: 2020年3月16日
PostgreSQLには、標準SQLの拡張である SQL/MED (“Management of External Data”)に沿った外部データアクセスの仕組みが備わっています。外部データの種類に応じた外部データラッパー（ Foreign Data Wrapper 、FDW）を適用することで、様々な外部データをPostgreSQL内のテーブルと同様に扱うことができます。
oracle_fdw は外部データラッパーの1つで Oracle Database に対応しています。oracle_fdw を使用することで、Oracle Database のテーブルやビューを PostgreSQL 上のSQLから読み書きできるようになります。
oracle_fdwはバージョン9.1以降のPostgreSQLおよびバージョン10.1以上のOracle Client（および Oracle Instant Client）で利用可能です。Oracle Database のサーバ側バージョンは、使用するOracle Clientが対応していれば大丈夫です。
本記事ではoracle_fdw の導入方法、使用方法を紹介します。続いて公開予定の後編の記事では、実際の使用にあたって気になる「こんなことをしたらどうなる？」「どの程度 PostgreSQL側で、どの程度Oracle側で処理されるの？」「データ型の違いはどうなるの？」といった様々な挙動の詳細に迫ります。
oracle_fdw を導入する
始めに導入方法を解説します。本記事作成にあたっては以下のバージョンで動作確認を行っています。
| PostgreSQL 10.4 |
| oracle_fdw 2.1.0 |
| Oracle Database 11g R2 XE |
| Oracle Instant Client 12.1 |
Oracleデータベースの用意
Oracle Database のデータベースは既にセットアップ済みであるものとします。本記事では Oracle Database 11g R2 XE バージョンを使用しています。
OCI ライブラリのインストール
oracle_fdw は OCI（Oracle Call Interface）ライブラリを使って Oracleデータベースにアクセスします。そのため、PostgreSQL稼動サーバに OCI ライブラリのインストールが必要になります。本記事では Oracle Instant Client 12.1をインストールします。Oracle Instant Clientは OTN（Oracle Technology Network）Webサイトで配布されています。
# rpm -ivh oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
また、接続確認用にSQL*Plusモジュールもインストールしています。
# rpm -ivh oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
Oracle ClientとServerとの互換性については、以下のページを参考にして下さい。
https://www.oracle.com/jp/system-requirement/interoperability-support-195844-ja.html
インストール
oracle_fdwのページ（https://github.com/laurenz/oracle_fdw）からoracle_fdwのソースコードをZIP形式でダウンロードします。ここではリポジトリ上の最新コードをダウンロードすることにします。
ダウンロード後、解凍処理を行いソースコンパイルを実施します。このとき、PostgreSQLのコマンドにPATHが通っている必要があります。また、ソースコードを展開したディレクトリにあるMakefile 内に、Oracle Clientの標準的なパスが一通り列挙されています。導入したOracle Clientのパスがここに無ければ、既存の記述を真似て適宜に書き加えてから make を実行してください。
$ unzip oracle_fdw-master.zip $ cd oracle_fdw-master $ make $ make install
ソフトウェアのインストールが済んだら、PostgreSQLサービスを起動（既に起動しているなら再起動）します。このとき、PostgreSQLを起動するユーザにおいて Oracle Instant Client のライブラリにライブラリパスが通っている必要があります。
PostgreSQLをrpmからインストールしている場合には、以下のファイルが作られていますので、
/etc/ld.so.conf.d/postgresql-pgdg-libs.conf
そこに以下のようにライブラリのパスを追加しておきます。ファイルを書き換えたら忘れずに rootユーザでldconfigコマンドを実行して変更を反映させましょう。
/usr/pgsql-10/lib /usr/lib/oracle/12.1/client64/lib
手動でPostgreSQLを起動しているなら、以下のように LD_LIBRARY_PATH環境変数を使って指定しても良いでしょう。
$ export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib $ pg_ctl start
データベースへの登録
次に使用したいデータベース（ここでは db1）に対して、SQL の CREATE EXTENSIONコマンドを使ってoracle_fdw拡張モジュールをインストールします。ややこしいのですが、これも「インストールする」と表現します。psql の dx コマンドでインストール済みの拡張モジュールを確認できます。
db1=# CREATE EXTENSION oracle_fdw; CREATE EXTENSION db1=# \dx oracle_fdw インストール済みの拡張一覧 名前 | バージョン | スキーマ | 説明 ------------+------------+----------+---------------------------------------- oracle_fdw | 1.1 | public | foreign data wrapper for Oracle access (1 行)
このとき、PostgreSQL起動前に Oracle Clientライブラリの設定が正しく行われていないときには以下のエラーが発生します。
db1=# CREATE EXTENSION oracle_fldw; ERROR: could not load library "/usr/pgsql-10/lib/oracle_fdw.so": libclntsh.so.12.1: cannot open shared object file: No such file or directory
Oracle外部テーブルを定義する
それでは Oracleデータベース上に存在するテーブルをPostgreSQLの外部テーブルとして定義してみましょう。
Oracleデータベース上には以下のようにテーブルTBL_ORA_TEST1 があるものとします。
SQL> SELECT OWNER, TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'ORAUSER'; OWNER TABLE_NAME --------------- ------------------------------ ORAUSER TBL_ORA_TEST1 SQL> DESC TBL_ORA_TEST1 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER(5) ITEM VARCHAR2(100) UPD_DATE TIMESTAMP(6) SELECT * FROM TBL_ORA_TEST1; ID ITEM UPD_DATE ---------- ------------------------------ ------------------------------ 1 ORACLE DATA 1 18-07-18 14:19:29.402316 2 PostgreSQL INSERT DATA 1 18-07-18 14:56:15.870785
以下の(1) から(3) の手順で、OracleデータベースのテーブルをPostgreSQLの外部テーブルに定義します。
(1) 外部サーバ作成
Oracleデータベースにアクセスするための接続情報を外部サーバ(oradb112)として定義します。
db1=# CREATE SERVER oradb112 FOREIGN DATA WRAPPER oracle_fdw db1-# OPTIONS (dbserver '//192.168.156.102:1521/XE'); CREATE SERVER db1=# \des 外部サーバー一覧 名前 | 所有者 | 外部データラッパ ----------+----------+------------------ oradb112 | postgres | oracle_fdw (1 行)
テスト用の一般データベースユーザpguserが定義した外部サーバを使用できるように、権限を付与します。
db1=# GRANT USAGE ON FOREIGN SERVER oradb112 TO pguser; GRANT
(2) ユーザマッピング定義
外部サーバ（oradb112）とユーザ（pguser）の対応付けの定義を行います。PostgreSQL上で ユーザpguser が外部テーブルにアクセスしたとき、oradb112外部サーバに定義されたリモート先Oracleデータベースにアクセスするデータユーザはorauserである、ということを定義しています。
db1=> CREATE USER MAPPING FOR pguser SERVER oradb112 OPTIONS (USER 'orauser', PASSWORD 'orapass'); CREATE USER MAPPING db1=> db1=> deu ユーザーマッピング一覧 サーバー | ユーザー名 ----------+------------ oradb112 | pguser (1 行)
(3) 外部テーブル定義
Oracleデータベース上のテーブル、ビューをPostgreSQLの外部テーブルとして定義します。ここでは Oracleデータベース上のテーブルTBL_ORA_TEST1 を外部テーブルftbl_ora_test1 として定義します。
db1=> CREATE FOREIGN TABLE ftbl_ora_test1 db1-> ( db1(> id int OPTIONS (key 'true') NOT NULL, db1(> item varchar(100), db1(> upd_date timestamp db1(> ) db1-> SERVER oradb112 OPTIONS (SCHEMA 'ORAUSER', TABLE 'TBL_ORA_TEST1'); CREATE FOREIGN TABLE
id列に付加されている「OPTIONS (key ‘true’)」という記述は、この列が主キー列であることを示しています。複数の列から構成される主キーである場合には、それらの列全てにこのオプションを付加します。
作成した外部テーブルを確認します。
db1=> \det 外部テーブル一覧 スキーマ | テーブル | サーバー ----------+----------------+---------- public | ftbl_ora_test1 | oradb112 (1 行) db1=> db1=> \d ftbl_ora_test1 外部テーブル "public.ftbl_ora_test1" 列 | 型 | 照合順序 | Null 値を許容 | デフォルト | FDW オプション ----------+-----------------------------+----------+---------------+------------+---------------- id | integer | | not null | | (key 'true') item | character varying(100) | | | | upd_date | timestamp without time zone | | | | サーバー: oradb112 FDW オプション: (schema 'ORAUSER', "table" 'TBL_ORA_TEST1')
oracle_fdwではクエリを外部テーブルとして定義することもできます。
以下の例では、Oracleデータベース上のテーブルに対するクエリを外部テーブルとして定義しています。
クエリを外部テーブルとして定義する際には、OPTIONS句のSCHEMAを定義してはいけません。
db1=> CREATE FOREIGN TABLE ftbl_ora_test1_q
db1-> (
db1(> id int OPTIONS (key 'true') NOT NULL,
db1(> item varchar(100),
db1(> upd_date timestamp
db1(> )
db1-> SERVER oradb112 OPTIONS(TABLE '(SELECT id, item, upd_date FROM tbl_ora_test1 where item like ''%PostgreSQL%'')');
CREATE FOREIGN TABLE
db1=> \det
外部テーブル一覧
スキーマ | テーブル | サーバー
----------+------------------+----------
public | ftbl_ora_test1_q | oradb112
db1=> d ftbl_ora_test1_q
外部テーブル "public.ftbl_ora_test1_q"
列 | 型 | 照合順序 | Null 値を許容 | デフォルト | FDW オプション
----------+-----------------------------+----------+---------------+------------+----------------
id | integer | | not null | | (key 'true')
item | character varying(100) | | | |
upd_date | timestamp without time zone | | | |
サーバー: oradb112
FDW オプション: ("table" '(SELECT id, item, upd_date FROM tbl_ora_test1 where item like ''%PostgreSQL%'')')
Oracle外部テーブルを使用する
外部テーブルとして定義を行うことで、OracleのテーブルをPostgreSQLからSQLでアクセスできるようになりました。
SELECTの実行
上記４で定義を行った外部テーブルftbl_ora_test1、 ftbl_ora_test1_q に SELECT を実行します。
db1=> select * from ftbl_ora_test1; id | item | upd_date ----+--------------------------+---------------------------- 1 | ORACLE DATA 1 | 2018-07-18 14:19:29.402316 2 | PostgreSQL INSERT DATA 1 | 2018-07-18 14:56:15.870785 (2 行) db1=> select * from ftbl_ora_test1_q; id | item | upd_date ----+--------------------------+---------------------------- 2 | PostgreSQL INSERT DATA 1 | 2018-07-18 14:56:15.870785 (1 行)
無事データを取り出すことができました。
INSERT/UPDATE/DELETEの実行
PostgreSQL9.3以降であれば、oracle_fdw は外部テーブルに対して、INSERT/UPDATE/DELETEを実行することができます。
ただし、UPDATE/DELETEについてはOracleのテーブルに設定された主キー列に対応する外部テーブルの列に”key”オプションを設定することが必要です。
“key” オプションが指定されている場合、以下のように動作します。
db1=> d ftbl_ora_test1 外部テーブル "public.ftbl_ora_test1" 列 | 型 | 照合順序 | Null 値を許容 | デフォルト | FDW オプション ----------+--------------------------------+----------+---------------+------------+---------------- id | integer | | not null | | (key 'true') item | character varying(100) | | | | upd_date | timestamp(6) without time zone | | | | サーバー: oradb112 FDW オプション: (schema 'ORAUSER', "table" 'TBL_ORA_TEST1') db1=> UPDATE ftbl_ora_test1 db1-> SET item = 'PostgreSQL UPDATE DATA2', db1-> upd_date = CURRENT_TIMESTAMP db1-> WHERE id = 3; UPDATE 1
“key” オプションが指定されていない場合、以下のように更新操作に対してエラーが発生します。
db1=> d ftbl_ora_test1_ng 外部テーブル "public.ftbl_ora_test1_ng" 列 | 型 | 照合順序 | Null 値を許容 | デフォルト | FDW オプション ----------+-----------------------------+----------+---------------+------------+---------------- id | integer | | not null | | item | character varying(100) | | | | upd_date | timestamp without time zone | | | | サーバー: oradb112 FDW オプション: (schema 'ORAUSER', "table" 'TBL_ORA_TEST1') db1=> UPDATE ftbl_ora_test1_ng db1-> SET item = 'PostgreSQL UPDATE DATA2', db1-> upd_date = CURRENT_TIMESTAMP db1-> WHERE id = 3; ERROR: no primary key column specified for foreign Oracle table DETAIL: For UPDATE or DELETE, at least one foreign table column must be marked as primary key column. HINT: Set the option "key" on the columns that belong to the primary key.
IMPORT FOREIGN SCHEMA
oracle_fdw の機能を使用することで、OracleデータベースのテーブルやビューをPostgreSQLの外部テーブルとして定義しアクセスが可能であることを説明してきました。
ここで、Oracleのデータベースリンク機能と対比して考えたとき、Oracleのデータベースリンクであればスキーマに対して１つの定義で
対象スキーマが権限を保有するオブジェクトへのアクセスが可能となることに対して、PostgreSQLではアクセスを行いたいテーブル１つ１つを定義することに手間を感じることもあるかと思います。
PostgreSQLが9.5以降であれば、「IMPORT FOREIGN SCHEMA」を使用することでOracleデータベースの特定スキーマに存在するテーブルやビューの定義をPostgreSQLの外部テーブルとして一括で定義することが可能となります。
Oracleデータベース上のテーブルリストが以下のようにであるとします。
SQL> SELECT owner, table_name FROM dba_tables WHERE OWNER = 'ORAUSER' ORDER BY table_name OWNER TABLE_NAME ---------- ------------------------------ ORAUSER TBL_ORA_TEST1 ORAUSER TBL_ORA_TEST2 ORAUSER TBL_ORA_TEST3 ORAUSER TBL_ORA_TEST4 ORAUSER TBL_ORA_TEST5
上記のOracleデータベース上のスキーマ「ORAUSER」が所有するテーブルをIMPORT FOREIGN SCHEMAでまとめて外部テーブル定義をすることができます。
db1=> det 外部テーブル一覧 スキーマ | テーブル | サーバー ----------+----------+---------- (0 行) db1=> db1=> IMPORT FOREIGN SCHEMA "ORAUSER" FROM SERVER oradb112 INTO pguser; IMPORT FOREIGN SCHEMA db1=> db1=> det 外部テーブル一覧 スキーマ | テーブル | サーバー ----------+---------------+---------- pguser | tbl_ora_test1 | oradb112 pguser | tbl_ora_test2 | oradb112 pguser | tbl_ora_test3 | oradb112 pguser | tbl_ora_test4 | oradb112 pguser | tbl_ora_test5 | oradb112 (5 行)
識別子の大文字と小文字
OracleデータベースとPostgreSQLではテーブル名やユーザ名などの識別子について、大文字小文字の扱いが異なります。クォーティングすることなく識別子名を書くと、Oracleデータベースでは大文字に揃えられ、PostgreSQLでは小文字に揃えられます。この違いにより以下の点に注意が必要となります。
- 外部テーブル定義（CREATE FOREIGN TABLE）の際、OPTIONS句のスキーマ名およびユーザ名は Oracleのシステムカタログに定義されている情報（基本的には大文字）と同様に指定する必要があります。
- IMPORT FOREIGN SCHEMAを実行する際に、Oracleデータベースのスキーマを指定しますが Oracleのシステムカタログに定義されている情報（基本的には大文字）と同様に指定する必要があります。
- PostgreSQLでは、小文字に変換されてしまうため、Oracleデータベース上のスキーマ名を “（ダブルクォート）で括る必要があります。
まとめ
oracle_fdw は、Oracle上のテーブルやビューをPostgreSQLの外部テーブルにマッピングすることで PostgreSQLから直接SQLアクセスを実行可能とするツールです。これにより、PostgreSQL上のテーブルとOracle上のテーブルを結合させるSQLが実行可能となります。
PostgreSQLとOracleが共存している環境で Oracleのデータベースリンクのような機能をお探しであればデータへのアクセスのみとなりますが、代替機能として有用な手段となります。
本記事の後編ではoracle_fdw の動作・挙動の詳細を検証していきます。