PostgreSQL的遠端資料操作—postgres_fdw

jackson198574發表於2015-12-22
SQL
PostgreSQL提供了外部資料包裝器postgres_fdw,作用跟dblink相同，即查詢遠端資料庫中的資料資訊，但是
postgres_fdw比dblink在某些場景更穩定、更方便。同時PostgreSQL也提供對其他資料庫如Oracle和MySQL等資料庫的外
部資料包裝器:oracle_fdw和mysql_fdw，可查詢Oracle和MySQL資料庫中的相關表資訊。
  注意，不論使用PG的哪種外部資料包裝器，儘可能的保證兩端的表中欄位的數量、型別和順序一致，否則可能導致很多問題。
下面我們來體驗一下該功能：
測試環境準備：
在遠端資料庫上建立新的資料庫musician，並在庫裡建立表man，插入測試資料：
postgres=# create database musician;
CREATE DATABASE
music=# c musician eric
您現在已經連線到資料庫 “musician”,使用者 “eric”.
musician=> create table man(id bigint);
CREATE TABLE
musician=> insert into man select * from generate_series(1,8000);
INSERT 0 8000
musician=> select count(*) from man;
 count 
——-
  8000
(1 行記錄)
musician=> d
             關聯列表
 架構模式 | 名稱 |  型別  | 擁有者 
———-+——+——–+——–
 public   | man  | 資料表 | eric
(1 行記錄)
在本地測試庫安裝外掛postgres_fdw：
postgres=# create extension postgres_fdw;
CREATE EXTENSION
music=> c music postgres
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 musician_fdw_server foreign data wrapper postgres_fdw options
(host `192.168.1.129`,dbname `musician`,port `5432`);
CREATE SERVER
建立使用者對映，相關資訊：
本地使用者：eric
外部伺服器物件：musician_fdw_server
遠端資料庫使用者名稱密碼：eric,gao
music=#  create user mapping for eric  server musician_fdw_server options (user `eric`,password `gao`);
CREATE USER MAPPING
配置外部表,相關資訊：
外部表在本庫的名稱：manid
外部伺服器：musician_fdw_server
外部表名：man
music=> c music postgres
You are now connected to database “music” as user “postgres”.
music=# create foreign table manid(id bigint) server musician_fdw_server options(table_name `man`);
CREATE FOREIGN TABLE
注意：
  在遠端資料庫的pg_hba.conf中修改一下相關的配置：
最終這樣修改的：
# IPv4 local connections:
host    all           all            192.168.1.0/24              md5
因為遠端連線的話，PG要求是需要有密碼驗證的，設定成trust的話會報錯。
設定完成之後驗證一下查詢效果：
music=> c music eric
You are now connected to database “music” as user “postgres”.
music=# select count(*) from manid;
 count 
——-
  8000
(1 row)
驗證一下刪除和插入操作：
從本地刪除遠端資料庫musician中表man的所有資料：
music=> c music postgres
You are now connected to database “music” as user “postgres”.
music=# delete from manid;
DELETE 10000
在遠端資料庫執行查詢資料條目：
musician=> select count(*) from man;
 count 
——-
     0
(1 行記錄)
資料已全部清除。
從本地向遠端資料庫musician中的表man插入1萬條資料：
music=# insert into manid select * from generate_series(1,10000);
INSERT 0 10000
在遠端資料庫中看到1萬條資料已入賬：
musician=> select count(*) from man;
 count 
——-
 10000
(1 行記錄)
資料是可以看到了，效能如何呢？我們來測試一下：
在遠端資料庫本地執行語句：
musician=> explain analyze select count(*) from man;
                                                 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: 26.189 ms
(4 行記錄)
在本地資料庫本地執行語句：
music=> explain analyze select count(*) from manid;
                                                     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: 44.411 ms
(4 rows)
看起來差別不是太大，但是測試的資料量和型別也不復雜，那我們接下來換一條語句：
遠端資料庫本地執行語句：
musician=> explain analyze select * from man;
                                              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: 18.758 ms
(3 行記錄)
本地資料庫本地執行語句：
music=> explain analyze select * from manid;
                                                  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: 64.936 ms
(3 rows)
看起來差別還是比較明顯的，更別提用到量大且複雜的生產環境中了。如果是該查詢用的不頻繁並且查詢的量不大不復雜，客戶也可以忍受響應速度，那這樣就OK。
如果對響應速度有相對較高的要求，則需要使用另一種武器：物化檢視。
物化檢視可以理解為是對目標表格的一個副本，可能是一模一樣的，也可能是經過篩選的。本次我們們為了改善效能，簡單的建立一個跟遠端資料庫表格一模一樣的物化檢視：
在本地資料庫建立物化檢視：
物化檢視名稱為：mv_manid，通過該檢視儲存manid表能查到的資料的實體：
music=> create materialized view mv_manid as select * from manid;
SELECT 8000   —資料條目跟剛才一樣為8千條
檢視一下物化檢視的效能如何：
music=> explain analyze select * from mv_manid;
                                                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: 2.864 ms
(3 rows)
music=> explain analyze select count(*) from mv_manid;
                                                   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: 1.363 ms
(4 rows)
比manid的強不少吧？~~~
物化檢視需要對錶進行重新整理才能同步遠端表的資料：
在遠端資料庫表裡插入新資料：
musician=> insert into man select * from generate_series(8001,10000);
INSERT 0 2000
musician=> select count(*) from man;
 count 
——-
 10000
(1 行記錄)
本地庫查詢發現還是8千條資料：
music=> select count(*) from mv_manid;
 count 
——-
  8000
(1 row)
重新整理一下本地的物化檢視即可看到新進來的資料：
music=> refresh materialized view mv_manid;
REFRESH MATERIALIZED VIEW
music=> select count(*) from mv_manid;
 count 
——-
 10000
(1 row)
OK!~


相關文章