pgpool复制和负载均衡操作

2023-07-06 22:56:03 210

简介

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的复制和负载均衡

安装pgpool

1.源码安装

wgethttp://www.pgpool.net/download.php?f=pgpool-II-3.5.3.tar.gz
tar-zxvfpgpool-II-3.5.3.tar.gz
cdpgpool-II-3.5.3
./configure--prefix=/usr/local/pgpool--with-openssl
#报错configure:error:libpqisnotinstalledorlibpqisold
#安装yuminstallpostgresql-devel即可
make&&makeinstall

2.安装pgpool-regclass

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

cdpgpool-II-3.5.3/src/sql/pgpool-regclass
make&&makeinstall

-bash-4.2$psql-fpgpool-regclass.sqltemplate1
或
psqltemplate1
=#CREATEEXTENSIONpgpool_regclass;

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

3.建立insert_lock表

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

cdpgpool-II-3.5.3/src/sql/
psql-finsert_lock.sqltempalte1

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

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数据库中安装这些函数。

cdpgpool-II-3.5.3/src/sql/pgpool-recovery
make&&makeinstall
psql-fpgpool-recovery.sqltemplate1
或
psqltemplate1
=#CREATEEXTENSIONpgpool_recovery

配置pgpool

1.配置pcp.conf

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

cd/usr/local/pgpool
cpetc/pcp.conf.sampleetc/pcp.conf
bin/pg_md5-m-upgpool-p
会自动生成pool_passwd
postgres:md533c14731dfdfsdfsdf91e8d10c4bff5
catetc/pcp.conf
pgpool:md533c14731dfdfsdfsdf91e8d10c4bff5

2.配置pool_hba.conf

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

cd/usr/local/pgpool
cpetc/pool_hba.conf.sampleetc/pool_hba.conf
bin/pg_md5-m-upostgres-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
cpetc/pgpool.conf.sampleetc/pgpool.conf
#创建相关目录
mkdir-p/var/run/pgpool
mkdir-p/usr/local/pgpool/logs
vimetc/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-Upostgres-p9999-h10.10.10.56
Passwordforuserpostgres:
psql(9.2.15)
Type"help"forhelp.
postgres=#

测试

1.创建bench_replication数据库

psql-Upostgres-p9999-h10.10.10.56
Passwordforuserpostgres:
psql(9.2.15)
Type"help"forhelp.
postgres=#createdatabasebench_replication;

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

psql-Upostgres-p5432-h10.10.10.56
Passwordforuserpostgres:
psql(9.2.15)
Type"help"forhelp.

postgres=#\list
Listofdatabases
Name|Owner|Encoding|Collate|Ctype|Accessprivileges
-------------------+----------+----------+-------------+-------------+-----------------------
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
(4rows)

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

2.pgbench测试

#安装pgbench
yuminstallpostgresql-contrib
pgbench-i-h10.10.10.56-Upostgres-p9999bench_replication

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

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

总结

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

补充：pgpool使用中遇到的坑总结

1，复制模式可靠性低

最早时候使用的是复制模式，数据到pgpool然后pgpool分别写入n个postgres.发现经常出现数据不一致问题，导致最终只有一个数据库可用

2，onlinerecovery

基于PIRT的onlinerecovery配置复杂

3，基于流复制的主备模式

这个用到postgres9的新特性，前期配置测试都很easy，failover也很好用，但是当服务连接上pgpool时，事务往往报错postgreserror:failedtoreadkindfrombackend，这个我在之前的文章中提到过，至今无法解决。

4，连接数的困扰

num_init_children原来理解成了一个池的大小，如果超过了会自动扩增，但是实际上往往不够用，确切的说该值也是pgpool-II支持的从客户端发起的最大并发连接数。

所以这个值配的尽量大些，并且对这个值的更改必须重启pgpool.

5,client_idle_limit不要配置

当一个客户端在执行最后一条查询后如果空闲到了client_idle_limit秒数，到这个客户端的连接将被断开.连接不应该让pgpool来断开，应该是应用主动去断开。如果让pgpool去断开，会导致客户端不可用。

当然pgpool也有一个好处，能够快速找到连接的应用。因为每个连接都是单独的进程，所以启动后会有num_init_children个进程可以接受连接

使用#ps-ef|greppgpool可以看到

pgpool:waitforconnectionrequest的进程是空进程，等待连接。

pgpool:postgresdbtest10.115.53.167(51883)idle这些进程是使用中的进程，并且可以看到是来自哪台机器，什么用户，连接的是什么数据库。

当然使用select*frompg_stat_activity也能查到连接情况

以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。