PostgreSQL数据库和Oracle类似,在逻辑上是相互独立的,如果要访问其他数据库,需要做跨库操作,Postgres本身提供了一些扩展,比如dblink,pgsql_fdw等能够实现跨数据库访问。
PG数据库的扩展
PostgreSQL设计的初衷,易于扩展是它重要的特性之一。当我们需要的一些功能在发布版本中没有时,我们可以尝试通过安装扩展实现。
通常通过CREATE EXTENSION加载一个新的扩展到当前的数据库中,加载到数据库中的扩展功能就可以像内置的特性一样运行。
与源码一起发布的contrib/ 目录包含一些常用的扩展,部分扩展是独立开发的,需要我们手动进行安装,比如PostGis。
加载一个扩展,实际上是执行扩展的脚本文件,创建数据库对象,比如函数, 数据类型, 运算符和索引支持方法等等。CREATE EXTENSION记录了所有已创建对象的标识,以便在发出DROP EXTENSION时可以再次删除它们。
PG跨数据库访问方案
在Oracle数据库中,我们可以通过DBLINK实现跨数据库的访问。PG数据库本身并没有提供这样的功能,需要我们安装对应的扩展来实现。
在PG中可以通过安装DBLINK或postgres_fdw扩展的方式实现跨库访问。
在上一篇文章<PostgreSQL通过DBLINK执行存储过程创建表>中,介绍了通过DBLINK跨数据库访问的步骤
-- 在B服务器创建扩展db_link
create extension dblink;
--- 在B服务器创建连接,其中host地址为A服务器地址
select dblink_connect('test_dblink','host=10.110.38.201 dbname=admin user=postgres password=postgres');
--- 执行A服务器存储过程,创建表(as t(ret int)为函数返回值类型,此处为int)
select * from dblink('test_dblink','select crt_tab()') as t(ret int);
--- 关闭连接
select dblink_disconnect('test_dblink');
在DBLINK在使用中存在如下缺点
- 每次连接时,都要使用dblink_connect建立连接才能查询,对用户来说并不是透明的。
- 每次查询一张表都要在sql中命名字段和类型,或者配置视图把sql“封装”起来。逐个封装视图,也是一件很麻烦的事。
- dblink在运行数据量大的sql时容易丢失远程连接,导致查询或者更新失败
相比较而言,postgres_fdw要方便的多。
创建扩展create extension postgres_fdw;
第一步创建远程服务器,需要指定主机地址,数据库名和端口。
create server dbmess foreign data wrapper postgres_fdw OPTIONS (host '待访问数据库IP', port '5432', dbname 'mess', use_remote_estimate 'true');
第二步定义到外部服务器的映射
create user mapping FOR mess SERVER dbmess options (user 'mess', password 'mess');
第三步一键创建外部表(可以使用except来排除.)
import FOREIGN SCHEMA public except (geography_columns,geometry_columns,raster_columns,raster_overviews,spatial_ref_sys) from server dbmess into public OPTIONS (import_default 'true');
第四步和操作本地表一样操作远程表。
通过postgres_fdw跨数据库访问遇到的问题
虽然相比DBLINK,postgres_fdw的方式要高效很多,使用也更加方便。但在实际使用中依然存在一些问题。
如下这些问题是在实际使用中遇到的,没有进一步研究,可能都会归因到权限问题。
- 在本地访问远端数据库视图,如果创建视图的语句中存在自定义函数,则在本地远程访问时,会提示错误。没有使用自定义函数则没有该问题。
- 触发器函数中,表名需要显示的标明表所归属的schema.即便是public的。
- 某些情况下,触发器可能会失效,需要重建。
- 无论是哪种方式,都还不能对远程表的数据量实际估算,join表时也不会进行优化,所以在查询性能上可能会有影响。