PG 利用FDW的水平分片

最新推荐文章于 2024-04-01 06:38:39 发布

式微式微胡不归

最新推荐文章于 2024-04-01 06:38:39 发布

阅读量935

点赞数 1

分类专栏： postgresql

本文链接：https://blog.csdn.net/weixin_42767321/article/details/94135532

版权

postgresql 专栏收录该内容

33 篇文章 2 订阅

订阅专栏

利用FDW的水平分片

一、说明

架构采用一主多从，主节点和从节点之间采用FDW外部表的方式关联。利用PG的继承和分区表特性做到数据的水平分片。

在这里插入图片描述

二、实施验证

找五个服务器安装数据库，1一个master库存放全局数据和数据分片的定义，数据路由算法。4个下层节点数据库，用于存放分片数据。

207.121.127.106 admin_123 64位 CentOS6.2 TMS9500硬件 master
207.101.67.102 admin_123 64位 CentOS6.5
207.101.67.128 admin_123 64位 CentOS6.5
207.101.67.130 admin_123 64位 CentOS6.5
207.101.67.19 admin_123 64位 CentOS6.5

初始化，都创建数据库create database lpj;

连接到master库，创建外部server：

postgres=# \c lpj
You are now connected to database "lpj" as user "postgres".
lpj=# create extension postgres_fdw;
CREATE EXTENSION
lpj=# create server bm102 foreign data wrapper postgres_fdw options (hostaddr '207.101.67.102', port '5432', dbname 'lpj');
CREATE SERVER
lpj=# create server bm128 foreign data wrapper postgres_fdw options (hostaddr '207.101.67.128', port '5432', dbname 'lpj');
CREATE SERVER
lpj=# create server bm130 foreign data wrapper postgres_fdw options (hostaddr '207.101.67.130', port '5432', dbname 'lpj');
CREATE SERVER
lpj=# create server bm19 foreign data wrapper postgres_fdw options (hostaddr '207.101.67.19', port '5432', dbname 'lpj');
CREATE SERVER
lpj=#

创建user mapping
lpj=# create user mapping for postgres server bm102 options (user 'postgres', password 'passwd');
CREATE USER MAPPING
lpj=# create user mapping for postgres server bm128 options (user 'postgres', password 'passwd');
CREATE USER MAPPING
lpj=# create user mapping for postgres server bm130 options (user 'postgres', password 'passwd');
CREATE USER MAPPING
lpj=# create user mapping for postgres server bm19 options (user 'postgres', password 'passwd');
CREATE USER MAPPING
lpj=#

在四个外部server节点创建表tbl_test_bm*
--207.101.67.128
create table tbl_test_bm128(id int primary key, info text, crt_time timestamp);
alter table tbl_test_bm128 add constraint check_tbl_test_bm128 check (abs(mod(id,4))=0);

--207.101.67.130
create table tbl_test_bm130(id int primary key, info text, crt_time timestamp);
alter table tbl_test_bm130 add constraint check_tbl_test_bm130 check (abs(mod(id,4))=1);

--207.101.67.102
create table tbl_test_bm102(id int primary key, info text, crt_time timestamp);
alter table tbl_test_bm102 add constraint check_tbl_test_bm102 check (abs(mod(id,4))=2);

--207.101.67.19
create table tbl_test_bm19(id int primary key, info text, crt_time timestamp);
alter table tbl_test_bm19 add constraint check_tbl_test_bm19 check (abs(mod(id,4))=3);

连接到主节点，创建外部表，这里使用了import foreign schema语法，一键创建
lpj=# import FOREIGN SCHEMA public from server bm128 into public;
IMPORT FOREIGN SCHEMA
lpj=# import FOREIGN SCHEMA public from server bm130 into public;
IMPORT FOREIGN SCHEMA
lpj=# import FOREIGN SCHEMA public from server bm102 into public;
IMPORT FOREIGN SCHEMA
lpj=# import FOREIGN SCHEMA public from server bm19 into public;
IMPORT FOREIGN SCHEMA
lpj=# \det
      List of foreign tables
 Schema |     Table      | Server 
