PostgreSQL_FDW_安装和简单使用

在使用异构数据库构建数据平台的过程中，异构数据库之间的访问一直是比较复杂的问题。我们使用PostgeSQL的过程中，遇到需要访问 MySQL 实时数据的场景。可供我们选择的方案包括

• 使用OGG等三方 工具 实现实时数据复制；
• 使用PostgreSQL的DBLink机制远程访问异构数据库；
• 使用MySQL_FDW扩展远程访问异构数据库；

FDW(Foreign Data Wrapper)是我们选用的开源方案，这个方案明显的优点是使用统一的接口方式实现多种数据库的远程访问，包括但不限于PostgreSQL, MySQL, MongoDB, HDFS 等等。

本次我们专注于 MySQL _FDW 的使用，软件环境如下

CentOS 7 x64

PostgreSQL 11.1

MySQL_FDW 11

准备工作

源库和目标库的具体搭建过程不再描述。我们需要使用的DB和用户信息如下

• 源库（MySQL 5.7）：DB名称lhb，用户名称lhb
• 目标库（PostgreSQL 11.1）：DB名称demo，用户名称demo

安装软件

CentOS下通过yum可以直接安装FDW的最新版本，这次测试的版本是11

yum install -y mysql_fdw_11
复制代码

创建FDW并授权

非常重要：

• 因为权限的原因，只有superuer才能创建FDW；
• 因为FDW是在DB内生效的，所以必须进入具体的DB操作；

### 进入PostgreSQL。 admin是我们建立的超级用户， demo是我们建立的测试DB
psql --username=admin --dbname=demo --password
复制代码

为了更好的进行演示，代码片段中我会把提示符和操作结果信息也显示出来。拷贝脚本的时候要注意！

-- 确认自己的用户和DB信息
demo=# \c
You are now connected to database "demo" as user "admin".

-- 建立FDW，一定要在目标DB中操作，这里是demo库。
-- mysql_fdw 名称是固定的
demo=# create extension mysql_fdw;

-- 给目标用户demo授权
demo=# grant usage on foreign data wrapper mysql_fdw to demo;

-- 查看一下已经建立的FDW信息
demo=# \dew
                List of foreign-data wrappers
   Name    | Owner |      Handler      |      Validator
-----------+-------+-------------------+---------------------
 mysql_fdw | admin | mysql_fdw_handler | mysql_fdw_validator
复制代码

定义远程服务器

因为前面已经进行了授权，所以除非特别说明，下面的操作都使用普通用户(demo)执行。

### demo是我们建立的普通用户， demo是我们建立的测试DB
psql --username=demo --dbname=demo --password
复制代码

-- server_lhb 是远程服务器的别名，随便取
create server server_lhb foreign data wrapper mysql_fdw options (host '172.16.x.x',port '3306');

-- 查看一下
demo=> \des
        List of foreign servers
  Name   | Owner | Foreign-data wrapper
---------+-------+----------------------
 server_lhb | demo  | mysql_fdw
复制代码

定义用户映射

在目标库定义用户映射，即本地的用户可以映射为源库的指定用户。

-- 本地的PUBLIC用户映射为源库的lhb用户，xxx是源库的密码
create user mapping for public server server_lhb options (username 'lhb',password 'xxx');

-- 查看一下
demo=> \deu+
                 List of user mappings
 Server  | User name |           FDW options
---------+-----------+----------------------------------
 server_lhb | public    | (username 'lhb', password 'xxx')
复制代码

使用外部表

在真正能访问源库数据之前，我们还需要把源库的表结构同步到目标库上。根据不同的场景会有不同的方法。

-- 建立一个新的schema来存放外部表
demo=> create schema src_lhb;

-- 查看一下
demo=> \dn
  List of schemas
  Name   |  Owner
---------+----------
 src_lhb | demo
 public  | postgres
复制代码

明确定义外部表

当源表有很多字段，但是我仅仅需求几个的时候，我可以明确定义一个外部表来指定字段。这种场景下，只有源表中的指定字段发生变化才会影响查询。

-- 建立一个account_ft表，映射为源库lahuobao下的account表。这里表名称可以不同于源库
create foreign table src_lhb.account_ft (
	account_id int not null,
	bank_card_no varchar(30)
)server server_lhb 
options (dbname 'lahuobao', table_name 'account');

-- 查看一下数据
demo=> select * from src_lhb.account_ft limit 3;
 account_id |     bank_card_no
------------+----------------------
          1 |
          2 | 6228481722089439218
          3 | 62252546325498753698
(3 rows)
复制代码

直接导入外部表

更多的时候，我们仅需要直接使用源表的结构。这种场景下，通过直接导入的方式即可批量建立外部表。

注意：如果源表的结构发生变化，大概率可能会造成查询失败。需要重新导入一次表结构。

指定表

-- 仅导入指定的表，lahuobao库下的account，waybill两张表到src_lhb 模式(schema)下
import foreign schema lahuobao limit to (account,waybill) from server server_lhb into src_lhb;
复制代码

所有表

-- 一次性导入指定DB下的所有表
import foreign schema lahuobao from server server_lhb into src_lhb;
复制代码

查看外部表信息

-- 查看一下已经有哪些外部表
demo=> select * from information_schema.foreign_tables;
 foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name
-----------------------+----------------------+--------------------+------------------------+---------------------
 demo                  | src_lhb              | account_ft         | demo                   | server_lhb
 demo                  | src_lhb              | account            | demo                   | server_lhb
 demo                  | src_lhb              | waybill            | demo                   | server_lhb
(3 rows)
复制代码

删除外部表

-- 指定表名称，删除多个表
drop foreign table src_lhb.account_ft, src_lhb.account, src_lhb.waybill;

-- 或者构造 sql 语句批量删除
select 
	'drop foreign table ' || t.table_schema || '.' || t.table_name || ';' as drop_sql
from information_schema.tables t
where t.table_type in ('FOREIGN')
	and t.table_schema in ('src_lhb', 'public')
;
复制代码

-- 或者直接删除FDW扩展来删除所有外部表(必须是owner，这里就是admin用户)
drop extension mysql_fdw cascade;
复制代码