使用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;

先收藏起来，有机会的时候做一下试试。