pgbouncer是postgreSQL的轻量级的连接池。
1、ession pooling/会话连接池
2、Transaction pooling/事务连接池
3、Statement pooling/语句连接池
1、源码下载
[root@testsource]#wget https://github.com/downloads/libevent/libevent/libevent-2.0.21-stable.tar.gz
[root@testsource]# wget http://pgfoundry.org/frs/download.php/3393/pgbouncer-1.5.4.tar.gz
2、编译libevent
[root@testsource]# tar zxf libevent-2.0.21-stable.tar.gz
[root@testsource]# cd libevent-2.0.21-stable
[root@testlibevent-2.0.21-stable]# ./configure --prefix=/usr/local/libevent
[root@testlibevent-2.0.21-stable]#gmake
[root@testlibevent-2.0.21-stable]#gmake install
3、加载libvent动态库
[root@testlibevent-2.0.21-stable]# cd /etc/ld.so.conf.d/
[root@testlibevent-2.0.21-stable]# vim libevent2.0.21.conf
/usr/local/libevent/lib
[root@test ld.so.conf.d]# cat libevent2.0.21.conf
/usr/local/libevent/lib
[root@testld.so.conf.d]#ldconfig
4、编译pgbouncer
[root@testsource]# tar zxf pgbouncer-1.5.4.tar.gz
[root@testpgbouncer-1.5.4]# ./configure --prefix=/usr/local/pgbouncer --with-libevent=/usr/local/libevent
[root@test source]# gmake
[root@test source]# gmake install
5、配置运行环境变量
[root@testpgbouncer-1.5.4]# vim /etc/profile
PATH=/usr/local/pgsql/bin:/usr/local/pgbouncer/bin:$PATH
export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE HISTCONTROL
注意：配置profile一个是配置postgresql的安装路径，另外一个是pgbouncer的安装路径
配置好后需要重载一下
source /etc/profile
6、配置pgbouncer运行环境
[root@testpostgres]# su postgres
[postgres@test~]$ cd /home/postgres/
[postgres@test~]$ mkdir pgbouncer/
[postgres@test~]$ cd pgbouncer/
[postgres@pgbouncer]$vim user.txt
"pgb_admin" "admin"
"pgb_user" "user"
[postgres@pgbouncer]$ vim pgbouncer.ini
[databases]
update_db= host=192.168.1.12 port=5432dbname=mydbuser=update_user password=xxx
read_db = host=192.168.1.12port=5432dbname=mydbuser=read_userpassword=xxx
[pgbouncer]
listen_port = 5433
listen_addr = *
auth_type = md5
auth_file = /home/postgres/pgbouncer/user.txt
logfile = /home/postgres/pgbouncer/pgbouncer.log
pidfile = /home/postgres/pgbouncer/pgbouncer.pid
admin_users = pgb_admin
pool_mode = session
max_client_conn = 6000
default_pool_size = 128
7、pgbouncer服务管理
启动服务
[postgres@dywl pgbouncer]$ /usr/local/pgbouncer/bin/pgbouncer -d /home/postgres/pgbouncer/pgbouncer.ini
服务重载
[postgres@dywl pgbouncer]$ /usr/local/pgbouncer/bin/pgbouncer -R -d /home/postgres/pgbouncer/pgbouncer.ini
停止服务
[postgres@dywl pgbouncer]$ psql -p 5432 pgbouncer -h 127.0.0.1 -U pgb_admin -c 'shutdown'
重载
修改为无密码管理员登录
使用pgbouncer服务
cd /home/postgres/pgbouncer/
su postgres
/usr/local/pgbouncer/bin/pgbouncer -d /home/postgres/pgbouncer/pgbouncer.ini
/usr/local/pgbouncer/bin/pgbouncer -R -d /home/postgres/pgbouncer/pgbouncer.ini
psql -h 127.0.0.1 -U pgb_admin -d pgbouncer -p 5433
mydb=# create role readonly_user with login password '000';
CREATE ROLE
mydb=#
mydb=# grant select on t1 to readonly_user;
GRANT
mydb=#
配置pgbouncer数据库
[databases]
pgbouncer数据库名称= host=Postgresql数据库主机ip或hostport=连接端口号dbname=数据库名称user=连接用户名password=连接密码pool_size=准许连接数