Mogdb 3.0中dblink插件使用

dblink是一个支持从数据库会话中连接到其他Mogdb数据库的插件,目前dblink仅支持MogDB数据库访问另一个MogDB数据库，不支持MogDB数据库访问PostgreSQL数据库。

1.dblink插件安装

(1)下载插件包

这里的插件包的位置可以任意位置
[omm@node1 static]$ wget https://cdn-mogdb.enmotech.com/mogdb-media/3.0.1/Plugins-3.0.1-CentOS-x86_64.tar.gz
--2022-08-31 20:20:14--  https://cdn-mogdb.enmotech.com/mogdb-media/3.0.1/Plugins-3.0.1-CentOS-x86_64.tar.gz
Resolving cdn-mogdb.enmotech.com (cdn-mogdb.enmotech.com)... 124.236.20.228
Connecting to cdn-mogdb.enmotech.com (cdn-mogdb.enmotech.com)|124.236.20.228|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 74443447 (71M) [application/gzip]
Saving to: ‘Plugins-3.0.1-CentOS-x86_64.tar.gz’
100%[============================================================================================================================================>] 74,443,447  9.12MB/s   in 5.9s

(2)进行安装

--无编译安装，进入插件包中dblink的文件中将其中的文件拷贝到如下目录
[omm@node1 dblink]$ cp dblink.so  $GAUSSHOME/lib/postgresql
[omm@node1 dblink]$ cp dblink.control $GAUSSHOME/share/postgresql/extension
[omm@node1 dblink]$ cp dblink--1.0.sql $GAUSSHOME/share/postgresql/extension
[omm@node1 dblink]$ cp dblink--unpackaged--1.0.sql $GAUSSHOME/share/postgresql/extension
[omm@node1 dblink]$ gsql -d postgres -p26000 -r
gsql ((MogDB 3.0.1 build 1a363ea9) compiled at 2022-08-05 17:31:04 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
MogDB=# create extension dblink ;
CREATE EXTENSION
MogDB=# \dx
                                                      List of installed extensions
      Name       | Version |   Schema   |                                          Description
-----------------+---------+------------+-----------------------------------------------------------------------------------------------
 dblink          | 1.0     | public     | connect to other PostgreSQL databases from within a database
 dist_fdw        | 1.0     | pg_catalog | foreign-data wrapper for distfs access
 file_fdw        | 1.0     | pg_catalog | foreign-data wrapper for flat file access
 hdfs_fdw        | 1.0     | pg_catalog | foreign-data wrapper for flat file access
 hstore          | 1.1     | pg_catalog | data type for storing sets of (key, value) pairs
 log_fdw         | 1.0     | pg_catalog | Foreign Data Wrapper for accessing logging data
 mot_fdw         | 1.0     | pg_catalog | foreign-data wrapper for MOT access
 plpgsql         | 1.0     | pg_catalog | PL/pgSQL procedural language
 security_plugin | 1.0     | pg_catalog | provides security functionalit

(3)dblink插件使用

1.创建测试用户和测试库
MogDB=# create database test_db;
CREATE DATABASE
MogDB=# create user test_usr password 'test@123';
CREATE ROLE
[omm@node1 dblink]$ gsql -d test_db -p26000 -r  -U test_usr -W test@123
test_db=> create table a(id int);
CREATE TABLE
test_db=> insert into a values(1);
INSERT 0 1
test_db=> insert into a values(2);
INSERT 0 1
2.创建远端库连接
MogDB=# select dblink_connect('conn','hostaddr=127.0.0.1 port=26000 dbname=test_db user=test_usr password=test@123');
 dblink_connect
----------------
 OK
(1 row)
2.进行远端库表查询
MogDB=# SELECT * FROM dblink('dbname=test_db hostaddr=127.0.0.1 port=26000  user=test_usr password=test@123', 'select * from a')t(id int);
 id
----
  1
  2
(2 rows)
3.在远端库执行表操作
MogDB=# select dblink_exec('conn', 'create table b(id int, name int)');
 dblink_exec
--------------
 CREATE TABLE
(1 row)
MogDB=# select dblink_exec('conn', 'insert into b values(2,1)');
 dblink_exec
-------------
 INSERT 0 1
(1 row)
MogDB=# select dblink_exec('conn', 'update b set name=2 where id=1');
 dblink_exec
-------------
 UPDATE 0
(1 row)
MogDB=# select dblink_exec('conn', 'delete from b where id=1');
 dblink_exec
-------------
 DELETE 0
(1 row)
4.解除连接
select dblink_disconnect('dconn')
MogDB=# select dblink_disconnect('conn');
 dblink_disconnect
-------------------
 OK
(1 row)