其实说到这个问题,有些同学会有疑问,访问同instance 的有那么难吗? 估计用过SQL SERVER ,MYSQL的同学会提出这样的疑问, 而ORACLE的同学则会提出什么同一个instance 多个库, really ?
实际上SQL SERVER 本身是多个数据库和schema 并存的数据库, POSTGRESQL 也是. 这时估计SQL SERVER 的同学会嘴角上扬,我们的功能是最全的,哪有访问同一个INSTANCE 的多个数据库还这么麻烦.
其实我到是有不同的意见,原因如下
同一个数据库的INSTANCE 下多个数据库可以无障碍的访问,本身是弊大于利还是利大于弊,这不好说, 尤其现在开发中使用MYSQL时,都已经分库分表了,同一个INSTANCE 下多个数据库能互访的方便,当然不是坏事,但你见过一个INSTANCE 下挂了N 个数据库,然后库和库互相夸库查询, 等到拆库的时候有多麻烦, 另外从现在软件开发的角度来说,如果还算是一个项目的话, 这样的跨库访问的方式应该被减少,甚至是禁止.
当然不是还有那么多不是项目的项目,PG 也是可以进行跨库访问,常用的2中方式
1 dblink
2 postgres_fdw
以下操作基于PG 11版本及以上
1 dblink
dblink 熟悉这个名词是ORACLE SQL SERVER 的同学,一般都是访问另一个物理库的数据表, POSTGRESQL 可以通过这样方式,访问本实例中的另外的数据库或远程的数据库.
1 建立一个连接
select dblink_connect('test','host=192.168.198.100 port=5432 user=test password=test dbname=test');
2 直接进行查询
select * from dblink('test','select * from actor limit 10') as t ( actor_id int,first_name varchar(45),last_name varchar(45), last_update timestamp);
3 增加条件怎么办,是写在里面还是写在外面
select * from dblink('test','select * from actor limit 10') as t ( actor_id int,first_name varchar(45),last_name varchar(45), last_update timestamp) where first_name = 'Penelope';
上面的例子是写在外面, 但是有条件的,应该是本地库的跨库查询
那为什么不写到里面, 其实涉及到转移符的问题,撰写起来比较麻烦,所以写到了外面.
大部分文章到底就为止了,实际上这就完了,怎么查询我都有多少了dblink
怎么清理dblink ,即使 G 类似的知识也没找到说的明白的.
实际上DBLINK是一套功能集合,下面的看看常用的 DBLINK的功能有哪些
问题
1 建立dblink , 是通过 dblink_connect 来建立的上面是有的,但如果你建立的dblink太多了,怎么查看当前建立了多少dblink
SELECT dblink_get_connections();
通过上面的方式就可以查看到具体本地库已经启用了db_link,的名字和数量2
2 解除dblink
dblink_disconnect();
3 判断当前BDLINK 是否在使用中
代码语言：javascript复制dblink_is_busy
另外DBLINK 还支持异步调用,将语句发送给remote 的数据库再等待后将信息取回. 这点实际上在其他数据库上如果找到类比, 可能类似 SQL SERVER SERVICE BROKE 但也仅仅是类似.
使用上应该是类似于句柄,将信息发送给远程的数据库服务器,然后,在通过判断句柄将信息取回. 但返回的信息会在本地机的内存中保存,所以不建议获取数据量较大的信息. 这里面建议使用标准的方式来获取数据,也就是最上面的方式.
同时建议提高work_mem 的大小和临时表的大小.
上面的访问的方式应该会有一部分人吐槽,很麻烦,PG 还提供另一种方式 POSTGRES_FDW
其实POSTGRES_FDW 做起来并不麻烦 4 步就OK
1 在目的库上创建 create extension
2 创建于目的库的连接
3 创建本地用户和远程连接之间的账号mapping
4 创建外部表
create extension postgres_fdw;
create server foreign_server_t foreign data wrapper postgres_fdw options (host '192.168.198.100',port '5432',dbname 'test');
create user mapping for postgres server foreign_server_t options (user 'test',password 'test');
CREATE FOREIGN TABLE foreign_table (
actor_id integer NOT NULL,
first_name varchar(45),
last_name varchar(45),
last_update timestamp
SERVER foreign_server_t
OPTIONS (schema_name 'public', table_name 'actor');
查询和在本地库是没有区别的,这是第二种解决本地库中两个数据库中的表进行跨库访问的方式.
到此可以总结一下
1 如果是非灵活的使用外部表,例如仅仅是一次查询,或者不是模式化的程序方式的访问,使用DBLINK 是一个快速的好方式
2 如果是程序端使用,则POSTGRES_FDW 则是一种好的方式,直接将目的表映射到本地,访问的方式和访问本地库的表是一样的.
到此结束了吗? 没有一般DB 人员都是对于需求无限的满足,并且将自己逼到死角, 现在的程序设计中,本地库的多库访问应该被禁止,或消减, 这不是现在主流的程序设计所推崇的, 所以物理库多库跨库查询表, 可以支持, 但对于频繁使用同物理机,跨库查询的架构设计,我只能报以遗憾.
