PostgreSQL

PostgreSQLでDBリンク(dblink)を利用する

はじめに

PostgreSQLにてDBリンクを実現するには2つのやり方があり、何れも拡張モジュールを導入する事になるのですが今回は「dblinkモジュール」を利用します。OracleのDBリンクはDDL文で(create database link)事前にデータベースリンクの定義をして利用しますが、「dblinkモジュール」ではOracleのDDL文に相当するものは存在しません。事前にdblink_connect()ファンクションを使い、接続識別子の定義とセッションを確立しその後発行するSQLではこの接続識別子を利用する事でリモートDBへのアクセスができます。当然ですが元のセッション(dblink_connect()ファンクションを発行したセッション)が閉じられるとこちらも切れますので、新規セッション作成のたびにこの dblink_connect() ファンクションで接続を確立する必要があります。これが少し不便なところですが、DBリンクを実現するもう一つの拡張モジュールpostgres_fdwではこの問題が解消できます。それなら、postgres_fdwを使えばいいじゃないかと考えそうなところですが、dblinkモジュールでないと実現できない事(補足1)もありますので記事にしてみました。
(補足1)Oracleには自律型トランザクション (PRAGMA AUTONOMOUS_TRANSACTION)といって、メインのトランザクションとは別に独自のトランザクションを作成する機能が存在しますが、PostgreSQLにはこの機能がありません。これを「DBリンク越しの更新」で代替えしようというものです。

extentionとしてdblinkを導入する

oracleと異なり、postgresqlの場合デフォルトで利用できるようにはなっていません。拡張モジュールという扱いなので別途追加が必要です。以下は現在の拡張モジュールの追加状況を確認しています。

myposdb=> \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)


早速追加を試みるも、、
myposdb=> create extension dblink;
ERROR:  could not open extension control file "/usr/pgsql-13/share/extension/dblink.control": No such file or directory
myposdb=>
※contribモジュールが存在しない事に気づく
\dxの代わりにSQLで確認する場合は以下
SELECT 
    e.extname AS 拡張機能名,
    e.extversion AS バージョン,
    n.nspname AS スキーマ,
    obj_description(e.oid) AS 説明
FROM 
    pg_extension e
JOIN 
    pg_namespace n ON e.extnamespace = n.oid;

contribのインストール

まずは当方の環境を確認します。以下の通りです。

# cat /etc/redhat-release
CentOS Linux release 8.5.2111
# psql --version
psql (PostgreSQL) 13.5
素のpostgresqlをインストールしただけだったのでこの通りです。
# ls -l /usr/pgsql-13/share/extension/
合計 8
-rw-r--r--. 1 root root 658 11月 11 15:36 plpgsql--1.0.sql
-rw-r--r--. 1 root root 193 11月 11 15:36 plpgsql.control
dnfコマンドでインストールを試みるも、、
# dnf install postgresql13-contrib
CentOS Linux 8 - AppStream                                                                           74  B/s |  38  B     00:00
エラー: repo 'appstream' のメタデータのダウンロードに失敗しました : Cannot prepare internal mirrorlist: No URLs in mirrorlist

# ls -l /etc/yum.repos.d/CentOS-Linux-*
-rw-r--r--. 1 root root  719  9月 15  2021 /etc/yum.repos.d/CentOS-Linux-AppStream.repo
-rw-r--r--. 1 root root  704  9月 15  2021 /etc/yum.repos.d/CentOS-Linux-BaseOS.repo
-rw-r--r--. 1 root root 1130  9月 15  2021 /etc/yum.repos.d/CentOS-Linux-ContinuousRelease.repo
-rw-r--r--. 1 root root  318  9月 15  2021 /etc/yum.repos.d/CentOS-Linux-Debuginfo.repo
-rw-r--r--. 1 root root  732  9月 15  2021 /etc/yum.repos.d/CentOS-Linux-Devel.repo
-rw-r--r--. 1 root root  704  9月 15  2021 /etc/yum.repos.d/CentOS-Linux-Extras.repo
-rw-r--r--. 1 root root  719  9月 15  2021 /etc/yum.repos.d/CentOS-Linux-FastTrack.repo
-rw-r--r--. 1 root root  740  9月 15  2021 /etc/yum.repos.d/CentOS-Linux-HighAvailability.repo
-rw-r--r--. 1 root root  693  9月 15  2021 /etc/yum.repos.d/CentOS-Linux-Media.repo
-rw-r--r--. 1 root root  706  9月 15  2021 /etc/yum.repos.d/CentOS-Linux-Plus.repo
-rw-r--r--. 1 root root  724  9月 15  2021 /etc/yum.repos.d/CentOS-Linux-PowerTools.repo
-rw-r--r--. 1 root root 1124  9月 15  2021 /etc/yum.repos.d/CentOS-Linux-Sources.repo
※CentOS Linux 8のサポート終了でリポジトリを更新しないといけない模様、、
以下の通りURLを変更
sed -i 's/mirrorlist/#mirrorlist/g' /etc/yum.repos.d/CentOS-Linux-*
sed -i 's|#baseurl=http://mirror.centos.org|baseurl=http://vault.centos.org|g' /etc/yum.repos.d/CentOS-Linux-*
今度は成功
# dnf install postgresql13-contrib
CentOS Linux 8 - AppStream                                                                           13 MB/s | 8.4 MB     00:00
CentOS Linux 8 - BaseOS                                                                             7.2 MB/s | 4.6 MB     00:00
CentOS Linux 8 - Extras                                                                              71 kB/s |  10 kB     00:00
PostgreSQL common RPMs for RHEL/CentOS 8 - x86_64                                                    72  B/s | 195  B     00:02
PostgreSQL common RPMs for RHEL/CentOS 8 - x86_64                                                   133 kB/s | 620 kB     00:04
PostgreSQL 14 for RHEL/CentOS 8 - x86_64                                                             91  B/s | 195  B     00:02
---途中省略
  検証             : postgresql13-libs-13.6-1PGDG.rhel8.x86_64                                                                  4/7
  検証             : postgresql13-libs-13.5-1PGDG.rhel8.x86_64                                                                  5/7
  検証             : postgresql13-server-13.6-1PGDG.rhel8.x86_64                                                                6/7
  検証             : postgresql13-server-13.5-1PGDG.rhel8.x86_64                                                                7/7

