PostgreSQL 利用Pgpool-II的集群搭建方案（Partition+LoadBalance+Replication）

1.       下载安装

在官网 http://pgfoundry.org/projects/pgpool/ 下载 pgpool-II 2.2.2.tar.gz ，执行以下命令安装：51Testing软件测试网 Ju/Q4Yf^-?3iiz

#tar xvf pgpool-II 2.2.2.tar.gz51Testing软件测试网~e Xnv1D([

#cd pgpool-II 2.2.251Testing软件测试网-}$K{Ew0L U:SA

#./ configure; make; make install;51Testing软件测试网V#_VQoS*z:W

2.       Parallel_Mode 配置

数据库的 Patition 可以利用 Pgpool-II 的 Parallel_Mode 来实现，在这种方式下，必须设置另外一个叫做 “System Database” 的数据库（我们称之为 SystemDB ）。 SystemDB 保存决定数据如何在各节点中保存的用户定义规则，另一个用途是合并使用数据库链（ dblink ）从数据库节点返回的结果。51Testing软件测试网k9yZ RD2X

本文中的配置， Pgpool 和 SystemDB 都放在服务器 192.168.1.239 ，占用端口分别为： 9999 和 5444 。

配置文件的默认路径是： /usr/local/etc ，首先需要配置 pgpool.conf51Testing软件测试网Y _1mEy#m

#cd /usr/local/etc ; cp pgpool.conf.sample pgpool.conf;

下面是具体的配置过程：51Testing软件测试网'LYYQ)|

设置并发查询 51Testing软件测试网!Me4Q
w~9Y8{ e,ld
将 pgpool.conf 文件中的 parallel_mode 参数设置为 true 开启数据库复制功能： 
]K9K` A9i(m0parallel_mode = true 
4fonn:c8PU0这样并不能自动开始并发查询， pgpool-II 还需要 SystemDB 以及分布规则来知道如何在各节点中分布数据。

另外， SystemDB 使用数据库链连接 pgpool-II ，因此，需要设置 listen_addresses 参数好让 pgpool-II 接受这些连接请求：51Testing软件测试网-eYO)eY*J-?;]?o

listen_addresses = '*'51Testing软件测试网*{1K6F-`*DB'^

注意：并发查询和复制是不能共存的，使用并发查询功能时， replication_mode 必须设置为 false 。此外，并发查询和复制采用不同的格式储存数据，这样前边我们创建的 “bench_replication” 数据库无法被重用（ reuse ）。

replication_mode = false

load_balance_mode = false

parallel_mod 设置为 true 、 listen_addresses 设置为 '*' 、 replication_mode 和 load_balance_mode 设置为false 。51Testing软件测试网%Jlz2ff3]$b

配置 SystemDB51Testing软件测试网+M&E^'yr4S

SystemDB 仅仅是一个安装有数据库链的数据库，数据表 “dist_def” 被用来保存数据分布规则。可以在单独的电脑中放置SystemDB ，也可以与数据库节点之一共存。51Testing软件测试网C6[_@0tV+Q U&UL

在服务器 192.168.1.121 上的 5444 端口上创建 SystemDB ，下边是 SystemDB 的参数设置列表：51Testing软件测试网_k\2h+{x)i,\

system_db_hostname = '192.168.1.121'51Testing软件测试网o\ A5e dT

system_db_port = 5444

system_db_dbname = 'pgpool'51Testing软件测试网]Z HT6e4A ]%s

system_db_schema = 'pgpool_catalog'

system_db_user = 'pgpool'51Testing软件测试网y |p6P9O:u

system_db_password = ''51Testing软件测试网
N3`Z]%\5r9u

事实上，这是 pgpool.conf 中的缺省设置。

然后创建叫做 “pgpool” 的用户，再创建所有者为 “pgpool” 的数据库 “pgpool” ：51Testing软件测试网%vQ3[O%[}.G7|$@8j

#createuser -p 5444 pgpool51Testing软件测试网v_
v;m
LU*r$G

#createdb -p 5444 -O pgpool pgpool51Testing软件测试网*wv,vxks/Np

安装数据链51Testing软件测试网9ZC)PR+zT

接下来，必须将数据库链安装到 “pgpool” 数据库，它是包含在 PostgreSQL 源代码 contrib 目录下的工具之一。51Testing软件测试网+X5P1jg8MgX.b

在“pgpool” 数据库中定义数据库链函数。PostgreSQL 安装在/opt/PostgresPlus/8.3AS ，dblink.sql （函数定义文件）会被放置在/opt/PostgresPlus/8.3AS/dbserver/share/contrib 中，运行如下命令创建函数：51Testing软件测试网f LDi2sF5l
x:H

# edb-psql -f /opt/PostgresPlus/8.3AS/dbserver/share/contribdblink.sql -p 5444 pgpool

定义 dist_def 数据表51Testing软件测试网iY:O:v-R7H?` t

