はじめに
PostgreSQLにてDBリンクを実現するには２つのやり方があり、何れも拡張モジュールを導入する事になるのですが今回は「dblinkモジュール」を利用します。OracleのDBリンクはDDL文で（create database link）事前にデータベースリンクの定義をして利用しますが、「dblinkモジュール」ではOracleのDDL文に相当するものは存在しません。事前にdblink_connect()ファンクションを使い、接続識別子の定義とセッションを確立しその後発行するSQLではこの接続識別子を利用する事でリモートDBへのアクセスができます。当然ですが元のセッション（dblink_connect()ファンクションを発行したセッション）が閉じられるとこちらも切れますので、新規セッション作成のたびにこの dblink_connect() ファンクションで接続を確立する必要があります。これが少し不便なところですが、DBリンクを実現するもう一つの拡張モジュールpostgres_fdwではこの問題が解消できます。それなら、postgres_fdwを使えばいいじゃないかと考えそうなところですが、dblinkモジュールでないと実現できない事（補足１）もありますので記事にしてみました。
（補足１）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.controldnfコマンドでインストールを試みるも、、
# 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=>