アップグレード済み:
  postgresql13-13.6-1PGDG.rhel8.x86_64   postgresql13-libs-13.6-1PGDG.rhel8.x86_64   postgresql13-server-13.6-1PGDG.rhel8.x86_64
インストール済み:
  postgresql13-contrib-13.6-1PGDG.rhel8.x86_64

完了しました!
[root@vm022 ~]#
この通り沢山ダウンロードしてきました
# ls -l /usr/pgsql-13/share/extension/ | wc
    188    1685   12404
これで再度
create extensionを実行する

myposdb=# create extension dblink;
CREATE EXTENSION

今度は成功しました。
dxで拡張一覧を確認する
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=#
pg_extension を参照しても確認できます。

myposdb=# select e.extname,n.nspname from pg_extension e inner join pg_namespace n on e.extnamespace = n.oid
myposdb-# ;
 extname |  nspname
---------+------------
 plpgsql | pg_catalog
 dblink  | public
(2 行)

myposdb=#

動作を確認する

前準備

リモート先用のデータベースを作成する
myposdb=# create database prdb owner = sooni TABLESPACE = tbspc_01;
CREATE DATABASE
myposdb=# \l
                                        データベース一覧
   名前    |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |     アクセス権限
-----------+----------+------------------+----------+-------------------+-----------------------
 myposdb   | sooni    | UTF8             | C        | C                 | =Tc/sooni            +
           |          |                  |          |                   | sooni=CTc/sooni
 postgres  | postgres | UTF8             | C        | C                 |
 prdb      | sooni    | UTF8             | C        | C                 |
 template0 | postgres | UTF8             | C        | C                 | =c/postgres          +
           |          |                  |          |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | C        | C                 | =c/postgres          +
           |          |                  |          |                   | postgres=CTc/postgres
(5 行)

myposdb=#
作成したデータベースへログインしテスト用のテーブル作成
# psql -h 192.168.3.22 -p 5432 -U sooni -d prdb
ユーザ sooni のパスワード:
psql (13.6)
"help"でヘルプを表示します。

prdb=> create table kudamono( lno int,kana varchar(30),price int);
CREATE TABLE
prdb=> insert into kudamono(lno,kana,price) values (1,'リンゴ',120);
INSERT 0 1

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

prdb=>

DBリンクを使ってリモートデータベースのテーブルへアクセスする

都度リモートDBへ接続する使い方

# psql -h 192.168.3.22 -p 5432 -U sooni -d myposdb
ユーザ sooni のパスワード:
psql (13.6)
"help"でヘルプを表示します。

myposdb=>
myposdb=> SELECT * FROM dblink('dbname=prdb user=sooni password=soopass','select lno,kana,price from kudamono') AS (lno int,kana varchar,price int);
 lno |  kana  | price
-----+--------+-------
   1 | リンゴ |   120
(1 行)

myposdb=>

dblink_connect()ファンクションで事前にセッションを確立しておく使い方

myposdb=> select dblink_connect('PRLINK','host=192.168.3.22 dbname=prdb user=sooni password=soopass');
 dblink_connect
----------------
 OK
(1 行)

myposdb=> select * from dblink('PRLINK','select lno,kana,price from kudamono') AS (lno int,kana varchar,price int);
 lno |  kana  | price
-----+--------+-------
   1 | リンゴ |   120
(1 行)

myposdb=>
接続したコネクションを明示的にクローズする
myposdb=> select dblink_disconnect('PRLINK');
 dblink_disconnect
-------------------
 OK
(1 行)

myposdb=> select * from dblink('PRLINK','select lno,kana,price from kudamono') AS t(lno int,kana varchar,price int);
ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the connection string.
myposdb=>
当然エラーになりますよね

VIEWにして利用すれば便利です

ローカルDBへ接続する都度 dblink_connect ()でリモートDBとの接続を確立する事は避けられませんがVIEWを使う事でSQLも随分すっきりします。

myposdb=> select dblink_connect('PRLINK','host=192.168.3.22 dbname=prdb user=sooni password=soopass');
 dblink_connect
----------------
 OK
(1 行)

myposdb=> create or replace view prdb_kudamono as
myposdb-> select * from dblink('PRLINK','select lno,kana,price from kudamono') AS t(lno int,kana varchar,price int);
CREATE VIEW
myposdb=> select * from prdb_kudamono;
 lno |  kana  | price
-----+--------+-------
   1 | リンゴ |   120
(1 行)

myposdb=>
注意点として既にリモートDBと接続が確立しているところに(接続識別子が同じ)再度接続を試みるとエラーになります。
myposdb=> select dblink_connect('PRLINK','host=192.168.3.22 dbname=prdb user=sooni password=soopass');
ERROR:  duplicate connection name
myposdb=>
スポンサーリンク