今回は PostgreSQL の dblink & postgres_fdw です。
PostgreSQL には異なるデータベース間で通信を行うデータベースリンクという機能が備わっています。
自分で調べながらやったところ、仕組みは難しくないのですが、解説ブログ等が少なく理解するまでに苦労しました。
そのため、ここで解説しておきたいと思います。
DBLINK は2種類ありました
私は、そもそも PostgreSQL の DBLINK は1種類しかないと思いこんでいました。
それが理解を大きく遅らせた原因です。人間の思い込みとは恐ろしいものです。
現時点（2020年6月）においては、PostgreSQL の DBLINK の手法は2つ存在します。それが dblink と postgres_fdw です。
dblink と postgres_fdw
ざっくり説明すると、昔からあったのが dblink で PostgreSQL9.3（2014年頃） に追加されたのが postgres_fdw です。
どちらもデータベース間の通信を行う機能を提供するのですが、dblink はその都度 SQL を実行するのが必要なのに対して、postgres_fdw は1回で大丈夫です。
内部の仕組みとしては同じで、libpq というライブラリがバックエンドプロセスとして、データベース間の通信を行っています。
事前準備
以下の構成で検証したいと思います。
ロール・データベース・スキーマ作成
では、ロール・データベース・スキーマを作っていきます。
-- postgre で login
-- create user
postgres=> create role user01 LOGIN PASSWORD 'user01';
postgres=> create role user02 LOGIN PASSWORD 'user02';
postgres=> create role dblink LOGIN PASSWORD 'dblink';
-- create database testdb01
postgres=> create database testdb01 ENCODING='UTF8' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE template0;
-- grant connect
postgres=> \c testdb01
testdb01=> grant connect on database testdb01 to user01;
testdb01=> revoke all on database testdb01 from PUBLIC; -- PUBLIC = All users
-- create shema
testdb01=> create schema schema01;
testdb01=> grant all on schema schema01 to user01;
-- create database testdb02
postgres=> create database testdb02 ENCODING='UTF8' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE template0;
-- grant connect
postgres=> \c testdb02
testdb02=> grant connect on database testdb02 to user02;
testdb02=> grant connect on database testdb02 to dblink;
testdb02=> revoke all on database testdb02 from PUBLIC; -- PUBLIC = All users
-- create shema
testdb02=> create schema schema02;
testdb02=> grant all on schema schema02 to user02;
testdb02=> grant usage on schema schema02 to dblink;
DB：testdb01 の中に スキーマ：schema01 があります。 DB：testdb02 の中に スキーマ：schema02 があります。
user01 は schema01 へのフルアクセス権を有し、user02 は schema02 へのフルアクセス権を有します。
dblink は schema02 への USAGE を有します。
作成されたか確認
見てみましょう。
testdb02=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
testdb01 | postgres | UTF8 | C | C | postgres=CTc/postgres+
| | | | | user01=c/postgres
testdb02 | postgres | UTF8 | C | C | postgres=CTc/postgres+
| | | | | user02=c/postgres +
| | | | | dblink=c/postgres
testdb02=> \dn+
List of schemas
Name | Owner | Access privileges | Description
----------+----------+----------------------+------------------------
public | rdsadmin | rdsadmin=UC/rdsadmin+| standard public schema
| | =UC/rdsadmin |
schema02 | postgres | postgres=UC/postgres+|
| | user02=UC/postgres +|
| | dblink=U/postgres |
大丈夫そうですね。
schema02 にテーブル作成
それでは、schema02 にテーブル（test02）を作成しておきましょう。dblink の参照権限も付与しておきます。
-- user02 で login
testdb02=> create table schema02.test02 (id int,first_name VARCHAR(20),last_name VARCHAR(25));
testdb02=> insert into schema02.test02 values(1,'atsushi','koizumi');
testdb02=> insert into schema02.test02 values(2,'ryu','nakata');
testdb02=> insert into schema02.test02 values(3,'shinichi','watanabe');
testdb02=> select * from schema02.test02;
id | first_name | last_name
----+------------+-----------
1 | atsushi | koizumi
2 | ryu | nakata
3 | shinichi | watanabe
(3 rows)
testdb02=> grant select on schema02.test02 to dblink;
GRANT
dblink で接続しなおして、select 出来るか確認しておきます。
testdb02=> \c
psql (9.2.24, server 11.6)
....
You are now connected to database "testdb02" as user "dblink".
testdb02=> select * from schema02.test02;
id | first_name | last_name
----+------------+-----------
1 | atsushi | koizumi
2 | ryu | nakata
3 | shinichi | watanabe
(3 rows)
データベース間の接続情報を設定
まずは、モジュール postgres_fdw をインストールしましょう。ここからは、postgre での操作です。
postgres=> \c testdb01
You are now connected to database "testdb01" as user "postgres".
testdb01=> \dew
List of foreign-data wrappers
Name | Owner | Handler | Validator
------+-------+---------+-----------
(0 rows)
testdb01=> create EXTENSION postgres_fdw with SCHEMA schema01;
CREATE EXTENSION
testdb01=> \dew
List of foreign-data wrappers
Name | Owner | Handler | Validator
--------------+---------------+----------------------+------------------------
postgres_fdw | rds_superuser | postgres_fdw_handler | postgres_fdw_validator
(1 row)
データベース間の接続情報を定義します。
wrapper server user
wrapper は接続先データベース（server）の接続情報とユーザー（user）のユーザー名・パスワードを保持するオブジェクトです。
絵に書いてみましたが、あんまり出来が良くないですねｗ
要するに、接続先のデータベースの接続情報・ユーザーを紐づけて、オブジェクトにまとめておくことで、後から簡単に呼び出せるようにしておくための設定、ということです。
user01 に対して testdb02 の dblink をマッピング
以下のように定義します。
testdb01=> create SERVER testdb02_dblink FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'koizumi-postgresql-01.xxx.eu-west-1.rds.amazonaws.com', dbname 'testdb02', port '5432');
CREATE SERVER
testdb01=> \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
-----------------+----------+----------------------+-------------------+------+---------+---------------------------------------------------------------------------------------------------------+-------------
testdb02_dblink | postgres | postgres_fdw | | | | (host 'koizumi-postgresql-01.xxx.eu-west-1.rds.amazonaws.com', dbname 'testdb02', port '5432') |
(1 row)
testdb01=> create USER MAPPING FOR user01 SERVER testdb02_dblink OPTIONS (user 'dblink',password 'xxxxxxxxxx');
CREATE USER MAPPING
testdb01=> \deu+
List of user mappings
Server | User name | FDW Options
-----------------+-----------+-----------------------------------------
testdb02_dblink | user01 | ("user" 'dblink', password 'xxxxxxxxxx')
(1 row)
これにより、user01 で testdb01 にログイン中、 testdb02_dblink を呼び出せば、『 testdb02 に対して dblink で接続する』ということが実行されるようになります。
FOREIGN TABLE
私はこの FOREIGN TABLE がイケてるなと思いました。
接続先のデータベースのテーブルを自分のスキーマに持ってこれます。（表現が正確ではありませんが、だいたいそんな感じです。）
testdb01=> create FOREIGN TABLE schema01.schema02_test02 (id int,first_name VARCHAR(20),last_name VARCHAR(25))
testdb01-> SERVER testdb02_dblink OPTIONS (schema_name 'schema02',table_name 'test02');
CREATE FOREIGN TABLE
testdb01=> grant select on schema01.schema02_test02 to user01;
GRANT
testdb01=> select * from schema01.schema02_test02;
ERROR: user mapping not found for "postgres"
ここまでが、postgres の操作ですが、上記のマッピングの設定は、user01 に対して行っているので、postgres ではこの FOREIGN TABLE は参照できません。
user01 で FOREIGN TABLE を参照
testdb01=> \c testdb01
You are now connected to database "testdb01" as user "user01".
testdb01=> select * from schema01.schema02_test02;
id | first_name | last_name
----+------------+-----------
1 | atsushi | koizumi
2 | ryu | nakata
3 | shinichi | watanabe
(3 rows)
では、同時に user02 で schema02 に接続してデータを追加して反映されるか確認してみます。
testdb02=> \c
You are now connected to database "testdb02" as user "user02".
testdb02=> insert into schema02.test02 values(4,'emiri','moromizato');
INSERT 0 1
user01 側で select します。
testdb01=> \c
You are now connected to database "testdb01" as user "user01".
testdb01=> select * from schema01.schema02_test02;
id | first_name | last_name
----+------------+------------
1 | atsushi | koizumi
2 | ryu | nakata
3 | shinichi | watanabe
4 | emiri | moromizato
(4 rows)
すごいですね！データ移行の代替手段として考慮してもいいかもしれません。
同じインスタンス内でのリンクですのでオーバーヘッドも少ないと思います。
今回は FOREIGN TABLE を検証しましたが、これのスキーマ版（IMPORT FOREIGN SCHEMA）もあります！これはまた別の機会に。
てか、スキーマごとリンクできるとか本当に面倒見が良いですね。
ここまでが、postgres_fdw の動作確認でした。
ここから DBLINK
DBLINK は軽く説明したいと思います。
まずは、postgre でモジュールをインストールします。
postgres=> \c testdb01
You are now connected to database "testdb01" as user "postgres".
testdb01=> create EXTENSION dblink with SCHEMA schema01;
CREATE EXTENSION
こちらに使い方が書かれていますが、特に設定をしなくても DBLINK できちゃいます。
testdb01=> select dblink_connect('testdb02', 'host=koizumi-postgresql-01.cmgvm1pyvu2h.eu-west-1.rds.amazonaws.com dbname=testdb02 port=5432 user=user02 password=xxxxxxxxxx');
dblink_connect
----------------
OK
(1 row)
testdb01=> select * from dblink('testdb02','select * from schema02.test02;') as t(id int,first_name text,last_name VARCHAR(25));
id | first_name | last_name
----+------------+------------
1 | atsushi | koizumi
2 | ryu | nakata
3 | shinichi | watanabe
2 | memiri | moromizato
(4 rows)
testdb01=> select dblink_disconnect('testdb02');
dblink_disconnect
-------------------
OK
(1 row)
では、user01 に切り替えて先ほど登録した server 情報を使って DBLINK をやってみます。
testdb01=> \c
You are now connected to database "testdb01" as user "user01".
testdb01=> select dblink_connect('testdb02','testdb02_dblink');
ERROR: permission denied for foreign server testdb02_dblink
permission denied
Error が出てしまいました。原因は permission denied なので権限不足ですね。
postres に切り替えて testdb02_dblink の権限を確認します。
postgres=> \c testdb01
You are now connected to database "testdb01" as user "postgres".
testdb01=> \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
-----------------+----------+----------------------+-------------------+------+---------+---------------------------------------------------------------------------------------------------------+-------------
testdb02_dblink | postgres | postgres_fdw | | | | (host 'koizumi-postgresql-01.cmgvm1pyvu2h.eu-west-1.rds.amazonaws.com', dbname 'testdb02', port '5432') |
(1 row)
testdb01=> grant USAGE ON FOREIGN SERVER testdb02_dblink to user01;
GRANT
testdb01=> \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description
-----------------+----------+----------------------+---------------------+------+---------+---------------------------------------------------------------------------------------------------------+-------------
testdb02_dblink | postgres | postgres_fdw | postgres=U/postgres+| | | (host 'koizumi-postgresql-01.cmgvm1pyvu2h.eu-west-1.rds.amazonaws.com', dbname 'testdb02', port'5432') |
| | | user01=U/postgres | | | |
(1 row)
Access privileges に user01 が追加されていますね。これでいけるはずです。
testdb01=> \c
You are now connected to database "testdb01" as user "user01".
testdb01=> select dblink_connect('testdb02','testdb02_dblink');
dblink_connect
----------------
OK
(1 row)
testdb01=> select * from dblink('testdb02','select * from schema02.test02;') as t(id int,first_name text,last_name VARCHAR(25));
id | first_name | last_name
----+------------+------------
1 | atsushi | koizumi
2 | ryu | nakata
3 | shinichi | watanabe
2 | memiri | moromizato
(4 rows)
testdb01=> select dblink_disconnect('testdb02');
dblink_disconnect
-------------------
OK
(1 row)
成功しました。
DBLINK + VIEW
VIEW を使えば、postgres_fdw の FOREIGN TABLE みたいなことが出来るのでしょうか。
testdb01=> select dblink_connect('testdb02','testdb02_dblink');
dblink_connect
----------------
OK
(1 row)
testdb01=> create view schema01.view_schema02_test02 as select * from dblink('testdb02','select * from schema02.test02;') as t(id int,first_name text,last_name VARCHAR(25));
CREATE VIEW
testdb01=> select * from schema01.view_schema02_test02;
id | first_name | last_name
----+------------+------------
1 | atsushi | koizumi
2 | ryu | nakata
3 | shinichi | watanabe
2 | memiri | moromizato
(4 rows)
testdb01=> select dblink_disconnect('testdb02');
dblink_disconnect
-------------------
OK
(1 row)
testdb01=> select * from schema01.view_schema02_test02;
ERROR: password is required
DETAIL: Non-superusers must provide a password in the connection string.
dblink_disconnect した後は VIEW は見れなくなってしまいました。
つまり、明示的にセッションを張っていなければデータは参照できないということです。
postgres_fdw と dblink の比較
さすがに後からできた機能だけあって、postgres_fdw に分があるように思います。
実行計画も dblink の VIEW よりも postgres_fdw の FOREIGN TABLE が優れていますので、検索スピードは速いですね。
比較表
|
postgres_fdw |
dblink |
| 便利さ |
〇 |
× |
| 速さ |
〇 |
△ |
| かんさ |
△ |
〇 |
dblink は都度、セッションを張らなければいけないというのが、面倒ですね。
監査
ただ、これは盲点でしたが、dblink の監査はできます。
ここで言う監査というのは、testdb02 側で監査ログが出力されるということです。
dblink では都度、セッションを張って SQL を実行しているので、dblink ユーザーからの接続や SQL 情報が testdb02 側で監査ログで取得されています。
一方、postgres_fdw の FOREIGN TABLE は testdb02 側で全くログに出ません。
そのため、重要なデータを扱う場合は、testdb01 側でも FOREIGN TABLE を監視する必要がありますね。
最後に
実際に検証を指定て、FOREIGN TABLE を使える環境であれば、データ移行は不要ではないのか？と感じました。
これを確認するためには、ある程度の大きなテーブルを使ってベンチマークを取ってみる必要がありますね。
もし、良い結果が得られれば、複数のデータベースを扱うときに選択肢の一つとしてアリですね。
AuditPostgreSQLRDSSQL
