PostgreSQL 9.5 の IMPORT FOREIGN SCHEMA を使ってみた

こんにちは。サイオステクノロジー OSS サポート担当 W です。

今回は PostgreSQL 9.5 の目玉機能でもある IMPORT FOREIGN SCHEMA を紹介します。以前から CREATE FOREIGN TABLE は利用できましたが、SCHEMA としてインポートできるようになり、より便利になりました。

CREATE FOREIGN TABLE

以前から IMPORT FOREIGN TABLE により、リモート PostgreSQL のテーブルをインポート可能でした。しかし、テーブルをインポートするには長い SQL を書く必要がありました。

CREATE FOREIGN TABLE の書式

このような書式をもっており、カラム名やデータ型を指定してインポートする必要がありました。

CREATE FOREIGN TABLEの使用例

この例では dest_server に定義されている customers テーブルを public のテーブルとして利用できるように定義しています。

外部サーバからテーブルのインポートはできるのですが、一つ一つのテーブルに対して設定するのは手間もかかり、間違いも起きやすかったです。

IMPORT FOREIGN SCHEMA

IMPORT FOREIGN SCHEMA は CREATE FOREIGN TABLE に比べとてもシンプルにテーブルをインポートできます。

IMPORT FOREIGN SCHEMA の書式

実際の使用例を見ると CREATE FOREIGN TABLE との違いは一目瞭然です。

IMPORT FOREIGN SCHEMA の使用例

この例では dest_server の public スキーマをローカルの remote スキーマにインポートしています。dest_server の public スキーマ内のテーブル定義が丸ごと remote にインポートされます!今まで CREATA FOREGIN TABLE は面倒すぎて使っていない方も多かったと思います。IMPORT FOREIGN SCHEMA ならとても簡単です。

LIMIT TO でインポートするテーブルを限定したり、EXCEPT で除外するテーブルを指定することも可能です。

customers, purchases だけをインポート

reports, audit を除外

IMPORT FOREIGN SCHEMA の導入

IMPORT FOREIGN SCHEMA はデフォルトで有効な機能ではありません。外部データラッパーが必要で、利用できるようにするには準備が必要です。今回はリモートデータベースに PostgreSQL を使用するので postgres_fdw が必要です。対応する外部データラッパーが在れば他のデータベースなどにも利用できます。手順の概要は以下の通りです。

必要条件
– ローカルの PostgreSQL は 9.5 以降であること(リモートの PostgreSQL は古いバージョンでも構わない)

手順

  1. postgres_fdw をローカルサーバに導入する
  2. CREATE EXTENSION postgres_fdw をローカルデータベースで実行する
  3. CREATE SERVER でリモートデータベースサーバの定義を作成する
  4. CREATE USER MAPPING でリモートデータベースユーザとローカルユーザのマッピングを定義する
  5. IMPORT FOREIGN SCHEMA でリモートデータベースをインポートする

順番に解説します。

postgres_fdw の導入

postgres_fdw は contrib モジュールです。ソースから PostgreSQL をインストールした場合、contrib/postgres_fdw ディレクトリで make install を実行するとインストールできます。RPM を利用している場合、yum install postgresql-contrib で導入できます。

CREATE EXTENSION

psql コマンドでリモートデータベースをインポートするローカルのデータベースに接続します。

例: psql -h 127.0.0.1 -p 5495 mydb

psql から

を実行します。

CREATE EXTENSION の実行例

問題がなければエラーメッセージがなく終了します。

CREATE SERVER

CREATE SERVER の書式

詳しいオプションの説明はマニュアルを参照してください。

CREATE SERVER の実行例

この例では remote という名前のサーバを定義しています。外部データラッパーに postgres_fdw を使用し、リモートデータベースサーバの接続オプションを OPTIONS で host, dbname, port を指定しています。

CREATE USER MAPPING

実際にリモートデータベースに接続するにはローカルデータベースのユーザとのユーザのマッピングを作成しなければなりません。CREATE USER MAPPING で定義します。

CREATE USER MAPPING の書式

CREATE USER MAPPING の実行例

この例ではリモートユーザの pg_user をローカルの pg_user にマッピングしています。

IMPORT FORGIN SCHEMA

最後に IMPORT FORGIN SCHEMA でリモートスキーマをインポートします。

IMPORT FORGIN SCHEMA の実行例

この例では remote のサーバの public スキーマをローカルの public にインポートしています。

インポート後のテーブル一覧の例

このスキーマではローカルテーブルの test 以外は全てインポートされた外部テーブルであることがわかります。pgbench を使ったベンチマークをした事がある方は、pgbench のテーブルがあることも分かると思います。後にベンチマークを行います。

定義やインポートの削除

作成したサーバ定義やスキーマを削除したくなる場合もあります。

を実行すると関連するユーザマッピングやインポートしたテーブルを含めて削除してくれます。個別に削除するには DROP FOREIGN TABLE で削除します。テーブル自体は削除されず、外部テーブル定義のみが削除されます。外部テーブルは削除できません。削除しようとすると

のようなエラーが発生します。

外部テーブルのベンチマーク

リモートのデータベースは別のホストではなく、同じホスト上の別サーバとして別ポートで待機している PostgreSQL です。ローカル/リモート共に執筆時点(2015/2/25)の git リポジトリの PostgreSQL 9.5 です。

ローカル側ポート: 5499
リモート側ポート: 5495

pgbench プログラムを使ってローカル/リモートの PostgreSQL をベンチマークします。-N オプションを付けて一部の更新を無くしています。これは外部テーブルへのアクセスがローカルテーブルと同じようには動作せずエラーになるからです。

以下は更新を含む pgbench 結果です。

外部テーブル経由

直接アクセス

更新が入っている場合、遜色ない速度で実行されていることが分かります。次は参照のみの結果です。

外部テーブル経由

直接アクセス

参照のみは更新有りに比べ、何十倍も速いです。TPS が増えるほど、オーバーヘッドの微妙な差が大きな差として現れます。外部テーブル経由はかなり遅いことが分かります。このため外部テーブルは参照先の分散などには適しないことが分かります。

まとめ

IMPORT FORGIN SCHEMA はテーブルをコピーしたい場合にはとても便利です。外部テーブルをインポートしてローカルのテーブルにコピーするだけです。

外部テーブルはトランザクションでも使えるのですが、pgbench とは相性が悪いようです。並列実行されたトランザクションでエラーになり、ベンチマークは取れませんでした。

このことからも分るように、今のところは外部テーブルを速度の遅いローカルテーブル、のように利用するには注意が必要です。

参考

https://wiki.postgresql.org/wiki/What’s_new_in_PostgreSQL_9.5
https://www.postgresql.jp/document/9.5/html/sql-createforeigntable.html
https://www.postgresql.jp/document/9.5/html/sql-importforeignschema.html
https://www.postgresql.jp/document/9.5/html/sql-createserver.html
https://www.postgresql.jp/document/9.5/html/postgres-fdw.html

>> 雑誌等の執筆依頼を受付しております。
   ご希望の方はお気軽にお問い合わせください!

ご覧いただきありがとうございます! この投稿はお役に立ちましたか?

役に立った 役に立たなかった

1人がこの投稿は役に立ったと言っています。

コメント投稿

メールアドレスは表示されません。


*