Postgresql - Middleware - Pgbouncer Installation

pgbouncer is a PostgreSQL connection pooler. The aim of pgbouncer is to lower the performance impact of opening new connections to PostgreSQL.

为了连接的复用。减少重复打开数据库连接对数据库性能造成的影响。

 

支持几种类型的pool：

Session pooling：当客户端连接，整个连接期间，服务端连接都将分给它。客户端断开时，服务端连接被放回池中。默认。

Transaction pooling：在事务期间分配给客户端，当pgbouncer意识到事务结束，服务端连接被放回池中。

Statement pooling：最积极的方法。查询完成后，立即将服务器连接放回池中。多语句事务在这种模式下是不允许的。

 

 

 

准备：

yum -y install libevent-devel libevent

 

1. 下载：

# wget http://pgbouncer.github.io/downloads/files/1.8.1/pgbouncer-1.8.1.tar.gz

# tar -zxvf pgbouncer-1.8.1.tar.gz

# cd pgbouncer-1.8.1

 

2. 安装

# ./configure --prefix=/usr/local --with-libevent=libevent-prefix

# make && make install

 

3. 创建ini文件和users文件

# mkdir /usr/local/pgbouncer

# cd /usr/local/pgbouncer

# vim pgbouncer.ini

[databases] mytest_3 = host=dbwtest03bc.daodao.com port=5432 dbname=mytest

mytest_21 = host=dbw21as.daodao.com port=5432 dbname=mytest [pgbouncer] listen_port = 6543 listen_addr = * auth_type = md5 auth_file = /usr/local/pgbouncer/users.txt logfile = /usr/local/pgbouncer/pgbouncer.log pidfile = /usr/local/pgbouncer/pgbouncer.pid admin_users = mytest

 

这是明文密码

# vim users.txt

"mytest" "mytest"

 

4. 执行pgbouncer启动不能使用root用户，将文件夹所有者改成postgres用户

# chmod -R postgres /usr/local/pgbouncer

 

5. 启动

# su - postgres

$ pgbouncer -d pgbouncer.ini   或   $ pgbouncer -v pgbouncer.ini

 

6. 连接

# psql -U mytest -p 6543 pgbouncer

pgbouncer=# show help;

NOTICE: Console usage

DETAIL:

SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION

SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM

SHOW DNS_HOSTS|DNS_ZONES

SHOW STATS|STATS_TOTALS|STATS_AVERAGES

SET key = arg

RELOAD

PAUSE [<db>]

RESUME [<db>]

DISABLE <db>

ENABLE <db>

KILL <db>

SUSPEND

SHUTDOWN

SHOW

 

# psql -U mytest -p 6543 mytest_3

mytest_3=# \d

List of relations

Schema | Name | Type | Owner

--------+---------------------+---------------+---------

public | test01 | table | dbadmin

public | test01_id_seq | sequence | dbadmin

(2 rows)