--------+----------------+--------
 public | tbl_test_bm102 | bm102
 public | tbl_test_bm128 | bm128
 public | tbl_test_bm130 | bm130
 public | tbl_test_bm19  | bm19
(4 rows)

lpj=#

创建主表，用户操作主表即可。当然用户也可以直接操作子表，这个特点结合BM业务可以将访问数据库直接移到自己本地数据库的查询修改上。

lpj=# create table tbl_test(id int, info text, crt_time timestamp);
CREATE TABLE
lpj=#

设置外部表的继承关系，继承到主表

alter foreign table tbl_test_bm128 inherit tbl_test;
alter foreign table tbl_test_bm130 inherit tbl_test;
alter foreign table tbl_test_bm102 inherit tbl_test;
alter foreign table tbl_test_bm19 inherit tbl_test;

创建外部表的约束，约束即路由算法的一部分。
注意，带约束条件的SQL，数据库会自动选择对应的外部表进行操作。
不带约束条件的SQL，数据库会选择所有节点操作。
所以建议每条SQL都带上约束条件。这一点和原先的分区表一样。

使用外部表的方式接入master节点的时候是不带约束的。

lpj=# \d tbl_test_bm19
                                Foreign table "public.tbl_test_bm19"
  Column  |            Type             | Collation | Nullable | Default |       FDW options        
----------+-----------------------------+-----------+----------+---------+--------------------------
 id       | integer                     |           | not null |         | (column_name 'id')
 info     | text                        |           |          |         | (column_name 'info')
 crt_time | timestamp without time zone |           |          |         | (column_name 'crt_time')
Server: bm19
FDW options: (schema_name 'public', table_name 'tbl_test_bm19')
Inherits: tbl_test

lpj=#

alter foreign table tbl_test_bm128 add constraint check_tbl_test_bm128 check (abs(mod(id,4))=0);
alter foreign table tbl_test_bm130 add constraint check_tbl_test_bm130 check (abs(mod(id,4))=1);
alter foreign table tbl_test_bm102 add constraint check_tbl_test_bm102 check (abs(mod(id,4))=2);
alter foreign table tbl_test_bm19 add constraint check_tbl_test_bm19 check (abs(mod(id,4))=3);

加完之后外部表在master节点上就有约束了

lpj=# \d tbl_test_bm19
                                Foreign table "public.tbl_test_bm19"
  Column  |            Type             | Collation | Nullable | Default |       FDW options        
----------+-----------------------------+-----------+----------+---------+--------------------------
 id       | integer                     |           | not null |         | (column_name 'id')
 info     | text                        |           |          |         | (column_name 'info')
 crt_time | timestamp without time zone |           |          |         | (column_name 'crt_time')
Check constraints:
    "check_tbl_test_bm19" CHECK (abs(mod(id, 4)) = 3)
Server: bm19
FDW options: (schema_name 'public', table_name 'tbl_test_bm19')
Inherits: tbl_test

lpj=#

由于约束是表达式需要带上表达式才能使约束直接起作用。（类似于表达式索引）

lpj=# explain select * from tbl_test where id=1;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Append  (cost=0.00..500.68 rows=25 width=44)
   ->  Seq Scan on tbl_test  (cost=0.00..0.00 rows=1 width=44)
         Filter: (id = 1)
   ->  Foreign Scan on tbl_test_bm128  (cost=100.00..125.17 rows=6 width=44)
   ->  Foreign Scan on tbl_test_bm130  (cost=100.00..125.17 rows=6 width=44)
   ->  Foreign Scan on tbl_test_bm102  (cost=100.00..125.17 rows=6 width=44)
   ->  Foreign Scan on tbl_test_bm19  (cost=100.00..125.17 rows=6 width=44)
(7 rows)

lpj=# explain select * from tbl_test where id=1 and abs(mod(id,4)) = (abs(mod(1,4)));
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Append  (cost=0.00..134.10 rows=2 width=44)
   ->  Seq Scan on tbl_test  (cost=0.00..0.00 rows=1 width=44)
         Filter: ((id = 1) AND (abs(mod(id, 4)) = 1))
   ->  Foreign Scan on tbl_test_bm130  (cost=100.00..134.10 rows=1 width=44)
