MogDB3.0中使用dblink插件跨库访问

原创巧克力加糖 2023-09-26

280

适用范围

MogDB 3.0.4 使用dblink跨库访问

方案概述

某企业上线了多套MogDB集群,分别承担mes,wms,pms等生产业务系统,这些系统间经常发生跨库访问;因原mes,wms,pms等生产系统,早年使用oracle数据库,客户希望在MogDB数据库间,也能使用dblink实现跨库访问,本次采用MogDB的db_link插件实现跨库访问。

实施步骤

本文以mes系统使用dblink插件访问pms为例

1.分别创建新用户和新库

create user pms with sysadmin password 'Enmo@123';
create database pms with encoding 'UTF-8' owner pms;

2.使用新建用户远程连接到pms库，创建测试表和数据

gsql -U pms -W 'Enmo@123' -h 192.XXX.XXX.81 -d pms -r

create table pms_test1(a smallint,b char(20) default 'pms');
insert into pms_test1 values(1);
insert into pms_test1 values(2);
insert into pms_test1 values(3);
select * from pms_test1;
create table pms_test2 as select * from pms_test1;
insert into pms_test2 select * from pms_test1;
select * from pms_test2;

3.创建dblink访问用户及授权可访问的对象

create user for_mes_dblink with password 'Enmo@123';
grant all on pms_test1 to for_mes_dblink;

4.使用新建用户远程连接到mes库，安装dblink插件

5.创建dblink连接,测试跨库查询和执行DML

注：跨库操作create、insert、update、delete的DML语句，其实这种行为一般不跨库操作。慎用！，以下仅为演示

在MogDB中dblink是会话级别,dblink_connect只在当前会话中可连接
select dblink_connect('link_to_pms', 'host=192..XXX.XXX.81 port=26000 user=for_mes_dblink password=Enmo@123 dbname=pms');
通过dblink跨库查询
select a,b from dblink('link_to_pms', 'select a,b from public.pms_test1') as pms_test1(a smallint,b char(20) );
通过dblink跨库执行DDL
select dblink_exec('link_to_pms', 'insert into public.pms_test1 values (100,''insert into from mes'')');
关闭dblink连接
在Mogdb中dblink是会话级别；会话断开即dblink也关闭.
SELECT dblink_disconnect('link_to_pms');

在pms中源库,没有将表pms_test2对for_mes_dblink用户授权，所以在link_to_pms的db_link不可访问,可实现按需授权跨库访问

在当前会话手动关闭dblink后,则不再允许跨库访问

6.dblink跨库查询扩展

使用dblink跨库查询,都要带有一串conn_str；非常不简洁；在会话中可使用临时表/视图来保存。两种效果不同,到底选择视图/临时表,取决于需求；
使用临时表在会话结束后是不会保持的,不再使用的话,无需删除对应的临时表,同时临时表一但创建后,就是一张独立的数据表,与源表数据不再同步.

select dblink_connect('link_to_pms', 'host=192.XXX.XXX.81 port=26000 user=for_mes_dblink password=Enmo@123 dbname=pms');
create temp table pms_test1_temp as select a,b from dblink('link_to_pms', 'select a,b from public.pms_test1') as pms_test1(a smallint,b char(20) );
select a,b from pms_test1_temp;
create view pms_test1_view as select a,b from dblink('link_to_pms', 'select a,b from public.pms_test1') as pms_test1(a smallint,b char(20) );
select a,b from pms_test1_view;
select dblink_exec('link_to_pms', 'insert into public.pms_test1 values (200,''insert into from mes'')');