【PostgreSQL】postgres_fdw を使って同インスタンス上の別 DB を参照してみた

Postgres で別の DB にあるテーブルと JOIN したくなったので、方法はないのか調べてみました。

簡単にまとめると下記の 2 通りの方法があるようです。

1. dblink
2. postgres_fdw

従来からある方法の dblink は毎回認証が必要になるようですが、 postgres_fdw なら一回設定してしまえば、あとは同 DB のテーブルのように扱うことができそうです。

そこで、今回は postgres_fdw を使って別 DB 上のテーブルを参照できるようにしてみました。

■ Docker コンテナとしてお試し環境を実行できるようにしてあるリポジトリはこちら:
→ utahka/try-postgres_fdw

問題設定

それでは、本記事で扱う問題をまとめておきます。今回は CS DB にある customers というテーブルと、App DB にある users テーブルを JOIN するケースを考えてみます。

※ 上記の 2 つのテーブルは、各 DB の public スキーマに格納されているものとします。

Docker イメージを作る

本記事では、公式の PostgreSQL イメージを利用して postgres_fdw のお試し環境を作っていきます。

スクリプトを /docker-entrypoint-initdb.d に配置しておき、コンテナ起動時に設定が完了して環境構築が完了するような姿を目指します。

FROM postgres:16.1

COPY ./scripts /docker-entrypoint-initdb.d/

ENV POSTGRES_USER root
ENV POSTGRES_PASSWORD postgres

EXPOSE 5432

① ロールやデータベースの作成

まずは、ロールやデータベースを作成します。問題設定でも触れた通り cs と app DB を作成します。ロールはデータベース名を同じものをそれぞれ作成しておきます。

これは ./scripts/01_init.sql というファイル名で保存しておきます。

-- ロール作成
CREATE ROLE app WITH LOGIN PASSWORD 'secretapp';
CREATE ROLE cs WITH LOGIN PASSWORD 'secretcs';

-- データベース作成
CREATE DATABASE cs;
CREATE DATABASE app;

-- 権限付与
\c cs
GRANT ALL PRIVILEGES ON SCHEMA public TO cs;

\c app
GRANT ALL PRIVILEGES ON SCHEMA public TO app;

どちらの DB にも専用のロールを作成していますが、最終的には app ロールから cs DB 内のテーブルを参照できるようにしていきます。

② テーブルの作成

次に、① で作成した DB に customers テーブルと users テーブルを作成します。

項目はよくある最小限な形にしてみています。customer_id と user_id は一致しないけど、email でなら JOIN できそう...みたいなケースを想定しています。

\c cs

CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP WITH TIME ZONE DEFAULT NULL
);

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "public" TO cs;

\c app

CREATE TABLE users (
    user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP WITH TIME ZONE DEFAULT NULL
);

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "public" TO app;

このクエリは ./scripts/02_create_tables.sql というファイル名で保存しました。

③ データ作成

最後に、テーブルまで作れたのでデータを適当に作成しておきます。こちらも ./scripts/03_insert_records.sql として保存しておきます。

\c cs

INSERT INTO customers (customer_name, email)
VALUES ('Martin Garrix', '[e-mail]'),
       ('Nicky Romero', '[e-mail]'),
       ('Alan Walker', '[e-mail]'),
       ('David Guetta', '[e-mail]'),
       ('Afrojack', '[e-mail]');

\c app

INSERT INTO users (user_name, email)
VALUES ('Steve Aoki', '[e-mail]')
       ('Nicky Romero', '[e-mail]'),
       ('Alan Walker', '[e-mail]'),
       ('Oliver Heldens', '[e-mail]'),
       ('Afrojack', '[e-mail]');

④ コンテナ実行

ここまでできたら、一旦ビルドしてコンテナとして起動してみましょう。

docker build -t try-postgres_fdw --platform linux/amd64 .

実行は docker-compose.yml で管理すると楽です。

version: '3'
services:
  postgres:
    image: try-postgres_fdw
    platform: linux/amd64
    container_name: fdw
    ports:
      - 5432:5432
    volumes:
      - ./volumes/postgres:/var/lib/postgresql/data

docker compose up -d

データの確認

$ docker exec -it fdw psql -U root -W
Password:
psql (16.1 (Debian 16.1-1.pgdg120+1))
Type "help" for help.

root=# \c cs
Password:
You are now connected to database "cs" as user "root".
cs=# select * from customers;
 customer_id | customer_name |        email         |          created_at           |          updated_at           | deleted_at
-------------+---------------+----------------------+-------------------------------+-------------------------------+------------
           1 | Martin Garrix | [e-mail]   | 2024-02-12 04:43:03.245996+00 | 2024-02-12 04:43:03.245996+00 |
           2 | Nicky Romero  | [e-mail]    | 2024-02-12 04:43:03.245996+00 | 2024-02-12 04:43:03.245996+00 |
           3 | Alan Walker   | [e-mail]     | 2024-02-12 04:43:03.245996+00 | 2024-02-12 04:43:03.245996+00 |
           4 | David Guetta  | [e-mail]    | 2024-02-12 04:43:03.245996+00 | 2024-02-12 04:43:03.245996+00 |
           5 | Afrojack      | [e-mail] | 2024-02-12 04:43:03.245996+00 | 2024-02-12 04:43:03.245996+00 |
