文章转载自公众号：AustinDatabases
其实说到这个问题,有些同学会有疑问,访问同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是否在使用中
dblink_is_busy复制
另外DBLINK还支持异步调用,将语句发送给remote的数据库再等待后将信息取回. 这点实际上在其他数据库上如果找到类比, 可能类似SQL SERVER SERVICE BROKE但也仅仅是类似.
使用上应该是类似于句柄,将信息发送给远程的数据库服务器,然后,在通过判断句柄将信息取回. 但返回的信息会在本地机的内存中保存,所以不建议获取数据量较大的信息. 这里面建议使用标准的方式来获取数据,也就是最上面的方式.
同时建议提高work_mem的大小和临时表的大小.
上面的访问的方式应该会有一部分人吐槽,很麻烦,PG还提供另一种方式POSTGRES_FDW
其实POSTGRES_FDW做起来并不麻烦 4 步就OK
在目的库上创建create extension
创建于目的库的连接
创建本地用户和远程连接之间的账号mapping
创建外部表
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 timestampSERVER foreign_server_tOPTIONS (schema_name 'public', table_name 'actor');复制
查询和在本地库是没有区别的,这是第二种解决本地库中两个数据库中的表进行跨库访问的方式.
到此可以总结一下
1 如果是非灵活的使用外部表,例如仅仅是一次查询,或者不是模式化的程序方式的访问,使用DBLINK是一个快速的好方式.
2 如果是程序端使用,则POSTGRES_FDW则是一种好的方式,直接将目的表映射到本地,访问的方式和访问本地库的表是一样的.
到此结束了吗? 没有一般DB人员都是对于需求无限的满足,并且将自己逼到死角, 现在的程序设计中,本地库的多库访问应该被禁止,或消减, 这不是现在主流的程序设计所推崇的, 所以物理库多库跨库查询表, 可以支持, 但对于频繁使用同物理机,跨库查询的架构设计,我只能报以遗憾.
I Love PG 复制
关于我们
中国开源软件推进联盟PostgreSQL分会（简称：中国PG分会）于2017年成立，由国内多家PostgreSQL生态企业所共同发起，业务上接受工信部中国电子信息产业发展研究院指导。中国PG分会是一个非盈利行业协会组织。我们致力于在中国构建PostgreSQL产业生态，推动PostgreSQL产学研用发展。
复制
技术文章精彩回顾 PostgreSQL学习的九层宝塔 PostgreSQL职业发展与学习攻略 2019，年度数据库舍 PostgreSQL 其谁? Postgres是最好的开源软件 PostgreSQL是世界上最好的数据库 从Oracle迁移到PostgreSQL的十大理由 从“非主流”到“潮流”，开源早已值得拥有 PG活动精彩回顾 创建PG全球生态！PostgresConf.CN2019大会盛大召开 首站起航！2019“让PG‘象’前行”上海站成功举行 走进蓉城丨2019“让PG‘象’前行”成都站成功举行 中国PG象牙塔计划发布，首批合作高校授牌仪式在天津举行 群英论道聚北京，共话PostgreSQL 相聚巴厘岛| PG Conf.Asia 2019 DAY0、DAY1简报 相知巴厘岛| PG Conf.Asia 2019 DAY2简报 独家|硅谷Postgres大会简报 直播回顾 | Bruce Momjian：原生分布式将在PG 14版本发布 PG培训认证精彩回顾 中国首批PGCA认证考试圆满结束，203位考生成功获得认证！ 中国第二批PGCA认证考试圆满结束，115位考生喜获认证！ 重要通知：三方共建，中国PostgreSQL认证权威升级！ 近500人参与！首次PGCE中级、第三批次PGCA初级认证考试落幕！ 2020年首批 | 中国PostgreSQL初级认证考试圆满结束 一分耕耘一分收获，第五批次PostgreSQL认证考试成绩公布 复制