接 下来，定义数据库表“dist_def” 存放数据分布规则。安装pgpool-II 时，system_db.sql 文件放置在/opt/PostgresPlus/8.3AS/dbserver/share /system_db.sql （这里我们使用缺省安装路径），它包括了一些包括“dist_def” 在内的特殊用途数据表，执行下边的命令创 建dist_def 数据表：51Testing软件测试网fpA]+}c'l*Q

# edb-psql -f /opt/PostgresPlus/8.3AS/dbserver/share/system_db.sql -p 5444 -U pgpool pgpool51Testing软件测试网ds3jf,fmO;`{

在system_db.sql 文件里，dist_def 被创建在叫做pgpool_catalog 的schema 中，如果参数system_db_schema 设置为其他的schema ，需要相应的修改system_db.sql 。

下边是dist_def 的定义语句：

CREATE TABLE pgpool_catalog.dist_def (51Testing软件测试网,Z%vYPmp'JL hh

dbname text, -- database name

schema_name text, -- schema name

table_name text, -- table name51Testing软件测试网7y_c0i]&d+^

col_name text NOT NULL CHECK (col_name = ANY (col_list)), -- distribution key-column51Testing软件测试网.KZ"fL_.\
]7Mp q
col_list text[] NOT NULL, -- list of column names

type_list text[] NOT NULL, -- list of column types

dist_def_func text NOT NULL, -- distribution function name51Testing软件测试网_{yE)P6ZP

PRIMARY KEY (dbname, schema_name, table_name)51Testing软件测试网3Vf[(@{{%r

);51Testing软件测试网)L_H'm*g7MTBv%N

dist_def 中的每一行数据分为两个部分：51Testing软件测试网;a0I'U#E;lRjS([Q

数据分布规则（col_name 、dist_def_func ）51Testing软件测试网4D8T9K6C)B%~

数据表的meta-information （dbname 、schema_name 、table_name, col_list 、type_list ）

分布规则决定了数据如何在各节点中分布，也就是按照“col_name” 字段的值分布。“dist_def_func” 以“col_name” 值为参数的函数，返回数据应该被储存在哪个节点的ID 。

meta-information 用来重写查询，并发查询必须重写查询以便能够将个节点返回的结果合并为一个结果。

system_db.sql 中定义的其他几个表，可以打开该文件具体参考。51Testing软件测试网V dq%eTZ7r

定义数据分布节点51Testing软件测试网c PD { U

例子中的三个分布节点，分别为：192.168.1.239:5444 , 192.168.1.201:5444, 192.168.1.201:544351Testing软件测试网f/@W!o&X3|

在pgpool.conf 中定义这三个节点：51Testing软件测试网 x]vL.A-J^f n)WM

backend_hostname0 = '192.168.1.239'51Testing软件测试网a#C8o'^+r?1e

backend_port0 = 5444

backend_weight0 = 1

