linux下的pgbouncer应用

pgbouncer是postgreSQL的轻量级的连接池

1、ession pooling/会话连接池

2、Transaction pooling/事务连接池

3、Statement pooling/语句连接池

 

pgbouncer安装

 

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=准许连接数

 

配置文件