pgbouncer的安装使用

由于 PostgreSQL 采用进程模式，大量连接对数据库性能会产生较大影响，在大量连接情况下，
一般会在数据库和应用程序之间配置 pgbouncer，pgbouncer 可以配置在数据库主机上，也可以配置在
单独一台服务器上，但是采用了 pgbouncer 后，在维护方面和以前会有些区别

================================================================================
说白了就是一连接池。现在有好多模块，plproxy，pgpool什么的，都有这功能。pgbouncer与之相比，最大的好处是简单，小型。
如果不需要负载均衡什么的功能，用pgbouncer就足够了。

PgBouncer的特点
    a.内存消耗低(默认为2k/连接)，因为Bouncer不需要每次都接受完整的数据包
    b.可以把不同的数据库连接到一个机器上，而对客户端保持透明
    c.支持在线的重新配置而无须重启

///////////////////////////////////////////////////////////////////////
实际操作：
环境：
centos 6.5  64位
pg  9.1.1
pgbouncer 1.5.4  最新版本
libevent 2.0.22

1.下载pgbouncer的安装包
http://pgfoundry.org/frs/?group_id=1000258&release_id=1952

   
2.下载libevent包
http://monkey.org/~provos/libevent/
这玩意也是个好东西，在很多地方用到


3.安装libevent
[root@pgtina packages]# wget http://192.168.10.100/pgbouncer-1.5.4.tar.gz
[root@pgtina packages]# tar -xzvf pgbouncer-1.5.4.tar.gz
[root@pgtina packages]# ll
total 1176
drwxr-xr-x.  8 1000 1000   4096 Jan  5  2015 libevent-2.0.22-stable
-rw-r--r--.  1 root root 854987 Nov 12 14:51 libevent-2.0.22-stable.tar.gz
drwxr-xr-x. 10 1000 1000   4096 Nov 28  2012 pgbouncer-1.5.4
-rw-r--r--.  1 root root 339610 Nov 12 14:46 pgbouncer-1.5.4.tar.gz


cd /root/packages/
cd libevent-2.0.22-stable
./configure --prefix=/home/pgsql/libevent
make
make install



4.安装pgbouncer

/root/packages/pgbouncer-1.5.4
./configure --prefix=/home/pgsql/pgbouncer/ --with-libevent=/home/pgsql/libevent/
make
make install
查看pgbouncer是否安装成功，可以通过查看config.log中最后的返回值exit来确认，0是成功1是失败.

[root@pgtina pgbouncer-1.5.4]# tail -5 config.log
#define HAVE_EVENT_BASE_NEW 1
#define HAVE_EVDNS_BASE_NEW 1
#define USE_EVDNS 1

configure: exit 0    ----安装成功

二.配置
修改属主：
drwxr-xr-x. 5 root     root     4096 Nov 12 15:05 libevent
drwxr-xr-x. 4 root     root     4096 Nov 12 15:07 pgbouncer
[root@pgtina pgsql]# chown -R postgres:postgres pgbouncer/
[root@pgtina pgsql]# chown -R postgres:postgres libevent/

1.配置pgbouncer的cfg文件
cp /root/packages/pgbouncer-1.5.4/etc/pgbouncer.ini  /home/pgsql/pgbouncer/
chown postgres:postgres pgbouncer.ini


[postgres@localhost config]$ pwd
/home/postgres/pgbouncer/config
[postgres@localhost config]$ more pgbouncer.ini
[databases]

yunwei = host=192.168.12.250 port=5432 user=postgres  password=1qaz@WSX68 dbname=yunwei pool_size=100
tm_samples = host=192.168.12.250 port=5432 user=postgres  password=1qaz@WSX68 dbname=tm_samples pool_size=30

[databases]
yunwei = host=192.168.12.250 port=5432 user=postgres  password=** dbname=yunwei pool_size=50

[pgbouncer]
listen_port = 1999
listen_addr = 192.168.12.250
auth_type = md5
auth_file = /home/pgsql/pgbouncer/user.txt
logfile = /home/pgsql/pgbouncer/pgbouncer.log
pidfile = /home/pgsql/pgbouncer/pgbouncer.pid
admin_users = root
pool_mode = Transaction

//////////可选参数
max_client_conn = 100
default_pool_size = 900
reserve_pool_timeout = 0
reserve_pool_size = 30
server_reset_query = DISCARD ALL;
admin_users = pgadmin
stats_users = pgstats
ignore_startup_parameters = extra_float_digits,application_name,geqo
stats_period = 30
server_idle_timeout，max_connections也比较重要，后期再写。
md5: 基于md5的密码验证，auth_file中需要有普通文本和md5值两种形式的密码；
    crypt: 基于crypt的密码验证(man 3 crypt), auth_file必须包含文本密码；
    plain: 明文验证方式；
    trust: 不进行验证，但auth_file依然需要保存用户名；
    any: 也不进行验证，而且auth_file中不需要保存用户名了。但此种方式需要在pg_template1中明确说明用户名进行真实数据库的登录。
    如: pg_template1 = host=127.0.0.1 user=exampleuser dbname=template1.否则会报错的。
