MoggDB 也支持Oracle DBlink功能了
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: MoggDB 也支持Oracle DBlink功能了
MogDB 的最新版本2.1中，新增了很多企业级特性，其中就有类似Oracle dblink一样的功能，这里进行一些测试验证。
首先需要去官网下载插件然后并进行安装加载。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
--安装插件 [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) |
源环境准备完毕后，我们还需要创建一个新库，作为目标段来进行测试验证dblink 功能。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
---新初始化一个库 [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 |
目标端新库创建用户和测试表等步骤这里省略。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
++++目标库 [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=> |
接下来我们在源库创建dblink指向我们新初始化的库。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
+++源库 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', 'update test set a=1000'); dblink_exec ------------- UPDATE 1 (1 row) |
这里我们可以直接进行跨dblink 进行dml操作。进步验证一下数据是否更新成功。
|
1 2 3 4 5 6 7 8 |
+++目标库 enmotech=> select * from test; a | b ------+------------------ 1000 | www.enmotech.com (1 row) enmotech=> |
可以看到MogDB的dblink功能还是非常不错的，可以通过DBLINK进行select和DML操作，类似Oracle dblink功能。
这对开发人员来说十分优化，极大的简化了开发代价，十分方便。
Leave a Reply
You must be logged in to post a comment.