KingbaseES DBLink 介绍

DBLink 扩展插件功能与 Kingbase_FDW 类似，用于远程访问KingbaseES 数据库。相比于Kingbase_FDW，DBLink 功能更强大，可以执行DML，还可以通过 begin ... end 完成事务操作。以下介绍dblink扩展的使用。

一、测试环境

	本地数据库	远程数据库
IP	192.168.237.42	192.168.237.43
用户	user_local	user_remote

1、本地数据库创建用户

test=# create role user_local with login;

CREATE ROLE

test=# alter role user_local with password 'user_local';

ALTER ROLE

test=# create schema user_local authorization user_local;

CREATE SCHEMA

2、远程数据库创建用户与测试表

test=# create role user_remote with login;

CREATE ROLE

test=# alter role user_remote with password 'user_remote';

ALTER ROLE

test=# create schema user_remote authorization user_remote;

CREATE SCHEMA

test=# \c test user_remote

You are now connected to database "test" as user "user_remote".

test=> create table t1(id integer,name char(9));

CREATE TABLE

test=> insert into t1 values(1,'a');

INSERT 0 1

test=> insert into t1 values(2,'b');

INSERT 0 1

二、创建扩展插件

1、创建扩展

test=# create extension dblink;

CREATE EXTENSION

创建后，pg_foreign_data_wrapper 会新增一条记录。

test=# select * from pg_foreign_data_wrapper where fdwname='dblink_fdw';

  oid  |  fdwname   | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions

-------+------------+----------+------------+--------------+--------+------------

 16466 | dblink_fdw |       10 |          0 |        16465 |        |

2、创建Server

create server dblink_to43 foreign data wrapper dblink_fdw options (hostaddr '192.168.237.43' , dbname 'test');

test=# select * from pg_foreign_server where srvname='dblink_to43';

  oid  |   srvname   | srvowner | srvfdw | srvtype | srvversion | srvacl |              srvoptions

-------+-------------+----------+--------+---------+------------+--------+---------------------------------------

 24693 | dblink_to43 |       10 |  24692 |         |            |        | {hostaddr=192.168.237.43,dbname=test}

(1 row)

grant usage on foreign server dblink_to43 to user_local;

3、创建用户映射

create user mapping for user_local server dblink_to43 options (user 'user_remote' , password 'user_remote');

test=# select * from pg_user_mappings;

 umid  | srvid |   srvname   | umuser |  usename   |                umoptions

-------+-------+-------------+--------+------------+-----------------------------------------

 24694 | 24693 | dblink_to43 |  24645 | user_local | {user=user_remote,password=user_remote}

(1 row)

三、测试数据库访问

1、dblink 连接

test=> \c test user_local

You are now connected to database "test" as user "user_local".

test=> select dblink_connect('conn01','dblink_to43') ;

 dblink_connect

----------------

 OK

(1 row)

test=> select * from sys_database_link;

 oid | lnkname | lnknamespace | lnkuser | lnkowner | lnkserver | dbtype | lnkoptions | lnkcreated

-----+---------+--------------+---------+----------+-----------+--------+------------+------------

(0 rows)

test=> select * from all_db_links;

 owner | db_link | username | host | created

-------+---------+----------+------+---------

(0 rows)

以dblink_connect 方式连接，采用的是长连接，直到显示关闭，或者数据库会话结束。

Note：dblink 信息在 sys_database_link 是看不到的，sys_database_link 是配合 kdb_database_link 插件使用。

2、数据访问

test=> select * from dblink('conn01','select * from t1');

 id |   name

----+-----------

  1 | a

  2 | b

(2 rows)

test=> select dblink_exec('conn01','insert into t1 values(1,''a'')');

 dblink_exec

-------------

 INSERT 0 1

(1 row)

test=>  select * from dblink_exec('conn01','insert into t1 values(1,''a'')');

 dblink_exec

-------------

 INSERT 0 1

(1 row)

3、事务操作

test=> SELECT dblink_exec('conn01', 'BEGIN');

 dblink_exec

-------------

 BEGIN

(1 row)

test=> select dblink_exec('conn01','insert into t1 values(1,''a'')');

 dblink_exec

-------------

 INSERT 0 1

(1 row)

test=> SELECT dblink_exec('conn01', 'COMMIT');

 dblink_exec

-------------

 COMMIT

(1 row)

4、断开连接

test=> SELECT dblink_disconnect('conn01');

 dblink_disconnect

-------------------

 OK

(1 row)

四、其他注意点

以上例子的是用dblink_connect 创建连接，是长连接。也可以在执行SQL的同时创建连接：

test=> select * from dblink('dbname=test host=192.168.237.43 port=54321 user=user_remote password=user_remote','select * from t1');

还有，dblink 实际是数据库的连接，前提是目标端的 sys_hba.conf 必须支持连接。

五、DBLINK缺点

（1）每次连接时，都要使用<wiz_tmp_highlight_tag class="cm-searching">dblink_connect建立连接才能查询，对用户来说并不是透明的。

（2）每次查询一张表都要在sql中命名字段和类型，或者配置视图把sql“封装”起来。逐个封装视图，也是一件很麻烦的事。

（3）dblink在运行数据量大的sql时容易丢失远程连接，导致查询或者更新失败

巴特西