POSTGRESQL 到底怎么访问同instance 的库

其实说到这个问题,有些同学会有疑问,访问同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

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 人员都是对于需求无限的满足,并且将自己逼到死角, 现在的程序设计中,本地库的多库访问应该被禁止,或消减, 这不是现在主流的程序设计所推崇的, 所以物理库多库跨库查询表, 可以支持, 但对于频繁使用同物理机,跨库查询的架构设计,我只能报以遗憾.