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

概述1.       下载安装 在官网 http://pgfoundry.org/projects/pgpool/ 下载 pgpool-II 2.2.2.tar.gz ， 执行以下命令安装： #tar xvf pgpool-II 2.2.2.tar.gz #cd pgpool-II 2.2.2 #./ configure; make; make install; 2.       Parallel_M

1. 下载安装

在官网 http://pgfoundry.org/projects/pgpool/ 下载 pgpool-II 2.2.2.tar.gz ， 执行以下命令安装：

#tar xvf pgpool-II 2.2.2.tar.gz

#cd pgpool-II 2.2.2

#./ configure; make; make install;

2. Parallel_Mode 配置

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

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

配置文件的默认路径是： /usr/local/etc ，首先需要配置 pgpool.conf

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

下面是具体的配置过程：

设置并发查询
将 pgpool.conf 文件中的 parallel_mode 参数设置为 true 开启数据库复制功能：
parallel_mode = true
这样并不能自动开始并发查询， pgpool-II 还需要 SystemDB 以及分布规则来知道如何在各节点 中分布数据。

另外， SystemDB 使用数据库链连接 pgpool-II ，因此，需要设置 Listen_addresses 参数好让 pgpool-II 接受这些连接请求：

Listen_addresses = '*'

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

replication_mode = false

load_balance_mode = false

parallel_mod 设置为 true 、 Listen_addresses 设置为 '*' 、 replication_mode 和 load_balance_mode 设置为 false 。

配置 SystemDB

SystemDB 仅仅是一个安装有数据库链的数据库，数据表 “dist_def” 被用来保存数据分布规则。可 以在单独的电脑中放置 SystemDB ，也可以与数据库节点之一共存。

在服务器 192.168.1.121 上的 5444 端口上创建 SystemDB ，下边是 SystemDB 的参数设置列表：

system_db_hostname = '192.168.1.121'

system_db_port = 5444

system_db_dbname = 'pgpool'

system_db_schema = 'pgpool_catalog'

system_db_user = 'pgpool'

system_db_password = ''

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

然后创建叫做 “pgpool” 的用户，再创建所有者为 “pgpool” 的数据库 “pgpool” ：

#createuser -p 5444 pgpool

#createdb -p 5444 -O pgpool pgpool

安装数据链

接下来，必须将数据库链安装到 “pgpool” 数据库，它是包含在 Postgresql 源代码 contrib 目录下的工具之一。

在“pgpool” 数据库中定义数据库链函数。Postgresql 安装在/opt/PostgresPlus/8.3AS ，dblink.sql （函数定义文件）会被放置在/opt/PostgresPlus/8.3AS/dbserver/share/contrib 中，运行如下命令创建函数：

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

定义 dist_def 数据表

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

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

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

下边是dist_def 的定义语句：

CREATE table pgpool_catalog.dist_def (

dbname text,-- database name

schema_name text,-- schema name

table_name text,-- table name

col_name text NOT NulL CHECK (col_name = ANY (col_List)),-- distribution key-column
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 name

PRIMARY KEY (dbname,schema_name,table_name)

);

dist_def 中的每一行数据分为两个部分：

数据分布规则（col_name 、dist_def_func ）

数据表的Meta-information （dbname 、schema_name 、table_name,col_List 、type_List ）

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

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

system_db.sql 中定义的其他几个表，可以打开该文件具体参考。

定义数据分布节点

例子中的三个分布节点，分别 为：192.168.1.239:5444,192.168.1.201:5444,192.168.1.201:5443

在pgpool.conf 中定义这三个节点：

backend_hostname0 = '192.168.1.239'

backend_port0 = 5444

backend_weight0 = 1

backend_data_directory0 = '/usr/postgresql/data'

backend_hostname1 = '192.168.1.201'

backend_port1 = 5444

backend_weight1 = 1

backend_data_directory1 = '/usr/postgresql/data'

backend_hostname2 = '192.168.1.201'

backend_port2 = 5443

backend_weight2 = 1

backend_data_directory2 = '/usr/postgresql/data2'

