开发者社区> libydwei2019> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL如何实现跨数据库访问?

简介: PostgreSQL数据库和Oracle类似,在逻辑上是相互独立的,如果要访问其他数据库,需要做跨库操作,Postgres本身提供了一些扩展,比如dblink,pgsql_fdw等能够实现跨数据库访问。
+关注继续查看

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在使用中存在如下缺点

  1. 每次连接时,都要使用dblink_connect建立连接才能查询,对用户来说并不是透明的。
  2. 每次查询一张表都要在sql中命名字段和类型,或者配置视图把sql“封装”起来。逐个封装视图,也是一件很麻烦的事。
  3. 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的方式要高效很多,使用也更加方便。但在实际使用中依然存在一些问题。
如下这些问题是在实际使用中遇到的,没有进一步研究,可能都会归因到权限问题。

  1. 在本地访问远端数据库视图,如果创建视图的语句中存在自定义函数,则在本地远程访问时,会提示错误。没有使用自定义函数则没有该问题。
  2. 触发器函数中,表名需要显示的标明表所归属的schema.即便是public的。
  3. 某些情况下,触发器可能会失效,需要重建。
  4. 无论是哪种方式,都还不能对远程表的数据量实际估算,join表时也不会进行优化,所以在查询性能上可能会有影响。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
【数据库架构】PostgreSQL常用的开源扩展
如果您使用的是PostgreSQL,您可以利用优秀的开源扩展来根据您的业务需求增强或添加功能。这些扩展是由其社区积极开发的,这些社区与PostgreSQL社区本身是分开的。PostgreSQL有数百个OSS扩展,其中许多是在生产环境中实现的。
0 0
Diesel框架对于数据库的使用和实战,在PostgreSQL的基础上的使用【Diesel】
Diesel框架对于数据库的使用和实战,在PostgreSQL的基础上的使用【Diesel】
0 0
26PostgreSQL 对象安全控制 | 学习笔记
快速学习26PostgreSQL 对象安全控制
0 0
OceanBase 数据库源码解读之模块结构
本文为《带你读源码系列》第一篇,主要为大家介绍 OceanBase 数据库代码的整体架构和模块构成,以及各模块的功能。
0 0
阿里云数据库和自建数据库有什么区别?优缺点对比
无论是企业还是个人用户都可以选择自建数据库,也可以将数据库搬到云端,比如说阿里云数据库,阿里云数据库和传统的自建数据库有什么区别?笔者来说说阿里云数据库和自建数据库的优缺点对比: 阿里云数据库和自建数据库综合对比 下表为二者的综合对比,包括数据库的安全性、数据库可靠性、数据库运维、资源利用率、扩容和成本方面考虑: 优势对比 云数据库RDS 自购服务器搭建数据库服务 服务可用性 99.95% 需自行保障, 自行搭建主从复制,自建RAID等。
2164 0
如何实现高容量大并发数据库服务 | 数据库分布式架构设计
袋鼠学院和优云、阿里云联合举办的沙龙结束之后,总是有小伙伴们来问PPT内容,想要进一步了解Topic内容。(哦,对了对了,竟然还有小伙伴专门冲着袋鼠云去听沙龙,感动cry~~) 千呼万唤,忙成狗的袋鼠小妹终于把沙龙总结整理了出来(⊙o⊙) 本次沙龙的主题是“云时代下的运维管理实践”,受邀请的演讲嘉宾,花名宏翊(经常关注袋鼠云的同学,肯定已经对这个名字很熟悉了),是袋鼠云首席数据库架构师,袋鼠学院数据库讲师。
6382 0
PostgreSQL 逻辑订阅 - 给业务架构带来了什么希望?
标签 PostgreSQL , 逻辑订阅 , 10.0 , 数据汇聚 , 数据共享 , IDC多活 , 云端线下同步 背景 逻辑订阅是PostgreSQL 10.0的新特性。 具体的原理,使用方法可以参考如下文章。
4341 0
postgresql_fdw 跨数据库查询
--PostgreSQL的数据库逻辑上是相互独立的,和Oracle类似,如果要访问其他数据库,需要做跨库操作, --Postgres本身提供了一些扩展,比如dblink,pgsql_fdw等,高版本的建议使用postgres_fdw,也就是pgsql_fdw的升级版。
958 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
PostgreSQL高并发数据库应用数据
立即下载
PostgresChina2018_权宗亮_基于odyssey连接池实现企业级PostgreSQL数据分布中间件
立即下载
PolarDB PG核心特性介绍
立即下载