pgbouncer安装练习

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
创建libevent的安装目录：
[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/
创建pgbouncer的安装目录
[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的安装目录以后再测试。
如果出现以下错误
checking for OpenSSL... configure: error: not found
切换到root用户，使用yum安装：
yum install openssl-devel* -y
切回原来的用户和目录后重新运行
[postgres@node1 pgbouncer-1.7.2]./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 ]$ mkdir /home/postgres/pgbouncer/config/
[postgres@node1 ]$ cd /home/postgres/pgbouncer/share/doc/pgbouncer
[postgres@node1 pgbouncer]$ ll
-rw-r--r-- 1 postgres postgres 36783 Mar  8 15:13 NEWS.rst
-rw-r--r-- 1 postgres postgres  8598 Mar  8 15:13 pgbouncer.ini
-rw-r--r-- 1 postgres postgres  4638 Mar  8 15:13 README.rst
-rw-r--r-- 1 postgres postgres    56 Mar  8 15:13 userlist.txt
[postgres@node1 pgbouncer]$ cp pgbouncer.ini /home/postgres/pgbouncer/config/
[postgres@node1 pgbouncer]$ cd /home/postgres/pgbouncer/config/
[postgres@node1 pgbouncer]$ vi pgbouncer.ini

[databases]
postgres=host=192.168.1.12 port=5432 user=postgres dbname=postgres pool_size=100
[pgbouncer]


logfile = /home/postgres/pgbouncer/pgbouncer.log
pidfile = /home/postgres/pgbouncer/pgbouncer.pid
auth_file = /home/postgres/pgbouncer/userlist.txt
auth_type = trust
listen_addr = 192.168.1.12
listen_port = 6432
pool_mode = transaction
admin_users = postgres  


配置用户密码文件userlist.txt
[postgres@node1 pgbouncer]$ cd /home/postgres/pgbouncer/
[postgres@node1 pgbouncer]$ vi userlist.txt
"postgres" "postgres"
配置环境变量
[postgres@node1 pgbouncer]$ vi ~/.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@pg pgbouncer]$ psql -h 192.168.1.12 -p 6432 -U postgres 
psql: ERROR:  no pg_hba.conf entry for host "192.168.1.12", user "postgres", database "postgres"
[postgres@pg data]$ vi $PGDATA/pg_hba.conf 
在ipv4 部分增加一行
host    all             all             0.0.0.0/0               trust
[postgres@node1 pgbouncer]$ psql -h 192.168.1.12 -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.1.12 -p 6432 -U postgres -d pgbouncer
假如出现以下问题：
psql: ERROR:  not allowed
大概是参数文件pgbouncer.ini出现问题，参数没有配置好。确认参数配置好以后，重启pgbouncer。
[postgres@pg config]$ cat /home/postgres/pgbouncer/pgbouncer.pid 
6459
[postgres@pg config]$ kill -9 6459
[postgres@pg config]$ /home/postgres/pgbouncer/bin/pgbouncer -d /home/postgres/pgbouncer/config/pgbouncer.ini
2017-03-09 09:39:35.409 6530 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 100, max fds possible: 210
2017-03-09 09:39:35.410 6530 LOG Stale pidfile, removing
[postgres@pg config]$ psql -h 192.168.1.12 -p 6432 pgbouncer
psql (9.4.4, 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.


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 的连接而导致的性能损失。