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

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 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表时也不会进行优化,所以在查询性能上可能会有影响。
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
4天前
|
关系型数据库 分布式数据库 数据库
阿里云PolarDB登顶2024中国数据库流行榜:技术实力与开发者影响力
近日,阿里云旗下的自研云原生数据库PolarDB在2024年中国数据库流行度排行榜中夺冠,并刷新了榜单总分纪录,这一成就引起了技术圈的广泛关注。这一成就源于PolarDB在数据库技术上的突破与创新,以及对开发者和用户的实际需求的深入了解体会。那么本文就来分享一下关于数据库流行度排行榜的影响力以及对数据库选型的影响,讨论PolarDB登顶的关键因素,以及PolarDB“三层分离”新版本对开发者使用数据库的影响。
22 3
阿里云PolarDB登顶2024中国数据库流行榜:技术实力与开发者影响力
|
1月前
|
关系型数据库 分布式数据库 数据库
阿里云瑶池数据库训练营权益:PolarDB开发者大会主题资料开放下载!
阿里云瑶池数据库训练营权益:PolarDB开发者大会主题资料开放下载!
|
2天前
|
关系型数据库 MySQL 测试技术
数据库专家带你体验PolarDB MySQL版 Serverless的极致弹性特性!
本次基于阿里云瑶池数据库解决方案体验馆,带你体验PolarDB MySQL Serverless形态下的性能压测环境,基于可选择的标准压测工具进行压测,构造弹性场景进行压测,实时动态展示弹性能力、价格和性价比结果,压测环境可开放定制修改、可重复验证。参与活动即有机会获得鼠标、小米打印机、卫衣等精美礼品。
数据库专家带你体验PolarDB MySQL版 Serverless的极致弹性特性!
|
3天前
|
Cloud Native 关系型数据库 分布式数据库
热烈祝贺阿里云PolarDB登顶2024最新一期中国数据库流行榜
【2月更文挑战第3天】热烈祝贺阿里云PolarDB登顶2024最新一期中国数据库流行榜
|
3天前
|
关系型数据库 MySQL 测试技术
数据库专家带你体验PolarDB MySQL版 Serverless的极致弹性特性
本次基于阿里云瑶池数据库解决方案体验馆,带你体验PolarDB MySQL Serverless形态下的性能压测环境,基于可选择的标准压测工具进行压测,构造弹性场景进行压测,实时动态展示弹性能力、价格和性价比结果,压测环境可开放定制修改、可重复验证。参与活动即有机会获得鼠标、小米打印机、卫衣等精美礼品。
|
8天前
|
关系型数据库 MySQL 分布式数据库
PolarDB for MySQL数据库外网连接解析失败的原因
【2月更文挑战第5天】PolarDB for MySQL数据库外网连接解析失败的原因
52 8
|
10天前
|
Cloud Native 关系型数据库 分布式数据库
推荐你使用数据库排行榜第一的PolarDB
数据库大家都使用的,才是最好的,有足够多的案例和样本。
32805 15
|
10天前
|
SQL 关系型数据库 分布式数据库
|
25天前
|
SQL 存储 关系型数据库
MySQL技能完整学习列表——1、数据库基础概念——1、关系型数据库(Relational Database)
MySQL技能完整学习列表——1、数据库基础概念——1、关系型数据库(Relational Database)
133 0
|
26天前
|
关系型数据库 分布式数据库 数据库
PolarDB for PostgreSQL报错问题之psql连接数据库报错如何解决
PolarDB for PostgreSQL是基于PostgreSQL开发的一款云原生关系型数据库服务,它提供了高性能、高可用性和弹性扩展的特性;本合集将围绕PolarDB(pg)的部署、管理和优化提供指导,以及常见问题的排查和解决办法。

热门文章

最新文章