backend_data_directory0 = '/usr/postgresql/data'51Testing软件测试网Qh0yAV2HE{$F

backend_hostname1 = '192.168.1.201'

backend_port1 = 5444

backend_weight1 = 1

backend_data_directory1 = '/usr/postgresql/data'51Testing软件测试网 CW$j;m6U9d I

backend_hostname2 = '192.168.1.201'51Testing软件测试网!h0@"MVAAn

backend_port2 = 544351Testing软件测试网 F WE0T1Pl

backend_weight2 = 151Testing软件测试网 pyZ| u

backend_data_directory2 = '/usr/postgresql/data2'

定义数据分布规则51Testing软件测试网NI"b \#??;]

我们将定义把pgbench 生成的示例数据分布在三个数据库节点的分布规则，创建名为“bench_parallel” 的数据库，并且使用“pgbench -i -s 3” 生成示例数据。 51Testing软件测试网:m \'dpd j8~b
在pgpool-II 源代码的sample 目录能够找到dist_def_pgbench.sql 文件，使用这个文件创建数据分布规则，执行如下命令：

$ edb-psql -f sample/dist_def_pgbench.sql -p 5444 pgpool

以下是dist_def_pgbench.sql 文件内容的解释。51Testing软件测试网Y+_vaDj;d![-f*|

插 入四行数据到数据表“dist_def” 中。每个数据表（前边提到过的accounts 、branches 、tellers 以及history ）各自有一 个不同的分布函数。分别为branches 、tellers 、accounts 定义bid 、tid 、aid 作为他们的key-columns 字段（这几个字段也是他们的主键），history 以tid 作为key-columns 字 段。

INSERT INTO pgpool_catalog.dist_def VALUES (51Testing软件测试网
@2Z D3iDoC

'bench_parallel',51Testing软件测试网w.VxN,hYc8e4@

'public',51Testing软件测试网gZ yxnc6F(xWB

'branches',51Testing软件测试网0K
t7MF'U0Q.g

'bid',

ARRAY['bid', 'bbalance', 'filler'],

ARRAY['integer', 'integer', 'character(88)'],

'pgpool_catalog.dist_def_branches'

);

INSERT INTO pgpool_catalog.dist_def VALUES (51Testing软件测试网1BW^!Rv&l%f

'bench_parallel',51Testing软件测试网K9v+\6x$] B%[;YX

'public',51Testing软件测试网](O NCxv

'tellers',

'tid',

ARRAY['tid', 'bid', 'tbalance', 'filler'],

ARRAY['integer', 'integer', 'integer', 'character(84)'],51Testing软件测试网P2?u*e&C?3Lc-k,S

'pgpool_catalog.dist_def_tellers'

);

INSERT INTO pgpool_catalog.dist_def VALUES (51Testing软件测试网 {8r%nUMM%c

'bench_parallel',51Testing软件测试网#D
z&]%z8Q[

'public',51Testing软件测试网0A~J5z%R?c` hM6_D)^

'accounts',51Testing软件测试网M0CWP] zdo[2R+\7~

'aid',

ARRAY['aid', 'bid', 'abalance', 'filler'],

ARRAY['integer', 'integer', 'integer', 'character(84)'],

'pgpool_catalog.dist_def_accounts'

);

INSERT INTO pgpool_catalog.dist_def VALUES (

'bench_parallel',51Testing软件测试网-k:G!y'r(\+S"a|

'public',51Testing软件测试网n3Z8O)ab+Z/]_L \/g

'history',51Testing软件测试网$WRgM-h2C

'tid',51Testing软件测试网M!@/nI a&M

ARRAY['tid', 'bid', 'aid', 'delta', 'mtime', 'filler'],

ARRAY['integer', 'integer', 'integer', 'integer', 'timestamp without time zone', 'character(22)'],51Testing软件测试网2L-Y){Z c"?-z

'pgpool_catalog.dist_def_history'51Testing软件测试网+HRq$@yt+b#gK3\4me H!f

);

接下来，必须为每个表定义分布函数，不同的表可以使用同一个分布函数，并且使用过程语言（PL/pgSQL 、PL/Tcl 等等）定义而不是SQL 。

下边是由pgbench -i -s 3 生成的数据概要：51Testing软件测试网/A9cY)Cu2n;T+`

 51Testing软件测试网oI%e&T?#c7Ck'o

继续定义4 个函数将上述数据平分到三个节点，根据给出的参数返回0 、1 或者2

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_branches(anyelement)

RETURNS integer AS $$51Testing软件测试网4XgXt
x

SELECT CASE WHEN $1 > 0 AND $1 <= 1 THEN 0 WHEN $1 > 1 AND $1 <= 2 THEN 1 ELSE 2 END;

$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_tellers(anyelement)

RETURNS integer AS51Testing软件测试网,`'y;i]2h{'KK3OC F

$$ SELECT CASE WHEN $1 > 0 AND $1 <= 10 THEN 0 WHEN $1 > 10 AND $1 <= 20 THEN 1 ELSE 2 END; $$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_accounts(anyelement)51Testing软件测试网\ybl~wKI

RETURNS integer AS $$

SELECT CASE WHEN $1 > 0 AND $1 <= 100000 THEN 0 WHEN $1 > 100000 AND $1 <= 200000 THEN 1 ELSE 2 END;51Testing软件测试网B
j"M `E

$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_history(anyelement)

RETURNS integer AS $$ SELECT CASE WHEN $1 > 0 AND $1 <= 10 THEN 0 WHEN $1 > 10 AND $1 <= 20 THEN 1 ELSE 2 END;51Testing软件测试网W,F|5U|(G)`Op6U

$$ LANGUAGE sql;

检验并发查询51Testing软件测试网)p$Yut/VFv$w#j

重新启动pgpool-II 载入pgpool.conf 的变化，然后我们来检验一下并发查询是否在正常运行。

首先，命名为“bench_parallel” 的分布数据库，通过pgpool-II 创建到每一个节点上：51Testing软件测试网)Z1_ U;xc~

$ createdb -p 9999 bench_parallel51Testing软件测试网5nLY!`oDi!T^ag6Jtg

接下来，生成测试数据：51Testing软件测试网O0J+Bp Cq-P

$ pgbench -i -s 3 -p 9999 bench_parallel51Testing软件测试网-{kS-@
mXmQ

预想的数据分布情况：51Testing软件测试网@V7z!fh'y,Fu

这时可以用数据库工具分别查看各节点的数据来确认结果。也可以在 Pgpool 的服务端口进入 SQL 操作来查看数据：51Testing软件测试网8AIWFn'_R^MT

#ebd-psql –p 9999 bench_parallel51Testing软件测试网:@t Z.Y+K5`X

#select * from accounts;

此时三个节点的数据可以通过一个共同的接口跟应用进行关联，该接口就是pgpool ，默认端口为9999 。51Testing软件测试网8{/E+m/mo

3.      load_balance_mode 配置51Testing软件测试网K i eQ#}

Parallel_Mode 配置成功后，每一个数据分布的节点还可继续利用Pgpool 的load_balance_mode 进一步配置小的集群，实现数据库的负载均衡。51Testing软件测试网kb A;Z(|:f

数 据分布节点的集群仍需要一个对外的共同接口，默认端口9999 。本例中的节点Pgpool 安装在了服务器192.168.1.239 上，两个节点分别为192.168.1.239:5444 ，192.168.1.121:5445 配置pgpool.conf 如下：

load_balance_mode = true51Testing软件测试网1[c}S)~}*D

…51Testing软件测试网(NltY `-_R`O9R

master_slave_mode = true51Testing软件测试网!~8P2G K0O7s0s

…51Testing软件测试网`\!Zl+OE"q`Q

backend_hostname1 = '192.168.1.239'

backend_port1 = 544451Testing软件测试网6g0}#~t-m hxE Y ~

backend_weight1 = 151Testing软件测试网Mv!P6Z&^5`Q_v

backend_data_directory1 = '/usr/postgresql/data'51Testing软件测试网${y.])S!k!Fs1F

 51Testing软件测试网+BpMiw3\p8J

backend_hostname2 = '192.168.1.121'

backend_port2 = 5445

backend_weight2 = 1

backend_data_directory2 = '/usr/postgresql/data2'

在 load_balance_mode 模式下，Insert 、Update 、Delete 操作只会发给Master ，Select 将会随机分发到两个节点上，Slave 可以通过 Slonely 复制 Master 的数据，任意一个服务出现故障都会通过 Failover 处理，继续提供数据库服务，实现了高可用性和负载均衡。51Testing软件测试网!m8Y@!e9i7{(DU

Failover 处理51Testing软件测试网}\B c(g'MH Q8~

在节点发生故障后， Pgpool 提供了一个调用 Command 的方法，在 pgpool.conf 文件中

# Execute command by failover.

# special values:  %d = node id

#                  %h = host name

#                  %p = port number51Testing软件测试网*`6P[$J,rx

#                  %D = database cluster path

#                  %m = new master node id51Testing软件测试网ymsC/J$n

#                   %M = old master node id51Testing软件测试网g'h"cH#l:f'i6L4xp

#                  %% = '%' character

failover_command = '/usr/local/etc/failover.sh %d %m %M'

当某个节点发生了故障时，会自动调用存放在Pgpool 服务器上的Shell 文件。调用时，可以传递一些参数，在本例中，传递了%d %m %M 这三个参数。具体的定义可以参考说明。

在调用的Shell 中，根据参数的值来判断发生故障的服务是否为Master ，并分别调用不同的Shell ，完成Slonely 的Failover

if [$2 = $3];then

  #slave failed51Testing软件测试网YY&Zb,U.V

   ssh root@192.168.1.121 /opt/PostgresPlus/8.3AS/dbserver/bin/./edb_cluster_dropslave.sh $1 $2 $3

else

  #master failed

   ssh root@192.168.1.121 /opt/PostgresPlus/8.3AS/dbserver/bin/./edb_cluster_failover.sh $1 $2 $351Testing软件测试网j*Z:w
@ NTpn

fi

Failback 处理51Testing软件测试网0C$B5GKW

4.   Replication_mode 配置

在上文中master_slave_mode 和Slonely 相结合同样可以实现复制功能，但异步复制的情况下，数据的统一在时间上是有延迟的，有可能会造成数据检索结果与实现不一致的情况。

Pgpool 的replication_mode 是将Insert 、Update 、Delete 命令同时发送、Select 命令随机发送的一种方式，这样可以避免上述的情况，但是在failback 时数据的同步需要借助其他的方法来实现。51Testing软件测试网J&y)?z.?]&sk

修改配置pgpool.conf 如下：

replication_mode = true51Testing软件测试网jm.q2C+J!C

…

master_slave_mode = false51Testing软件测试网t"Y)U4A\
|"B4s