(4 rows)

lpj=#

创建插入路由触发器函数

create or replace function f_tbl_ins() returns trigger as 
$$
declare
begin
  case abs(mod(NEW.id, 4))
    when 0 then
      insert into tbl_test_bm128 (id, info, crt_time) values (NEW.*); 
    when 1 then 
      insert into tbl_test_bm130 (id, info, crt_time) values (NEW.*); 
    when 2 then 
      insert into tbl_test_bm102 (id, info, crt_time) values (NEW.*);
    when 3 then 
      insert into tbl_test_bm19 (id, info, crt_time) values (NEW.*); 
    else 
      return null; 
  end case; 
    return null;
end;  
$$
 language plpgsql; 

create trigger tg1 before insert on tbl_test for each row execute procedure f_tbl_ins();

三、特性验证

1. 支持分布式事务：

出错全部回滚，可以回退外部表

insert into tbl_test values (1,'test1',now());
insert into tbl_test values (2,'test2',now());
update tbl_test set id=6;
ERROR: new row for relation "tbl_test_bm130" violates check constraint "check_tbl_test_bm130" DETAIL: Failing row contains (6, test_change, 2018-07-10 11:46:30.897977). CONTEXT: Remote SQL command: UPDATE public.tbl_test_bm130 SET id = 6 SQL 状态:23514

此时两条记录都不会被改动。

支持外部表事务退滚。

begin;
insert into tbl_test values (3,'test3',now());
select * from tbl_test;
rollback;

支持跨库事务和本地事务结合时的全局一致性
验证略

2. 支持绑定变量

lpj=# prepare p1 (int,text,timestamp) as insert into tbl_test values ($1,$2,$3);
PREPARE
lpj=# execute p1(4,'test4',now());
INSERT 0 0
lpj=# select * from tbl_test;
 id | info  |          crt_time          
----+-------+----------------------------
  4 | test4 | 2018-07-10 14:02:23.025278
  1 | test2 | 2018-07-10 11:46:30.897977
  2 | test2 | 2018-07-10 11:56:37.573761
(3 rows)

lpj=#

3. 支持外部表和外部表的跨库join，外部表、全局表的JOIN

验证略

4. 大查询性能消耗

待验证

四、扩展能力和性能

1. master和数据分片都可以水平扩展。

2. 性能可以随着节点数的增加线性提升。

3. 更适合OLTP。OLAP目前还是MPP做得比较好。

补充：
联机事务处理OLTP系统强调数据库内存效率，强调内存各种指标的命令率，强调绑定变量，强调并发操作；
联机分析处理OLAP系统则强调数据分析，强调SQL执行市场，强调磁盘I/O，强调分区等。

五、存在的缺点

1. truncate 目前不支持外部表

直接报错，不支持。

2. JOIN目前不能下推到数据节点执行

create table tbl_aa(info text,des text);
insert into tbl_aa values ('test2','aa');
insert into tbl_aa values ('test2','bb');
insert into tbl_aa values ('test4','cccc');
insert into tbl_aa values ('test4','dddd');
select * from tbl_test join tbl_aa using (info);

在这里插入图片描述
所以最好是所有的join查询能分解到下层节点单个库里面去。

3. 条件可以下推到数据节点

select * from tbl_test where substring(info,1,4)='test';

看执行计划是在每一个外部服务器执行带条件的查询再把数据发回master节点处理。

4. 我们之前的缓存机制基于listen和notify，无法直接适配，需要一定的修改。

六、疑问

1. （A union B）join C 和（A join C）union (B join C)是否等价？