(5 rows)

cs=# \c app
Password:
You are now connected to database "app" as user "root".
app=# select * from users;
 user_id |   user_name    |        email         |          created_at           |          updated_at           | deleted_at
---------+----------------+----------------------+-------------------------------+-------------------------------+------------
       1 | Steve Aoki     | [e-mail]    | 2024-02-12 04:43:03.297445+00 | 2024-02-12 04:43:03.297445+00 |
       2 | Nicky Romero   | [e-mail]    | 2024-02-12 04:43:03.297445+00 | 2024-02-12 04:43:03.297445+00 |
       3 | Alan Walker    | [e-mail]     | 2024-02-12 04:43:03.297445+00 | 2024-02-12 04:43:03.297445+00 |
       4 | Oliver Heldens | [e-mail]   | 2024-02-12 04:43:03.297445+00 | 2024-02-12 04:43:03.297445+00 |
       5 | Afrojack       | [e-mail] | 2024-02-12 04:43:03.297445+00 | 2024-02-12 04:43:03.297445+00 |
(5 rows)

別DBのテーブルとJOINしてみる

下地はできたので、本題の別DBのテーブル参照に移っていきます。

postgres_fdw 設定

まずは、postgres_fdw の設定をしていきます。先の Docker イメージをカスタマイズする要領です。(./scripts に設定を追加していく)

下記に示すクエリのコメントを読んでいけば、なんとなくどういうことをするのかは掴めるかなと思います。大まかな流れはこんな具合です。

1. postgres_fdw のインストール (初回だけでOK)
2. 外部 DB を FOREIGN SERVER として定義する
3. 外部 DB の接続情報を USER MAPPING として定義する
4. 外部 DB から所望のテーブルをインポートする

一方、Docker 環境では localhost に対しては trust 認証方式になっているので注意してください。要するに、同じコンテナ内から接続するときはパスワードが不要になっているということです。

ですので、USER MAPPING がパスワード認証のつもりで設定されているとコケます。筆者はここで詰まったのでお気をつけください。(本番環境などではパスワード認証が必要でしょう)

\c app

-- postgres_fdw をインストール
CREATE EXTENSION postgres_fdw WITH SCHEMA public;

-- fdw の権限を app ロールに付与
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO app;

-- cs_dblink サーバーを作成
CREATE SERVER cs_dblink
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (dbname 'cs');

-- cs_dblink サーバーの権限を app ロールに付与
GRANT USAGE ON FOREIGN SERVER cs_dblink TO app;

-- USER MAPPING を作成 (Docker 環境では、password_required=false でないとコケる)
CREATE USER MAPPING
  FOR app SERVER cs_dblink
  OPTIONS (user 'cs', password_required 'false');

-- cs まわりの最低限の権限を app に付与
\c cs
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT ON customers TO app;

-- cs.public から customers をインポート
\c app app
IMPORT FOREIGN SCHEMA public
  LIMIT TO (customers)
  FROM SERVER cs_dblink INTO public;

これまで通り、04_fdw_setteings.sql として保存しイメージをビルドし直しましょう。

さっそく JOIN

ここまでできたら、customers テーブルと users テーブルの JOIN ができるようになっているはず！

まずは app ユーザーとして app DB にログインします。

docker exec -it fdw psql -U app -W

\dE コマンドで外部テーブルの一覧が見れます。お、参照できそうですね。

app=> \dt
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | users | table | root
(1 row)

app=> \dE
             List of relations
 Schema |   Name    |     Type      | Owner
--------+-----------+---------------+-------
 public | customers | foreign table | app
(1 row)

それでは JOIN してみます。

app=> SELECT user_id, user_name, customer_id, customer_name, u.email  FROM users u JOIN customers c ON u.email = c.email;
 user_id |  user_name   | customer_id | customer_name |        email
---------+--------------+-------------+---------------+----------------------
       2 | Nicky Romero |           2 | Nicky Romero  | [e-mail]
       3 | Alan Walker  |           3 | Alan Walker   | [e-mail]
       5 | Afrojack     |           5 | Afrojack      | [e-mail]
(3 rows)

まるで、同じ DB 内のテーブルのように JOIN することができました。

まとめ

この記事では、postgres_fdw を利用して、同インスタンス上の別DB内にあるテーブルを参照する方法を説明しました。以上の内容は、GitHub リポジトリとして公開していますので適宜参照ください。

utahka/try-postgres_fdw

また、間違いや改善点などあれば、ぜひご教示いただけますと幸いです！