【PostgreSQL】postgres_fdwを使ってみよう #PostgreSQL

fdwについて

fdwとはForeign Data Wrapperの略
外部にあるデータにアクセスできるようにするための拡張機能
他にもOracleに対して同様のことができるoracle_fdwなどもある。

postgres_fdwの使用手順

① CREATE EXTENSION
postgres_fdwをインストールする。

CREATE EXTENSION postgres_fdw;

インストールの確認
installed_versionにバージョン番号があればインストール完了

SELECT * FROM pg_available_extensions WHERE name IN ('postgres_fdw');
     name     | default_version | installed_version |                      comment                       
--------------+-----------------+-------------------+----------------------------------------------------
 postgres_fdw | 1.0             | 1.0               | foreign-data wrapper for remote PostgreSQL servers
(1 row)

② CREATE SERVER
接続する外部サーバーへの接続情報と利用するfdwを定義

CREATE SERVER [サーバー名] FOREIGN DATA WRAPPER postgres_fdw;

③ CREATE USER MAPPING
リモートテーブルには、権限を持つユーザー名でアクセスする。
MAPPINGで外部サーバーとローカルのユーザーを紐づける。

CREATE USER MAPPING FOR [ユーザー名] SERVER [サーバー名]

④ CREATE FOREING TABLE または IMPORT FOREIGN SCHEMA
外部テーブルを定義する。
リモートテーブルと同じ定義をしておく

CREATE FOREING TABLEの場合

CREATE FOREIGN TABLE table_name (
  id                     BIGINT,
  name                   VARCHAR(40)  NOT NULL,
  ~~~
) SERVER server_name
OPTIONS (table_name 'remote_tbl_name');

注意点

外部の統計情報が使われない

デフォルトでは外部テーブルの統計情報を利用しないため、もし利用したい場合はuse_remote_estimateをtrueにしておくことでpostgres_fdwがリモートのEXPLAINコマンドを発行します。

外部の拡張機能が使われない

外部テーブルを参照する際に、デフォルトでは外部テーブルで利用されている拡張機能が使用されない。
利用する場合は以下のようにextensionsの指定をする。

CREATE SERVER [サーバー名] FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
  ~~~,
    extensions '利用したい拡張機能名'
);

参考

PosgreSQLのFDWを使ってデータベース間で透過的にクエリする
 外部データとの連携～FDWで様々なデータソースとつなぐ～