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>