my opinion is my own

PostgreSQLのdblink拡張機能の実行と注意点

postgres_fdwではなくdblinkの話。環境はAurora PostgreSQLで実行

dblink https://www.postgresql.jp/document/13/html/contrib-dblink-function.html

dblinkの実行

dblinkのインストール

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

postgres=> create extension dblink;
CREATE EXTENSION
postgres=> \dx
                                 List of installed extensions
  Name   | Version |   Schema   |                         Description                          
---------+---------+------------+--------------------------------------------------------------
 dblink  | 1.2     | public     | connect to other PostgreSQL databases from within a database
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=>

事前準備：データベースとテーブル作成

test1データベースのt1テーブルをdblinkで別データベースから取得する。そのためのデータベースとテーブル作成。

create database test1;
\c test1;
create table t1(a numeric primary key, b varchar(30));
insert into t1 values(1,'this data is at test1 database');

方法1:コネクションを生成して接続する方法

コネクションの生成

postgres=> select dblink_connect('dblink-test1','dbname=test1 user=postgres password=postgres');
 dblink_connect 
----------------
 OK
(1 row)

検索

検索時にはリモートテーブル側のデータ型まで記載する必要がある

postgres=> select * from dblink('dblink-test1','select a,b from t1') as t1(a numeric, b varchar(30)) ;
 a |               b                
---+--------------------------------
 1 | this data is at test1 database
(1 row)

データ型がないと下記の通り、怒られる

postgres=> select * from dblink('dblink-test1','select a,b from t1');
ERROR:  a column definition list is required for functions returning "record"
LINE 1: select * from dblink('dblink-test1','select a,b from t1');

切断

postgres=> select dblink_disconnect('dblink-test1');
 dblink_disconnect 
-------------------
 OK
(1 row)

切断後は当然エラーとなる

postgres=> select * from dblink('dblink-test1','select a,b from t1') as t1(a numeric, b varchar(30)) ;
ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the connection string.
postgres=>

方法2:コネクションを生成せずに接続する方法

postgres=> select * from dblink('dbname=test1 user=postgres password=postgres','select a,b from t1') as t1(a numeric, b varchar(30)) ;
 a |               b                
---+--------------------------------
 1 | this data is at test1 database
(1 row)

注意点

まとめ

データベースリンク機能であれば後継的扱いのpostgres_fdwを使った方が良い。

https://www.postgresql.jp/document/13/html/postgres-fdw.html

実質上、本モジュールの提供する機能は以前のdblinkモジュールが提供する機能と重複していますが、postgres_fdwはリモートのテーブルにアクセスするためにより透過的で標準に準拠した構文を利用できるほか、多くの場合においてより良い性能を得る事ができます。

参考

dblink https://www.postgresql.jp/document/13/html/contrib-dblink-function.html

dblink | Let’s POSTGRES https://lets.postgresql.jp/documents/technical/contrib/dblink

PostgreSQL9.3 新機能を検証してみた Vol.2 | アシスト https://www.ashisuto.co.jp/corporate/column/technical-column/detail/1198469_2274.html

 ---

関連しているかもしれない記事


#PostgreSQL