当前位置:  首页  >  数据库  >  postgresql

如何使用Plproxy设计分布式PostgreSQL数据库(二)

PostgreSQL的分布式数据库方案很多,如GridSQL,pgpool-ii,plproxy等。其中plproxy是skype开发的一个数据库组件.使用plproxy设计跨互联网部署还要考虑带宽的问题和应用是否要做本地缓存等问题,plproxy源代码对代理库的个数做了限制必须是2的次方,如果要

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;

吐了个 "CAO" !
扫码关注 PHP1 官方微信号
PHP1.CN | 中国最专业的PHP中文社区 | PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | PHP问答
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved PHP1.CN 第一PHP社区 版权所有