pgpool复制和负载均衡

简介

pgpool-II是一个位于postgresql数据库和客户端之间的中间件，可以提供一下几个功能：

1.连接池

pgpool-II保持已经连接到postgresql数据库的连接，并在使用相同参数连接进来时重用他们，减少了连接开销，并增加了系统的总体吞吐量。

2.复制

pgpool-II可以管理多个postgresql数据库，激活复制功能并使在2台或多台postgresql节点中建立一个实时备份称为可能，这样，如果其中一台节点失效，服务可以不被终端继续运行。

3.负载均衡

如果数据库进行了复制，则在任何一台服务器中执行一个select查询将返回相同的结果。pgpool-II利用了复制的功能以降低每台postgresql服务器的负载。它通过分发select查询到所有可用的服务器中，增强了系统的整体吞吐量。在理想的状况下，读性能应该和postgresql数量成正比。负载均衡功能在大量用户同时执行很多读查询的场景中工作效果最好。

4.限制超过限度的连接

postgresql会限制当前的最大连接数，当到达这个数量时，新的连接将被拒绝。增加这个连接数会增加资源消耗并且对系统的全局性能有一定的负面影响。pgpool-II也支持限制最大连接数，但她的做法是将连接放入队列，而不是立即返回一个错误。

另外，pgpool-II也支持并行查询，数据被分割到多台服务器上，所以一个查询可以在多台服务器上同时执行，以减少总体执行时间。并行查询在查询大规模数据的时候非常有效。

测试环境

IP	应用	版本
10.10.10.56	postgresql	9.2.15
10.10.10.57	postgresql	9.2.15
10.10.10.56	pgpool-II	3.5

环境说明：两台postgresql，pgpool和其中一台postgresql在一台服务器上。
测试需求：测试pgpool实现postgresql的复制和负载均衡

postgresql数据库搭建请参考博文http://blog.csdn.net/yanggd1987/article/details/51150190

安装pgpool

1.源码安装

wget http://www.pgpool.net/download.php?f=pgpool-II-3.5.3.tar.gz
tar -zxvf pgpool-II-3.5.3.tar.gz 
cd pgpool-II-3.5.3
./configure --prefix=/usr/local/pgpool --with-openssl
#报错configure: error: libpq is not installed or libpq is old
#安装 yum install postgresql-devel即可
make && make install

2.安装pgpool-regclass

使用 PostgreSQL 8.0 至 PostgreSQL 9.3，强烈推荐在需要访问的 PostgreSQL 中安装 pgpool_regclass 函数，因为它被 pgpool-II 内部使用。 如果不这样做，在不同的 schema 中处理相同的表名会出现问题（临时表不会出问题）；而PostgreSQL 9.4或更高版本则不需要

cd pgpool-II-3.5.3/src/sql/pgpool-regclass
make && make install

-bash-4.2$ psql -f pgpool-regclass.sql template1
或
psql template1
=# CREATE EXTENSION pgpool_regclass;

应在每台通过 pgpool-II 访问的数据库中执行 pgpool-regclass.sql 或者 CREATE EXTENSION。 你不需要在你执行“psql -f pgpool-regclass.sql template1” 或者 CREATE EXTENSION 后建立的数据库中这么做， 因为这个模板数据库将被克隆成新建的数据库。

3.建立insert_lock表

如果你在复制模式中使用了 insert_lock ，强烈推荐建立 pgpool_catalog.insert_lock 表，用于互斥。 到现在为止，insert_lock 还能够工作。但是，在这种情况下，pgpool-II 需要锁定插入的目标表。 这种行为和 pgpool-II 2.2 和 2.3 系列类似。由于表锁与 VACUUM 冲突，所以 INSERT 操作可能因而等待很长时间。

cd pgpool-II-3.5.3/src/sql/
psql -f insert_lock.sql tempalte1

应在在每台通过 pgpool-II 访问的数据库中执行 insert_lock.sql。 你不需要在你执行“psql -f insert_lock.sql template1”后建立的数据库中这么做， 因为这个模板数据库将被克隆成新建的数据库。

4.安装pgpool_recovery

