HGDB-pgbouncer配置

实验步骤：
一.安装
1.下载pgbouncer的安装包
http://pgfoundry.org/frs/?group_id=1000258&release_id=1952,本次下载的安装包是pgbouncer-1.5.2.tar.gz


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


3.安装libevent
$ cd libevent-2.0.19-stable
$ ./configure --prefix=/opt/pgbouncer/libevent
$ make
$ make install


加载libevent动态库
cd /etc/ld.so.conf.d/
vim libevent2.0.21.conf
/usr/local/libevent/lib
ldconfig


4.安装pgbouncer
$ cd pgbouncer-1.5.2
$ ./configure --prefix=/opt/pgbouncer/pgbouncer --with-libevent=/opt/pgbouncer/libevent/
$ make 
$ make install


 
配置运行环境变量
vim ~/.bash_profile
PATH=/opt/pgbouncer/pgbouncer/bin:$PATH
export PATH
执行生效
source ~/.bash_profile
查看pgbouncer是否安装成功，可以通过查看config.log中最后的返回值exit来确认，0是成功1是失败. 


二.配置 
1.配置pgbouncer的cfg文件
[highgo@sourcedb config]$ pwd
/opt/pgbouncer/pgbouncer/config        ----可以手动创建此目录和文件
$ more pgbouncer.ini        ----修改该文件内容后，需要重启pgbouncer后才生效 ----重启pgbouncer命令（pgbouncer -R -d pgbouncer.ini）
[databases]
aaa = host=127.0.0.1 port=5866 dbname=highgo user=highgo password=highgo123


[pgbouncer]
listen_port = 1999 
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /opt/pgbouncer/pgbouncer/users.txt
logfile = /opt/pgbouncer/pgbouncer/pgbouncer.log     
pidfile = /opt/pgbouncer/pgbouncer/pgbouncer.pid
admin_users = highgo       
pool_mode = Transaction
2.配置用户密码文件users.txt
[highgo@sourcedb pgbouncer]$ pwd
/opt/pgbouncer/pgbouncer
$ cat users.txt 
"highgo" "highgo123"






三.启动 
1.启动命令
[postgres9.6@localhost config]$ pgbouncer -d pgbouncer.ini 
2012-08-21 00:29:55.573 4247 LOG File descriptor limit: 1024 (H:1024), max_client_conn: 100, max fds possible: 130
2.查看日志 
$ tail -f /opt/pgbouncer/pgbouncer/pgbouncer.log 






[highgo@sourcedb pgbouncer]$ psql -h 127.0.0.1 -p 5866 -U highgo highgo
Password for user highgo: 
psql (3.1.4)
Type "help" for help.


highgo=# 


[highgo@sourcedb config]$ psql -h 127.0.0.1 -p 1999  -d aaa -U highgo
Password for user highgo: 
psql (3.1.4)
Type "help" for help.


aaa=# select current_database();
 current_database 
------------------
 highgo
(1 row)
aaa=# \l
                                  List of databases
     Name     |    Owner     | Encoding |  Collate   |   Ctype    | Access privileges 
--------------+--------------+----------+------------+------------+-------------------
 benchmarksql | benchmarksql | UTF8     | zh_CN.utf8 | zh_CN.utf8 | 
 highgo       | highgo       | UTF8     | zh_CN.utf8 | zh_CN.utf8 | 
 template0    | highgo       | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/highgo        +
              |              |          |            |            | highgo=CTc/highgo
 template1    | highgo       | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/highgo        +
              |              |          |            |            | highgo=CTc/highgo
(4 rows)


aaa=# 
aaa=# \q
[highgo@sourcedb config]$ psql -h 127.0.0.1 -p 1999  -U highgo -d pgbouncer
Password for user highgo: 
psql (3.1.4, server 1.5.4/bouncer)
Type "help" for help.


pgbouncer=# \l
ERROR:  invalid command 'SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       pg_catalog.array_to_string(d.datacl, '\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;', use SHOW HELP;


pgbouncer=# show config
pgbouncer-# ;
            key            |                 value                  | changeable 
---------------------------+----------------------------------------+------------
 job_name                  | pgbouncer                              | no
 conffile                  | pgbouncer.ini                          | yes
 logfile                   | /opt/pgbouncer/pgbouncer/pgbouncer.log | yes
 pidfile                   | /opt/pgbouncer/pgbouncer/pgbouncer.pid | no
 listen_addr               | *