使用Plproxy设计PostgreSQL分布式数据库
背景
PostgreSQL的分布式数据库方案很多,如GridSQL,pgpool-ii,plproxy等.
其中plproxy是skype开发的一个数据库组件.
使用plproxy设计跨互联网部署还要考虑带宽的问题和应用是否要做本地缓存等问题,
plproxy源代码对代理库的个数做了限制必须是2的次方,如果要打破这个限制需要修改源代码.
测试方案大纲
1. 安装postgres 8.3.3 , plproxy , libevent , pgbouncer ;
2. 配置数据库
配置pg_hba.conf , postgres.conf
创建表空间目录
创建role
创建表空间
创建数据库
创建schema
安装过程语言plproxy , plpgsql
3. 配置启用pgbouncer
4. 配置plproxy连接函数
5. 分区库(建表,函数)
6. 代理库(建表,函数)
7. 哈希分区函数
8. 测试.
9. 参考文档.
模拟环境构想
(使用地区1 , 地区2 , 地区3 , 地区4 , 地区5目的是便于理解跨互联网的概念) :
1. 数据中心 ( 地区1 , 地区2 , 地区3 , 地区4 , 地区5 )
2. 数据库布局 :
地区1 : 分区库 ( sgap_testrole_0 , sgap_testrole_1 ) ; 代理库 ( sgap_plproxy_0 )
地区2 : 分区库 ( bj_testrole_0 , bj_testrole_1 ) ; 代理库 ( bj_plproxy_0 )
地区3 : 分区库 ( sh_testrole_0 , sh_testrole_1 ) ; 代理库 ( sh_plproxy_0 )
地区4 : 分区库 ( gz_testrole_0 , gz_testrole_1 ) ; 代理库 ( gz_plproxy_0 )
地区5 : 分区库 ( hz_testrole_0 , hz_testrole_1 ) ; 代理库 ( hz_plproxy_0 )
设计规范
1. role_name规划
分区库 ( 考虑到程序设计的方便,业务库统一使用testrole角色名,密码配置完全相同 )
代理库 ( 所有分区库统一使用与业务库同名角色名,便于管理, 密码配置完全相同 )
权限 nosuperuser nocreatedb nocreaterole noinherit login
2. schema设计 :
分区库schema设计 ( 所有业务数据放到testrole schema下 )
代理库schema设计 ( 创建一个和业务库同名的schema , 创建一个plproxy schema)
3. 集群组划分 :
all – 包含了所有的分区库
dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
- dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
- dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
plproxy_all - 包含了所有的代理库
dbname=sgap_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
dbname=bj_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
dbname=sh_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
dbname=gz_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
- dbname=hz_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
sgap_testrole_all – 包含了当地的所有分区库
dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601
bj_testrole_all
sh_testrole_all
gz_testrole_all
hz_testrole_all
sgap_testrole_0
sgap_testrole_1
bj_testrole_0
bj_testrole_1
sh_testrole_0
sh_testrole_1
gz_testrole_0
gz_testrole_1
hz_testrole_0
hz_testrole_1
4. 表名设计 :
记录分析列的表名:
- pk_tbl_test : partition_key tbl_test ;
5. 函数名 :
前缀设计 (-prefix缩写) :
Clust_all <==> ca_
Cluster_plproxy_all <==> cpa_
Cluster_plproxy <==> cp_
Cluster_local_all <==> cla_
后缀设计 (-suffix缩写) :
Select <==> sel
Insert <==> ins
Update <==> up
Delete <==> del
Insert + update + delete <==> iud
嵌入名(-midfix缩写) : 如表名嵌入
示例:
ca_tbl_test_sel : 解析 cluster 'all' , select , tbl_test ;
ca_iud : 解析 cluster 'all' , insert & update & delete ; -- 无表名一般指所有表通用此函数 ;
cpa_iud : 解析 cluster 'plproxy_all' , insert & update & delete ; -- 有表名一般指每个表都得建此函数 ;
5. 数据库命名规范 :
$LOCALIZATION_$NAME_$ID
细节
1. 安装postgres 8.3.3 , plproxy , libevent , pgbouncer
-
参考相关文档 .
-
值得注意的是libevent安装好后,继续安装pgbouncer需要配置LD_LIBRARY_PATH使之能找到对应的so文件.
2. 配置数据库
1. 配置pg_hba.conf , postgres.conf
host all all 0.0.0.0/0 md5
listen_addresses = '*'
2. 创建表空间目录
su - postgres
mkdir $PGDATA/tbs_sgap_testrole_0_def
mkdir $PGDATA/tbs_sgap_testrole_1_def
mkdir $PGDATA/tbs_sh_testrole_0_def
mkdir $PGDATA/tbs_sh_testrole_1_def
mkdir $PGDATA/tbs_bj_testrole_0_def
mkdir $PGDATA/tbs_bj_testrole_1_def
mkdir $PGDATA/tbs_gz_testrole_0_def
mkdir $PGDATA/tbs_gz_testrole_1_def
mkdir $PGDATA/tbs_hz_testrole_0_def
mkdir $PGDATA/tbs_hz_testrole_1_def
mkdir $PGDATA/tbs_sgap_plproxy_0_def
mkdir $PGDATA/tbs_bj_plproxy_0_def
mkdir $PGDATA/tbs_sh_plproxy_0_def
mkdir $PGDATA/tbs_gz_plproxy_0_def
mkdir $PGDATA/tbs_hz_plproxy_0_def
3. 创建role ( 分区库 , 代理库 )
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'testrole' ;
4. 创建表空间
create tablespace tbs_sgap_testrole_0 owner testrole location '/database/pgdata/tbs_sgap_testrole_0_def';
create tablespace tbs_bj_testrole_0 owner testrole location '/database/pgdata/tbs_bj_testrole_0_def';
create tablespace tbs_sh_testrole_0 owner testrole location '/database/pgdata/tbs_sh_testrole_0_def';
create tablespace tbs_gz_testrole_0 owner testrole location '/database/pgdata/tbs_gz_testrole_0_def';
create tablespace tbs_hz_testrole_0 owner testrole location '/database/pgdata/tbs_hz_testrole_0_def';
create tablespace tbs_sgap_testrole_1 owner testrole location '/database/pgdata/tbs_sgap_testrole_1_def';
create tablespace tbs_bj_testrole_1 owner testrole location '/database/pgdata/tbs_bj_testrole_1_def';
create tablespace tbs_sh_testrole_1 owner testrole location '/database/pgdata/tbs_sh_testrole_1_def';
create tablespace tbs_gz_testrole_1 owner testrole location '/database/pgdata/tbs_gz_testrole_1_def';
create tablespace tbs_hz_testrole_1 owner testrole location '/database/pgdata/tbs_hz_testrole_1_def';
create tablespace tbs_sgap_plproxy_0 owner testrole location '/database/pgdata/tbs_sgap_plproxy_0_def';
create tablespace tbs_bj_plproxy_0 owner testrole location '/database/pgdata/tbs_bj_plproxy_0_def';
create tablespace tbs_sh_plproxy_0 owner testrole location '/database/pgdata/tbs_sh_plproxy_0_def';
create tablespace tbs_gz_plproxy_0 owner testrole location '/database/pgdata/tbs_gz_plproxy_0_def';
create tablespace tbs_hz_plproxy_0 owner testrole location '/database/pgdata/tbs_hz_plproxy_0_def';
5. 创建数据库
create database sgap_testrole_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_sgap_testrole_0;
create database sgap_testrole_1 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_sgap_testrole_1;
create database sgap_plproxy_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_sgap_plproxy_0;
create database bj_testrole_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_bj_testrole_0;
create database bj_testrole_1 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_bj_testrole_1;
create database bj_plproxy_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_bj_plproxy_0;
create database sh_testrole_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_sh_testrole_0;
create database sh_testrole_1 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_sh_testrole_1;
create database sh_plproxy_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_sh_plproxy_0;
create database gz_testrole_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_gz_testrole_0;
create database gz_testrole_1 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_gz_testrole_1;
create database gz_plproxy_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_gz_plproxy_0;
create database hz_testrole_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_hz_testrole_0;
create database hz_testrole_1 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_hz_testrole_0;
create database hz_plproxy_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_hz_plproxy_0;
6. 创建schema ( partition db ==> testrole ; plproxy db ==> testrole + plproxy )
create schema testrole authorization testrole ;
create schema plproxy authorization testrole ;
7. 安装过程语言( partition db ==> plpgsql ; plproxy db ==> plpgsql + plproxy )
- plproxy ; ( psql PLPROXY_DB_NAME postgres -f $PGHOME/share/contrib/plproxy.sql )
- psql sgap_plproxy_0 postgres -f /app/pgsql/share/contrib/plproxy.sql
- psql bj_plproxy_0 postgres -f /app/pgsql/share/contrib/plproxy.sql
- psql sh_plproxy_0 postgres -f /app/pgsql/share/contrib/plproxy.sql
- psql gz_plproxy_0 postgres -f /app/pgsql/share/contrib/plproxy.sql
- psql hz_plproxy_0 postgres -f /app/pgsql/share/contrib/plproxy.sql
create language plpgsql ;
3. 配置启用pgbouncer
1. 创建日志目录 , 配置文件 , 用户密码文件目录
- 日志目录
su - root
mkdir -p /var/applog/pgbouncerlog
chown -R postgres:postgres /var/applog/pgbouncerlog
- 配置文件目录,注意权限管理
su - postgres
mkdir ~/config
chmod -R 700 ~/config
- 配置文件
vi ~/config/pgbouncer.ini
[databases]
sgap_testrole_0 = host=xxx.xxx.150.88 dbname=sgap_testrole_0 port=1921
sgap_testrole_1 = host=xxx.xxx.150.88 dbname=sgap_testrole_1 port=1921
bj_testrole_0 = host=xxx.xxx.150.88 dbname=bj_testrole_0 port=1921
bj_testrole_1 = host=xxx.xxx.150.88 dbname=bj_testrole_1 port=1921
sh_testrole_0 = host=xxx.xxx.150.88 dbname=sh_testrole_0 port=1921
sh_testrole_1 = host=xxx.xxx.150.88 dbname=sh_testrole_1 port=1921
gz_testrole_0 = host=xxx.xxx.150.88 dbname=gz_testrole_0 port=1921
gz_testrole_1 = host=xxx.xxx.150.88 dbname=gz_testrole_1 port=1921
hz_testrole_0 = host=xxx.xxx.150.88 dbname=hz_testrole_0 port=1921
hz_testrole_1 = host=xxx.xxx.150.88 dbname=hz_testrole_1 port=1921
sgap_plproxy_0 = host=xxx.xxx.150.88 dbname=sgap_plproxy_0 port=1921
bj_plproxy_0 = host=xxx.xxx.150.88 dbname=bj_plproxy_0 port=1921
sh_plproxy_0 = host=xxx.xxx.150.88 dbname=sh_plproxy_0 port=1921
gz_plproxy_0 = host=xxx.xxx.150.88 dbname=gz_plproxy_0 port=1921
hz_plproxy_0 = host=xxx.xxx.150.88 dbname=hz_plproxy_0 port=1921
[pgbouncer]
pool_mode = statement
listen_port = 9601
listen_addr = *
auth_type = md5
auth_file = /home/postgres/config/users.txt
logfile = /var/applog/pgbouncerlog/pgbouncer.log
pidfile = /var/applog/pgbouncerlog/pgbouncer.pid
admin_users = pgbouncer_admin
stats_users = pgbouncer_guest
- 密码文件
vi ~/config/users.txt
"testrole" "testrole"
"pgbouncer_admin" "admin"
"pgbouncer_guest" "guest"
#and all other database role&password
4. 配置plproxy连接函数
- 获取集群组配置
CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions(cluster_name text)
RETURNS SETOF text AS $$
BEGIN
IF cluster_name = 'all' THEN
RETURN NEXT 'dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN NEXT 'dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 1
RETURN NEXT 'dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 2
RETURN NEXT 'dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 3
RETURN NEXT 'dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 4
RETURN NEXT 'dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 5
RETURN NEXT 'dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 6
RETURN NEXT 'dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 7
- RETURN NEXT 'dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 8
- RETURN NEXT 'dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 9
RETURN;
elsif cluster_name = 'plproxy_all' THEN
RETURN NEXT 'dbname=sgap_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; -- 0
RETURN NEXT 'dbname=bj_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; -- 1
RETURN NEXT 'dbname=sh_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; -- 2
RETURN NEXT 'dbname=gz_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; -- 3
- RETURN NEXT 'dbname=hz_plproxy_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601';- 4
RETURN;
elsif cluster_name = 'sgap_testrole_0' THEN
RETURN NEXT 'dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN;
elsif cluster_name = 'sgap_testrole_1' THEN
RETURN NEXT 'dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN;
elsif cluster_name = 'bj_testrole_0' THEN
RETURN NEXT 'dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN;
elsif cluster_name = 'bj_testrole_1' THEN
RETURN NEXT 'dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN;
elsif cluster_name = 'sh_testrole_0' THEN
RETURN NEXT 'dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN;
elsif cluster_name = 'sh_testrole_1' THEN
RETURN NEXT 'dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN;
elsif cluster_name = 'gz_testrole_0' THEN
RETURN NEXT 'dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN;
elsif cluster_name = 'gz_testrole_1' THEN
RETURN NEXT 'dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN;
elsif cluster_name = 'hz_testrole_0' THEN
RETURN NEXT 'dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN;
elsif cluster_name = 'hz_testrole_1' THEN
RETURN NEXT 'dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN;
elsif cluster_name = 'sgap_testrole_all' THEN
RETURN NEXT 'dbname=sgap_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN NEXT 'dbname=sgap_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 1
RETURN;
elsif cluster_name = 'bj_testrole_all' THEN
RETURN NEXT 'dbname=bj_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN NEXT 'dbname=bj_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 1
RETURN;
elsif cluster_name = 'sh_testrole_all' THEN
RETURN NEXT 'dbname=sh_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN NEXT 'dbname=sh_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 1
RETURN;
elsif cluster_name = 'gz_testrole_all' THEN
RETURN NEXT 'dbname=gz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN NEXT 'dbname=gz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 1
RETURN;
elsif cluster_name = 'hz_testrole_all' THEN
RETURN NEXT 'dbname=hz_testrole_0 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 0
RETURN NEXT 'dbname=hz_testrole_1 host=xxx.xxx.150.88 user=testrole password=testrole port=9601'; - 1
RETURN;
END IF;
RAISE EXCEPTION 'Unknown cluster';
END;
$$ LANGUAGE plpgsql;
- 获取集群组配置版本
CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(i_cluster_name text)
RETURNS int4 AS $$
declare
i_ver int4;
BEGIN
select max(version) into i_ver from testrole.tbl_cluster_version where cluster_name=i_cluster_name;
if found then
RETURN i_ver;
end if;
RAISE EXCEPTION 'Unknown cluster';
END;
$$ LANGUAGE plpgsql;
- 获取集群组配置参数
CREATE OR REPLACE FUNCTION plproxy.get_cluster_config(
in cluster_name text,out key text,out val text )
RETURNS SETOF record AS $$
BEGIN
- lets use same config for all clusters
key := 'connection_lifetime';
val := 30*60; -- 30m
RETURN NEXT;
- if cluster_name='singap_db_testrole_2' then
- - lets use same config for all clusters
- key := 'connection_lifetime';
- val := 30*60; -- 30m
- RETURN NEXT;
- end if;
RETURN;
END;
$$ LANGUAGE plpgsql;
5. 分区库(建表,函数)
- 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
- 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
- 创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
- 创建cluster_all范围内使用insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
- 创建cluster_all范围插入针对某个表,高效插入时可以使用这种方式,但是需要每个表建立相应的函数
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
- 在cluster_local_all范围内使用 , 当使用plproxy级联时
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
6. 代理库(建表,函数)
- 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
- 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
- cluster 版本记录表
create table testrole.tbl_cluster_version(cluster_name varchar(50),version int4);
insert into testrole.tbl_cluster_version values ('all',1),('plproxy_all',1),('sgap_testrole_0',1),('sgap_testrole_1',1),('bj_testrole_0',1),('bj_testrole_1',1),('sh_testrole_0',1),('sh_testrole_1',1),('gz_testrole_0',1),('gz_testrole_1',1),('hz_testrole_0',1),('hz_testrole_1',1),('sgap_testrole_all',1),('bj_testrole_all',1),('sh_testrole_all',1),('gz_testrole_all',1),('hz_testrole_all',1) ;
- 分区关键值表
create table testrole.pk_tbl_test(phonenum varchar(30));
create table testrole.pk_tbl_test_sgap() inherits (testrole.pk_tbl_test);
create table testrole.pk_tbl_test_bj() inherits (testrole.pk_tbl_test);
create table testrole.pk_tbl_test_sh() inherits (testrole.pk_tbl_test);
create table testrole.pk_tbl_test_gz() inherits (testrole.pk_tbl_test);
create table testrole.pk_tbl_test_hz() inherits (testrole.pk_tbl_test);
create table testrole.pk_tbl_test_def() inherits (testrole.pk_tbl_test);
insert into testrole.pk_tbl_test_sgap values('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');
insert into testrole.pk_tbl_test_bj values('10'),('11'),('12'),('13'),('14'),('15'),('16'),('17'),('18'),('19');
insert into testrole.pk_tbl_test_sh values('20'),('21'),('22'),('23'),('24'),('25'),('26'),('27'),('28'),('29');
insert into testrole.pk_tbl_test_gz values('30'),('31'),('32'),('33'),('34'),('35'),('36'),('37'),('38'),('39');
insert into testrole.pk_tbl_test_hz values('40'),('41'),('42'),('43'),('44'),('45'),('46'),('47'),('48'),('49');
- cluster_all范围内使用的查询函数 , 每个表需要一个查询函数
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
cluster 'all';
run on all;
select * from testrole.ca_tbl_test_sel(v_sql);
$BODY$ language plproxy;
- 每个代理库仅需要建一个,用于cluster_all范围内Insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns setof void as $BODY$
cluster 'all';
run on all;
$BODY$ language plproxy;
- cluster_plproxy_all范围内使用的函数 , 级联
create or replace function testrole.cpa_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
cluster 'plproxy_all';
run on testrole.get_pk_tbl_test(i_phonenum);
select * from testrole.cla_tbl_test_ins(i_phonenum,i_price,i_createtime);
$BODY$ language plproxy;
- (各代理库配置不一样)对上面函数的接口,每个代理库配置对应自己的代理名
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
cluster 'sgap_testrole_all'; -- 对应自己所在的代理
run on abs(trunc(mod(hashtext(i_phonenum),2))::int4);
$BODY$ language plproxy;
- cluster_plproxy_all 范围内对数据进行分类的函数
create or replace function testrole.get_pk_tbl_test(i_phonenum varchar) returns int4 as $BODY$
begin
perform 1 from testrole.pk_tbl_test_sgap where phonenum=i_phonenum;
if found then
return 0 ;
end if;
perform 1 from testrole.pk_tbl_test_bj where phonenum=i_phonenum;
if found then
return 1 ;
end if;
perform 1 from testrole.pk_tbl_test_sh where phonenum=i_phonenum;
if found then
return 2 ;
end if;
perform 1 from testrole.pk_tbl_test_gz where phonenum=i_phonenum;
if found then
return 3 ;
end if;
- perform 1 from testrole.pk_tbl_test_hz where phonenum=i_phonenum;
- if found then
- return 4 ;
- end if;
perform 1 from testrole.pk_tbl_test_def where phonenum=i_phonenum;
if found then
return 0 ; -- 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end if ;
insert into testrole.pk_tbl_test_def values(i_phonenum);
return 0 ; - 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;
end;
$BODY$ language plpgsql;
7. 哈希分区
abs(trunc(mod(hashtext(createtime::text),2))::int4)
8. 测试
/* 分区库详细操作 */
/* 地区1sgap_testrole_0 */
- 创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'testrole';
- 创建表空间
create tablespace tbs_sgap_testrole_0 owner testrole location '/database/pgdata/tbs_sgap_testrole_0_def';
- 创建分区数据库
create database sgap_testrole_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_sgap_testrole_0;
- 连接至已创建好的分区数据库及role
\c sgap_testrole_0 testrole;
- 创建本地schema
create schema testrole authorization testrole;
- 创建过程语言
create language plpgsql;
- 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
- 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
- 创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
- 创建全局insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
- 在cluster_all范围内使用,每个表一个
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
- 在cluster_local_all范围内使用 , 当使用plproxy级联时
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
/* 地区1sgap_testrole_1 */
- 创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'testrole';
- 创建表空间
create tablespace tbs_sgap_testrole_1 owner testrole location '/database/pgdata/tbs_sgap_testrole_1_def';
- 创建分区数据库
create database sgap_testrole_1 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_sgap_testrole_1;
- 连接至已创建好的分区数据库及role
\c sgap_testrole_1 testrole;
- 创建本地schema
create schema testrole authorization testrole;
- 创建过程语言
create language plpgsql;
- 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
- 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
- 创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
- 创建全局insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
- 创建global_all,每个表一个
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
- 创建local_all
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
/* 地区2bj_testrole_0 */
- 创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'testrole';
- 创建表空间
create tablespace tbs_bj_testrole_0 owner testrole location '/database/pgdata/tbs_bj_testrole_0_def';
- 创建分区数据库
create database bj_testrole_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_bj_testrole_0;
- 连接至已创建好的分区数据库及role
\c bj_testrole_0 testrole;
- 创建本地schema
create schema testrole authorization testrole;
- 创建过程语言
create language plpgsql;
- 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
- 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
- 创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
- 创建全局insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
- 创建global_all,每个表一个
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
- 创建local_all
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
/* 地区2bj_testrole_1 */
- 创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'testrole';
- 创建表空间
create tablespace tbs_bj_testrole_1 owner testrole location '/database/pgdata/tbs_bj_testrole_1_def';
- 创建分区数据库
create database bj_testrole_1 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_bj_testrole_1;
- 连接至已创建好的分区数据库及role
\c bj_testrole_1 testrole;
- 创建本地schema
create schema testrole authorization testrole;
- 创建过程语言
create language plpgsql;
- 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
- 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
- 创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
- 创建全局insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
- 创建global_all,每个表一个
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
- 创建local_all
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
/* 地区3sh_testrole_0 */
- 创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'testrole';
- 创建表空间
create tablespace tbs_sh_testrole_0 owner testrole location '/database/pgdata/tbs_sh_testrole_0_def';
- 创建分区数据库
create database sh_testrole_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_sh_testrole_0;
- 连接至已创建好的分区数据库及role
\c sh_testrole_0 testrole;
- 创建本地schema
create schema testrole authorization testrole;
- 创建过程语言
create language plpgsql;
- 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
- 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
- 创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
- 创建全局insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
- 创建global_all,每个表一个
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
- 创建local_all
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
/* 地区3sh_testrole_1 */
- 创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'testrole';
- 创建表空间
create tablespace tbs_sh_testrole_1 owner testrole location '/database/pgdata/tbs_sh_testrole_1_def';
- 创建分区数据库
create database sh_testrole_1 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_sh_testrole_1;
- 连接至已创建好的分区数据库及role
\c sh_testrole_1 testrole;
- 创建本地schema
create schema testrole authorization testrole;
- 创建过程语言
create language plpgsql;
- 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
- 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
- 创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
- 创建全局insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
- 创建global_all,每个表一个
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
- 创建local_all
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
/* 地区4gz_testrole_0 */
- 创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'testrole';
- 创建表空间
create tablespace tbs_gz_testrole_0 owner testrole location '/database/pgdata/tbs_gz_testrole_0_def';
- 创建分区数据库
create database gz_testrole_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_gz_testrole_0;
- 连接至已创建好的分区数据库及role
\c gz_testrole_0 testrole;
- 创建本地schema
create schema testrole authorization testrole;
- 创建过程语言
create language plpgsql;
- 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
- 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
- 创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
- 创建全局insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
- 创建global_all,每个表一个
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
- 创建local_all
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
/* 地区4gz_testrole_1 */
- 创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'testrole';
- 创建表空间
create tablespace tbs_gz_testrole_1 owner testrole location '/database/pgdata/tbs_gz_testrole_1_def';
- 创建分区数据库
create database gz_testrole_1 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_gz_testrole_1;
- 连接至已创建好的分区数据库及role
\c gz_testrole_1 testrole;
- 创建本地schema
create schema testrole authorization testrole;
- 创建过程语言
create language plpgsql;
- 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
- 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
- 创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
- 创建全局insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
- 创建global_all,每个表一个
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
- 创建local_all
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
/* 地区5hz_testrole_0 */
- 创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'testrole';
- 创建表空间
create tablespace tbs_hz_testrole_0 owner testrole location '/database/pgdata/tbs_hz_testrole_0_def';
- 创建分区数据库
create database hz_testrole_0 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_hz_testrole_0;
- 连接至已创建好的分区数据库及role
\c hz_testrole_0 testrole;
- 创建本地schema
create schema testrole authorization testrole;
- 创建过程语言
create language plpgsql;
- 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
- 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
- 创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
- 创建全局insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;
- 创建global_all,每个表一个
create or replace function testrole.ca_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
- 创建local_all
create or replace function testrole.cla_tbl_test_ins(i_phonenum varchar,i_price real,i_createtime timestamp with time zone) returns setof void as $BODY$
begin
insert into testrole.tbl_test values(i_phonenum,i_price,i_createtime);
end;
$BODY$ language plpgsql;
/* 地区5hz_testrole_1 */
- 创建role
create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'testrole';
- 创建表空间
create tablespace tbs_hz_testrole_1 owner testrole location '/database/pgdata/tbs_hz_testrole_1_def';
- 创建分区数据库
create database hz_testrole_1 with owner testrole template=template0 encoding='UTF8' tablespace=tbs_hz_testrole_1;
- 连接至已创建好的分区数据库及role
\c hz_testrole_1 testrole;
- 创建本地schema
create schema testrole authorization testrole;
- 创建过程语言
create language plpgsql;
- 创建测试收费表
create table testrole.tbl_test(phonenum varchar(30),price real,createtime timestamp with time zone default now());
- 创建测试下载记录表
create table testrole.tbl_test_table2(imsi varchar(50),appid varchar(10),createtime timestamp with time zone default now());
- 创建全局查询tbl_test的函数,(每个表都得建)
create or replace function testrole.ca_tbl_test_sel(v_sql text) returns setof testrole.tbl_test as $BODY$
declare
v_rec testrole.tbl_test%rowtype;
begin
for v_rec in execute v_sql loop
return next v_rec;
end loop;
return;
end;
$BODY$ language plpgsql;
- 创建全局insert,update,delete的函数
create or replace function testrole.ca_iud(v_sql text) returns void as $BODY$
begin
execute v_sql;
end;
$BODY$ language plpgsql;