centos 6.8 + postgresql 9.6 + dblink

postgresql extension 机制是其产品开放、优秀的一种体现。
简单示范如下:

进入 db_1 数据库

在 db_1 数据库创建 dblink

create extension dblink;

在db_1 数据库查询 db_2数据库的表

with tmp_t1 as (

select * from dblink(‘dbname=db_2 host=192.168.56.100 port=5432 user=usr_abc password=usr_abc’,’select userid from tmp_test1 ‘) as t1 (userid varchar)

)
select t1.custno as 客户编码,
t2.invnm as 姓名,
t2.idno as 身份证,
t2.mobileno as 手机号,
t2.email as 邮箱
from tmp_test2 t2
join tmp_t1 t1 on t1.userid = t2.userid
where 1=1
and t2.comment = ‘好吃’
;

Select dblink_get_connections();