文章目录
postgres_fdw 简介
postgres_fdw 安装
创建外部服务器对象
创建用户映射
创建外部表
访问远程数据表
postgres_fdw 选项
连接选项
对象名称选项
成本评估选项
远程执行选项
更新操作选项
导入选项
连接管理
事务管理
远程查询优化
远程查询执行环境
在某些情况下,例如数据仓库的 ETL 流程中,我们可能需要从一个 PostgreSQL 服务器访问另一个远程 PostgreSQL 服务器中的数据。为此,PostgreSQL 提供了一个扩展的模块:postgres_fdw。今天我们就来介绍一下这个模块的使用方法和案例。
如果觉得文章有用,欢迎评论📝、点赞👍、推荐🎁
postgres_fdw 简介
postgres_fdw 是基于 SQL/MED 标准开发的一个外部数据封装器(Foreign Data Wrapper),可以用于访问外部 PostgreSQL 服务器,对远程数据表执行 SELECT、INSERT、UPDATE 以及 DELETE 操作。
与 postgres_fdw 类似的另一个扩展模块是 dblink。它们的功能基本相同,但是 postgres_fdw 提供了更透明且符合标准的语法来访问远程表,并且在很多情况下可以提供更好的性能。postgres_fdw 可以支持的远程服务器最低版本为 PostgreSQL 8.3,如果是只读访问则可以支持到 PostgreSQL 8.1。
通过 postgres_fdw 访问远程数据表的步骤如下:
使用CREATE EXTENSION命令安装 postgres_fdw 模块;
使用CREATE SERVER命令创建一个外部服务器对象,该对象代表了想要连接的远程数据库;
使用CREATE USER MAPPING命令为本地用户创建一个远程用户映射;
使用CREATE FOREIGN TABLE或者IMPORT FOREIGN SCHEMA语句为每个远程数据库中的表创建一个外部表。
然后,就可以通过查询外部表访问远程表中的数据,包括 SELECT、INSERT、UPDATE 以及 DELETE 操作(当然,用户映射时指定的远程用户必须拥有这些表上的相应权限)。接下来我们就通过一个实际案例介绍如何实现以上步骤。
📝除了 postgres_fdw,PostgreSQL 还支持访问各种数据源的外部数据封装器,包括 Oracle、MySQL、SQL Server、SQLite 等关系型数据库,MongoDB、Redis、Neo4j、Cassandra 等 NoSQL 数据库,CSV、XML、JSON 等文件,Elasticsearch、Hive、HBase 等大数据平台等等,具体可以参考 PostgreSQL Wiki。
postgres_fdw 安装
首先,我们需要安装 postgres_fdw 模块。对于 Linux 操作系统,可以通过 postgresql-contrib 软件包下载 PostgreSQL 扩展模块;对于 Windows 和 macOS,默认安装已经包含了扩展模块。我们只需要执行以下语句在当前数据库中安装 postgres_fdw 模块:
CREATE EXTENSION postgres_fdw;
创建外部服务器对象
然后,使用 CREATE SERVER 语句创建一个外部服务器(foreign server)。例如:
CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.56.104', port '5432', dbname 'pagila');
其中,foreign_server 是我们指定的外部服务器名称;host 参数是远程服务器的地址,示例中为 192.168.56.104;port 参数是远程服务器的端口,示例中为 5432;dbname 参数是远程数据库的名称,示例中为 pagila。
📝关于 CREATE SERVER 语句的详细介绍,可以参考官方文档。
创建用户映射
下一步,使用 CREATE USER MAPPING 语句创建一个用户映射(user mapping),为本地用户指定一个访问远程服务器时的角色。例如:
CREATE USER MAPPING FOR postgres
SERVER foreign_server
OPTIONS (user 'tony', password 'pswd123#');
以上语句为本地 postgres 用户创建了一个访问远程服务器 foreign_server 时的用户映射,也就是使用用户名 tony 和密码 pswd123# 连接远程服务器。
📝关于 CREATE USER MAPPING 语句的详细介绍,可以参考官方文档。
创建外部表
然后,使用 CREATE FOREIGN TABLE 语句创建一个外部表(foreign table)。例如:
PostgreSQL 访问外部数据库之 postgres_fdw
最新推荐文章于 2024-07-05 09:48:57 发布