PostgreSQL sharding for Oracle, SQL Server, DB2, Sybase-阿里云开发者社区

开发者社区> 德哥> 正文

PostgreSQL sharding for Oracle, SQL Server, DB2, Sybase

简介:
+关注继续查看

Oracle 12c支持sharding,但是对于低版本,如何实现水平分库呢?
在写PostgreSQL 水平分库方案时,想到一招。何不利用PostgreSQL的分片技术来实现对Oracle的分片呢?
分库技术架构和实践请参考:
http://blog.163.com/digoal@126/blog/static/16387704020161239252998/

如果要做到对Oracle用户完全透明,需要满足几个条件:

  1. PostgreSQL必须支持Oracle的SQL语法,这一点 EnterpriseDB 可以满足需求。
  2. PostgreSQL必须支持Oracle的存储过程和函数,以及包。这一点 EnterpriseDB 可以满足需求。
    如果用户愿意修改不兼容的SQL和函数,使用社区版本的 PostgreSQL 就能满足分片需求了。

分片架构如下:
可以支持几乎任何数据库的分片。
_2
分为两层:

  1. 上层为PostgreSQL 或 EnterpriseDB(如果需要兼容Oracle特殊语法),在上层节点中,需要存储表的定义,路由算法,函数,存储过程,视图,序列等全局数据。
    上层的PostgreSQL数据库可以有1个或者多个,最少1个。
  2. 下层为数据分片节点,可以是任何数据库品种,譬如图中所述的Oracle, DB2, Sybase, SQL Server。在分片节点中,存储数据分片,维度表(用户可以自定义维度表的副本数)。

