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

SHARE

こんにちは。サイオステクノロジー 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 [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

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

CREATE FOREIGN TABLEの使用例

 CREATE FOREIGN TABLE remote.customers (
     id int NOT NULL,
     name text,
     company text,
     registered_date date,
     expiry_date date,
     active boolean,
     status text,
     account_level text) SERVER dest_server OPTIONS (schema_name 'public');

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

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

IMPORT FOREIGN SCHEMA

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

IMPORT FOREIGN SCHEMA の書式

IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER server_name
    INTO local_schema
    [ OPTIONS ( option 'value' [, ... ] ) ]

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

IMPORT FOREIGN SCHEMA の使用例

 IMPORT FOREIGN SCHEMA public
   FROM SERVER dest_server INTO remote;

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

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

customers, purchases だけをインポート

 IMPORT FOREIGN SCHEMA public
   LIMIT TO (customers, purchases)
   FROM SERVER dest_server INTO remote;

reports, audit を除外

 IMPORT FOREIGN SCHEMA public
   EXCEPT (reports, audit)
   FROM SERVER dest_server INTO remote;

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 postgres_fdw;

を実行します。

CREATE EXTENSION の実行例

pg_user@localhost ~=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

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

CREATE SERVER

CREATE SERVER の書式

CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]
    FOREIGN DATA WRAPPER fdw_name
    [ OPTIONS ( option 'value' [, ... ] ) ]

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

CREATE SERVER の実行例

pg_user@localhost ~=# CREATE SERVER remote FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', dbname 'pg_user', port '5495');
CREATE SERVER

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

CREATE USER MAPPING

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

CREATE USER MAPPING の書式

CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }
    SERVER server_name
    [ OPTIONS ( option 'value' [ , ... ] ) ]

CREATE USER MAPPING の実行例

pg_user@localhost ~=# CREATE USER MAPPING FOR pg_user SERVER remote OPTIONS (user 'pg_user');
CREATE USER MAPPING

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

IMPORT FORGIN SCHEMA

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

IMPORT FORGIN SCHEMA の実行例

pg_user@localhost ~=# IMPORT FOREIGN SCHEMA public FROM SERVER remote INTO public;
IMPORT FOREIGN SCHEMA

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

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

pg_user@localhost ~=# \d
                    リレーションの一覧
┌──────────┬────────────────────┬──────────────┬─────────┐
│ スキーマ │        名前        │      型      │ 所有者  │
├──────────┼────────────────────┼──────────────┼─────────┤
│ public   │ counter            │ 外部テーブル │ pg_user │
│ public   │ myusers            │ 外部テーブル │ pg_user │
│ public   │ pgbench_accounts   │ 外部テーブル │ pg_user │
│ public   │ pgbench_branches   │ 外部テーブル │ pg_user │
│ public   │ pgbench_history    │ 外部テーブル │ pg_user │
│ public   │ pgbench_tellers    │ 外部テーブル │ pg_user │
│ public   │ test               │ テーブル     │ pg_user │
│ public   │ url_access_counter │ 外部テーブル │ pg_user │
│ public   │ user_master        │ 外部テーブル │ pg_user │
└──────────┴────────────────────┴──────────────┴─────────┘
(9 行)

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

定義やインポートの削除

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

DROP SERVER remote CASCADE;

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

pg_user@localhost ~=# DROP TABLE user_master;
ERROR:  42809: "user_master" is not a table
HINT:  Use DROP FOREIGN TABLE to remove a foreign table.
LOCATION:  DropErrorMsgWrongType, tablecmds.c:791

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

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

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

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

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

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

外部テーブル経由

[pg_user@dev postgres_fdw]$ pgbench -h localhost -p 5499 -c 40 -s 2000 -n -N
Scale option ignored, using pgbench_branches table count = 1
transaction type: Update only pgbench_accounts
scaling factor: 1
query mode: simple
number of clients: 40
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 400/400
latency average: 0.000 ms
tps = 401.474616 (including connections establishing)
tps = 417.503413 (excluding connections establishing)

直接アクセス

[pg_user@dev postgres_fdw]$ pgbench -h localhost -p 5495 -c 40 -s 2000 -n -N
Scale option ignored, using pgbench_branches table count = 1
transaction type: Update only pgbench_accounts
scaling factor: 1
query mode: simple
number of clients: 40
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 400/400
latency average: 0.000 ms
tps = 379.430323 (including connections establishing)
tps = 393.706988 (excluding connections establishing)

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

外部テーブル経由

[pg_user@dev postgres_fdw]$ pgbench -h localhost -p 5499 -c 90 -s 2000 -n -S
scale option ignored, using count from pgbench_branches table (1)
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 90
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 900/900
latency average: 0.000 ms
tps = 4749.240122 (including connections establishing)
tps = 4772.880985 (excluding connections establishing)

直接アクセス

[pg_user@dev postgres_fdw]$ pgbench -h localhost -p 5495 -c 90 -s 2000 -n -S
scale option ignored, using count from pgbench_branches table (1)
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 90
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 900/900
latency average: 0.000 ms
tps = 8238.514138 (including connections establishing)
tps = 8311.517311 (excluding connections establishing)

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

まとめ

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

pg_user@localhost ~=# SELECT * INTO new_table FROM pgbench_accounts ;
SELECT 100000

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

client 69 aborted in state 11: ERROR:  could not serialize access due to concurrent update
CONTEXT:  Remote SQL command: SELECT bid, bbalance, filler, ctid FROM public.pgbench_branches WHERE ((bid = 1)) FOR UPDATE

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

参考

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人がこの投稿は役に立ったと言っています。

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

前の記事

HTTP セッション管理とトランザクション

次の記事

PostgreSQL の文字列型についてまとめてみた