MogDB 2.1的dblink功能是怎么样的

MogDB 2.1 的企业级功能增强其中包括对于dblink功能的支持；使用Oracle的朋友都知道，传统Oracle数据库中，跨库访问和操作通过dblink是十分方便的，

虽然也存在一定安全风险，总的来讲，在过去相当长一段时间内，其作用是无法替代的。这里来看看如何使用。

--安装插件
[omm@mogdb script]$ ./gs_install_plugin_local -X /opt/mogdb_soft_2.1/script/enmo.xml --all
SUCCESS: dblink.
SUCCESS: pg_trgm.
SUCCESS: pg_repack.
SUCCESS: wal2json.
SUCCESS: orafce.
SUCCESS: pg_bulkload.
SUCCESS: pg_prewarm.
[omm@mogdb script]$  

--创建
[omm@mogdb ~]$ gsql -d enmotech -p26000 -Uroger
Password for user roger: 
gsql ((MogDB 2.1.0 build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

enmotech=> create extension dblink;
CREATE EXTENSION
enmotech=>    
enmotech=> \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 functionality
(9 rows)


---新初始化一个库
[omm@mogdb bin]$ gs_initdb -D /opt/mogdb/data/db2  --nodename=mogdb
The files belonging to this database system will be owned by user "omm".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

creating directory /opt/mogdb/data/db2 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
Begin init undo subsystem meta.
[INIT UNDO] Init undo subsystem meta successfully.
creating template1 database in /opt/mogdb/data/db2/base/1 ... The core dump path from /proc/sys/kernel/core_pattern is an invalid directory:|/usr/libexec/
2022-02-15 11:49:17.894 [unknown] [unknown] localhost 140591212725824 0[0:0#0]  [BACKEND] WARNING:  macAddr is 12/692538944, sysidentifier is 796999/1312817746, randomNum is 2237530706
ok
initializing pg_authid ... ok
setting password ... ok
initializing dependencies ... ok
loading PL/pgSQL server-side language ... ok
creating system views ... ok
creating performance views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
initialize global configure for bucketmap length ... ok
creating information schema ... ok
loading foreign-data wrapper for distfs access ... ok
loading foreign-data wrapper for hdfs access ... ok
loading foreign-data wrapper for log access ... ok
loading hstore extension ... ok
loading foreign-data wrapper for MOT access ... ok
loading security plugin ... ok
update system tables ... ok
creating snapshots catalog ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
freezing database template0 ... ok
freezing database template1 ... ok
freezing database postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run gs_initdb.

Success. You can now start the database server of single node using:

    mogdb -D /opt/mogdb/data/db2 --single_node
or
    gs_ctl start -D /opt/mogdb/data/db2 -Z single_node -l logfile
复制

接下来测试一下dblink；新初始化的库先创建好库和相关用户：

++++目标库
[omm@mogdb db2]$ gsql -d enmotech -p54321 -Uroger -WRoger123
gsql ((MogDB 2.1.0 build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

enmotech=> \dt
                        List of relations
 Schema | Name | Type  | Owner |             Storage              
--------+------+-------+-------+----------------------------------
 public | test | table | roger | {orientation=row,compression=no}
(1 row)

enmotech=>  
enmotech=> 
enmotech=> select * from test;
  a  |        b         
-----+------------------
 100 | www.enmotech.com
(1 row)

enmotech=>        


+++源库

enmotech=>  select dblink_connect('dblink1','hostaddr=192.168.108.10 port=54321 dbname=enmotech user=roger password=Roger123');
 dblink_connect 
----------------
 OK
(1 row)

enmotech=>    select dblink_exec('dblink1', 'select * from test');
ERROR:  statement returning results not allowed
CONTEXT:  referenced column: dblink_exec
enmotech=> 
enmotech=>  select dblink_exec('dblink1', 'update test set a=1000');         
 dblink_exec 
-------------
 UPDATE 1
(1 row)



+++目标库
enmotech=> select * from test;
  a   |        b         
------+------------------
 1000 | www.enmotech.com
(1 row)

enmotech=> 
复制

可以看到MogDB的dblink功能还是非常不错的，可以通过DBLINK进行select和DML操作，类似Oracle dblink功能。使用上看上去相对要复杂一点点。