postgresql数据库连接池pgbouncer

pqwsa

7.1. 安装 pgbouncer




7.1.1. Ubuntu



　　Ubuntu 13.04

$ apt-cache search pgbouncer
pgbouncer - lightweight connection pooler for PostgreSQL
$ sudo apt-get install pgbouncer

$ dpkg -L pgbouncer
/.
/usr
/usr/share
/usr/share/man
/usr/share/man/man5
/usr/share/man/man5/pgbouncer.5.gz
/usr/share/man/man1
/usr/share/man/man1/pgbouncer.1.gz
/usr/share/doc
/usr/share/doc/pgbouncer
/usr/share/doc/pgbouncer/config.html
/usr/share/doc/pgbouncer/README
/usr/share/doc/pgbouncer/README.Debian
/usr/share/doc/pgbouncer/README.html
/usr/share/doc/pgbouncer/faq.html
/usr/share/doc/pgbouncer/copyright
/usr/share/doc/pgbouncer/todo.html
/usr/share/doc/pgbouncer/examples
/usr/share/doc/pgbouncer/examples/pgbouncer.ini.gz
/usr/share/doc/pgbouncer/examples/userlist.txt
/usr/share/doc/pgbouncer/NEWS.gz
/usr/share/doc/pgbouncer/AUTHORS
/usr/share/doc/pgbouncer/usage.html
/usr/share/doc/pgbouncer/changelog.Debian.gz
/usr/sbin
/usr/sbin/pgbouncer
/etc
/etc/init.d
/etc/init.d/pgbouncer
/etc/pgbouncer
/etc/pgbouncer/userlist.txt
/etc/pgbouncer/pgbouncer.ini
/etc/default
/etc/default/pgbouncer




7.1.2. CentOS



# yum install pgbouncer

# rpm -ql pgbouncer.x86_64 0:1.5.4-1.rhel6
/etc/pgbouncer/mkauth.py
/etc/pgbouncer/mkauth.pyc
/etc/pgbouncer/mkauth.pyo
/etc/pgbouncer/pgbouncer.ini
/etc/rc.d/init.d/pgbouncer
/etc/sysconfig/pgbouncer
/usr/bin/pgbouncer
/usr/share/doc/pgbouncer-1.5.4
/usr/share/doc/pgbouncer-1.5.4/AUTHORS
/usr/share/doc/pgbouncer-1.5.4/NEWS
/usr/share/doc/pgbouncer-1.5.4/README
/usr/share/man/man1/pgbouncer.1.gz
/usr/share/man/man5/pgbouncer.5.gz


 配置 pgbouncer



databases 配置

[databases]
main = host=localhost port=5432 dbname=mydb user=myuser password=mypass connect_query='SELECT 1'

pgbouncer

[pgbouncer]
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
stats_users = stats, postgres
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 20

例 7.1. /etc/pgbouncer/pgbouncer.ini

# grep -v '^;' /etc/pgbouncer/pgbouncer.ini | grep -v '^$'

[databases]
main = host=localhost port=5432 dbname=test user=test password=test connect_query='SELECT 1'
[pgbouncer]
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = 127.0.0.1
listen_port = 1521
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
stats_users = stats, postgres
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 20