PostgreSQL
v13

PostgreSQL postgres_fdwでDBリンクを利用する

2022.04.17

はじめに

PostgreSQLでDBリンクを実現するには２つのやり方があります。何れも拡張モジュールを導入する事になるのですが今回は postgres_fdw （ contribモジュールに含まれる ）を利用します。従来から存在する「dblinkモジュール」を使う方法より、パフォーマンス的にもこちらがお勧めです。（PostgreSQL 9.3以降に追加された機能のようです）何より従来のdblinkモジュールと大きく異なるのはリモート側テーブルへのアクセスとローカル側テーブルへのアクセスを同一トランザクションで行える事です。

今回はCentOS Linux8.5＋PostgreSQL 13.5の環境にてpostgres_fdwを導入します

今回の完成形

本来postgres_fdwは異なるクラスタのDB(別サーバのPostgreSQL)はもちろん、別DBMS(OracleやMySQL等）とも接続できますが今回は同一クラスタ内の別DBにてDBリンクを実現しています。

（注意）ローカル側（以下例ではmyposdb）で発行されたSQLがそのままリモート側のDBにて実行されます。つまり、ローカル側とリモート側のスキーマ名＋テーブル名は同じにする必要があります。
（参考情報ですがOracleではこのような制限はありません）

┌──────────┐     ┌─────────┐　
│prdb                │     │myposdb           │　
├──────────┤     ├─────────┤  sooniユーザにてmyposdbデータベースへログインし
│┌───────┐  │     │┌───────┐│  以下SQLを実行する事で、prdbデータベースのkudamonoテーブル
││ public       │  │     ││public        ││  の値を参照できできるようにします。
│├───────┤  │     │├───────┤│  select * from public.kudamono;
││              │  │     ││              ││
││┌─────┐│  │     ││┌─────┐││
│││kudamono  │<-udonman------│kudamono  ││<--sooni
││└─────┘│ログイン ││└─────┘││ ログイン
││  table       │  │     ││foreign table ││
│└───────┘  │     │└───────┘│
└──────────┘     └─────────┘

extentionモジュールを導入する

今回は何も問題なくextentionモジュールの導入はできたのですが、PostgreSQLをインストールしただけの環境などにおいては/usr/pgsql-xx/share/extension/配下に何も入っていない可能性があり、dnfコマンドを使って「contribモジュール（postgresqlxx-contrib）」をインストールする必要があります。その場合こちらの記事が参考になると思います。

現状のextentionモジュールの導入状況確認

以下はシステム権限を保有するユーザで実行します。当方環境はpostgresです。
extentionモジュールの導入はデータベース単位となります。今回のケースではmyposdbデータベースへextentionモジュールを導入する事になります。

[root@vm022 ~]# psql -h 192.168.3.22 -p 5432 -U postgres -d myposdb
ユーザ postgres のパスワード:
psql (13.6)
"help"でヘルプを表示します。
-- 導入前の確認
myposdb=# \dx
                                    インストール済みの拡張一覧
  名前   | バージョン |  スキーマ  |                             説明
---------+------------+------------+--------------------------------------------------------------
 dblink  | 1.2        | public     | connect to other PostgreSQL databases from within a database
 plpgsql | 1.0        | pg_catalog | PL/pgSQL procedural language
(2 行)

myposdb=#

extentionモジュールの導入

myposdb=# create extension postgres_fdw;
CREATE EXTENSION
-- 導入後の確認
myposdb=# \dx
                                      インストール済みの拡張一覧
     名前     | バージョン |  スキーマ  |                             説明
--------------+------------+------------+--------------------------------------------------------------
 dblink       | 1.2        | public     | connect to other PostgreSQL databases from within a database
 plpgsql      | 1.0        | pg_catalog | PL/pgSQL procedural language
 postgres_fdw | 1.0        | public     | foreign-data wrapper for remote PostgreSQL servers
(3 行)

myposdb=#

外部サーバ（リモートデータベース）サーバを定義する

ローカルサーバからリモートデータベースのテーブルをアクセスするためにローカルサーバ内に外部サーバを定義します。今回の例ではmyposdbデータベースにて実施します。

myposdb=# create server prdb_server foreign data wrapper postgres_fdw
myposdb-# options (host '192.168.3.22', port '5432', dbname 'prdb');
CREATE SERVER
myposdb=#
定義した外部サーバの確認SQL
select s.oid,s.srvname
,array_to_string(s.srvacl,CHR(10)) as srvacl
,array_to_string(array (select (pg_options_to_table(s.srvoptions))),CHR(10)) srvoptions
from pg_catalog.pg_foreign_server s
;
定義した外部サーバの確認例
myposdb=# select s.oid,s.srvname
myposdb-# ,array_to_string(s.srvacl,CHR(10)) as srvacl
myposdb-# ,array_to_string(array (select (pg_options_to_table(s.srvoptions))),CHR(10)) srvoptions
myposdb-# from pg_catalog.pg_foreign_server s
myposdb-# ;
  oid  |   srvname   |       srvacl        |     srvoptions
-------+-------------+---------------------+---------------------
 16584 | prdb_server | postgres=U/postgres+| (host,192.168.3.22)+
       |             | sooni=U/postgres    | (port,5432)        +
       |             |                     | (dbname,prdb)
(1 row)


myposdb=#

ローカルユーザとリモートデータベースのユーザをマッピングする

リモートデータベースのテーブルをアクセスする際のログインユーザと、ローカルデータベースへログインするユーザのマッピングを行います。

CREATE USER MAPPINGにてリモートサーバのユーザをマッピングする

今回は同一クラスタ内、別DB間でのＤＢリンクなので「ユーザマッピング」という作業に違和感覚えるかもしれませんが、本来は別のＲＤＢＭＳや、別クラスタDBに対するDBリンクを想定しているので当作業は必要になりますね。

create user mapping for sooni server prdb_server
options (user 'udonman', password 'udonpass');
;
ユーザのマッピング例
-- マッピング
myposdb=# create user mapping for sooni server prdb_server
myposdb-# options (user 'udonman', password 'udonpass')
myposdb-# ;
CREATE USER MAPPING
ユーザマッピング確認SQL
select pg_get_userbyid(umuser),s.srvname,array_to_string(m.umoptions,'/')
from pg_user_mapping m
inner join pg_foreign_server s
on m.umserver = s.oid
;
ユーザマッピング確認例
-- マッピングの確認
 pg_get_userbyid |   srvname   |        array_to_string
-----------------+-------------+-------------------------------
 sooni           | prdb_server | user=udonman/password=udonpass
(1 row)


myposdb=#

先日Aurora（postgreSQL）を使っていて気付いたのですが、Auroraではpostgresユーザからもpg_user_mappingへアクセスする事ができません。代わりにpg_user_mappingsを使って確認する事になります。

myposdb=# select * from pg_user_mappings;
 umid  | srvid |   srvname   | umuser | usename |            umoptions
-------+-------+-------------+--------+---------+---------------------------------
 17155 | 16584 | prdb_server |  16384 | sooni   | {user=udonman,password=udonman}
(1 行)
ユーザマッピングを削除する場合は以下
drop user mapping for sooni server prdb_server
;

外部サーバの定義を利用するユーザに対しアクセス権限を付与する

myposdb=# grant all on foreign server prdb_server to sooni;
GRANT

リモートDBのテーブルをローカルでも定義します

ここからは上記権限付与した一般ユーザ（sooni）で実行しています。

-- 通常のCREATE TABLE文と異なり最後にSERVER句が入ります
--
myposdb=> CREATE FOREIGN TABLE public.kudamono ( lno int,kana varchar(30),price int)
myposdb-> SERVER prdb_server;
CREATE FOREIGN TABLE
作成したテーブルを確認する
myposdb=> \d
                    リレーション一覧
 スキーマ |         名前         |    タイプ    | 所有者
----------+----------------------+--------------+---------
 public   | dictionary_address   | テーブル     | udonman
 public   | ex010                | テーブル     | udonman
 public   | ex03                 | テーブル     | udonman
 public   | kudamono             | 外部テーブル | sooni
 public   | ptbl01               | テーブル     | sooni
 sooni    | ex01                 | テーブル     | sooni
 sooni    | ex02                 | テーブル     | sooni
(7 行)
SQLを使ってより詳しく確認します

information_schema.foreign_tablesを参照する事でテーブル名と外部サーバ名の関係がわかります。

select
 r.rolname as owner
,n.nspname as schema
,c.relname as table_name
, case 
  when c.relkind = 'r' then 'table'
  when c.relkind = 'p' then 'partitioned table'
  when c.relkind = 'f' then 'foreign table'
  else concat(c.relkind,'')
end relkind_dec
,foreign_server_name
from pg_class c inner join pg_namespace n
on c.relnamespace = n.oid
inner join pg_roles r
on c.relowner  = r.oid
left outer join information_schema.foreign_tables f
on n.nspname = f.foreign_table_schema and relname = f.foreign_table_name
where 1=1
and n.nspname not in ( 'pg_catalog','information_schema')
and c.relkind in('r','p','f')     -- オブジェクトタイプ
order by schema,relname
;
   owner    |  schema   |      table_name      |    relkind_dec    | foreign_server_name
------------+-----------+----------------------+-------------------+---------------------
 udonman    | public    | dictionary_address   | table             |
 udonman    | public    | ex010                | table             |
 udonman    | public    | ex03                 | table             |
 sooni      | public    | kudamono             | foreign table     | prdb_server
 sooni      | public    | ptbl01               | table             |
 sooni      | sooni     | ex01                 | table             |
 sooni      | sooni     | ex02                 | table             |
(7 行)

実際にアクセスしてみる

通常のテーブルと同様に使えます

myposdb=> select * from kudamono;
 lno |  kana  | price
-----+--------+-------
   1 | リンゴ |   120
(1 行)

myposdb=> insert into kudamono(lno,kana,price) values (2,'バナナ',280);
INSERT 0 1
myposdb=> select * from kudamono;
 lno |  kana  | price
-----+--------+-------
   1 | リンゴ |   120
   2 | バナナ |   280
(2 行)

myposdb=>

セッションを確認する

一度アクセスするとセッションは残ります。基本的に毎回接続し直すdblinkと異なり2回目以降のアクセスコストが下がります。

myposdb=> select pid,state,datname,application_name,usename
myposdb-> ,to_char(backend_start,'YYYY-MM-DD HH24:MI:SS') backend_start
myposdb-> from pg_stat_activity where client_addr is not null
myposdb-> order by backend_start;
 pid  |        state        | datname |     application_name     | usename |    backend_start
------+---------------------+---------+--------------------------+---------+---------------------
 7322 | active              | myposdb | pgAdmin 4 - DB:myposdb   | sooni   | 2022-04-17 19:37:03
 7388 | active              | myposdb | psql                     | sooni   | 2022-04-17 19:42:47
 7403 | idle                | prdb    | postgres_fdw             | sooni   | 2022-04-17 19:43:50
 7440 | idle in transaction | myposdb | pgAdmin 4 - CONN:8799663 | sooni   | 2022-04-17 19:46:17
(4 行)

myposdb=>
スポンサーリンク
sooni
タイトルとURLをコピーしました