pgbouncer-install pgbouncer-install

<b>前言</b><b></b>

   每次应用程序和postgres连接，都会克隆出一个服务进程来为应用程序服务，在频繁的创建和销毁进程，会耗费比较多的资源。而pgbouncer会把与后端postgresql数据库的连接缓存住，当有前端请求时，只为分配一个空闲的连接给前端程序使用，这样就降低了资源的消耗。

<b>要求</b><b></b>

软件：pgbouncer-1.5.5.tar.gz，libevent-2.0.22-stable.tar.gz

<b>安装</b><b></b>

1、<b>上传软件到opt并解压</b><b></b>

# tar xvf libevent-2.0.22-stable.tar.gz

# tar xvf pgbouncer-1.5.5.tar.gz

2、<b>安装libevent</b><b></b>

# ./configure --prefix=/home/postgres/libevent

# make

# make install

3、<b>安装pgbouncer</b><b></b>

# ./configure --prefix=/home/postgres/pgbouncer/ --with-libevent=/home/postgres/libevent/

<b>4、配置.bash_profile,修改文件权限</b><b></b>

# cp /opt/pgbouncer-1.5.5/etc/pgbouncer.ini /home/postgres/pgbouncer/

# chown -R postgres:postgres pgbouncer/

# chown -R postgres:postgres libevent/

# su - postgres

$ vim .bash_profile

export LD_LIBRARY_PATH=/home/postgres/libevent/lib:$LD_LIBRARY_PATH

<b>5、修改配置文件pgbouncer.ini文件并参考该文件要求创建userlist.txt</b>

$ vim pgbouncer.ini

[databases]

postgres = host=localhost port=5432 dbname=postgres user=postgres password=postgres connect_query='SELECT 1'

[pgbouncer]

logfile = /home/postgres/pgbouncer/pgbouncer.log

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

listen_addr = *

listen_port = 6432

auth_type = trust

auth_file = /home/postgres/pgbouncer/userlist.txt

pool_mode = transaction

server_reset_query = DISCARD ALL

max_client_conn = 100

default_pool_size = 20

$ vim userlist.txt

"postgres" "postgres"

<b>6、启动pgbouncer</b><b></b>

./pgbouncer  -d /home/postgres/pgbouncer/pgbouncer.ini

<b>7、登录</b><b></b>

$ psql -p 6432 postgres

<b>8、停止pgbouncer</b><b></b>

$ cat /home/postgres/pgbouncer/pgbouncer.pid

$ kill number

<b>9、查看连接池信息</b><b></b>

$ psql -p 6432 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 [&lt;db&gt;]

RESUME [&lt;db&gt;]

KILL &lt;db&gt;

SUSPEND

SHUTDOWN

SHOW

=# show pools;

<b>报错信息</b><b></b>

error 1:

[postgres@postgres93 bin]$ ./pgbouncer -d /home/postgres/pgbouncer/pgbouncer.ini

./pgbouncer: error while loading shared libraries: libevent-2.0.so.5: cannot open shared object file: No such file or directory

[solution]

error 2:

[postgres@postgres93 ~]$ psql -p 6432 pgbouncer

psql: ERROR:  not allowed

$ vim pgbouncer.init

admin_users = postgres

需要重启pgbouncer

参考文档：

http://pgbouncer.github.io/downloads/

<a href="https://yq.aliyun.com/articles/43328">https://yq.aliyun.com/articles/43328</a>

http://www.bubuko.com/infodetail-1203143.html

http://blog.csdn.net/lk_db/article/details/77939005?locationNum=8&amp;fps=1