01
SCHEMA
在瀚高数据库中，创建一个数据库，在创建的数据库中建立多个不同的SCHEMA（模式），经过权限管理后进行访问。
项目背景：
在某软件开发商现场进行Oracle数据库到瀚高数据库的迁移，软件开发商技术人员，给了Oracle的9个用户和密码，并告知需要在瀚高数据库中创建9个database进行迁移。
完成数据库迁移，进行应用适配的过程中，应用系统代码中需要进行跨库查询。采用dblink、fdw的方式，需要修改代码，不够友好，协商后，更改为在瀚高数据库的一个database中创建多个schema。对要跨模式访问的表进行赋权。在highgoql.conf文件search_path参数中，指定多个schema的名称。
上面这种情况，从代码调整方面，跨多个模式查询时，比跨多个数据库查询要简单一些。但schema中有同名表，也需要在表名前加上schema的名称。
02
DBLINK
dblinks支持在一个数据库会话中连接到其他远程瀚高数据库的模块。
使用前需先在瀚高数据库中创建扩展，如下：
创建完成之后可以在数据库中看到dblink的函数：
(1)dblink_connect 打开一个到远程数据库的持久连接。
dblink_connect(text connstr) 返回 text；
dblink_connect(text connname, text connstr) 返回 text；
connname要用于这个连接的名字，可忽略。connstr为连接信息串。
例如：
hostaddr=127.0.0.1 ；port=5866 ；dbname=mydb ；user=highgo ；password=mypasswd。
可以一次打开多个命名的连接，但是一次只允许一个未命名的连接。连接将会持续直到被关闭或数据库会话结束。
只有超级用户能够使用dblink_connect创建无口令认证连接，如：
把options=-csearch_path=增加到connstr。
非超级用户或不可信用户无法使用。
非超级用户可以使用dblink_connect_u来创建。dblink_connect_u — 不安全地打开一个到远程数据库的持久连接。
返回一个text数组，其中是所有打开的命名dblink连接的名称。
dblink_disconnect(text connname) 返回 text；
关闭一个之前被dblink_connect()打开的连接。不带参数的形式关闭一个未命名的连接。
select dblink_disconnect('myconn');
(4)dblink 在一个远程数据库中执行一个查询。
dblink(text connname, text sql) 返回记录集；
dblink(text connstr, text sql) 返回记录集；
dblink(text sql) 返回记录集；
select * from dblink('myconn','select * from mytable') as t(a int, b text, c text[]) ; —必须as成本地表。
通过建立view视图方便进行查询；
dblink配合视图view使用，如果每次写dblink是不方便的。
CREATE VIEW v_hg_test AS
select * from dblink('hostaddr=192.168.52.105 port=5866 dbname=highgo user=highgo password=123456','select name from test) AS testTable (name VARCHAR);
这样以后可以通过视图v_hg_test来查询结果，更方便。
select * from v_hg_test;
select dblink_exec('myconn','insert into mytable values(21,''z'',''{"a0","b0","c0"}'');');
dblink_exec
-------------
INSERT 0 1
(1 row)
常规使用:
select * from dblink('hostaddr=192.168.52.105 port=5432 dbname=highgo user=highgo password=123456','select name from people') AS testTable (name VARCHAR);
如果不只是查询数据，而是需要修改数据库数据如下：
先执行dblink_connect保持连接：
SELECT dblink_connect('mycoon','hostaddr=192.168.0.105 port=5432 dbname=highgo user=highgo password=123456');
执行BEGIN命令：
SELECT dblink_exec('mycoon', 'BEGIN');
执行数据操作（update，insert，create等命令）：
SELECT dblink_exec('mycoon', 'insert into tb1 select generate_series(10,20),''hello''');
SELECT dblink_exec('mycoon', 'insert into people(username,name) values ("张三","小三") ');
执行事务提交：
SELECT dblink_exec('mycoon', 'COMMIT');
解除连接：
SELECT dblink_disconnect('mycoon');
dblink_open(text cursorname, text sql [, bool fail_on_error]) 返回 text；
dblink_open(text connname, text cursorname, text sql [, bool fail_on_error]) 返回 text；
一个游标只能在一个事务中持续，如果远端还没有在一个事务中，dblink_open会开始一个显式事务块。如果使用dblink_exec在dblink_open和dblink_close之间改变数据，并且接着发生了一个错误或者在dblink_close之前使用了dblink_disconnect，将会因为事务被终止而丢失在事务中所做的更改。
SELECT dblink_open('tmpCur','select proname,prosrc from pg_proc');
dblink_open
-------------
OK
(1 row)
dblink_fetch(text cursorname, int howmany [, bool fail_on_error]) 返回record集合；
dblink_fetch(text connname, text cursorname, int howmany [, bool fail_on_error]) 返回record集合；
highgo=# SELECT * FROM dblink_fetch('tmpCur',2) as (funcname name,source text);
funcname | source
----------+---------
boolin | boolin
boolout | boolout
(2 rows)
highgo=# SELECT * FROM dblink_fetch('tmpCur',5) as (funcname name,source text);
funcname | source
----------+----------
byteain | byteain
byteaout | byteaout
charin | charin
charout | charout
namein | namein
(5 rows)
dblink_close(text cursorname [, bool fail_on_error]) 返回 text；
dblink_close(text connname, text cursorname [, bool fail_on_error]) 返回 text；
highgo=# SELECT dblink_close('tmpCur');
dblink_close
--------------
OK
(1 row)
highgo=# SELECT dblink_send_query('myconn','select proname,prosrc from pg_proc limit 5');
dblink_send_query
-------------------
1
(1 row)
如果查询被成功地派送返回1，否则返回0。
highgo=# SELECT dblink_is_busy('myconn');
dblink_is_busy
----------------
0
(1 row)
dblink_is_busy(text connname) 测试是否一个异步查询正在进行中。返回1表示连接正忙，返回0则表示不忙。
dblink_get_result(text connname [,bool fail_on_error])
对于一个异步查询，该函数返回查询产生的行。要使用这个函数，将需要指定所期待的列的集合，如dblink()。
对于一个异步命令，该函数返回一个只有单个文本列的单行，其中包含了该命令的状态字符串。仍必须在调用的FROM子句中指定结果将具有一个单一文本行。
如果dblink_send_query()返回1，如果该查询还没有完成，dblink_get_result将等待直到它完成。这个函数必须被调用。对每一个已发送的查询都必须调用一次这个函数，并且在连接再次可用之前还要再多调用一次来得到一个空结果集。
highgo=# SELECT * FROM dblink_send_query('myconn','select * from bar where id < 3;select * from bar where id > 8') AS t1;
t1
----
1
(1 row)
highgo=# SELECT * FROM dblink_get_result('myconn') AS t1(id int,name varchar);
id | name
----+-------
1 | name1
2 | name2
(2 rows)
highgo=# SELECT * FROM dblink_get_result('myconn') AS t1(id int,name varchar);
id | name
----+--------
9 | name9
10 | name10
(2 rows)
highgo=# SELECT * FROM dblink_get_result('myconn') AS t1(id int,name varchar);
id | name
----+------
(0 rows)
highgo=# SELECT dblink_cancel_query('myconn');
dblink_cancel_query
---------------------
OK
(1 row)
(12)dblink_error_message 得到在命名连接上的最后一个错误消息。
SELECT dblink_error_message('myconn');
03
FDW
瀚高数据库跨库访问瀚高数据库，使用postgres_fdw功能，共分为下面几步：
(1)使用CREATE EXTENSION来安装postgres_fdw扩展。
(2)使用CREATE SERVER创建一个外部服务器对象。指定除了user和password之外的连接信息作为该服务器对象的选项。
(3)使用CREATE USER MAPPING创建一个用户映射，每一个用户映射都代表允许一个数据库用户访问一个外部服务器。指定远程用户名和口令作为用户映射的user和password选项。
(4)为远程表使用CREATE FOREIGN TABLE或者IMPORT FOREIGN SCHEMA创建一个外部表。外部表的列必须匹配被引用的远程表。如果在外部表对象的选项中指定了正确的远程名称，可以使用不同于远程表的表名和/或列名。
以下为例：
在瀚高数据库中创建postgres_fdw扩展：
create extension postgres_fdw;
test远程库中创建表fdwtb01：
create table fdwtb01(id int,name varchar);
INSERT INTO fdwtb01 VALUES(1,'小名'),(2,'张三');
在oa_cfd中创建外部服务器，根据实际情况输入跨库test所在的IP地址(host)，档案库的名称（dbname），端口号（port）等信息：
create server vmfdwdb foreign data wrapper postgres_fdw
options (host '127.0.0.1',dbname 'test',port '5866');
授权：
grant usage on foreign server vmfdwdb to oa_cfd;
创建用户映射：
在oa_cfd库中，用CREATE USER MAPPING定义一个用户映射来标识“test”使用哪个用户。SQL中的server是用户映射到档案数据库的服务名，上一步已创建。此处直接用上一步创建好的服务名“vmfdwdb”即可。
create user mapping FOR public server vmfdwdb options (user 'oa_cfd',password 'Hello@123');
在本地oa_cfd库中创建外部表:
需要注意，外部表的列要和test库中表的字段名称要匹配，字段数据类型最好是一样。远程库的列可以少，字段顺序不影响结果。因为postgres_fdw是根据字段名称来匹配的。
create foreign table vmfdwtb001 (id int,name varchar) server vmfdwdb options (schema_name 'public',table_name 'fdwtb01');
测试及验证：
在本地外表中插入数据，再远程test库中查询数据；
如图：
在本地外表中更新数据，再远程test库中查询数据；
如图：
在本地外表中删除数据，再远程test库中查询数据。
如图：
PART02 Oracle_fdw
使用Oracle_fdw扩展，实现从HGDB访问Oracle数据库。
① 使用highgo用户登录操作系统：
② 执行命令：psql -U highgo -d highgo，登录到数据库，如下图：
③ 创建扩展：
highgo=# create extension oracle_fdw;
执行报如下图所示错误：
错误信息：
无法加载库
"/opt/HighGoDB-5.6.4/lib/highgoql/oracle_fdw.so": libclntsh.so.19.1:
无法打开共享对象文件: 没有那个文件或目录。
切换root用户登录系统，执行如下命令：
[root@localhost ~]# cd /etc/ld.so.conf.d/
[root@localhost ld.so.conf.d]# vi oracle-x86_64.conf
写入并保存：/usr/local/oracle/instantclient
[root@localhost ld.so.conf.d]# ldconfig //让动态链接库为系统所共享
切换highgo用户，再次登录数据库后，执行：
create extension oracle_fdw;
如下：
成功创建 Oracle_fdw扩展。
[root@localhost ~]# cd /usr/local/oracle/instantclient
[root@localhost instantclient]# mkdir -p network/admin/
创建一个或者复制一个tnsnames.ora过来，配置TNS。
(3)外部表使用
创建外部服务器：
create server oracle_test foreign data wrapper oracle_fdw options(dbserver 'orcl');
注：oracle_test为外部服务器的名称，db为tnsnames.ora中配置的TNS。
授权：
grant usage on foreign server oracle_test to highgo;
如下：
创建到oracle的映射：
create user mapping for highgo server oracle_test options (user 'highgo', password 'hg1234rs');
注：create user mapping for highgo server oracle_test options (user 'highgo', password 'hg1234rs')为Oracle用户名和密码。
创建外部表：
create foreign table test_fdw(id int options(key 'true'),name varchar(10)) server oracle_test options (schema 'HIGHGO',table ' USER01');
注：schema和table必须为大写，否则无法使用。
附图：
查看外部服务的信息：
查询操作：
HG查询外表：
insert操作：
向外表插入数据：
在Oracle端查询：
update操作：
外表更新数据：
在Oracle端查询：
delete操作：
外表删除数据：
在Oracle端查询：
(4)其他操作
查询已创建的到oracle的连接；
SELECT * from pg_user_mappings;
删除所创建的对象。
drop foreign table test_fdw;
drop user mapping for highgo server oracle_test;
drop server oracle_test;
FDW远程可写且不会出现丢失连接的情况，dblink在运行数据量大的sql时容易丢失远程连接，导致查询或者更新失败。
易用性：dblink需要先连接才可以查询，账号密码需在代码中配置（安全方面需要考虑），fdw不需要。
功能性：dblink的函数较多，实现的功能多。
总结：三种跨库方式各有特点，建议在不同的场景下，选择合适的跨库方式。
