PostgreSQL中使用FDW+MV玩轉資料同步

一、問題的提出

經常有這樣的 case ，業務線開發同學需要 DBA 幫助其在不同的 PostgreSQL 資料庫或叢集之間同步資料。

資料量較小時，使用 pg_dump 工具或 copy 命令即可完成同步需求。但當資料量較大時，直接同步資料容易引發源 DB 或目標 DB 出現 slow query 或主從同步延遲等問題，容易對資料庫的效能及穩定性造成不良影響。

而且無論資料量大小與否，有時候手工或寫定時任務同步資料都很容易出錯，且目標DB的資料如果恰好有人在使用極易與同步資料的任務相互阻塞。

是否存在其他資料同步方案避免上述問題呢？透過調研加實踐，PostgreSQL中運用FDW與Materialized View可以較好的最佳化上述問題。

二、FDW

2003年，SQL/MED（SQL Management of External Data）作為新規範被新增到 SQL 標準中，它是處理從 SQL 資料庫訪問遠端物件的標準化方法。

PostgreSQL實現了部分的SQL/MED規範，2011年 PostgreSQL 9。1提供了對該規範的只讀支援，2013年 PostgreSQL 9。3 增加了對寫的支援。SQL/MED 規範的實現，允許我們使用普通 SQL 查詢來訪問位於 PostgreSQL 之外的資料。

PostgreSQL 透過 FDW（Foreign Data Wrapper），即外部資料包裝器來訪問外部資料。FDW 是一個可以與外部資料來源通訊的庫，它隱藏了連線到資料來源並從中獲取資料的細節。

PostgreSQL 官方提供的 FDW 有 postgres_fdw 和 file_fdw ，其他型別的 FDW 由第三方擴充套件實現。透過不同的 FDW ， PostgreSQL 能夠訪問不同的外部同構/異構資料來源，除了 PostgreSQL、Oracle、MySQL 等關係型資料庫，還支援訪問 MongoDB、Redis、Hive/Hadoop/HDFS、檔案等遠端資料儲存。

無論使用什麼型別的FDW，外部資料表只能從外部資料來源獲取資料，或者在更新命令的情況下傳送資料到外部資料來源。

在 FDW 出現之前，dblink 也可以實現遠端資料庫的訪問，但 FDW 是一種更現代和更加相容標準的架構，提供了更為強大的功能。

比如 dblink 無法使用源 DB 表上的 index ，而 FDW 會區分 where 或 join 等限制條件，將能夠傳送到 remote 端的限制條件傳送到 remote 端執行，此時就只需從 remote 端僅取回需要的資料。

PostgreSQL 9。5 支援使用 IMPORT FOREIGN SCHEMA 命令匯入整個 schema，PostgreSQL 9。6 已經支援 join 條件的 remote 端下推， PostgreSQL 10 已支援聚合函式的下推。PostgreSQL 14 還增加了使用 FDW 查詢 remote Database時的並行執行功能，postgres_fdw 還支援批次插入，使用 IMPORT FOREIGN SCHEMA 命令匯入分割槽表，以及外部表上的 TRUNCATE 命令。

可見 PostgreSQL 對 FDW 的支援越來越完善，功能越來越強大。

所以還在使用 PostgreSQL 9.x 的使用者，升級至 PostgreSQL 11+，是十分有必要且明智的選擇！

FDW同步資料示例：

可見，有了 FDW ，可以實現跨 DB ，或跨 DB 叢集，甚至跨DBMS（比如Qunar的廣告系統透過 Oracle_fdw 實現 PostgreSQL 訪問 Oracle 中的資料）的資料同步。

在 PostgreSQL 伺服器中外部表不儲存資料，只是指向外部資料來源的一個連結，資料依舊儲存在外部資料來源中。在本地庫透過訪問這張外部表，就可以訪問外部資料來源中相對應的待同步的表。

使用 FDW 同步資料時，真實的資料依然在源 DB ，主要的同步資料應用場景：

目標 DB 需要跨 DB 獲取實時資料，必須每次都使用FDW獲取最新資料

目標 DB 端的應用的 query 的速度不需要像執行在本地表上一樣快，且引數不固定，可能涉及到全量原始資料

但是有些不需要獲取源DB中實時資料的資料同步場景，尤其統計/報表等資料倉庫類的應用，基本是同步T-1的資料即可滿足需求，需要某一維度（比如按日期或按使用者）的聚合後的資料，且查詢的query 頻率雖然較高，但是引數和結果在一定時間內相對固定，有沒有更優的方案呢？

經過調研，答案是肯定的！

接下來我們就來了解一下開篇提到的 Materialized View 。

三、MV

View（檢視）是表示資料庫查詢結果的虛擬表。當定義一個檢視的時候，資料庫只儲存定義該檢視的查詢語句，每當查詢涉及該檢視時，資料庫就會將其轉換為已儲存的查詢表示式。因此無論我們何時執行這個查詢，檢視關係都被重新計算。

MV（Materialized View），即物化檢視，是一個其內容已計算並存儲的檢視。MV將查詢結果快取為一張具體的表，可以不時地從定義檢視的實際關係中更新。這使得訪問更加有效，但代價是額外的儲存和一些可能過期的資料。2013年 PostgreSQL 9。3 提供了對物化檢視的支援。

MV 帶來一個問題，就是它們必須能夠在檢視定義所使用的資料變化時保持更新。這種保持MV與原始資料同步更新的任務稱作檢視維護（ View Maintenance ）。不同的資料庫系統採取不同的檢視維護策略，PostgreSQL 中使用 REFRESH MATERIALIZED VIEW語句更新MV。

REFRESH MATERIALIZED VIEW table_name ；

