PostgreSQL的遠端資料操作---postgres_fdw

postgresql提供了外部資料包裝器postgres_fdw,作用跟dblink相同，即查詢遠端資料庫中的資料資訊，但是

postgres_fdw比dblink在某些場景更穩定、更友善。同時postgresql也提供對其他資料庫如oracle和mysql等資料庫的外

部資料包裝器:oracle_fdw和mysql_fdw，可查詢oracle和mysql資料庫中的相關表資訊。

  注意，不論使用pg的哪種外部資料包裝器，盡可能的保證兩端的表中字段的數量、類型和順序一緻，否則可能導緻很多問題。

下面我們來體驗一下該功能：

<b>測試環境準備：</b>

在遠端資料庫上建立新的資料庫musician，并在庫裡建立表man，插入測試資料：

postgres=# create database musician;

create database

music=# \c musician eric

您現在已經連線到資料庫 "musician",使用者 "eric".

musician=&gt; create table man(id bigint);

create table

musician=&gt; insert into man select * from generate_series(1,8000);

insert 0 8000

musician=&gt; select count(*) from man;

 count 

-------

  8000

(1 行記錄)

musician=&gt; \d

             關聯清單

 架構模式 | 名稱 |  型别  | 擁有者 

----------+------+--------+--------

 public   | man  | 資料表 | eric

<b>在本地測試庫安裝插件postgres_fdw：</b>

postgres=# create extension postgres_fdw;

create extension

music=&gt; \c music postgres

you are now connected to database "music" as user "postgres".

<b>建立外部伺服器對象，需要指定相關資訊：</b>

對象名稱：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

<b>建立使用者映射，相關資訊：</b>

本地使用者：eric

外部伺服器對象：musician_fdw_server

遠端資料庫使用者名密碼：eric,gao

music=#  create user mapping for eric  server musician_fdw_server options (user 'eric',password 'gao');

create user mapping

<b>配置外部表,相關資訊：</b>

外部表在本庫的名稱：manid

外部伺服器：musician_fdw_server

外部表名：man

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的話會報錯。

<b>設定完成之後驗證一下查詢效果：</b>

music=&gt; \c music eric

music=# select count(*) from manid;

(1 row)

<b>驗證一下删除和插入操作：</b>

從本地删除遠端資料庫musician中表man的所有資料：

music=# delete from manid;

delete 10000

在遠端資料庫執行查詢資料條目：

資料已全部清除。

從本地向遠端資料庫musician中的表man插入1萬條資料：

music=# insert into manid select * from generate_series(1,10000);

insert 0 10000

在遠端資料庫中看到1萬條資料已入賬：

 10000

<b>資料是可以看到了，性能如何呢？我們來測試一下：</b>

在遠端資料庫本地執行語句：

musician=&gt; 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)

   -&gt;  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=&gt; 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)

   -&gt;  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=&gt; 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=&gt; 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。

<b>如果對響應速度有相對較高的要求，則需要使用另一種武器：物化視圖。</b>

物化視圖可以了解為是對目标表格的一個副本，可能是一模一樣的，也可能是經過篩選的。本次咱們為了改善性能，簡單的建立一個跟遠端資料庫表格一模一樣的物化視圖：

<b>在本地資料庫建立物化視圖：</b>

物化視圖名稱為：mv_manid，通過該視圖儲存manid表能查到的資料的實體：

music=&gt; create materialized view mv_manid as select * from manid;

select 8000   ---資料條目跟剛才一樣為8千條

檢視一下物化視圖的性能如何：

music=&gt; 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

music=&gt; 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)

   -&gt;  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

比manid的強不少吧？~~~

<b>物化視圖需要對表進行重新整理才能同步遠端表的資料：</b>

在遠端資料庫表裡插入新資料：

musician=&gt; insert into man select * from generate_series(8001,10000);

insert 0 2000

本地庫查詢發現還是8千條資料：

music=&gt; select count(*) from mv_manid;

重新整理一下本地的物化視圖即可看到新進來的資料：

music=&gt; refresh materialized view mv_manid;

refresh materialized view

<b>ok!~</b>