PostgreSQL数据库开源连接池pgbouncer的使用

首先，先介绍一个postgresql的资源网站:http://pgfoundry.org/ 这里面有非常多和POSTGRESQL相关的资源。

pgbouncer是一个非常小型的连接池，最经典的用法是在plproxy环境中。

这里介绍一下在LINUX平台下的安装和使用，安装时需要libevent,gcc,make等常用工具。

下载：

源码 http://pgfoundry.org/frs/download.php/2608/pgbouncer-1.3.2.tgz

文档 http://developer.skype.com/SkypeGarage/DbProjects/PgBouncer

libevent源码 http://monkey.org/~provos/libevent/ 下一个稳定版

安装：

1. 安装LIBEVENT

解包

cd libevent-1.4.13-stable

less README  查看一下帮助文档

./configure && make

make install

2.安装PGBOUNCER

解包

cd pgbouncer-1.3.2

less README

./configure –prefix=/opt/pgbouncer –with-libevent=/usr/local/

make

make install

3.修改环境变量

su – postgres

export LD_LIBRARY_PATH=/usr/local/lib:$LD_LIBRARY_PATH

4.编写配置文件与用户密码文件

su – postgres

mkdir -p ~/pgconfig

cd ~/pgconfig

vi users.txt

“gamehall” “*************”

vi config.ini

[databases]

gamehall = host=192.168.*.* dbname=**** port=****

[pgbouncer]

pool_mode = transaction

listen_port = 9601

listen_addr = *

auth_type = md5

auth_file = /home/postgres/pgconfig/users.txt

logfile = /dev/null

pidfile = /home/postgres/pgconfig/pgbouncer.pid

max_client_conn = 1500

default_pool_size = 200

reserve_pool_timeout = 0

reserve_pool_size = 30

server_reset_query = DISCARD ALL;

admin_users = pgbouncer_admin

stats_users = pgbouncer_guest

ignore_startup_parameters = extra_float_digits

5.启动pgbouncer

su – postgres

/opt/pgbouncer/bin/pgbouncer -d /home/postgres/pgconfig/config.ini

6.测试

psql -h 127.0.0.1 -p 9601 -U gamehall -d **** -W

正常

PGBOUNCER配置介绍:

非常详细的介绍在DOC里面有，这里简单介绍一下连接池模式

Session pooling::

Most polite method.  When client connects, a server connection

will be assigned to it for the whole duration it stays connected.

When client disconnects, the server connection will be put back

into pool.

Transaction pooling::

Server connection is assigned to client only during a transaction.

When !PgBouncer notices that transaction is over, the server

will be put back into pool.  This is a hack as it breaks application

expectations of backend connection.  You can use it only when

application cooperates with such usage by not using features

that can break.  See the table below for breaking features.

Statement pooling::

Most aggressive method.  This is transaction pooling with a twist -

multi-statement transactions are disallowed.  This is meant to enforce

“autocommit” mode on client, mostly targeted for PL/Proxy.