PgBouncer连接池来自于PostgreSQL社区，它可以为多个数据库管理连接池，并且这些数据库可以位于不同的PostgreSQL后端。PgBouncer会为每一种数据库用户与数据库的组合建立一个池。一个被池化的连接只能被来自于同一个用户和数据库的另一个连接请求重用。

客户端应用不需要做软件修改，但是要连接到连接池的主机和端口而不是PostgreSQL的主机和端口。PgBouncer会创建新的数据库连接或者重用一个已有的连接。当客户端断开连接时，该连接会被返回给连接池以备重用。

1、安装pgbounce

PgBounce的安装有两种方式，源代码安装和安装rpm二进制软件包。这里使用rpm软件安装，如下：

[root@hdp06 ~]# yum -y install pgbouncer [root@hdp06 ~]# rpm -ql pgbouncer/etc/logrotate.d/pgbouncer/etc/pgbouncer/etc/pgbouncer/mkauth.py/etc/pgbouncer/mkauth.pyc/etc/pgbouncer/mkauth.pyo/etc/pgbouncer/pgbouncer.ini/etc/sysconfig/pgbouncer/usr/bin/pgbouncer/usr/lib/systemd/system/pgbouncer.service/usr/lib/tmpfiles.d/pgbouncer.conf/usr/share/doc/pgbouncer/usr/share/doc/pgbouncer/NEWS.rst/usr/share/doc/pgbouncer/README.rst/usr/share/doc/pgbouncer/pgbouncer.ini/usr/share/doc/pgbouncer/userlist.txt/usr/share/licenses/pgbouncer-1.9.0/usr/share/licenses/pgbouncer-1.9.0/COPYRIGHT/usr/share/man/man1/pgbouncer.1.gz/usr/share/man/man5/pgbouncer.5.gz/var/run/pgbouncer

2、配置pgbounce

编辑配置文件，修改以下内容：

[databases]kkdb= host=192.168.120.149 port=5432 user=kkuser password=redhatpostgres= host=192.168.120.149 port=5432 user=postgres password=redhat[pgbouncer]logfile = /var/log/pgbouncer/pgbouncer.logpidfile = /var/run/pgbouncer/pgbouncer.pidlisten_addr = *listen_port = 6432auth_type = md5auth_file = /etc/pgbouncer/userlist.txtadmin_users = postgres,kkuserpool_mode = sessionserver_reset_query = DISCARD ALLignore_startup_parameters = extra_float_digitsmax_client_conn = 1000default_pool_size = 200

上面的认证模式md5，所以指定认证文件的路径，并加入以下内容：

[root@hdp06 pgbouncer]# vi userlist.txt "postgres" "md5fe4b5cfa57349c59d9b18d6920de5f59""kkuser" "md5ad9501a851743f17648b943395f791e0"

其中的md5值，可以在数据库中通过md5将密码进行编码，如下：

postgres=# SELECT 'md5'||md5('redhat'||'postgres');

如果安装的是rpm二进制软件包，则使用下面的方法：

[root@hdp06 ~]# cd /etc/pgbouncer[root@hdp06 pgbouncer]# ./mkauth.py userlist.txt "host=192.168.120.149 port=5432 dbname=kkdb user=kkuser password=redhat"[root@hdp06 pgbouncer]# chown pgbouncer:pgbouncer userlist.txt--或者直接查询数据库postgres=# select usename, passwd from pg_shadow order by 1;

3、启动pgbouncer

如果通过源代码安装，只能使用非root用户启动。

[root@hdp06 pgbouncer]# systemctl enable pgbouncer[root@hdp06 pgbouncer]# systemctl start pgbouncer[root@hdp06 pgbouncer]# systemctl status pgbouncer● pgbouncer.service - A lightweight connection pooler for PostgreSQL   Loaded: loaded (/usr/lib/systemd/system/pgbouncer.service; enabled; vendor preset: disabled)   Active: active (running) since Thu 2018-12-13 17:13:07 CST; 5s ago  Process: 21134 ExecStart=/usr/bin/pgbouncer -d -q ${BOUNCERCONF} (code=exited, status=0/SUCCESS) Main PID: 21136 (pgbouncer)   CGroup: /system.slice/pgbouncer.service           └─21136 /usr/bin/pgbouncer -d -q /etc/pgbouncer/pgbouncer.iniDec 13 17:13:07 hdp06 systemd[1]: Starting A lightweight connection pooler for PostgreSQL...Dec 13 17:13:07 hdp06 systemd[1]: PID file /var/run/pgbouncer/pgbouncer.pid not readable (yet?) after start.Dec 13 17:13:07 hdp06 systemd[1]: Started A lightweight connection pooler for PostgreSQL.[root@hdp06 pgbouncer]# netstat -antpl|grep 6432tcp        0      0 0.0.0.0:6432            0.0.0.0:*               LISTEN      21136/pgbouncer     tcp6       0      0 :::6432                 :::*                    LISTEN      21136/pgbouncer

4、测试pgbouncer

[postgres@pg07 ~]$ psql -Upostgres -dpostgres -p6432 -h192.168.120.104Password for user postgres: psql (10.6)Type "help" for help.postgres=# \l

5、管理PgBouncer

PgBouncer有一个管理控制台，可以登录到pgbouncer虚拟数据库来访问它。该控制台接受类SQL命令，这些命令允许用户监控、重新配置和管理PgBouncer。

[postgres@pg07 ~]$ psql -Upostgres -dpgbouncer -p6432 -h192.168.120.104Password for user postgres: psql (10.6, server 1.9.0/bouncer)Type "help" for help.pgbouncer=# show clients;pgbouncer=# show servers;

如果变更了pgbouncer配置文件，不用重启，直接reload下就ok。

pgbouncer=# reload;

更多pgbouncer管理，请参考官方文档。