702
PostgreSQL的遠程數據操作---postgres_fdw
PostgreSQL提供了外部數據包裝器postgres_fdw,作用跟dblink相同，即查詢遠程數據庫中的數據信息，但是 postgres_fdw比dblink在某些場景更穩定、更方便。同時PostgreSQL也提供對其他數據庫如Oracle和MySQL等數據庫的外 部數據包裝器:oracle_fdw和mysql_fdw，可查詢Oracle和MySQL數據庫中的相關表信息。 注意，不論使用PG的哪種外部數據包裝器，盡可能的保證兩端的表中字段的數量、類型和順序一致，否則可能導致很多問題。
下麵我們來體驗一下該功能：
測試環境準備：
在遠程數據庫上創建新的數據庫musician，並在庫裏創建表man，插入測試數據：
postgres=#
CREATE DATABASE
music=#
您現在已經連線到數據庫 "musician",用戶 "eric".
musician=>
CREATE TABLE
musician=>
INSERT 0 8000
musician=>
count
-------
8000
(1 行記錄)
musician=>
關聯列表
架構模式 | 名稱 | 型別 | 擁有者
----------+------+--------+--------
public | man | 資料表 | eric
(1 行記錄)
在本地測試庫安裝插件postgres_fdw：
postgres=#
CREATE EXTENSION
music=>
You are now connected to database "music" as user "postgres".
創建外部服務器對象，需要指定相關信息：
對象名稱：musician_fdw_server
包裝器類型：postgres_fdw，如果要連接Oracle或者MySQL數據庫的話，可用oracle_fdw或mysql_fdw
主機IP:192.168.1.129
數據庫名稱：musician（剛剛創建的數據庫名）
端口號：5432
music=#
CREATE SERVER
創建用戶映射，相關信息：
本地用戶：eric
外部服務器對象：musician_fdw_server
遠程數據庫用戶名密碼：eric,gao
music=#
CREATE USER MAPPING
配置外部表,相關信息：
外部表在本庫的名稱：manid
外部服務器：musician_fdw_server
外部表名：man
music=>
You are now connected to database "music" as user "postgres".
music=#
CREATE FOREIGN TABLE
注意：
在遠程數據庫的pg_hba.conf中修改一下相關的配置：
最終這樣修改的：
# IPv4 local connections:
host all all 192.168.1.0/24 md5
因為遠程連接的話，PG要求是需要有密碼驗證的，設置成trust的話會報錯。
設置完成之後驗證一下查詢效果：
music=>
You are now connected to database "music" as user "postgres".
music=#
count
-------
8000
(1 row)
驗證一下刪除和插入操作：
從本地刪除遠程數據庫musician中表man的所有數據：
music=>
You are now connected to database "music" as user "postgres".
music=#
DELETE 10000
在遠程數據庫執行查詢數據條目：
musician=>
count
-------
0
(1 行記錄)
數據已全部清除。
從本地向遠程數據庫musician中的表man插入1萬條數據：
music=#
INSERT 0 10000
在遠程數據庫中看到1萬條數據已入賬：
musician=>
count
-------
10000
(1 行記錄)
在遠程數據庫本地執行語句：
musician=>
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Aggregate (cost=136.00..136.01 rows=1 width=0) (actual time=26.128..26.129 rows=1 loops=1)
-> Seq Scan on man (cost=0.00..116.00 rows=8000 width=0) (actual time=0.014..13.068 rows=8000 loops=1)
Planning time: 0.045 ms
Execution time:
(4 行記錄)
在本地數據庫本地執行語句：
music=>
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=220.92..220.93 rows=1 width=0) (actual time=42.804..42.804 rows=1 loops=1)
-> Foreign Scan on manid (cost=100.00..212.39 rows=3413 width=0) (actual time=2.264..41.813 rows=8000 loops=1)
Planning time: 0.067 ms
Execution time:
(4 rows)
看起來差別不是太大，但是測試的數據量和類型也不複雜，那我們接下來換一條語句：
遠程數據庫本地執行語句：
musician=>
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on man (cost=0.00..116.00 rows=8000 width=8) (actual time=0.012..10.277 rows=8000 loops=1)
Planning time: 0.036 ms
Execution time:
(3 行記錄)
本地數據庫本地執行語句：
music=>
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Foreign Scan on manid (cost=100.00..186.80 rows=2560 width=8) (actual time=14.445..60.194 rows=8000 loops=1)
Planning time: 12.400 ms
Execution time:
(3 rows)
看起來差別還是比較明顯的，更別提用到量大且複雜的生產環境中了。如果是該查詢用的不頻繁並且查詢的量不大不複雜，客戶也可以忍受響應速度，那這樣就OK。
物化視圖可以理解為是對目標表格的一個副本，可能是一模一樣的，也可能是經過篩選的。本次咱們為了改善性能，簡單的創建一個跟遠程數據庫表格一模一樣的物化視圖：
在本地數據庫創建物化視圖：
物化視圖名稱為：mv_manid，通過該視圖保存manid表能查到的數據的實體：
music=>
SELECT 8000 ---數據條目跟剛才一樣為8千條
查看一下物化視圖的性能如何：
music=>
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on mv_manid (cost=0.00..113.04 rows=7704 width=8) (actual time=0.024..1.823 rows=8000 loops=1)
Planning time: 0.254 ms
Execution time:
(3 rows)
music=>
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Aggregate (cost=132.30..132.31 rows=1 width=0) (actual time=1.336..1.336 rows=1 loops=1)
-> Seq Scan on mv_manid (cost=0.00..113.04 rows=7704 width=0) (actual time=0.010..0.738 rows=8000 loops=1)
Planning time: 0.032 ms
Execution time:
(4 rows)
比manid的強不少吧？~~~
物化視圖需要對表進行刷新才能同步遠程表的數據：
在遠程數據庫表裏插入新數據：
musician=>
INSERT 0 2000
musician=>
count
-------
10000
(1 行記錄)
本地庫查詢發現還是8千條數據：
music=>
count
-------
8000
(1 row)
刷新一下本地的物化視圖即可看到新進來的數據：
music=>
REFRESH MATERIALIZED VIEW
music=>
count
-------
10000
(1 row)
最後更新：2017-04-01 13:44:33