今天在虚拟机上整理了下pgbouncer的安装使用过程，记录如下。
说明：pgbouncer是一款轻量级针对postgresql的数据库连接工具，可以对客户端的连接做限制，防止恶意连接，另外也可以减少数据库的实际连接数，从而减少数据库的开销。
环境：
VMWARE 8
CentOS 5.7
PG 9.1.2
pgbouncer 1.5.2
libevent-2.0.19
一.安装
1.下载pgbouncer的安装包 http://pgfoundry.org/frs/?group_id=1000258&release_id=1952,本次下载的安装包是pgbouncer-1.5.2.tar.gz 2.下载libevent包 http://monkey.org/~provos/libevent/，见pgbouncer源码包中的Readme 这玩意也是个好东西，在很多地方用到 3.安装libevent $ cd libevent-2.0.19-stable $ ./configure --prefix=/home/postgres/libevent $ make $ make install 4.安装pgbouncer $ cd pgbouncer-1.5.2 $ ./configure --prefix=/home/postgres/pgbouncer/ --with-libevent=/home/postgres/libevent/ $ make $ make install
查看pgbouncer是否安装成功，可以通过查看config.log中最后的返回值exit来确认，0是成功1是失败.
二.配置
1.配置pgbouncer的cfg文件
[postgres@localhost config]$ pwd /home/postgres/pgbouncer/config [postgres@localhost config]$ more pgbouncer.ini [databases] f_game = host=127.0.0.1 port=1233 dbname=test_db user=test password=test [pgbouncer] listen_port = 1999 listen_addr = 127.0.0.1 auth_type = md5 auth_file = /home/postgres/pgbouncer/user.txt logfile = /home/postgres/pgbouncer/pgbouncer.log pidfile = /home/postgres/pgbouncer/pgbouncer.pid admin_users = u_kenyon pool_mode = Transaction
[postgres@localhost pgbouncer]$ pwd /home/postgres/pgbouncer [postgres@localhost pgbouncer]$ more user.txt "u_kenyon" "123"
三.启动
1.启动命令
[postgres@localhost config]$ pgbouncer -d pgbouncer.ini 2012-08-21 00:29:55.573 4247 LOG File descriptor limit: 1024 (H:1024),max_client_conn: 100,max fds possible: 130
2.查看日志
tail -f /home/postgres/pgbouncer/pgbouncer.log
3.登录
[postgres@localhost pgbouncer]$ psql -h 127.0.0.1 -p 1999 -U u_kenyon f_game Password for user u_kenyon: psql (9.1.2) Type "help" for help. f_game=> \d List of relations Schema | Name | Type | Owner --------+----------+-------+---------- public | t_kenyon | table | postgres public | test | table | test public | test2 | table | postgres (3 rows) f_game=> \q
4.参数使用例子
连接pgbouncer本身的DB
[postgres@localhost config]$ psql -h 127.0.0.1 -p 1999 -U u_kenyon pgbouncer Password for user u_kenyon: psql (9.1.2,server 1.5.2/bouncer) WARNING: psql version 9.1,server version 1.5. Some psql features might not work. Type "help" for help. pgbouncer=# show help; NOTICE: Console usage DETAIL: SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM SHOW DNS_HOSTS|DNS_ZONES SET key = arg RELOAD PAUSE [] RESUME [] KILL SUSPEND SHUTDOWN SHOW pgbouncer=# show config; key | value | changeable ---------------------------+----------------------------------------+------------ job_name | pgbouncer | no conffile | pgbouncer.ini | yes logfile | /home/postgres/pgbouncer/pgbouncer.log | yes pidfile | /home/postgres/pgbouncer/pgbouncer.pid | no listen_addr | 127.0.0.1 | no listen_port | 1999 | no listen_backlog | 128 | no unix_socket_dir | /tmp | no unix_socket_mode | 511 | no unix_socket_group | | no auth_type | md5 | yes auth_file | /home/postgres/pgbouncer/user.txt | yes pool_mode | transaction | yes max_client_conn | 100 | yes default_pool_size | 20 | yes min_pool_size | 0 | yes reserve_pool_size | 0 | yes reserve_pool_timeout | 5 | yes syslog | 0 | yes syslog_facility | daemon | yes syslog_ident | pgbouncer | yes user | | no autodb_idle_timeout | 3600 | yes server_reset_query | DISCARD ALL | yes server_check_query | select 1 | yes server_check_delay | 30 | yes query_timeout | 0 | yes query_wait_timeout | 0 | yes client_idle_timeout | 0 | yes client_login_timeout | 60 | yes idle_transaction_timeout | 0 | yes server_lifetime | 3600 | yes server_idle_timeout | 600 | yes server_connect_timeout | 15 | yes server_login_retry | 15 | yes server_round_robin | 0 | yes suspend_timeout | 10 | yes ignore_startup_parameters | | yes disable_pqexec | 0 | no dns_max_ttl | 15 | yes dns_zone_check_period | 0 | yes pkt_buf | 2048 | no sbuf_loopcnt | 5 | yes tcp_defer_accept | 1 | yes tcp_socket_buffer | 0 | yes tcp_keepalive | 1 | yes tcp_keepcnt | 0 | yes tcp_keepidle | 0 | yes tcp_keepintvl | 0 | yes verbose | 0 | yes admin_users | u_kenyon | yes stats_users | | yes stats_period | 60 | yes log_connections | 1 | yes log_disconnections | 1 | yes log_pooler_errors | 1 | yes (56 rows)
本次测试环境修改的参数不多，还有其他参数如server_idle_timeout，max_connections也比较重要，后期再写。
使用reload可以不用重启pgbouncer而使修改的参数生效
pgbouncer=# reload; RELOAD pgbouncer=#
5.使用效果
在连接池端看连接数：
pgbouncer=# show clients; type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | ptr | link ------+----------+-----------+--------+-----------+-------+------------+------------+---------------------+---------------------+----------+------ C | u_kenyon | f_game | active | 127.0.0.1 | 59555 | 127.0.0.1 | 1999 | 2012-08-21 00:32:19 | 2012-08-21 01:47:14 | 0xbc4670 | C | u_kenyon | f_game | active | 127.0.0.1 | 59558 | 127.0.0.1 | 1999 | 2012-08-21 00:32:35 | 2012-08-21 02:09:04 | 0xbc4940 | C | u_kenyon | f_game | active | 127.0.0.1 | 59573 | 127.0.0.1 | 1999 | 2012-08-21 02:09:08 | 2012-08-21 02:09:10 | 0xbc43a0 | C | u_kenyon | f_game | active | 127.0.0.1 | 59576 | 127.0.0.1 | 1999 | 2012-08-21 02:09:44 | 2012-08-21 02:09:46 | 0xbc4aa8 | C | u_kenyon | f_game | active | 127.0.0.1 | 59578 | 127.0.0.1 | 1999 | 2012-08-21 02:09:58 | 2012-08-21 02:10:10 | 0xbc4c10 | C | u_kenyon | pgbouncer | active | 127.0.0.1 | 59570 | 127.0.0.1 | 1999 | 2012-08-21 02:01:58 | 2012-08-21 02:10:12 | 0xbc4508 | C | u_kenyon | pgbouncer | active | 127.0.0.1 | 59574 | 127.0.0.1 | 1999 | 2012-08-21 02:09:17 | 2012-08-21 02:09:29 | 0xbc47d8 | (7 rows) pgbouncer=# show fds; fd | task | user | database | addr | port | cancel | link | client_encoding | std_strings | datestyle | timezone ----+--------+----------+----------+-----------+-------+---------------------+------+-----------------+-------------+-----------+------------ 8 | pooler | | | 127.0.0.1 | 1999 | 0 | 0 | | | | 9 | pooler | | | unix | 1999 | 0 | 0 | | | | 13 | client | u_kenyon | f_game | 127.0.0.1 | 59555 | 8879718699069493200 | 0 | UTF8 | on | ISO,MDY | US/Pacific 15 | client | u_kenyon | f_game | 127.0.0.1 | 59558 | 2718240829565746463 | 0 | UTF8 | on | ISO,MDY | US/Pacific 14 | client | u_kenyon | f_game | 127.0.0.1 | 59573 | 9376066101244793463 | 0 | UTF8 | on | ISO,MDY | US/Pacific 16 | client | u_kenyon | f_game | 127.0.0.1 | 59576 | 9911177087544032756 | 0 | UTF8 | on | ISO,MDY | US/Pacific 17 | client | u_kenyon | f_game | 127.0.0.1 | 59578 | 7987308370302986343 | 0 | UTF8 | on | ISO,MDY | US/Pacific 12 | server | test | f_game | 127.0.0.1 | 1233 | 20770794712503 | 0 | UTF8 | on | ISO,MDY | US/Pacific (8 rows)
在DB端看连接数：
f_game=> select count(1) from pg_stat_activity;
count
-------
2
(1 row)
只有2个,看来池的效果已经初显成效,可预计高并发的时候池连接的可重复性使用，实际连接到DB端的进程并不是很多.
6.pool_mode说明
有三种模式:session,transaction,statement
session是默认的模式,每开启一个进程，DB端也会开启一个新的进程
transaction是基于事务模式的
statement是基于每个查询的，开启此模式不适合执行事务，会报错
原文见安装后pgbouncer中的usage.txt,本次文件地址 /home/postgres/pgbouncer/share/doc/pgbouncer
Session pooling :: Most polite method. When client connects,a server connection will be assigned to it for the whole duration the client stays connected. When the client disconnects,the server connection will be put back into the pool. This is the default method. Transaction pooling :: A server connection is assigned to client only during a transaction. When PgBouncer notices that transaction is over,the server connection will be put back into the pool. Statement pooling :: Most aggressive method. The server connection will be put back into pool immediately after a query completes. Multi-statement transactions are disallowed in this mode as they would break.
四.总结 1.本次在虚拟机上测试，使用的模式是修改后的transcation模式，在服务端开启6个psql进程，但在数据库端实际只有1个连接，池的效果可见一斑，对并发高的访问，可以有效减少DB端的连接进程，减少消耗。 2.测试过程中，假如第一次访问被拒绝以后(不是密码不对)，后续访问会直接拒绝，此举可以有效降低攻击性的访问。
原文链接：https://www.f2er.com/postgresql/193863.html