如果是用过Oracle 的同学,那肯定有用到 DBLINK。今天我们要说的 FDW 也是跟 DBLINK 类似,不过比 DBLINK 更加强大,不仅可以访问 postgresql,还可以访问 MySQL 和 Oracle,还能访问文件。但配置也稍微比 DBLINK 难一点。

FDW 介绍

PostgreSQL 外部数据包装器,英文全程为 PostgreSQL Foreign Data Wrappers(太长了,下面我们简称为 FDW)。

如其名,它是用来访问存储在外部的数据,这些数据可以是外部的 pg 数据库,也可以是 Oracle 和 mysql等数据库,甚至可以是文件。

FDW 用途

FDW 可以用于以下场景:

  1. 跨数据库查询:在 PostgreSQL 数据库中,我们可以通过 FDW 直接请求和其他 PostgreSQL 实例,或者其他数据库数据。

  2. 数据整合:当我们需要从不同数据源整合数据时,FDW 能够帮助我们轻松实现这种跨来源的数据整合。

  3. 数据迁移:利用 FDW,我们可以高效地将数据从旧系统迁移到新的 PostgreSQl 数据库中。

  4. 实时数据访问:通过 FDW,我们能够访问外部实时更新的数据源。

FDW 原理

FDW 机制由四个核心组件构成:

1. Foreign Data Wrapper:特定于各数据源的库,定义了如何建立与外部数据源的连接、执行查询及处理其他操作。例如,postgres_fdw用于连接其他 PostgreSQL 服务器,mysql_fdw则专门连接 MySQL 数据库。

2. Foreign Server:在本地 PostgreSQL 中定义一个外部服务器对象,对应实际的远程或非本地数据存储实例。

3. User Mapping:为每个外部服务器设置用户映射,明确哪些本地用户有权访问,并提供相应的认证信息,如用户名和密码。

4. Foreign Table:在本地数据库创建表结构,作为外部数据源中表的映射。对这些外部表发起的 SQL 查询将被转换并传递给相应的 FDW,在外部数据源上执行。

小试牛刀-案例演示

想要使用 FDW 对远程数据库进行访问,主要有以下几个步骤:

  • 安装 postgres_fdw 扩展

  • 创建外部服务器

  • 创建用户映射

  • 创建外部表或导入外部模式

这里有条件的同学们,可以使用两台服务器来做实验,要是只有一台服务器的话,可以创建两个数据库来进行实验。只要思想不滑坡,方法总比困难多!

postgresql 版本要 10 以上

前提工作

远程 PostgreSQL 数据库创建用户

使用 superuser 在远程 PostgreSQL 数据库执行如下语句创建普通用户fdw_user,供后面本地数据库建立 FDW 连接时使用。

CREATE USER fdw_user WITH ENCRYPTED PASSWORD 'secret';

在远程 PostgreSQL 数据库创建表

在远程数据库创建用于测试的天气表weather,插入测试数据,并为用户fdw_user授权针对该表的增删改查权限。

CREATE TABLE weather (
    city        varchar(80), -- city name (城市名)
    temp_low    int,  -- low temperature (最低温度)
    temp_high   int,  -- high temperature (最高温度)
    prcp        real, -- precipitation (降水量)
    date        date  -- date (日期)
);

INSERT INTO weather (city, temp_low, temp_high, prcp, date)
    VALUES ('Beijing', 18, 32, 0.25, '2021-05-19'),
          ('Beijing', 20, 30, 0.0, '2021-05-20'),
          ('Dalian', 16, 24, 0.0, '2021-05-21');

GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE weather TO fdw_user;

注意:若是真实的远程数据库,要想从本地建立连接,需要在远程数据库的 pg_hba.conf 配置文件增加记录以对访问 IP 开通防火墙。

在本地 PostgreSQL 数据库创建用户

使用 superuser 在本地 PostgreSQL 数据库执行如下语句创建普通用户local_user。

CREATE USER local_user WITH ENCRYPTED PASSWORD 'secret';

所有准备工作都做好了,现在可以使用 superuser 在本地数据库开始正式的步骤了。

安装 postgres_fdw 扩展

使用CREATE EXTENSION语句安装postgres_fdw扩展。

CREATE EXTENSION postgres_fdw;

如果没有这个扩展,需要到源代码目录下的 contrib,然后 make && make install 就行了

为用户local_user授权postgres_fdw的使用权限。

GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO local_user;

创建外部服务器

使用CREATE SERVER语句创建外部服务器,需要指定远程数据库的主机、端口及数据库名。

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host 'localhost', port '5432', dbname 'postgres');

为用户local_user授权外部服务器foreign_server的使用权限。

GRANT USAGE ON FOREIGN SERVER foreign_server TO local_user;

创建用户映射

使用CREATE USER MAPPING语句创建远程用户与本地用户的映射,需要提供远程用户的用户名及密码。

CREATE USER MAPPING FOR local_user
        SERVER foreign_server
        OPTIONS (user 'fdw_user', password 'secret');

创建外部表或导入外部模式

使用CREATE FOREIGN TABLE语句创建远程表。需要注意各列的类型需与实际的远程表相匹配,列名也最好保持一致,否则您需要使用column_name参数为每一列单独指定远程表中的列名。

CREATE FOREIGN TABLE foreign_weather (
      city        varchar(80),
      temp_low    int,
      temp_high   int,
      prcp        real,
      date        date
  )
        SERVER foreign_server
        OPTIONS (schema_name 'public', table_name 'weather');

外部表多的话,这样一个一个新建会比较痛苦,多数情形下,您只要使用IMPORT FOREIGN SCHEMA语句直接将外部模式下的所有表导入本地指定的模式即可。

注意:因未给 super_user 指定用户映射,如下语句需要使用用户local_user执行,否则会报ERROR: user mapping not found for "super_user"错误。

-- 导入外部模式下的所有表,该操作相当于初始化,再次执行会报错ERROR: relation "***" already exists

如果后续有新增的表,想再次执行该命令,可以先 drop 掉所有表再次执行。这里 drop 只是外部表,对远程表无影响

IMPORT FOREIGN SCHEMA public FROM SERVER foreign_server INTO public;

-- 导入外部模式下的指定表

IMPORT FOREIGN SCHEMA public LIMIT TO (weather) FROM SERVER foreign_server INTO public;

为local_user授权 public 模式下所有表(包括外部表)的增删改查权限。

GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO local_user;

这样,使用用户local_user连接到本地数据库,即可以对外部表进行操作了。

[postgres@192 data]$ psql -U local_user -d postgres
psql (17.1)
Type "help" for help.

postgres=> select * from weather;
  city   | temp_low | temp_high | prcp |    date    
---------+----------+-----------+------+------------
 Beijing |       18 |        32 | 0.25 | 2021-05-19
 Beijing |       20 |        30 |    0 | 2021-05-20
 Dalian  |       16 |        24 |    0 | 2021-05-21
(3 rows)

postgres=> UPDATE weather SET prcp=0 WHERE city='Beijing' AND date='2021-05-19';
UPDATE 1
postgres=> select * from weather;
  city   | temp_low | temp_high | prcp |    date    
---------+----------+-----------+------+------------
 Beijing |       20 |        30 |    0 | 2021-05-20
 Dalian  |       16 |        24 |    0 | 2021-05-21
 Beijing |       18 |        32 |    0 | 2021-05-19
(3 rows)

参考链接

https://leileiluoluo.com/posts/postgres-foreign-data-wrappers.html