如果你使用在线回复，需要以下一些函数： pgpool_recovery, pgpool_remote_start, pgpool_switch_xlog。另外，附带工具pgpoolAdmin 控制 pgpool-II 启停和重新连接后端的 PostgreSQL 节点， 它需要函数 pgpool_pgctl;而且 pgpoolAdmin 需要函数 pgpool_pgctl 来对 PostgreSQL 进行停止/重启/重新加载参数操作。可以使用和安装 pgpool_regclass 一样的方法安装着四个函数。 不过和 pgpool_regclass 不同的是，你只需要在 template1 数据库中安装这些函数。

cd pgpool-II-3.5.3/src/sql/pgpool-recovery
make && make install

psql -f pgpool-recovery.sql template1
或
psql template1
=# CREATE EXTENSION pgpool_recovery

配置pgpool

1.配置pcp.conf
pgpool提供pcp接口,可以查看,管理pgpool的状态,并且可以远程操作pgpool,pcp.conf用来对pcp相关命令认证的文件

cd /usr/local/pgpool
cp etc/pcp.conf.sample etc/pcp.conf
bin/pg_md5 -m -u pgpool -p
会自动生成pool_passwd
postgres:md533c14731dfdfsdfsdf91e8d10c4bff5
cat etc/pcp.conf
pgpool:md533c14731dfdfsdfsdf91e8d10c4bff5

2.配置pool_hba.conf
通过pool_hba.conf可以进行相关权限设置，类似于postgresql的pg_hba.conf

cd /usr/local/pgpool
cp etc/pool_hba.conf.sample etc/pool_hba.conf
bin/pg_md5 -m -u postgres -p
会自动生成pool_passwd
postgres:md533c14731dfdfsdfsdf91e8d10c4bff5

**注意：
1.pool_hba.conf中的加密方式必须和pg_hba.conf中的加密方式一样，都为mds、trust或其他方式。若不一样会报错。
2.pool_hba.conf中的用户必须在postgresql数据库中存在
**

3.配置pgpool.conf

cd /usr/local/pgpool
cp etc/pgpool.conf.sample etc/pgpool.conf
#创建相关目录
mkdir -p /var/run/pgpool
mkdir -p /usr/local/pgpool/logs
vim etc/pgpool.conf
listen_addresses = '*'
port = 9999
socket_dir = '/tmp'
listen_backlog_multiplier = 2
serialize_accept = off
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'
backend_hostname0 = '10.10.10.56'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/data/pgsql/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '10.10.10.57'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/data/pgsql/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
debug_level = 0
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/usr/local/pgpool/logs'
replication_mode = on
load_balance_mode = on
master_slave_mode = off

其中：
replication_mode = on 复制模式打开，可以在两台postgresql数据库上进行实时备份
load_balance_mode = on 负载均衡模式打开，可以实现负载均衡

3.启动pgpool

/usr/local/pgpool/bin/pgpool

4.连接pgpool

psql -U postgres -p 9999 -h 10.10.10.56
Password for user postgres: 
psql (9.2.15)
Type "help" for help.

postgres=#

测试

1.创建bench_replication数据库

psql -U postgres -p 9999 -h 10.10.10.56
Password for user postgres: 
psql (9.2.15)
Type "help" for help.

postgres=#create database bench_replication;

我们可以分别登陆56、57上的postgresql进行查看

psql -U postgres -p 5432 -h 10.10.10.56
Password for user postgres: 
psql (9.2.15)
Type "help" for help.

postgres=# \list
                                      List of databases
       Name        |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------------+----------+----------+-------------+-------------+-----------------------
 bench_replication | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                   |          |          |             |             | postgres=CTc/postgres
 template1         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                   |          |          |             |             | postgres=CTc/postgres
(4 rows)

可以看到两台postgresql都创建了bench_replication数据库，因此pgpool的复制是成功的。

2.pgbench测试

#安装pgbench
yum install postgresql-contrib

pgbench -i -h 10.10.10.56 -U postgres -p 9999 bench_replication

在所有pgsql节点中，下面列出的表和数据都被建立了，说明复制运行正常。

表名	行数
branches	1
tellers	10
accounts	100000
history	0

总结

以上只是简单介绍了pgpool-II的复制和负载均衡作为入门，还有很多功能没有介绍，需要我们继续探索。