使用Plproxy设计PostgreSQL分布式数据库03

来源：互联网发布：js给json对象添加属性编辑：程序博客网时间：2024/05/16 05:09

/* 地区2plproxy_0 */

psql postgres postgres

create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’; – 改

create tablespace tbs_bj_plproxy_0 owner testrole location ‘/database/pgdata/tbs_bj_plproxy_0_def’; – 改

create database bj_plproxy_0 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_bj_plproxy_0; – 改

\q;

psql bj_plproxy_0 postgres -f $PGHOME/share/contrib/plproxy.sql

psql bj_plproxy_0 testrole

– 以上须改

create language plpgsql;

create schema testrole authorization testrole;

create schema plproxy authorization testrole;

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.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());

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′);

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;

– 改

\c bj_plproxy_0 postgres

– 每个表需要一个查询函数

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;

– 每个代理库需要建一个,全局所有,本地所有,单一,数据库的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;

– 对应用程序的接口

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 ‘bj_plproxy_0′; –对应自己所在的代理

run on abs(trunc(mod(hashtext(i_phonenum),2))::int4);

$BODY$ language plproxy;

\c sgap_plproxy_0 testrole

– 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 1 ; — 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;

end if ;

insert into testrole.pk_tbl_test_def values(i_phonenum);

return 1 ; – 设计为默认往sgap插入.当然可以设计为每个代理库的该函数返回为该代理库的ID;即往本地插;

end;

$BODY$ language plpgsql;

/* 地区3plproxy_0 */

psql postgres postgres

create role testrole nosuperuser nocreatedb nocreaterole noinherit login encrypted password ‘testrole’; – 改

create tablespace tbs_sh_plproxy_0 owner testrole location ‘/database/pgdata/tbs_sh_plproxy_0_def’; – 改

create database sh_plproxy_0 with owner testrole template=template0 encoding=’UTF8′ tablespace=tbs_sh_plproxy_0; – 改

\q;

psql sh_plproxy_0 postgres -f $PGHOME/share/contrib/plproxy.sql

psql sh_plproxy_0 testrole

– 以上须改

create language plpgsql;

create schema testrole authorization testrole;

create schema plproxy authorization testrole;

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) ;