不指定 CONCURRENTLY 選項時，使用更少的資源並且完成地更迅速，但是會鎖定其他應用從該物化檢視讀資料的連線。

REFRESH MATERIALIZED VIEW CONCURRENTLY table_name ；

使用 PostgreSQL 9。4 新支援的 CONCURRENTLY 選項可以在 refresh MV 時不阻塞對該物化檢視的查詢 query ，雖然 refresh 的速度會變的稍慢，但是這種以時間來換取查詢鎖還是值得的。

物化檢視使用示例：

四、FDW+MV

如何運用 FDW 與 Materialized View 二者的組合拳來解決前述場景的資料同步問題呢？

資料同步方案的架構圖如下：

在該方案中，透過外部資料包裝器postgres_fdw訪問外部資料，左側是一個外部資料來源，包含一張待同步的表（instructor），右側是在本地建立的一張外部表（instructor_fdw）。

方案具體示例如下：

可見，在外部表 instructor_fdw 上定義一個 MV ： instructor_mv ，即將符合業務需求的資料以 MV 的形式週期性持久化在本地庫中，至此資料同步的目標已實現。

此後日常的資料同步將變得很簡單，只需要定時 refresh MV 即可。

五、小結

現簡要對比總結一下各種資料同步的方案的適用主要場景：

方案

適用場景

pg_dump&&reload

同步資料量較小，dump && reload一次至目標DB所耗時間和資源成本低

目標DB端的應用可以接受一定的資料時延

query五花八門，且引數不固定，需要源DB全量資料，甚至需要和其他本地表join，且對速度有一定要求

FDW

源DB資料量巨大，dump && reload一次至目標DB太耗時且耗資源

目標DB需要跨DB獲取實時資料，即使資料量不是特別大，但必須每次都使用FDW獲取最新資料，如訂單detail頁相關的查詢

目標DB端的應用的query的速度不需要像本地一樣快，且引數不固定，可能涉及到全量資料

FDW+MV

源DB資料量巨大，dump && reload一次至目標DB太耗時且耗資源

目標DB端的應用可以接受一定的資料時延，如資料倉庫類的應用，同步T-1的資料

目標端需要透過FDW運算資料，甚至是透過FDW和本地表join後才可以得到特定的聚合資料

目標端應用對query速度有一定要求，需要將結果資料持久化至本地

透過運用 PostgreSQL 中獨有的 FDW 與 MV ，為特定場景下的資料同步提供了一種全新的方案， FDW+MV 二者靈活組合運用，效果更佳！

例項

Qunar有很多運用FDW+MV組合拳進行資料同步的例項，下面看一個某業務線訂單相關資料同步到產品例項的實踐案例。

——源DBorder_product=# \！ hostnameremotehostorder_product=# \dt+ order_detail List of relations Schema | Name | Type | Owner | Size | Description————+————————+————-+——————+————-+——————- public | order_detail | table | postgres | 20 GB |（1 row） ——目標DBb2c_product=# \des List of foreign servers Name | Owner | Foreign-data wrapper——————————-+————-+———————————— remote_order_server | pgdba | postgres_fdw（1 row） b2c_product=# \deu List of user mappings Server | User name——————————-+——————- remote_order_server | pgdba（1 row） b2c_product=# \det+ order_detail List of foreign tables Schema | Table | Server | FDW options | Description ————+————————+——————————-+——————————————————————————-+——————————- public | order_detail | remote_order_server | （schema_name ‘public’， table_name ‘order_detail’） | 遠端訂單庫訂單明細外部表（1 row） b2c_product=# \dm+ order_product_report List of relations Schema | Name | Type | Owner | Size | Description————+————————————+——————————-+————-+————-+——————- public | order_product_report | materialized view | pgdba | 0 bytes | 產品當年銷量物化檢視（1 row） b2c_product=# \d+ order_product_report Materialized view “public。order_product_report” Column | Type | Collation | Nullable | Default | Storage | Stats target | Description——————+————-+——————-+——————+————-+————-+————————+——————- product_id | integer | | | | plain | | count | bigint | | | | plain | |View definition： SELECT order_detail。product_id， count（1） AS count FROM order_detail WHERE order_detail。create_time >= ‘2021-01-01 00：00：00+08’：：timestamp with time zone GROUP BY order_detail。product_id； b2c_product=# refresh materialized view order_product_report；REFRESH MATERIALIZED VIEWTime： 542。331 ms ——refresh materialized view CONCURRENTLY 必須有unique indexb2c_product=# refresh materialized view CONCURRENTLY order_product_report；ERROR： cannot refresh materialized view “public。order_product_report” concurrentlyHINT： Create a unique index with no WHERE clause on one or more columns of the materialized view。Time： 0。375 ms b2c_product=# create unique index CONCURRENTLY ON order_product_report（product_id）；CREATE INDEXTime： 25。973 ms b2c_product=# refresh materialized view CONCURRENTLY order_product_report；REFRESH MATERIALIZED VIEWTime： 681。510 ms b2c_product=# select count（1） from order_product_report； count————-666888（1 row） Time： 4。488 ms ——查詢當年銷量大於100單的產品b2c_product=# select title from product pjoin order_product_report o on p。id = o。product_idwhere count >100 and p。title ~ ‘北京’order by random（）limit 1； title ————————————————————- 北京八達嶺長城+頤和園+鳥巢純玩品質跟團一日遊（1 row） Time： 11。478 ms

可見，源 DB 中 20 GB 的表，透過 FDW +MV ，完美的將所需的外部遠端例項訂單庫中的資料正確且高效的同步至產品庫中。

與傳統的資料同步方案相比，在效能和可維護性等方面具有非常強大的優勢！