注意,如果要支持函数,必须将原数据库的函数转换为PostgreSQL的函数,在PostgreSQL中可以使用plpgsql语言来实现,包括自治事务也能实现
(参考 http://blog.163.com/digoal@126/blog/static/163877040201613982063/ )。
如果使用EnterpriseDB,则大多数的Oracle函数语法都兼容,用户可以不需要修改,直接使用。

以Oracle为例,介绍实施步骤:
.1. 安装Oracle数据节点,这里假设有4台Oracle数据库,分别为db0,db1,db2,db3。
.2. 安装一台PostgreSQL 9.5+ 以及 oracle_fdw插件。

插件位置:http://pgxn.org/dist/oracle_fdw/  
内含详细说明,推荐阅读。  
http://blog.163.com/digoal@126/blog/static/163877040201181505331588/  
安装好后,设置正确的 NLS_LANG 环境变量(<language>_<territory>.<charset> (for example  AMERICAN_AMERICA.AL32UTF8)),重启数据库。  

.3. 配置oracle数据库监听,以及主机防火墙,允许PostgreSQL数据库访问Oracle数据库。
.4. 在PostgreSQL数据库中创建所有数据节点的foreign server, 本例需要4个foreign server, user mapping。
例如 (请使用正确的 IP,端口和sid, username, password替换) :

master=# create extension oracle_fdw;    
master=# create server db0 foreign data wrapper oracle_fdw OPTIONS (dbserver '//ip:port/sid');    
master=# create server db0 foreign data wrapper oracle_fdw OPTIONS (dbserver '//ip:port/sid');    
master=# create server db0 foreign data wrapper oracle_fdw OPTIONS (dbserver '//ip:port/sid');    
master=# create server db0 foreign data wrapper oracle_fdw OPTIONS (dbserver '//ip:port/sid');    
master=# create user mapping for postgres server db0 options (user 'username', password 'pwd');    
master=# create user mapping for postgres server db1 options (user 'username', password 'pwd');    
master=# create user mapping for postgres server db2 options (user 'username', password 'pwd');    
master=# create user mapping for postgres server db3 options (user 'username', password 'pwd');    

.5. 规划表分区的分布列,如果分布列不是INT类型,可以使用hash函数转换为INT。按abs(mod(column,4))的值计算分布规则。
.6. 在所有的数据节点db[0-3],创建需要分片的表,以及分布列的 check 约束。
例如:

on db0:  
create table tbl ( id int primary key , info varchar2(32), crt_time date, check (abs(mod(id,4))=0));  
on db1:  
create table tbl ( id int primary key , info varchar2(32), crt_time date, check (abs(mod(id,4))=1));  
on db2:  
create table tbl ( id int primary key , info varchar2(32), crt_time date, check (abs(mod(id,4))=2));  
on db3:  
create table tbl ( id int primary key , info varchar2(32), crt_time date, check (abs(mod(id,4))=3));  

.7. 规划维度表的副本数,本文例子假设维度表有2个副本,分别放在db0, db1。
.8. 在数据节点db0, db1创建维度表。
例如:

on db0:  
create table test ( id int primary key, info varchar2(32), crt_time date);  
on db1:  
create table test ( id int primary key, info varchar2(32), crt_time date);  

.9. 在PostgreSQL节点,创建分片表的外部表,必须包含CHECN约束。必须制定KEY,否则不能写。

create FOREIGN table tbl0 (id int OPTIONS (key 'true') , info varchar(32), crt_time timestamp without time zone) server db0 options (table 'tbl', schema 'username');  
create FOREIGN table tbl1 (id int OPTIONS (key 'true') , info varchar(32), crt_time timestamp without time zone) server db1 options (table 'tbl', schema 'username');  
create FOREIGN table tbl2 (id int OPTIONS (key 'true') , info varchar(32), crt_time timestamp without time zone) server db2 options (table 'tbl', schema 'username');  
create FOREIGN table tbl3 (id int OPTIONS (key 'true') , info varchar(32), crt_time timestamp without time zone) server db3 options (table 'tbl', schema 'username');  
alter foreign table tbl0 add constraint ck_tbl0 check (abs(mod(id,4))=0);    
alter foreign table tbl1 add constraint ck_tbl1 check (abs(mod(id,4))=1);    
alter foreign table tbl2 add constraint ck_tbl2 check (abs(mod(id,4))=2);    
alter foreign table tbl3 add constraint ck_tbl3 check (abs(mod(id,4))=3);    

.10. 在PostgreSQL节点,创建维度表的外部表

 create FOREIGN table test0 (id int OPTIONS (key 'true'), info varchar(32), crt_time timestamp without time zone) server db0 options (table 'test', schema 'username');  
 create FOREIGN table test1 (id int OPTIONS (key 'true'), info varchar(32), crt_time timestamp without time zone) server db1 options (table 'test', schema 'username');  

.11. 在PostgreSQL节点,创建分片表的父表,设置继承关系,触发器函数,触发器。

create table tbl (id int primary key, info varchar(32), crt_time timestamp without time zone);  
alter foreign table tbl0 inherit tbl;    
alter foreign table tbl1 inherit tbl;    
alter foreign table tbl2 inherit tbl;    
alter foreign table tbl3 inherit tbl;    
create or replace function f_tbl_ins() returns trigger as 
$$
    
declare    
begin    
  case abs(mod(NEW.id, 4))     
    when 0 then    
      insert into tbl0 (id, info, crt_time) values (NEW.*);    
    when 1 then    
      insert into tbl1 (id, info, crt_time) values (NEW.*);    
    when 2 then    
      insert into tbl2 (id, info, crt_time) values (NEW.*);    
    when 3 then    
      insert into tbl3 (id, info, crt_time) values (NEW.*);    
    else    
      return null;    
  end case;    
    return null;    
end;    

$$
 language plpgsql;    
create trigger tg1 before insert on tbl for each row execute procedure f_tbl_ins();    

.12. 在PostgreSQL节点,创建维度表的父表,设置继承关系,触发器函数,触发器。

create table test (id int primary key, info varchar(32), crt_time timestamp without time zone);  
alter foreign table test0 inherit test;    
-- 在不同的master节点,设置不同的继承,从而实现均衡查询的目的,目前PG内核还不支持维度表的负载均衡。  
create or replace function f_test_iud() returns trigger as 
$$
    
declare    
begin    
  case TG_OP  
    when 'INSERT' then  
      insert into test0 (id, info, crt_time) values (NEW.*);    
      insert into test1 (id, info, crt_time) values (NEW.*);    
    when 'UPDATE' then  
      update test0 set id=NEW.id,info=NEW.info,crt_time=NEW.crt_time where id=OLD.id and info=OLD.info and crt_time=OLD.crt_time;  
      update test1 set id=NEW.id,info=NEW.info,crt_time=NEW.crt_time where id=OLD.id and info=OLD.info and crt_time=OLD.crt_time;  
    when 'DELETE' then  
      delete from test0 where id=OLD.id and info=OLD.info and crt_time=OLD.crt_time;  
      delete from test1 where id=OLD.id and info=OLD.info and crt_time=OLD.crt_time;  
  end case;  
    return null;    
end;    

$$
 language plpgsql;    
create trigger tg1 before insert or update or delete on test for each row execute procedure f_test_iud();    

现在,你可以测试这些表的插入,查询,更新,删除,JOIN。以及分布式事务。
插入tbl这个分片表时,会根据ID计算一个模值,插入到对应的分片节点。
更新,删除,查询时,如果提供了ID的模值,则会选择对应的子节点查询。
对于维度表test,查询时会自动查询test0, 更新,删除,插入则会在test0,test1同时操作 (非并行)。

使用这种方法给其他数据库做sharding, 除了EDB对Oracle兼容性比较好,其他的兼容性都需要用户去验证。
但是不管怎么样,用户可以获得如下好处:
ACID
分布式事务
跨库JOIN
主节点和数据节点都支持水平扩展
prepared statement
支持存储过程和函数

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
怎么设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程
6845 0
使用OpenApi弹性释放和设置云服务器ECS释放
云服务器ECS的一个重要特性就是按需创建资源。您可以在业务高峰期按需弹性的自定义规则进行资源创建,在完成业务计算的时候释放资源。本篇将提供几个Tips帮助您更加容易和自动化的完成云服务器的释放和弹性设置。
7737 0
阿里云服务器安全组设置内网互通的方法
虽然0.0.0.0/0使用非常方便,但是发现很多同学使用它来做内网互通,这是有安全风险的,实例有可能会在经典网络被内网IP访问到。下面介绍一下四种安全的内网互联设置方法。 购买前请先:领取阿里云幸运券,有很多优惠,可到下文中领取。
9407 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
5412 0
腾讯云服务器 设置ngxin + fastdfs +tomcat 开机自启动
在tomcat中新建一个可以启动的 .sh 脚本文件 /usr/local/tomcat7/bin/ export JAVA_HOME=/usr/local/java/jdk7 export PATH=$JAVA_HOME/bin/:$PATH export CLASSPATH=.
2121 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
16745 0
+关注
德哥
公益是一辈子的事, I&#39;m digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载