怎么证明？反正法可证明，过程略。
如果等价则有：
(A union B)join (C union D)
= (A join （C union D））union (B join （C union D）)
= (A join C) union (A join D) union (B join C) union (B join D)
如果关联字段满足，A和C绑定，B和D绑定。即可简化为
= (A join C) union (B join D)
AC同一台服务器，BD同一台服务器，即可实现压力分摊。

2. 如果下层节点坏了影响有多大？

验证，当下层节点服务宕机后，上层节点直接从维度表里面查询直接报错。手动停掉128的服务。

lpj=# select * from tbl_test;
ERROR:  server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
CONTEXT:  Remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ

直接查询其他外部表是没有问题的。可以考虑当BM异常的时候将相关继承关系disable掉，待服务正常后再启用。
alter table tbl_test_bm128 NO INHERIT tbl_test;
上层节点的维度表可以继续使用，功能OK。
可以再加一层异常处理，如果出现此问题，可以判断所有的下层节点，将有问题的剔除，但是什么时间进行恢复将会是一个难题。

3. 是否支持外部表是分区表？

lpj=# insert into tbl_test values (9,'test_2',now());
ERROR:  function func_create_inherit_table(character varying, character varying, unknown, timestamp without time zone, interval) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  Remote SQL command: INSERT INTO public.tbl_test_bm130(id, info, crt_time) VALUES ($1, $2, $3)
PL/pgSQL function public.func_insert_tbl_test_bm130() line 19 at PERFORM
SQL statement "insert into tbl_test_bm130 (id, info, crt_time) values (NEW.*)"
PL/pgSQL function f_tbl_ins() line 8 at SQL statement

不支持，关键在于FDW不支持使用触发器？
经过验证读取是没有问题的，也就是说只要可以业务上保证入库在各个从节点上入库，且不需要做消息通知就不会有问题。
但实际这样的应用场景下，主节点不能insert（可以update）。

考虑先做按时间分表在做按bm分表是否可行？bm上再创建一个维度表（全局表）。通过维度表的冗余，应该可以实现。实际维度表只存储路由规则，不影响使用。

后面和守星确认我们业务不需要master节点的insert的操作，可以直接使用第一种方案，操作简单一些。

4. 是否支持上下层节点互相共享数据，部分配置类的表需要BM从原先IMOS数据库获取。

验证，上层节点里面有一张配置表tbl_aa，看能否在上层节点以下层节点作为外部服务器的情况下，将上层节点作为下层节点的外部服务器使用。确认混合使用是否有问题。
在下层节点130上，添加fdw配置。

create extension postgres_fdw;
create server master foreign data wrapper postgres_fdw options (hostaddr '207.101.127.106', port '5432', dbname 'lpj');
create user mapping for postgres server master options (user 'postgres', password 'passwd');
import FOREIGN SCHEMA public from server master into public;

卡死在这一步，应该是不支持。也可能是import 全部无法支持，这个待确认。实在不行可以使用dblink来实现，应该不会成为风险点。（还要分析这种情况下的一致性问题，是否会需要修改上层节点配置表？是否存在跨库join，bm目前确认存在跨库join，这样就需要考虑同步的方案。）

import foreign schema public limit to (tbl_aa)  from server master into public;  -- 也还是不行 
IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER server_name
    INTO local_schema
    [ OPTIONS ( option 'value' [, ... ] ) ]

5. postgres_fdw和file_fdw的区别

file_fdw模块提供了外部数据封装器file_fdw，可以用来在服务器的文件系统中访问数据文件。数据文件必须是COPY FROM 可读的格式
postgres_fdw模块提供外部数据封装器的功能，PostgreSQL通过它可以访问存储在外部的 PostgreSQL服务器上的数据。
本模块提供的功能不但涵盖老版本中dblink模块实现的功能，而且postgres_fdw提供更加透明和符合标准的语法来访问远程表，并在许多情况下提供更好的性能。

式微式微胡不归

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
PG 利用FDW的水平分片

利用FDW的水平分片一、说明架构采用一主多从，主节点和从节点之间采用FDW外部表的方式关联。利用PG的继承和分区表特性做到数据的水平分片。二、实施验证找五个服务器安装数据库，1一个master库存放全局数据和数据分片的定义，数据路由算法。4个下层节点数据库，用于存放分片数据。207.121.127.106 admin_123 64位 CentOS6.2 TMS9500硬件 ma...
复制链接

扫一扫