postgresql dblink垮库查询

环境:
OS:Centos 7
DB:13.8

 

1.赋予普通用户超级用户权限
因为创建扩展需要超级用户的权限,否则报如下的错误:
db_test=> create extension dblink;
ERROR: permission denied to create extension "dblink"
HINT: Must be superuser to create this extension.

postgres=# alter user hxl superuser;
ALTER ROLE

1.登录hxl用户
[postgres@host134 pg13]$ psql -h 192.168.1.134 -U hxl -p15432 -d db_test
Password for user hxl:
psql (13.8)
Type "help" for help.

db_test=>

这里我们使用用户hxl登录,登录的库为db_test

 

2.创建dblink扩展
需要使用超级用户登录创建
[postgres@host134 pg13]$ psql -h 192.168.1.134 -U hxl -p15432 -d db_test
postgres=# create extension dblink;
CREATE EXTENSION

3.访问其他用户的表
这里访问用户hxl01下db_test01库下的表tb_t

db_test=# select * from dblink('hostaddr=192.168.1.134 port=15432 dbname=db_test01 user=hxl01 password=postgres','select "name" from "tb_t"') AS testTable ("name" VARCHAR);
 name  
-------
 name1
 name2
 name3
 name4
 name5
(5 rows)

 

注意这里的hxl01账号需要有访问tb_t表的权限，否则报权限不对

ERROR: permission denied for table tb_t
CONTEXT: while executing query on unnamed dblink connection

 

 

####################################主从环境下的插件问题#################################

主库创建视图:
db_test=# create view vw_tb_aa as
db_test-# select * from dblink('hostaddr=192.168.1.134 port=15432 dbname=db_test01 user=postgres password=postgres','select "name" from "tb_aa"') AS testTable ("name" VARCHAR);

从库查询:
db_test=# select * from vw_tb_aa;
ERROR: could not access file "$libdir/dblink": No such file or directory

解决办法:
从库也需要安装相应的扩展包
安装dblink扩展
[root@host134 dblink]# cd /soft/postgresql/postgresql-13.8/contrib/dblink
[root@host134 dblink]# make
[root@host134 dblink]# make install