实验步骤:
一.安装
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 | *
一.安装
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 | *