pgbouncer就是一连接池。现在有好多模块，plproxy，pgpool什么的，都有这功能。pgbouncer与之相比，最大的好处是简单，小型。 如果不需要负载均衡什么的功能，用pgbouncer就足够了。 PgBouncer的特点 a.内存消耗低(默认为2k/连接)，因为Bouncer不需要每次都接受完整的数据包 b.可以把不同的数据库连接到一个机器上，而对客户端保持透明 c.支持在线的重新配置而无须重启 环境： red hat 6.5 pg9.6.1 pgbouncer 1.7.2 libevent 2.0.21 1、安装过程 安装pgbouncer之前需要先安装libevent 安装libevent 下载地址https://github.com/downloads/libevent/libevent/libevent-2.0.21-stable.tar.gz [postgres@node1 opt]$mkdir /home/postgres/libevent [postgres@node1 opt]$ tar -zxvf libevent-2.0.21-stable.tar.gz [postgres@node1 opt]$ cd libevent-2.0.21-stable [postgres@node1 libevent-2.0.21-stable]$./configure --prefix=/home/postgres/libevent [postgres@node1 libevent-2.0.21-stable]$make [postgres@node1 libevent-2.0.21-stable]$make install 安装pgbouncer 下载地址：https://pgbouncer.github.io/downloads/ [postgres@node1 opt]$mkdir /home/postgres/pgbouncer [postgres@node1 opt]$ tar -zxvf pgbouncer-1.7.2.tar.gz [postgres@node1 opt]$ cd pgbouncer-1.7.2 [postgres@node1 pgbouncer-1.7.2]$ ./configure --prefix=/home/postgres/pgbouncer/ --with-libevent=/home/postgres/libevent/ 如果出现以下错误 checking for libevent... configure: error: not found, cannot proceed 指定libevent的安装目录 ./configure --prefix=/home/postgres/pgbouncer/ --with-libevent=/home/postgres/libevent/ [postgres@node1 pgbouncer-1.7.2]$ make [postgres@node1 pgbouncer-1.7.2]$ make install 查看pgbouncer是否安装成功，可以通过查看config.log中最后的返回值exit来确认，0是成功1是失败. [postgres@node1 pgbouncer-1.7.2]$ tail -f config.log #define HAVE_LSTAT 1 #define HAVE_LIBEVENT 1 #define HAVE_EVENT_LOOPBREAK 1 #define HAVE_EVENT_BASE_NEW 1 #define HAVE_EVDNS_BASE_NEW 1 #define USE_EVDNS 1 #define USUAL_LIBSSL_FOR_TLS 1 #define USUAL_TLS_CA_FILE "/etc/ssl/cert.pem" configure: exit 0 2、配置 配置pgbouncer的cfg文件 [postgres@node1 pgbouncer]$ mkdir /home/postgres/pgbouncer/config/ [postgres@node1 pgbouncer]$ ls NEWS.rst pgbouncer.ini README.rst userlist.txt [postgres@node1 pgbouncer]$ cp pgbouncer.ini /home/postgres/pgbouncer/config/ [postgres@node1 pgbouncer]$vim pgbouncer.ini [databases] postgres=host=192.168.11.70 port=5432 user=postgres dbname=postgres pool_size=100 [pgbouncer] logfile = /home/postgres/pgbouncer/pgbouncer.log pidfile = /home/postgres/pgbouncer/pgbouncer.pid admin_users = postgres pool_mode = transaction listen_addr = 192.168.11.70 listen_port = 6432 auth_type = trust auth_file = /home/postgres/pgbouncer/userlist.txt 配置用户密码文件userlist.txt [postgres@node1 pgbouncer]$ cat userlist.txt "postgres" "postgres" 配置环境变量 [postgres@node1 pgbouncer]$ cat ~/.bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs #PATH=$PATH:$HOME/bin export PGDATA=/home/postgres/pgsql96/data export LD_LIBRARY_PATH=/opt/psql-9.6/lib:$LD_LIBRARY_PATH:/home/postgres/libevent/lib export PATH=/opt/psql-9.6/bin:$PATH:/home/postgres/pgbouncer/bin/ [postgres@node1 pgbouncer]$ source ~/.bash_profile 启动pgbouncer [postgres@node1 pgbouncer]$ /home/postgres/pgbouncer/bin/pgbouncer -d /home/postgres/pgbouncer/config/pgbouncer.ini 2017-02-05 15:22:23.712 2430 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 100, max fds possible: 210 出现上面信息说明pgbouncer启动成功 [postgres@node1 pgbouncer]$ psql -h 192.168.11.70 -p 6432 -U postgres psql (9.6.1) Type "help" for help. postgres=# \d List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | a | table | user1 public | emp | table | postgres (2 rows) postgres=# 连接pgbouncer本身的DB [postgres@node1 pgbouncer]$ psql -h 192.168.11.70 -p 6432 -U postgres -d pgbouncer psql (9.6.1, server 1.7.2/bouncer) Type "help" for help. pgbouncer=# \d ERROR: failure server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. 如果报错： [postgres@node1 pgbouncer]$ psql -p 6432 pgbouncer psql: ERROR: not allowed 去修改pgbouncer.ini文件中的 admin_users ;admin_users = user2, someadmin, otheradmin admin_users=postgres9.6 Pgbouncer 三中模式： 1.session pooling：一个客户端连接对应一个服务器连接。客户端断开，服务器连接回收到连接池中。是默认的模式,每开启一个进程，DB端也会开启一个新的进程 2.transaction pooling：服务器连接在一个事务里是才给予客户端，事务结束，连接回收回连接池。 3.statement pooling：不允许多语句的事务，最激进的模式。主要给pl/proxy使用。是基于每个查询的，开启此模式不适合执行事务，会报错 Show config; #查看相关配置 Show stats; Show lists;#查看连接相关信息，如，数据库个数，空闲连接数等等 Show pools;#查看池中连接信息 Show databases;#查看相关数据库 Show clients; #查看连接数 如果修改了配置文件，那么需要reload重新加载 pgbouncer=# reload; RELOAD Pgbouncer是一个针对PostgreSQL数据库的轻量级连接池，任何目标应用都可以把 pgbouncer 当作一个 PostgreSQL 服务器来连接，然后pgbouncer 会处理与服务器连接，或者是重用已存在的连接。 pgbouncer 的目标是降低因为新建到 PostgreSQL 的连接而导致的性能损失。
