PostgreSQL 9.6 單元化,sharding (based on postgres_fdw) - 核心層支援前傳

digoal

2016-10-04

postgresql , 9.6 , 水準分庫 , sharding , 單元化 , postgres_fdw , fdw , foreign data wrapper

postgresql 從 2011年的9.1版本引入fdw開始，發展到現在已經支援幾乎所有的外部資料源讀寫操作，例如mysql,oracle,pgsql,redis,mongo,hive,jdbc,odbc,file,sqlserver,es,s3,......。

<a href="https://wiki.postgresql.org/wiki/fdw">https://wiki.postgresql.org/wiki/fdw</a>

開放的接口，允許使用者自己添加外部資料源的支援。

9.6針對postgres_fdw（即postgresql外部資料源）再次增強，開始支援對sort, where, join的下推，支援remote cancel query, 使用者使用fdw可以對應用透明的實作資料庫的sharding，單元化需求。      

核心層支援sharding，這種分片技術相比中間件分片技術的好處：

1. 支援跨庫join

2. 支援綁定變量

3. 支援master(coordinator)節點水準擴充

4. 支援segment(datanode)節點水準擴充

5. 支援函數和存儲過程

ps: 不支援分布式事務（需要使用者幹預2pc）

通常業務設計時需要規避跨庫事務，或者使用者能夠容忍跨庫事務的一緻性問題。

架構如圖

1. 每種資料源，需要定義對應的fdw handler，例如mysql, oracle, pgsql都各自有各自的fdw handler。

本文接下來要講的是pgsql 的fdw handler : postgres_fdw。

2. 基于fdw handler，我們可以建立server，server代表你要通路的目标，在postgresql這個資料源中，server的粒度為database。

換句話說一個server對應一個外部的postgresql database。

3. 定義好server，我們可以建立外部表，映射到目标server中的可通路對象（外部表即一種到remote database中對象的映射關系，remote 對象可以是表，物化視圖，視圖，外部表等）。

4. 建立好外部表之後，如何通路呢？

當本地使用者通路一張外部表時，因為外部表代表的是目标server的可通路對象，而server隻是目标，并不包含通路目标server的認證資訊。

認證資訊在postgresql的fdw中成為user mapping，是登陸到外部server的認證資訊，local user對一個server隻能存儲一份認證資訊。

例如本地使用者為role_a, foreign server所在的資料庫叢集有3個使用者分别為rmt_a, rmt_b, rmt_c。

role_a同一時間對一個foreign server隻能存儲一個認證關系，即要麼使用rmt_a認證，要麼使用rmt_b，要麼使用rmt_c。

如果你要通路的遠端表分别屬于三個角色，那麼建議建立三個foreign server，在每個foreign server下建立對應的外部表，以及建立相應的映射關系。

例子

外部資料源如下

使用者

表

建立foreign server，目标一樣，名字不一樣

建立外部表，on 不同的foreign server

建立user mapping，每個foreign server對應不同的遠端使用者

當然你還有另一種選擇，在目标庫建立一個使用者，有這三張表的對應權限。

那麼隻需要一個foreign server，并且在建立user mapping時使用這個遠端使用者認證，這樣通路外部表的權限就正确了。

例如

postgres_fdw用法參考

<a href="https://www.postgresql.org/docs/9.6/static/postgres-fdw.html">https://www.postgresql.org/docs/9.6/static/postgres-fdw.html</a>

<a href="https://www.postgresql.org/docs/9.6/static/sql-createforeigndatawrapper.html">https://www.postgresql.org/docs/9.6/static/sql-createforeigndatawrapper.html</a>

<a href="https://www.postgresql.org/docs/9.6/static/sql-createserver.html">https://www.postgresql.org/docs/9.6/static/sql-createserver.html</a>

<a href="https://www.postgresql.org/docs/9.6/static/sql-createforeigntable.html">https://www.postgresql.org/docs/9.6/static/sql-createforeigntable.html</a>

