ORACLE 迁移到 MogDB 之存储过程改造：@dblink

在 ORACLE 迁移到 MogDB 过程中遇到 dblink的情况，在视图和存储过程中经常有调用访问远程 dbloink的表，比如 select * from tt@dblink。
在迁移到 MogDB 的过程遇到的问题记录如下：
最近 MogDB 官方出了循序渐进丨MogDB 5.0 远程访问 MogDB/Oracle 数据库的简便方法(使用@符号)：https://mp.weixin.qq.com/s/9rUuWyRbJMDYLKGrFtdUSg
正好匹配项目，来实验一把。发现没有那么丝滑，有几个问题需要注意：
1、访问远程表的时候，不跟文章里说的直接访问即可。需要添加 schema. 的前缀，原因为：默认为 pg_catalog ，解决方案：临时修改视图和存储过程中的访问远程表添加 schema.
2、权限问题，MogDB 用普通用户会报 ERROR: permission denied for relation (null) ，解决方案：临时给予 sysadmin迁移，并提bug。

3、使用 gs_ssh 可能报会报不支持的 os，可以不用此工具，直接修改参数即可。

4、ERROR: permission denied for foreign-data wrapper postgres_fdw 需要给予权限

f001=> create server testDBLink_mogdb1 foreign data wrapper postgres_fdw options(host '10.0.0.196', port '26000',dbname 'testdb');
ERROR:  permission denied for foreign-data wrapper postgres_fdw
DETAIL:  N/A
f001=>
f001=> \c f001 omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "f001" as user "omm".
f001=# GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO u_f001;
GRANT
f001=# \c f001 u_f001
Password for user u_f001:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "f001" as user "u_f001".
f001=> create server testDBLink_mogdb1 foreign data wrapper postgres_fdw options(host '10.0.0.196', port '26000',dbname 'testdb');
CREATE SERVER
f001=>

00准备工作

在远端数据库创建用于数据库远程连接的用户，并创建数据库和表：

=== 远端数据库 ==
create database testdb;
\c testdb
create user testdblink password 'Enmotech@123';
grant all on database testdb to testdblink ;
create table testdblink.testdata as select generate_series(1,100) as id ;

同时，假定已配置合适的pg_hba.conf，允许下面步骤的数据库进行远程连接。可以通过以下进行测试是否可以远程连接。

1	gsql -h10.0.0.196 -p26000 -Utestdblink

输入密码可以正常登录，说明允许远程连接。

01安装和创建对应插件(create extension)

postgres_fdw插件默认包含在安装包中，因此无需专门安装，只需要创建出来即可：

1 2	===本地数据库=== create extension postgres_fdw;

f001=# \c f001 u_f001
Password for user u_f001:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "f001" as user "u_f001".
f001=> \conninfo
You are connected to database "f001" as user "u_f001" via socket in "/opt/mogdb/tmp" at port "26000".
f001=> create extension postgres_fdw;
ERROR:  permission denied to create extension "postgres_fdw"
HINT:  Must be system admin to create this extension.
f001=> \c f001 omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "f001" as user "omm".
f001=# create extension postgres_fdw;
CREATE EXTENSION

注意：Must be system admin to create this extension.

02创建远程数据库链接(create server)，指定对端数据库的物理信息

1 2	===本地数据库=== create server testDBLink_mogdb1 foreign data wrapper postgres_fdw options(host '10.0.0.196', port '26000',dbname 'testdb');

其中的ip、port、dbname可以根据实际情况修改。

03创建用户映射关系(create user mapping)，指定对端数据库的认证信息

注意，在做这一步之前，如果之前没有创建过针对usermapping的密码保护，则需要执行以下语句

1 2	===本地数据库的操作系统用户下=== gs_ssh -c "gs_guc generate -o usermapping -S default -D $GAUSSHOME/bin"

如果之前已经执行过同样操作，则可以略过。可以通过如下检测是否执行过。

[omm@orarmdb01 bin]$ ls -l $GAUSSHOME/bin/user*
-rw------- 1 omm dbgrp 56 Jun 14 16:02 usermapping.key.cipher
-rw------- 1 omm dbgrp 24 Jun 14 16:02 usermapping.key.rand
[omm@orarmdb01 bin]$

如果存在 usermapping.key.cipher/usermapping.key.rand 说明执行过。

在执行的时候，可能会提示 gs_ssh 不支持的操作系统，或者 python 报错，可以不用 gs_ssh，直接通过 gs_guc generate -o usermapping -S default -D $GAUSSHOME/bin 即可。

创建用户映射关系：

1	create user mapping for public server testDBLink_mogdb1 options(user 'testdblink',password 'Enmotech@123');

这里的user ‘testdblink’,password ‘Enmotech@123’对应远端数据库用户名密码。同时，为了简化，直接用了public，其实也可以改为特定的本地用户(for username)，改了之后只有指定用户才能用这个用户密码连接到远程数据库。类似于 oracle 私有dblink。

04开始使用

1	select * from testdata@testDBLink_mogdb1;

注意：

f001=> select * from testdata@testDBLink_mogdb1;
ERROR:  remote table "testdata" does not exist
f001=> select * from testdblink.testdata@testDBLink_mogdb1;
ERROR:  permission denied for relation (null)
DETAIL:  N/A
f001=> \c f001 omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "f001" as user "omm".
f001=# select * from testdblink.testdata@testDBLink_mogdb1;
 id
-----
   1
   2
   3

在这里不能直接通过上面的语句访问，会报错：ERROR: remote table “testdata” does not exist

但是可以通过

1	select * from testdblink.testdata@testDBLink_mogdb1;

这是由于默认的在postgres_fdw开启的远程会话中，search_path参数只被设置为pg_catalog 参考文章英文

https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-REMOTE-QUERY-EXECUTION-ENVIRONMENT

中文：http://www.postgres.cn/docs/9.6/postgres-fdw.html

由于是在源码中写的固定的，所以手动修改也是无效的：

1	ALTER DATABASE testdb SET search_path TO 'testdblink,public';

或者

liups=>  set search_path to testdblink;
SET
liups=> show search_path;
 search_path
-------------
 testdblink
(1 row)

liups=> select * from testdata@testDBLink_mogdb1;
ERROR:  remote table "testdata" does not exist

这样修改也是无效的。

附：操作记录

f001=> GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO u_f001;
ERROR:  permission denied for foreign-data wrapper postgres_fdw
DETAIL:  N/A
f001=> \c f001 omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "f001" as user "omm".
f001=# GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO u_f001;
GRANT
f001=# \c f001 u_f001
Password for user u_f001:
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "f001" as user "u_f001".
f001=> create server testDBLink_mogdb1 foreign data wrapper postgres_fdw options(host '10.0.0.196', port '26000',dbname 'testdb');
CREATE SERVER
f001=> create user mapping for public server testDBLink_mogdb1 options(user 'testdblink',password 'Enmotech@123');
CREATE USER MAPPING
f001=> select * from testdata@testDBLink_mogdb1;
ERROR:  remote table "testdata" does not exist
f001=> select * from testdblink.testdata@testDBLink_mogdb1;
ERROR:  permission denied for relation (null)
DETAIL:  N/A
f001=> \c f001 omm
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "f001" as user "omm".
f001=# select * from testdblink.testdata@testDBLink_mogdb1;
 id
-----
   1
   2
   3

1	select count(1) from testdblink.testdata@testDBLink_mogdb1;

原文作者: Hi.MogDB

原文链接: https://hi.mogdb.org/posts/c24e8365/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议