[postgres_fdw]PostgreSQLでの外部データベース取り込み方法


# はじめに
PostgreSQLを使っていて、「そういえば他のDBの情報を取り込みたいけどどうするんだっけ」、という疑問を持ったので調べてみた結果を記載します。
調べた動機は、PostgreSQLで接続している任意のDB内で、別のDBへの情報を読み取り内部結合や外部結合を行いたいというものです。こういったビューを作成して、プログラミング側の処理を減らせないかと考えました。

## 検証環境
- PostgreSQL 14.10

# 外部DBを参照するには
公式で確認した結果、特定のDB内で外部DBを確認する方法として、下記の二つが確認できました。
- [dblink](https://www.postgresql.jp/document/15/html/dblink.html)
- [postgres_fdw](https://www.postgresql.jp/document/15/html/postgres-fdw.html)

しかしながら、dblinkのサイト内には、　**「postgres_fdwも参照して下さい。より新しく標準に対する互換性の高い基盤を使ってほぼ同じ機能を提供しています。」**　との記載がありました。ほぼ同じ機能を提供し、新しいということなのでpostgres_fdwを試すことにしました。


# 事前準備
まずはテスト環境をdockerで構築します。

## ディレクトリ構成
テスト用のディレクトリは下記の構成です。
```text
PrjDirectory
∟postgres1/
  ∟Dockerfile
∟postgres2/
  ∟Dockerfile
docker-compose.yml
```

### Dockerfile
```docker
FROM postgres:14.10
RUN localedef -i ja_JP -c -f UTF-8 -A /usr/share/locale/locale.alias ja_JP.UTF-8
```

### docker-compose.yml
```docker
version: '3'
services:

  postgres1:
    container_name: 'postgres1'
    build: ./postgres1/
    ports:
      - 5441:5432
    volumes:
      - ./postgres1/data:/var/lib/postgresql/data
      - ./postgres1/init:/docker-entrypoint-initdb.d
    environment:
      POSTGRES_USER: postgres1
      POSTGRES_PASSWORD: postgres1
    networks:
      fdw_test_network:
        ipv4_address: 192.168.222.11

  postgres2:
    container_name: 'postgres2'
    build: ./postgres2/
    ports:
      - 5442:5432
    volumes:
      - ./postgres2/data:/var/lib/postgresql/data
      - ./postgres2/init:/docker-entrypoint-initdb.d
    environment:
      POSTGRES_USER: postgres2
      POSTGRES_PASSWORD: postgres2
    networks:
      fdw_test_network:
        ipv4_address: 192.168.222.12

networks: 
  fdw_test_network:
    driver: bridge
    ipam:
      driver: default
      config:
       - subnet: 192.168.222.0/24
         gateway: 192.168.222.1
```

## テスト環境の作成
ディレクトリ位置に移動してコマンドを実行すれば環境作成できます。
```bash
cd /any/path/to/PrjDirectory/
docker compose up -d
```

## テーブルとテストデータ挿入
postgres1側にテーブルとテストデータを入れておきましょう。
```sql
CREATE TABLE IF NOT EXISTS
  db_table_1
  (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(10),
	create_timestamp TIMESTAMP
  )
;

INSERT INTO db_table_1
  (name, create_timestamp)
VALUES
  ('あ', now()),
  ('い', now()),
  ('う', now()),
  ('え', now()),
  ('お', now())
;
```

こちらで準備完了です。
![テスト環境構築完了](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/2f3d69cc-8011-70ff-85a9-bf7c4e31b3ba.png)



# postgres_fdwインストール方法
機能インストールの概要は下記の通りです。
1. 拡張機能のインストール
1. 外部サーバオブジェクトの作成
1. 使用しているロールの外部サーバロールへのマッピング定義
1. 接続したいテーブルもしくはスキーマの宣言


## 拡張機能のインストール
何はともあれ機能がないと始まりません。まずはpostgres2に接続します。
![拡張機能のインストール](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/9e12c0c8-0a57-4855-b866-b48cd4cf6404.png)


接続できたら、下記コマンドでインストールしましょう。

```sql
CREATE EXTENSION postgres_fdw;
```
![スクリーンショット 2024-04-30 20.28.00.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/d2cb463b-898d-1883-8125-5002fc433983.png)

https://www.postgresql.jp/document/15/html/sql-createextension.html


## 外部サーバオブジェクトの作成
次にpostgres2上でpostgres1が見れるようにpostgres1オブジェクトを作成します。

```sql
CREATE SERVER
  fdw_test_db_1_mirror
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS
  (
    host '192.168.222.11',
    dbname 'postgres1',
    port '5432'
  )
;
```

:::note warn
ここでIPアドレスの指定に注意が必要です。
postgres2からは、postgres1はdocker内のネットワークを使用して認識できます。ですので、docker内のネットワークで使用しているIPアドレスを指定する必要があります。
:::

![スクリーンショット 2024-04-30 21.17.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/6ac17691-3ff1-fda5-2c5d-90cc464833ae.png)

https://www.postgresql.jp/document/15/html/sql-createserver.html


コマンドがうまく機能すれば、postgres2内のForegin Data Wrapperに項目が増えているはずです。

![FDWへのサーバオブジェクト追加](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/94c060f2-94f7-101b-4e46-58fd1b5cd24a.png)


## 使用しているロールの外部サーバロールへのマッピング定義
外部DBを使用する上で注意が必要な部分にロールがあります。postgre2に接続しているロールは、postgres1で有効なロールではありません。たとえ同じ名前となっていても無効なロールです。これを対応付ける必要がありますので、その作業を行います。

```sql
CREATE USER MAPPING IF NOT EXISTS FOR
  postgres2
SERVER
  fdw_test_db_1_mirror
OPTIONS
  (
    user 'postgres1',
    password 'postgres1'
  )
;
```
![ユーザマッピングの作成1](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/1f5171ce-2616-11d5-e54f-4aeeab19e90a.png)

コマンド実行に成功すると、先ほど作成したDBオブジェクトの下に、ユーザマッピングが作成されます。

![ユーザマッピングの作成2](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/c1e1070e-c05f-5108-3f53-045a2f44f9a3.png)

https://www.postgresql.jp/document/15/html/sql-createusermapping.html


## 接続したいテーブルもしくはスキーマの宣言
最後にforegin data wrapperで取得したDBからテーブルまたはスキーマを呼び出します。
今回はテーブルを呼び出すこととします。
下記コマンドを実行しましょう。

```sql
CREATE FOREIGN TABLE IF NOT EXISTS 
  db_table_1
  (
    id INTEGER,
    name VARCHAR(10),
    create_timestamp TIMESTAMP
  )
SERVER
  fdw_test_db_1_mirror
;
```

![外部テーブル追加1](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/e16411eb-8383-7f1e-d761-310efa284414.png)

実行が成功すればschema内の指定したスキーマのForeign Tables内にテーブルが追加されているはずです。

![外部テーブル追加2](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/dd35b447-210e-a6b0-8a03-04e9db21f613.png)

データの中身が問題ないかも念の為確認しましょう。

![外部テーブル追加3](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/60939b4c-49a9-14bd-ee9b-28d54d544e33.png)


https://www.postgresql.jp/document/15/html/sql-createforeigntable.html



# おまけの結合テスト

## テストデータ準備

![テストデータ準備1](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/4289cf38-e774-46fa-3c6b-a0636c9eb210.png)

![テストデータ準備2](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/0620ef98-7803-9d54-7984-2036b57392d7.png)


## left join
![結合1](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/79864314-6017-bc14-923c-474ff669f499.png)


## inner join
![結合2](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/671049/7f9e1940-2e0a-9a59-fe9e-156b2ee1ba29.png)


# 参考資料
- [postgres_fdw公式](https://www.postgresql.jp/document/15/html/postgres-fdw.html)