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 必须支持连接。
全部评论 (0)
还没有任何评论哟~