PostgreSQLで外部DBに接続する（postgres_fdw）

はじめに

PostgreSQLでは、postgres_fdw を使うことで外部のDBにアクセスできます。
外部のDBはPostgreSQLだけでなく、MySQLやOracleなど違うDBMSでも可能です。
この記事では、同じローカルPostgreSQL内で、違うDBを参照してみます。

ちなみに、外部DBへの接続にはdblinkを使う方法もあります。
postgres_fdw はdblinkの後継機能となっており、postgres_fdw の方が記述し易く、かつ性能もよいです。

環境

• OS：Windows10
• PostgreSQL：10.8
• MySQL：8.0.19

DB作成～データ登録

まず、PostgreSQLで2つのDBを作成し、それぞれテーブル作成とデータ登録も行います。

test_local

CREATE DATABASE test_local;

\c test_local;

CREATE TABLE PERSON(
    name varchar(20),
    age integer
);

INSERT INTO PERSON VALUES ('佐藤一郎', 30);

test_remote

CREATE DATABASE test_remote;

\c test_remote;

CREATE TABLE FRUIT(
    name varchar(20),
    price integer
);

INSERT INTO FRUIT VALUES ('林檎', 150);

postgres_fdw のセットアップ

以下の流れでセットアップします。

\c test_local;

-- 1. 拡張機能のインストール。初回のみ
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- 2. 接続先の外部サーバオブジェクトを作成。ここではユーザとパスワードは設定しない
CREATE SERVER remote_server 
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'localhost', port '5432', dbname 'test_remote');

-- 3. 外部サーバにアクセスするユーザとパスワードを指定
CREATE USER MAPPING
    FOR postgres SERVER remote_server
    OPTIONS (USER 'postgres', PASSWORD 'admin');

-- 4. 外部テーブル定義の設定
CREATE FOREIGN TABLE FRUIT(
    name varchar(20),
    price integer
) SERVER remote_server;

select 実行

これで、test_local から test_remote の FRUIT テーブルが参照できます。

\c test_local

-- test_remoteのテーブルを参照。外部DBを意識せずに書ける
 select * from Fruit;

IMPORT FOREIGN SCHEMA でテーブル定義をインポート

以上で参照は可能ですが、CREATE FOREIGN TABLE でテーブル定義を書くのが面倒です。
Postgres9.5 からは IMPORT FOREIGN SCHEMA でテーブル定義を丸ごとインポートできるので、そちらを使いましょう。
スキーマ指定ですべてのテーブルをインポートしますが、LIMIT TO で対象のテーブル指定、EXCEPT で除外するテーブル指定が可能です。

 IMPORT FOREIGN SCHEMA public
   LIMIT TO (FRUIT) -- 対象のテーブルだけインポート
   FROM SERVER remote_server INTO public;

外部テーブル定義の削除

外部テーブル定義を削除するときは、DROP FOREIGN TABLE を使えばよいです。
外部DBにあるテーブル自体は削除されません。テーブル自体を削除する場合は、外部DBにアクセスする必要があります。

test_local

DROP FOREIGN TABLE FRUIT;

外部テーブル定義をすべて削除する

スキーマを指定してテーブル定義をインポートすることは出来ますが、スキーマ指定で削除する構文はありません。
一つ一つDROPするか、PL / pgSQLを使いましょう。

-- すべてのスキーマの外部テーブル定義をDROP
do
$$
declare
  l_rec record;
begin
  for l_rec in (select foreign_table_schema, foreign_table_name 
                from information_schema.foreign_tables) loop
     execute format('drop foreign table %I.%I', l_rec.foreign_table_schema, l_rec.foreign_table_name);
  end loop;
end;
$$;

（参考：https://stackoverflow.com/questions/40728788/drop-foreign-schema-in-postgresql-using-a-foreign-data-wrapper）