使用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;
其中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;
作者: digoal 发布时间: 2011-02-22
先收藏起来，有机会的时候做一下试试。
作者: renxiao2003 发布时间: 2011-02-28