admin_users：列出哪些用户可以登录pgbouncer进行管理，以逗号进行分隔
stats_users：列出哪些用户可以登录pgbouncer进行只读操作，如可以列出服务器状态，访问链接等，但是不能执行reload。
/////////


2.配置用户密码文件users.txt
[postgres@pgtina pgbouncer]$ more user.txt
"root" "tina"
"postgres"  "test12"
"fenxi" "fenxi"
"sqluser" "sqlpassword"


三.启动
1.启动命令
[postgres@pgtina ~]$ /home/pgsql/pgbouncer/bin/pgbouncer -d /home/pgsql/pgbouncer/pgbouncer.ini

如果报错：
error while loading shared libraries: libevent-2.0.so.5:

就安装两个包：
[root@pgtina libevent-2.0.22-stable]# yum install libevent
[root@pgtina libevent-2.0.22-stable]# yum install libevent-devel

[postgres@pgtina ~]$ /home/pgsql/pgbouncer/bin/pgbouncer -d /home/pgsql/pgbouncer/pgbouncer.ini
2015-11-12 15:46:56.901 16634 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 100, max fds possible: 130   ---启动成功

2.查看日志
[root@pgtina pgbouncer]# ll
total 28
drwxr-xr-x. 2 postgres postgres 4096 Nov 12 15:32 bin
-rw-r--r--. 1 postgres postgres 5832 Nov 12 15:35 pgbouncer.ini
-rw-rw-r--. 1 postgres postgres  438 Nov 12 15:47 pgbouncer.log
-rw-r--r--. 1 postgres postgres    5 Nov 12 15:46 pgbouncer.pid
drwxr-xr-x. 4 postgres postgres 4096 Nov 12 15:32 share
-rw-r--r--. 1 postgres postgres   39 Nov 12 15:35 user.txt
tail -f /home/postgres/pgbouncer/pgbouncer.log

3.登录
[postgres@pgtina ~]$ psql -h 192.168.12.250 -p 1999 -U postgres -d yunwei     ---连接的时候一定要带-h ip
Password for user postgres:
psql (9.1.1)
Type "help" for help.

yunwei=# \d
                        List of relations
Schema |               Name                |   Type   |  Owner  
--------+-----------------------------------+----------+----------
public | ali_download_daily                | view     | postgres
public | am_android_wildmob_ru             | table    | postgres
public | am_android_wildmob_ru_id_seq      | sequence | postgres


4.参数使用例子
连接pgbouncer本身的DB
[postgres@pgtina pgbouncer]$ psql -h 192.168.12.250 -p 1999 -U root -d pgbouncer    --注意端口跟数据库的本身端口要区分开，密码就是tina ，且连接的时候一定要带-h ip
Password for user root:
psql (9.1.1, server 1.5.4/bouncer)
WARNING: psql version 9.1, server version 1.5.
         Some psql features might not work.
Type "help" for help.

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 [<db>]
RESUME [<db>]
KILL <db>
SUSPEND
SHUTDOWN
SHOW



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


pgbouncer=# show databases;
    name    |   host    | port |  database  | force_user | pool_size | reserve_pool
------------+-----------+------+------------+------------+-----------+--------------
mvds_url   | 192.168.12.250 | 5432 | mvds_url   | postgres   |        50 |            0
pgbouncer  |           | 1999 | pgbouncer  | pgbouncer  |         2 |            0
tm_samples | 192.168.12.250 | 5432 | tm_samples | postgres   |        30 |            0
yunwei     | 192.168.12.250 | 5432 | yunwei     | postgres   |       100 |            0



=====
2012-12-11 19:36:44.881 19072 WARNING C-0x3620a20: (nodb)/(nouser)@172.65.20.35:55419 Pooler Error: Unsupported startup parameter: extra_float_digits
报错很明显，有一个参数不支持， unsupported startup parameter  extra_float_digits ，pgbouncer中有一个参数是可以忽略启动参数的，叫ignore_startup_parameters

处理办法：
修改配置文件pgbouncer.ini，在[pgbouncer]下面新增一行
ignore_startup_parameters = extra_float_digits
重启pgbouncer，再次查看日志，该参数已被忽略，测试数据正常。

========

5.连接数测试
psql -U sqluser -d dw_report -p 1999
psql -U postgres -d devops -p 1999
Password for user postgres:
psql (9.1.1)
Type "help" for help.

devops=# select * from pg_tables where schemaname='public';
schemaname |  tablename   | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+--------------+------------+------------+------------+----------+-------------
public     | t_test_wubao | postgres   |            | f          | f        | f