<a href="https://www.postgresql.org/docs/9.6/static/sql-createusermapping.html">https://www.postgresql.org/docs/9.6/static/sql-createusermapping.html</a>

下面是詳解。

1. 文法

2. create server時支援的options

3. postgres_fdw額外支援的options

4. 用法舉例

2. create foreign table時支援的options

2. create user mapping時支援的options

3. 用法舉例

注意，隻有超級使用者支援無秘鑰認證，普通使用者需要提供密碼，是以當映射的遠端使用者為普通使用者時，必須提供密碼。

import foreign schema文法用于快速的将遠端資料庫的對象建立為本地的外部通路對象。

import foreign schema remote_schema [ { limit to | except } ( table_name [, ...] ) ] from server server_name into local_schema [ options ( option 'value' [, ... ] ) ]

2. options

将server s1的public schema中的table, view, foreign table, mview都import到本地schema ft中，作為外部表。

1. 遠端資料庫介紹

資料庫: rmt_db

使用者: digoal

schema: digoal

表: tbl1, tbl2

視圖，v1, v2

物化視圖, mv1, mv2

2. 本地資料庫介紹 資料庫: loc_db

使用者: test

schema: ft

3. 建立server

4. 建立user mapping

5. import foreign schema

6. 通路外部表

隻允許server和user mapping相同的外表join下推。

確定使用了同樣的使用者密碼，連接配接到了同樣的外部資料源。

目标同源，自然允許push down join。

除此之外，join 條件中用到的operation, function，必須是immutable的，并且是buildin的，或者在server中設定了extension時，屬于extension中的immutable function or operations.

是以，join push down的原則是，1、必須同源。 2、join條件必須使用内置或server指定extensions中的immutable function or operations。

<a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fbe5a3fb73102c2cfec11aaaa4a67943f4474383">https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fbe5a3fb73102c2cfec11aaaa4a67943f4474383</a>

代碼

在join時檢查外部表的源是否一緻，同時檢查user mapping是否一緻。

1. 增強create server文法，支援extension options。

意指遠端資料庫中也包含了這些extension，是以這些extension中的immutalbe function, operations在遠端目标庫也存在，用到這些function , operations時可以下推。

<a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d89494166351e1fdac77d87c6af500401deb2422">https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d89494166351e1fdac77d87c6af500401deb2422</a>

2. 允許遠端排序，不需要将資料全部收過來再排序。

consider performing sorts on the remote server (ashutosh bapat)

3. 允許遠端join，限制條件有2（1、必須同源（server+user mapping一緻）。 2、join條件必須使用内置或server指定extensions中的immutable function or operations。）

consider performing joins on the remote server (shigeru hanada, ashutosh bapat)

4. 當foreign table的dml query不需要本地處理任何資料時，可以将dml直接發送到remote database執行，而不需要通過發送select for update，再發送dml的方式。

5. 允許使用者設定server或foreign table的option fetch_size

如果表需要批量傳回很多資料，可以設定較大的值。

6. 當本地使用者對同一個server設定了同樣的遠端user時，可以使用單個連接配接。

use a single foreign-server connection for local user ids that all map to the same remote user (ashutosh bapat)

7. 當本地會話接收到cancel請求時，同時會發送cacnel 請求給目前會話正在查詢的remote database。

1. 本文主要講解了postgresql postgres_fdw的架構、用法以及9.6的增強。

目前postgres_fdw支援join\sort\where\dml的下推。

2. 結合postgresql的表繼承，使用者可以使用postgres_fdw實作資料庫的sharding，高效的解決了同資料源的dml，排序，join。

同時pg天然支援fdw與本地表，fdw表與fdw表的資料join，複雜查詢。 如果非頻繁的通路這種跨庫的join，也是不錯的(如果頻繁的話使用者可以選擇性的使用邏輯複制)。

後面的文章将重點根據postgres_fdw結合繼承，複制，講一下pg的sharding如何使用。

<a href="http://info.flagcounter.com/h9v1">count</a>