pgsql_fdw -- 外部のPostgreSQLサーバに対して検索を実行するための外部データラッパです。
pgsql_fdwを用いると、外部のPostgreSQLサーバにあるテーブルに対応する外部テーブルを作成できます。また、、その外部テーブルを用いた通常のSELECT文を実行することで、通常のテーブルと同様に外部データを扱うことができます。
pgsql_fdwは、サーバ間でのデータ転送量を削減するために、外部のPostgreSQLに発行するクエリを最適化します。
PostgreSQL 9.1では外部テーブルは読み取り専用のため、pgsql_fdwで定義した外部テーブルに対する更新(INSERT/UPDATE/DELETE)はエラーとなり実行できません。
pgsql_fdwのインストール方法について説明します。
pgsql_fdwをソースコードからビルドするには、PostgreSQL 9.1 のソースツリーか、インストール済みのpgxsが必要です。以下の例のx.y.zは、ダウンロードしたバージョンに読み替えて下さい。
PostgreSQLのソースツリーを使用する場合は、PostgreSQL本体のconfigureおよびビルドを済ませてから、contrib配下にpgsql_fdwのソースを展開し、makeを実施します。
$ cd postgresql-9.1.x $ ./configure $ make $ cd contrib $ tar zxvf pgsql_fdw-x.y.z.tar.gz $ cd pgsql_fdw-x.y.z $ make $ sudo make install
pgxsを用いる場合は、USE_PGXS変数を設定してからビルドおよびインストールを実施します。
$ tar xzvf pgsql_fdw-x.y.z.tar.gz $ cd pgsql_fdw $ make USE_PGXS=1 $ sudo make USE_PGXS=1 install
pgsql_fdwはPostgreSQL 9.1で導入されたエクステンション形式に対応しているため、psqlなどでCREATE EXTENSIONコマンドを実行することで、関数などの構成要素とともに自動的に外部データラッパが作成されます。従来のcontribモジュールのように手動でSQLスクリプトを実行する必要はありません。なお、この作業にはスーパーユーザ権限が必要です。
$ psql
postgres=# CREATE EXTENSION pgsql_fdw;
CREATE EXTENSION
postgres=# \dew
List of foreign-data wrappers
Name | Owner | Handler | Validator
-----------+----------+-------------------+---------------------
pgsql_fdw | postgres | pgsql_fdw_handler | pgsql_fdw_validator
(1 row)
postgres=#
pgsql_fdwをアンインストールするには、DROP EXTENSION文を実行します。pgsql_fdwに関連するオブジェクトが存在する場合は、先にそれらを削除しておくか、CASCADEオプションが必要です。
以下の例では一つの外部サーバとユーザマッピング、四つの外部テーブルをまとめて削除しています。
postgres=# drop EXTENSION pgsql_fdw CASCADE; NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to server remote_db drop cascades to user mapping for public drop cascades to foreign table remote_accounts drop cascades to foreign table remote_branches drop cascades to foreign table remote_tellers drop cascades to foreign table remote_history DROP EXTENSION postgres=#
pgsql_fdwを用いて外部のPostgreSQLサーバにあるテーブルを検索する手順について説明します。なお、接続情報はサンプルですので、実際の環境に合わせて適宜読み替えてください。
スーパーユーザ権限のあるユーザでCREATE SERVERコマンドを実行して、検索対象のデータベースに対応する外部サーバを作成します。外部サーバのオプションとして、ホスト名やポート番号、データベース名を指定します。
postgres=# CREATE SERVER remote_db FOREIGN DATA WRAPPER pgsql_fdw postgres-# OPTIONS (host 'hostname', port '5432', dbname 'remote'); CREATE SERVER postgres=#
スーパーユーザ権限のあるユーザでCREATE USER MAPPINGコマンドを実行して、ローカルユーザとリモートユーザをひもづけるユーザマッピングを作成します。ユーザマッピングのオプションとして、リモートユーザのユーザ名やパスワードを指定します。
postgres=# CREATE USER MAPPING FOR postgres SERVER remote_db postgres-# OPTIONS (user 'postgres', password 'secret'); CREATE USER MAPPING postgres=#
任意のローカルユーザが外部テーブルを検索しても良いケースでは、ローカルユーザ名の変わりにpublicを指定することで、ユーザマッピングを持たないローカルユーザ用のマッピングを作成することもできます。
postgres=# CREATE USER MAPPING FOR public SERVER remote_db postgres-# OPTIONS (user 'app_user', password 'secret'); CREATE USER MAPPING postgres=#
スーパーユーザ権限のあるユーザでCREATE FOREIGN TABLEコマンドを実行して、検索対象のリモートテーブルに対応する外部テーブルを作成します。基本的な構文はCREATE TABLE文と同じですが、列リストの後に外部サーバを指定する必要があります。また、外部テーブルのオプションとして、スキーマ名とテーブル名を指定できます。以下の例では、pgbenchで使用するpgbench_accountsを検索するためのテーブルを定義しています。
postgres=# CREATE FOREIGN TABLE remote_accounts ( postgres(# aid integer, postgres(# bid integer, postgres(# abalance integer, postgres(# filler char(84)) postgres(# SERVER remote_db postgres(# OPTIONS (nspname 'public', relname 'pgbench_accounts'); CREATE FOREIGN TABLE postgres=#
外部テーブルが作成できたら、外部テーブルを使った通常のSELECT文を実行するだけで外部テーブルからデータを取得できます。実行するSELECTに制限はありませんが、外部テーブルに対する更新はできません。
postgres=# SELECT aid, bid, abalance FROM remote_accounts WHERE aid = 1; aid | bid | abalance -----+-----+---------- 1 | 1 | 0 (1 row) postgres=#
外部テーブルを作成したユーザ以外にも検索を許可したい場合は、通常のテーブルと同様にGRANT文でアクセス権限を付与します。
EXPLAIN コマンドを実行することで、実際に外部サーバに発行されているクエリを知ることができます。以下の例では、remote_accouns 外部テーブルを経由して pgbench_accounts テーブルにアクセスしていることが分かります。
postgres=# EXPLAIN SELECT aid, bid, abalance FROM remote_accounts WHERE aid = 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on remote_accounts (cost=100.00..130.82 rows=1 width=12)
Filter: (aid = 1)
Remote SQL: DECLARE pgsql_fdw_cursor_3 SCROLL CURSOR FOR SELECT aid, bid, abalance, NULL FROM public.pgbench_accounts WHERE ((aid OPERATOR(pg_catalog.=) 1))
(3 rows)
リモートユーザのパスワードなどの接続情報が変更された場合は、該当するオプションを定義したオブジェクトに対してALTER文を実行して、オプションの値を設定します。以下の例ではリモートユーザのパスワードを変更しています。CREATE文と異なり、既存オプションの変更にはSET指定が必要になるので注意してください。
postgres=# ALTER USER MAPPING FOR postgres SERVER remote_db postgres-# OPTIONS (SET password 'more secret'); ALTER USER MAPPING postgres=#
pgsql_fdwは、ある外部サーバ上の外部テーブルを最初に検索するときに接続を確立します。いったん確立された接続は、ローカルのセッションが続いている間はできる限り再利用されます。また、一つのクエリ中で同一外部サーバ上の複数の外部テーブルを使用していた場合も、接続を再利用します。このため、ある外部サーバへの接続は、ローカルユーザを切り替えない限り一つだけです。現在接続している外部サーバは、pgsql_fdw_connectionsビューで確認できます。
postgres=# select * from pgsql_fdw_connections; srvid | srvname | usesysid | usename -------+----------------+----------+---------- 16530 | remote_pgbench | 10 | postgres (1 row)
任意のタイミングで接続を切断したい場合は、接続先の外部サーバのOIDとローカルユーザのOIDを指定して pgsql_fdw_disconnect() を実行してください。pgsql_fdw_connectionsビューは外部サーバとローカルユーザのOIDを返すので、下記のクエリを実行すると全ての接続を切断できます。
postgres=# SELECT pgsql_fdw_disconnect(srvid, usesysid) FROM pgsql_fdw_connections; pgsql_fdw_disconnect ---------------------- OK OK (2 rows)
なお、ローカルのトランザクションがアボートすると、pgsql_fdwは全ての外部サーバへの接続を切断します。これは、エラーなどによる接続リークを防ぐための対処です。その後、同じ外部サーバ上の外部テーブルを検索すると、再び自動的に接続が確立されます。
pgsql_fdwでは、リモートデータベースに対して検索を実行するのに必要な情報を、関連するオブジェクトの FDW オプションから取得します。
libpq のオプションについては、「データベース接続制御関数」を参照してください。なお、接続オプションを省略した場合、ローカルのPostgreSQLサーバを起動したユーザの環境設定に従いますのでご注意ください。また、無効なオプションを指定した場合はオプション設定時にエラーが発生しますが、必須項目が省略されていたり設定した値が誤っていた場合は、検索実行時にエラーが発生するので注意してください。
外部テーブルの FDW オプションとして nspname や relname オプションを設定すると、外部テーブルのオブジェクト名(それぞれスキーマ名とテーブル名)を指定できます。省略時にはローカルの外部テーブルと同じ名称が使われます。
fetch_count オプションを指定することで、pgsql_fdw が内部的に使用するカーソルで一回にフェッチする行数を指定できます。デフォルトは10,000行です。外部テーブルと外部サーバのどちらにも指定できますが、両方指定した場合は外部テーブルの設定が使われます。
pgsql_fdwを使用する際には、以下の使用上の注意と制約があります。
より高度な利用方法や、内部構成について説明します。
pgsql_fdwでは、リモートデータベースに接続する際にclient_encodingとしてローカルのデータベースエンコーディングを使用します。これにより、コンバージョンが定義されていれば、エンコーディングの異なるデータベースからでもデータを取得することができます。また、内部でfallback_application_nameを「pgsql_fdw」に設定します。これにより、リモートデータベースのpg_stat_activityやサーバログで接続元がpgsql_fdwであることを確認できます。
pgsql_fdwでは、バリデータ関数として pgsql_fdw_validatorを、ハンドラ関数としてpgsql_fdw_handler関数をそれぞれ定義します。これらはCREATE EXTENSION文で自動的に作成されます。