はじめに
FDWは外部のデータへアクセスするためのPostgreSQLの拡張機能です。
例えば、外部のデーターベース（Oracle, PostgreSQL, Redis）等に対して接続し、SQLを実行することができます。
環境
今回はPostgreSQL（local_postgresサーバ）からPostgreSQL(source_postgresサーバ)へアクセスできるように設定します。
local_postgresサーバに接続し、source_postgresサーバのテーブルに対してSQLを実行できるようになります。
- CentOS 7.5
- PostgreSQL 12
local_postgresサーバ
- ホスト： 192.168.10.76
- データベース: testdb
- ユーザ： testuser
- スキーマ： public
source_postgresサーバ
- ホスト： 192.168.10.56(source_postgres)
- データベース: sourcedb
- ユーザ： sourceuser
- スキーマ： public
構築
以降の作業は全てlocal_postgresサーバで実行します。
まず、PostgreSQL用のFDWモジュールであるpostgres_fdwをインストールします。
postgres_fdwのモジュール自体はPostgreSQLに同梱されているため、create extensionを実行するだけです。
$ psql -U postgres testdb
# create extension postgres_fdw;
CREATE EXTENSION
次に、外部サーバを定義します。
外部サーバは、名前がforeign_serverで、source_postgresサーバのデータベース（sourcedb）に対して定義しています。
# create server foreign_server
foreign data wrapper postgres_fdw
options (host 'source_postgres', port '5432', dbname 'sourcedb');
次に外部サーバで使用するロールを指定するためにユーザマッピングを定義します。
外部サーバ（foreign_server）のユーザ（sourceuser）をローカルのユーザ（testuser）にマッピングしています。
# create user mapping for testuser
server foreign_server
options (user 'sourceuser', password 'sourceuser');
最後に外部サーバのテーブル定義を作成します。
1テーブルずつテーブル定義を作成するのは大変なので、ここでは外部サーバのテーブル定義をインポートします。
まず、権限を付与。
--grant all on foreign data wrapper postgres_fdw to testuser;
grant all on foreign server foreign_server to testuser;
--1行目は不要だった（メモ）
以下で外部サーバのpublicスキーマのsource_tbl01テーブルをローカルのpublicスキーマへインポートしています。
import foreign schema public limit to (source_tbl01)
from server foreign_server into public;
外部テーブルは以下で確認できます。Typeが"foreign table"となっていることが確認できます。
testdb=> \d
List of relations
Schema | Name | Type | Owner
--------+------------------+---------------+----------
～～
public | source_tbl01 | foreign table | testuser
(6 rows)
これで以下のように外部サーバへ対してSQLを実行できるようになりました。
testdb=> select * from source_tbl01 limit 1;
id | val
----+-----
1 | aa
(1 row)
外部サーバの統計情報について
use_remote_estimateは外部サーバの統計情報を使用するかどうか設定します。
"true"であれば外部サーバの統計情報を取得し実行計画を作成します。
"false"の場合、ローカルの統計情報を使用します。これは外部サーバのテーブルの統計情報が更新されても自動で更新されないため、統計情報が最新となっているかが利用者が担保する必要があります。デフォルトは"false"です。
デフォルトではローカルの統計情報を利用するため、以下のように統計情報を取得します。Autoanalyzeで更新されることはありません（たぶん）。
testdb=> analyze source_tbl01;
ANALYZE
以下のように取得した統計情報を確認できます。
testdb=> select relname, relkind, reltuples, relpages from pg_class where relname = 'source_tbl01';
relname | relkind | reltuples | relpages
--------------+---------+-----------+----------
source_tbl01 | f | 300051 | 1593
(1 row)
use_remote_estimateの設定を変更するためには以下を実行します。
testdb=# alter server foreign_server options (use_remote_estimate 'true');
# 設定を戻す場合
testdb=# alter server foreign_server options (drop use_remote_estimate);
トランザクション管理
F.33. postgres_fdw(PostgreSQL 11.5文書)を確認すると以下の記述がありました。
PostgreSQLではデフォルトでREAD UNCOMMITTEDレベルであり、トランザクションについてローカルのテーブルと違うことを意識して利用する必要があるようです。
ローカルトランザクションがSERIALIZABLE隔離レベルを用いている時、リモートトランザクションもSERIALIZABLE隔離レベルを使用します。 それ以外の場合にはREPEATABLE READ隔離レベルを使用します。