はじめに

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

検証環境

PostgreSQL 14.10

外部DBを参照するには

公式で確認した結果、特定のDB内で外部DBを確認する方法として、下記の二つが確認できました。

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

事前準備

まずはテスト環境をdockerで構築します。

ディレクトリ構成

テスト用のディレクトリは下記の構成です。

PrjDirectory
∟postgres1/
  ∟Dockerfile
∟postgres2/
  ∟Dockerfile
docker-compose.yml

Dockerfile

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

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

テスト環境の作成

ディレクトリ位置に移動してコマンドを実行すれば環境作成できます。

cd /any/path/to/PrjDirectory/
docker compose up -d

テーブルとテストデータ挿入

postgres1側にテーブルとテストデータを入れておきましょう。

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())
;

こちらで準備完了です。

postgres_fdwインストール方法

機能インストールの概要は下記の通りです。

拡張機能のインストール
外部サーバオブジェクトの作成
使用しているロールの外部サーバロールへのマッピング定義
接続したいテーブルもしくはスキーマの宣言

拡張機能のインストール

何はともあれ機能がないと始まりません。まずはpostgres2に接続します。

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

CREATE EXTENSION postgres_fdw;

外部サーバオブジェクトの作成

次にpostgres2上でpostgres1が見れるようにpostgres1オブジェクトを作成します。

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

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

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

使用しているロールの外部サーバロールへのマッピング定義

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

CREATE USER MAPPING IF NOT EXISTS FOR
  postgres2
SERVER
  fdw_test_db_1_mirror
OPTIONS
  (
    user 'postgres1',
    password 'postgres1'
  )
;

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

接続したいテーブルもしくはスキーマの宣言

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

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