定义数据分布规则

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

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

以下是dist_def_pgbench.sql 文件内容的解释。

插 入四行数据到数据表“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 (

'bench_parallel',

'public',

'branches',

'bID',

ARRAY['bID','bbalance','filler'],

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

'pgpool_catalog.dist_def_branches'

);

INSERT INTO pgpool_catalog.dist_def VALUES (

'bench_parallel',

'tellers',

'tID',

ARRAY['tID','bID','tbalance','character(84)'],

'pgpool_catalog.dist_def_tellers'

);

INSERT INTO pgpool_catalog.dist_def VALUES (

'bench_parallel',

'accounts',

'aID',

ARRAY['aID','abalance',

'pgpool_catalog.dist_def_accounts'

);

INSERT INTO pgpool_catalog.dist_def VALUES (

'bench_parallel',

'history','aID','delta','mtime','timestamp without time zone','character(22)'],

'pgpool_catalog.dist_def_history'

);

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

下边是由pgbench -i -s 3 生成的数据概要：

数据表名	数据行数
branches	3
tellers	30
accounts	300000
history	0

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

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_branches(anyelement)

RETURNS integer AS $$

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

$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_tellers(anyelement)

RETURNS integer AS

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

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_accounts(anyelement)

RETURNS integer AS $$

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

$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_history(anyelement)

RETURNS integer AS $$ SELECT CASE WHEN > 0 AND <= 10 THEN 0 WHEN > 10 AND <= 20 THEN 1 ELSE 2 END;

$$ LANGUAGE sql;

检验并发查询

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

首先，命名为“bench_parallel” 的分布数据库，通过pgpool-II 创建到每一个节点上：

$ createdb -p 9999 bench_parallel

接下来，生成测试数据：

$ pgbench -i -s 3 -p 9999 bench_parallel

预想的数据分布情况：

数据表名	Key-Column 名	值
		节点1	节点2	节点3
branches	bID	1	2	3
tellers	tID	1 - 10	11 - 20	21 - 30
accounts	aID	1 - 100000	100001 - 200000	200001 - 300000
history	tID	1 - 10	11 - 20	21 - 30

这时可以用数据库工具分别查看各节点的数据来确认结果。也可以在 Pgpool 的服务端口进入 sql *** 作来查看数据：

#ebd-psql –p 9999 bench_parallel

#select * from accounts;

此时三个节点的数据可以通过一个共同的接口跟应用进行关联，该接口 就是pgpool ，默认端口为9999 。

3. load_balance_mode 配置

Parallel_Mode 配置成功后，每一个数据分布的节点还可继续利用Pgpool 的load_balance_mode 进一步配置小的集群，实现数据库的负载均衡。

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

load_balance_mode = true

…

master_slave_mode = true

…

backend_hostname1 = '192.168.1.239'

backend_port1 = 5444

backend_weight1 = 1

backend_data_directory1 = '/usr/postgresql/data'

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 处理，继续提供数据库服务，实现 了高可用性和负载均衡。

Failover 处理

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

# Execute command by failover.

# special values: %d = node ID

# %h = host name

# %p = port number

# %D = database cluster path

# %m = new master node ID

# %M = old master node ID

# %% = '%' character

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

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

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

if [ = ];then

#slave Failed

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 $3

fi

Failback 处理

4. Replication_mode 配置

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

Pgpool 的replication_mode 是将Insert 、Update 、Delete 命令同时发送、Select 命令随机发送的一种方式，这样可以避免上述的情况，但是在failback 时数据的同步需要借助其他的方法来实现。

修改配置pgpool.conf 如下：

replication_mode = true

…

master_slave_mode = false

待完善......

原文地址：http://blog.csdn.net/xtlog/archive/2009/05/27/4219353.aspx

总结

以上是内存溢出为你收集整理的PostgreSQL 利用Pgpool-II的集群搭建方案（Partition+LoadBalance+Replication）全部内容，希望文章能够帮你解决PostgreSQL 利用Pgpool-II的集群搭建方案（Partition+LoadBalance+Replication）所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。