PostgreSQL外部資料插件：mysql_fdw

概述

PostgreSQL具有插件功能，通過不同的插件拓展，實作資料庫本身不包含的功能，以滿足使用者的需求。mysql_fdw 就是一個強大的外部表功能，所謂外部表，就是在PG資料庫中通過SQL通路外部資料源資料，就像通路本地資料庫一樣，下面就來測試一下使用mysql_fdw 來通路mysql中的資料

安裝使用步驟

下載下傳安裝插件

可以直接下載下傳rpm包進行安裝，編譯需要各種依賴，比較麻煩，下面連結，選擇mysql_fdw 的包下載下傳

https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/

下載下傳完成後安裝：

[root@stephen ~]# rpm -ivh mysql_fdw_11-2.5.0-1.rhel7.x86_64.rpm 
Preparing... ################################# [100%]
Updating / installing...
   1:mysql_fdw_11-2.5.0-1.rhel7 ################################# [100%]           

安裝完成之後建立拓展：

postgres=# create extension mysql_fdw ;
CREATE EXTENSION
postgres=# \dx
                            List of installed extensions
   Name | Version | Schema | Description                    
-----------+---------+------------+--------------------------------------------------
 mysql_fdw | 1.1 | public | Foreign data wrapper for querying a MySQL server
 plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)           

可以看到插件拓展就安裝完成了

建立server

在PG端建立sever，外部服務定義了具體外部資料源的連接配接資訊，如果外部源是資料庫，通常會包含資料庫的IP，端口号，資料庫名稱等資訊，如下：

postgres=# CREATE SERVER mysql_server_test FOREIGN DATA WRAPPER mysql_fdw OPTIONS (HOST '172.16.21.167', PORT '3306');
CREATE SERVER

postgres=# \des
               List of foreign servers
       Name | Owner | Foreign-data wrapper 
-------------------+----------+----------------------
 mysql_server_test | postgres | mysql_fdw
(1 row)
           

建立使用者映射

OPTIONS 是指外部拓展的選項，指定了通路外部資料标的本地使用者和遠端使用者資訊

postgres=# CREATE USER MAPPING FOR PUBLIC SERVER mysql_server_test OPTIONS (username 'root', password 'xxxxx');
CREATE USER MAPPING
           

如果想修改

alter user MAPPING FOR public server mysql_server options ( set password 'xxxxxx');           

注：一些高版本的mysql需要修改密碼政策

将mysql的密碼政策改為mysql_native_password

alter user root@localhost identified with mysql_native_password by 'password';           

建立外部表

理論上外部表的字段隻要包含于遠端mysql表字段内就可以，但是一般的，都建議将外部表與遠端表的字段一緻。但是mysql和pg字段類型有時會有些許差別，需要确認好字段類型，如下：

postgres=# create foreign table test01(id int,name text,age int,wherefrom text ) server mysql_server_test options(dbname 'test',table_name 'test01');
CREATE FOREIGN TABLE

postgres=# \d
             List of relations
 Schema | Name | Type | Owner   
--------+--------+---------------+----------
 public | test01 | foreign table | postgres
(1 row)
           

可以看到表的類型是外部表，和普通的表有差別

建立完成後，時就可以在PG端通路這個表

postgres=# select * from test01 ;
 id | name | age | wherefrom 
----+----------+-----+-----------
  1 | dwqdw | 34 | dadsa
  2 | dwdsaqdw | 24 | dsadadsa
  3 | dwdsaqdw | 20 | dsadadsa
  4 | dwdsdadw | 20 | dsadaadsa
(4 rows)           

此時mysql端發生的變化

mysql> insert into test01 values(122,'測試',20,'再次測試');
Query OK, 1 row affected (0.01 sec)           

PG 再次查詢時這邊馬上就能看到

postgres=# select * from test01 ;
 id | name | age | wherefrom 
-----+----------+-----+-----------
   1 | dwqdw | 34 | dadsa
   2 | dwdsaqdw | 24 | dsadadsa
   3 | dwdsaqdw | 20 | dsadadsa
   4 | dwdsdadw | 20 | dsadaadsa
 122 | 測試 | 20 | 再次測試
(5 rows)           

反寫資料

一般情況下，如果沒有唯一鍵限制，反寫資料就會報錯

ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation           

如果想反寫到mysql，需要在mysql上添加表的限制

ALTER TABLE test01 ADD CONSTRAINT idx_id UNIQUE (id);（示例）           

我這邊是ID已經是主鍵唯一鍵了，是以直接能使用

postgres=# insert into test01 values(123,'反寫測試',221,'再次測試');
INSERT 0 1

mysql> select * from test01;
+-----+--------------+------+--------------+
| id | name | age | wherefrom |
+-----+--------------+------+--------------+
| 1 | dwqdw | 34 | dadsa |
| 2 | dwdsaqdw | 24 | dsadadsa |
| 3 | dwdsaqdw | 20 | dsadadsa |
| 4 | dwdsdadw | 20 | dsadaadsa |
| 122 | 測試 | 20 | 再次測試 |
| 123 | 反寫測試 | 221 | 再次測試 |
+-----+--------------+------+--------------+
6 rows in set (0.00 sec)
           

事實上現在不僅僅是支援insert語句，update與delete語句均支援，前提是提供給PG的mysql使用者是有這些權限的。

物化資料

mysql_fdw 實作的一個關鍵特性就是支援持久連接配接的能力。查詢執行後，不會删除與遠端MySQL的連接配接。相反，它保留來自同一會話的下次查詢連接配接。然而，在某些情況下，因為網絡查詢等原因不能及時查詢資料，則可以考慮在本地實作資料保留。可以通過外部表建立物化視圖，如下

postgres=# CREATE MATERIALIZED VIEW test_view as select * from test01;
SELECT 6
postgres=# \d
                 List of relations
 Schema | Name | Type | Owner   
--------+-----------+-------------------+----------
 public | test01 | foreign table | postgres
 public | test_view | materialized view | postgres
(2 rows)

postgres=# select * from test_view;
 id | name | age | wherefrom 
-----+----------+-----+-----------
   1 | dwqdw | 34 | dadsa
   2 | dwdsaqdw | 24 | dsadadsa
   3 | dwdsaqdw | 20 | dsadadsa
   4 | dwdsdadw | 20 | dsadaadsa
 122 | 測試 | 20 | 再次測試
 123 | 反寫測試 | 221 | 再次測試
(6 rows)           

可以将重新整理任務放到定時任務中，定時去重新整理視圖

REFRESH MATERIALIZED VIEW test_view;           

新特性

如今mysql_fdw 已經支援

Where子句下推：

最新版本将外部表where子句下推到外部伺服器。外部表上的where條件将在外部伺服器上執行，是以将有更少的行傳遞給PostgreSQL。這是一個性能功能。

列下推：

以前的版本是從目标外表中擷取所有列。最新版本執行列下推，僅傳回屬于選擇目标清單的列。這是一個性能功能。