PostgreSQL 访问外部数据库之 postgres_fdw

文章目录

        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）。例如：

更多请见：http://www.mark-to-win.com/tutorial/51569.html