PostgreSQL dblink & postgres_fdw

  • Audit
  • PostgreSQL
  • RDS
  • SQL
  • スポンサーリンク

    今回は 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 というライブラリがバックエンドプロセスとして、データベース間の通信を行っています。

    事前準備

    以下の構成で検証したいと思います。

    title

    ロール・データベース・スキーマ作成

    では、ロール・データベース・スキーマを作っていきます。

    -- 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)のユーザー名・パスワードを保持するオブジェクトです。

    postgresql-dblink-02

    絵に書いてみましたが、あんまり出来が良くないですねw

    要するに、接続先のデータベースの接続情報・ユーザーを紐づけて、オブジェクトにまとめておくことで、後から簡単に呼び出せるようにしておくための設定、ということです。

    testdb02_dblink に testdb02 と user01 をマッピング

    以下のように定義します。

    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)

    これにより、testdb02_dblink を呼び出せば、『 testdb02 に対して user02 で接続する』ということが実行されるようになります。

    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 を使える環境であれば、データ移行は不要ではないのか?と感じました。

    これを確認するためには、ある程度の大きなテーブルを使ってベンチマークを取ってみる必要がありますね。

    もし、良い結果が得られれば、複数のデータベースを扱うときに選択肢の一つとしてアリですね。

  • Audit
  • PostgreSQL
  • RDS
  • SQL
  • スポンサーリンク