postgres_fdwで外部サーバのPostgreSQLへアクセスする

# はじめに

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を実行するだけです。

```sql
$ psql -U postgres testdb

# create extension postgres_fdw;
CREATE EXTENSION
```

次に、外部サーバを定義します。 
外部サーバは、名前がforeign_serverで、source_postgresサーバのデータベース（sourcedb）に対して定義しています。

```sql
# create server foreign_server
        foreign data wrapper postgres_fdw
        options (host 'source_postgres', port '5432', dbname 'sourcedb');
```

次に外部サーバで使用するロールを指定するためにユーザマッピングを定義します。
外部サーバ（foreign_server）のユーザ（sourceuser）をローカルのユーザ（testuser）にマッピングしています。

```sql
# create user mapping for testuser
        server foreign_server
        options (user 'sourceuser', password 'sourceuser');
```

最後に外部サーバのテーブル定義を作成します。
1テーブルずつテーブル定義を作成するのは大変なので、ここでは外部サーバのテーブル定義をインポートします。

まず、権限を付与。

```sql
--grant all on foreign data wrapper postgres_fdw to testuser;
grant all on foreign server foreign_server to testuser;
--1行目は不要だった（メモ）
```

以下で外部サーバのpublicスキーマのsource_tbl01テーブルをローカルのpublicスキーマへインポートしています。

```sql
import foreign schema public limit to (source_tbl01)
    from server foreign_server into public;
```

外部テーブルは以下で確認できます。Typeが"foreign table"となっていることが確認できます。

```sql
testdb=> \d
                  List of relations
 Schema |       Name       |     Type      |  Owner   
--------+------------------+---------------+----------
～～
 public | source_tbl01     | foreign table | testuser
(6 rows)
```

これで以下のように外部サーバへ対してSQLを実行できるようになりました。

```sql
testdb=> select * from source_tbl01 limit 1;
 id | val 
----+-----
  1 | aa
(1 row)
```


# 外部サーバの統計情報について

use_remote_estimateは外部サーバの統計情報を使用するかどうか設定します。
"true"であれば外部サーバの統計情報を取得し実行計画を作成します。
"false"の場合、ローカルの統計情報を使用します。これは外部サーバのテーブルの統計情報が更新されても自動で更新されないため、統計情報が最新となっているかが利用者が担保する必要があります。デフォルトは"false"です。


デフォルトではローカルの統計情報を利用するため、以下のように統計情報を取得します。Autoanalyzeで更新されることはありません（たぶん）。

```sql
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の設定を変更するためには以下を実行します。

```sql
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文書)](https://www.postgresql.jp/document/11/html/postgres-fdw.html)を確認すると以下の記述がありました。

PostgreSQLではデフォルトでREAD UNCOMMITTEDレベルであり、トランザクションについてローカルのテーブルと違うことを意識して利用する必要があるようです。

> ローカルトランザクションがSERIALIZABLE隔離レベルを用いている時、リモートトランザクションもSERIALIZABLE隔離レベルを使用します。 それ以外の場合にはREPEATABLE READ隔離レベルを使用します。 


# 参考

- [F.33. postgres_fdw(PostgreSQL 11.5文書)](https://www.postgresql.jp/document/11/html/postgres-fdw.html)

- [Foreign data wrappers](https://wiki.postgresql.org/wiki/Foreign_data_wrappers)

- [外部データとの連携 ～FDWで様々なデータソースとつなぐ～](https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/article-index/fdw-overview/index.html)

- [FDWを使った時の読取り異常、更新異常を見てみる](https://masahikosawada.github.io/2019/12/25/FDW_transaction/)