pgbouncer=# show clients;
type |  user   | database  | state  |   addr    | port  | local_addr | local_port |    connect_time     |    request_time     |   ptr    | link
------+---------+-----------+--------+-----------+-------+------------+------------+---------------------+---------------------+----------+------
C    | fenxi   | devops    | active | unix      |  1999 | unix       |       1999 | 2015-11-12 16:19:36 | 2015-11-12 16:19:45 | 0xc71b88 |
C    | sqluser | dw_report | active | unix      |  1999 | unix       |       1999 | 2015-11-12 16:21:07 | 2015-11-12 16:21:35 | 0xc71cf0 |
C    | root    | pgbouncer | active | 127.0.0.1 | 54713 | 127.0.0.1  |       1999 | 2015-11-12 15:59:48 | 2015-11-12 16:21:41 | 0xc71a20 |
(3 rows)

pgbouncer=# show pools;
database  |   user    | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait
-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------
devops    | postgres  |         1 |          0 |         0 |       0 |       1 |         0 |        0 |       0
dw_report | postgres  |         1 |          0 |         0 |       0 |       1 |         0 |        0 |       0
pgbouncer | pgbouncer |         1 |          0 |         0 |       0 |       0 |         0 |        0 |       0
yunwei    | postgres  |         0 |          0 |         0 |       0 |       0 |         0 |        0 |       0



如果是通过 pgsql -h 127.0.0.1 -p 6000 -U readonly pgbouncer 方式登录，在执行reload时就会提示“ERROR:  admin access needed”的错误信息了。
7. 如何停止
     以admin登录pgbouncer, 执行shutdown即可。
    
    
    
    
===========================================================   
区别一 pg_hba.conf 功能减弱
      采用了 pgbouncer 卑鄙，pg_hba.conf 功能减弱，这里说减弱，是因为原库上 pg_hba.conf
可以配置，但已经形同虚设，不能发挥原有的作用，因为可以配置原库上的 pg_hba.conf ，配置
成只允许 pgbouncer，原库 pg_hba.conf 配置如下。

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust

#Pgbouncer IP
host all all 192.168.1.10/32 md5

     尽管在原库上配置了 pg_hba.conf ，只允许 pgbouncer主机连库， 但是不能限制其它IP通过
pg_bouncer  连接数据库，从这个角度来说，采用 pgbouncer 无疑是增加了安全漏洞，当然可以
采用其它措施弥补。


区别二 主库上无法查询应用服务器 IP
    
         在数据库维护过程中，经常需要查询某个库的业务服务器IP信息，查询哪些服务器在连接数据库，
采用了 pgbouncer 后，这方面的查询没以前方便，例如在主库上查询连接 skytf 库的客户端 IP 信息，
如下所示：
postgres=# select datname,usename ,client_addr,client_port from pg_stat_activity where datname='skytf';
  datname  |  usename  |   client_addr   | client_port
-----------+-----------+-----------------+-------------
skytf | skytf | 192.168.1.10 |       32119
skytf | skytf | 192.168.1.10 |       18583
skytf | skytf | 192.168.1.10 |       31723
skytf | skytf | 192.168.1.10 |       32363
skytf | skytf | 192.168.1.10 |       58929
skytf | skytf | 192.168.1.10 |       58930
skytf | skytf | 192.168.1.10 |       58931
skytf | skytf | 192.168.1.10 |       58932
skytf | skytf | 192.168.1.10 |       58933
skytf | skytf | 192.168.1.10 |       58934
skytf | skytf | 192.168.1.10 |       58935
skytf | skytf | 192.168.1.10 |       58936
skytf | skytf | 192.168.1.10 |       58937
skytf | skytf | 192.168.1.10 |       58938
   

     从上面查询结果来看，上面的 clietn_addr 只显示 pgbouncer 的 IP，而无法显示真正的客户端 IP，
那么如何获取客户端的IP呢？ 有两种方法：

--4 方法一: 登陆 pgbouncer 控制台获取客户端IP信息
            登陆 pgbouncer 控制台，并且执行 "show clients" 命令。

postgres@db> psql -h 127.0.0.1 -p 1922  pgbouncer pgbouncer_admin
psql (9.1.2, server 1.4.2/bouncer)
WARNING: psql version 9.1, server version 1.4.
         Some psql features might not work.
Type "help" for help.

pgbouncer=# show clients;
type |      user       |  database  | state  |         addr          | port  |       local_addr       | local_port |    connect_tim
e     |    request_time     |    ptr     |    link   
------+-----------------+------------+--------+-----------------------+-------+------------------------+------------+---------------
------+---------------------+------------+------------
C    | skytf       | skytf  | active | ::ffff:192.168.1.15 | 57791 | ::ffff:192.168.173.215 |       1922 | 2012-03-20 06:
45:28 | 2012-03-20 10:36:31 | 0x1350c9d0 | 0x1352e330

  
    备注：为了格式输出，上面只列出一条记录，例如上面可以看出连接 skytf 库的业务服务器IP为
              192.168.1.15，显然通过 pgbouncer 查询客户端 IP 不太方便。