PostgreSQL 数据连接池PgBouncer
一.安装配置
1.下载pgbouncer的安装包
https://pgbouncer.github.io/downloads/,本次下载的安装包是pgbouncer-1.5.x.tar.gz
2.下载libevent包
http://libevent.org/，见pgbouncer源码包中的Readme
这玩意也是个好东西，在很多地方用到
3.安装libevent
tar -zxvf libevent-2.x-stable.tar.gz
cd libevent-2.x-stable
./configure
make
make verify
make install
4.安装pgbouncer
tar -zxvf pgbouncer-1.5.x.tar.gz
cd pgbouncer-1.5.x
./configure --prefix=/usr/local/pgbouncer --with-libevent
make
make install
--> doc
INSTALL pgbouncer.1 /usr/local/pgbouncer/share/man/man1
INSTALL pgbouncer.5 /usr/local/pgbouncer/share/man/man5
<-- doc
INSTALL pgbouncer /usr/local/pgbouncer/bin
INSTALL README /usr/local/pgbouncer/share/doc/pgbouncer
INSTALL NEWS /usr/local/pgbouncer/share/doc/pgbouncer
INSTALL etc/pgbouncer.ini /usr/local/pgbouncer/share/doc/pgbouncer
INSTALL etc/userlist.txt /usr/local/pgbouncer/share/doc/pgbouncer
5.这里算是个bug，不能找到库，需要创建个链接
ldd /usr/local/pgbouncer/bin/pgbouncer //分析是否缺少库，如果不缺少就是正常的
whereis libevent-2.0.so.5 //一般都找不到这个库，选找到位置
//做个链接就可以了
ln -s /usr/local/lib/libevent-2.0.so.5 /usr/lib64/libevent-2.0.so.5
6.建立快捷方式
ln -s /usr/local/pgbouncer/bin/pgbouncer /sbin/pgbouncer
7.pgbouncer 参数
Usage: pgbouncer [OPTION]... config.ini
-d, --daemon Run in background (as a daemon)
-R, --restart Do a online restart
-q, --quiet Run quietly
-v, --verbose Increase verbosity
-u, --user=<username> Assume identity of <username>
-V, --version Show version
-h, --help Show this help screen and exit
二.创建用户列表
文件位置例如:/database/pgsql/data/pg_user.conf
格式为：
"md5" + md5(password + username)，密码为=md5(密码+用户名)
"username1" "password" ...
"username2" "md5abcdef012342345" ...
"user1" "md5b162144de6ffd30d48c4da2ee642fc6b"
"user2" "md58c603bee4c1af520738e56f11895b7df"
"user3" "md54953ba06b54c8eeae47c15765eeed6c6"
三.创建配置文件
[databases]
* = host=127.0.0.1 port=5432 user=postgres
smfw = host=127.0.0.1 port=5432 dbname=smfw user=smfw
gzcom = host=127.0.0.1 port=5432 dbname=gzcom user=proot
[pgbouncer]
pool_mode = session
listen_port = 6543
listen_addr = *
auth_type = md5
auth_file = /database/pgsql/data/pg_user.conf
logfile = /database/pgsql/data/pgbouncer.log
pidfile = /database/pgsql/data/pgbouncer.pid
unix_socket_dir = /database/pgsql/data/pgbouncer.socket
admin_users = postgres
stats_users = stat_collector
min_pool_size=1
max_client_conn=200
server_reset_query = DISCARD ALL
- maxclientconn:(maxpoolsize X totaldatabases X totalusers)
- admin_users:列出哪些用户可以登录pgbouncer进行管理，以逗号进行分隔
- stats_users:列出哪些用户可以登录pgbouncer进行只读操作，如可以列出服务器状态，访问链接等，但是不能执行reload。
- = host=127.0.0.1 port=5432
其中[databases]小结中的格式为：
mydb = host=127.0.0.1 dbname=mydb port=1923 pool_size=2
mydb_test = host=* dbname=mydb_test port=1923 pool_size=
server_main = host=localhost port=5432 dbname=booksair user=postgres password=123456 connect_query='SELECT 1'
* = host=127.0.0.1 port=5432 user=postgres //配置可以管理多个数据库的用户
databases中的验证安全性很弱，用来控制管理器权限基本不行，权限控制上还是要到数据库里边配置。
server_reset_query 配置说明
server_reset_query = DISCARD ALL
- 当 pgbouncer 为 Transaction 模式时，由于pgbouncer 连接池的分配是以事务为周期的，当连接池被 pgbouncer
回收并分配给其它客户端时，session 级别设置的参数将不会保留。所以这个参数可以设置为空,serverresetquery=''。
四.修改权限
chown postgres:postgres /database/pgsql/data/pgbouncer.ini
chown postgres:postgres /database/pgsql/data/pg_user.conf
五.启动pgbouncer
pgbouncer -d /database/pgsql/data/pgbouncer.ini
六.测试连接
使用postgres 角色运行
psql -h 127.0.0.1 -p 6543 -U postgres pgbouncer
如果是通过 pgsql -h 127.0.0.1 -p 6543 -U postgres readonly pgbouncer 方式登录，
在执行reload时就会提示“ERROR: admin access needed”的错误信息。
命令参数
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 []
RESUME []
KILL SUSPEND
SHUTDOWN
SHOW
pgbouncer=# show config;
七.如何停止
以admin登录pgbouncer, 执行shutdown即可。
初次使用时，建议使用-v参数，并关注pgbouncer的log输入，能发现不少有意思的东西 :)
看是否还在运行
ps -ef | grep pgbouncer
postgres启动管理中
启动
su - $PGUSER -c "pgbouncer -d $PGDATA/pgbouncer.ini"
从启
su - $PGUSER -c "pgbouncer -R -d $PGDATA/pgbouncer.ini"
停止
su - $PGUSER -c "psql -h 127.0.0.1 -p 6543 -U postgres -d pgbouncer -c 'shutdown;'"
psql -h 127.0.0.1 -p 6543 -U postgres -d pgbouncer
pgbouncer.sh
#!/bin/bash
prefix=/database/pgsql
PGDATA="/database/pgsql/data"
PGUSER=postgres
case $1 in
start)
if [ -f "$PGDATA/pgbouncer.pid" ]; then
echo -e "pgbouncer is run ...to shutdown and start"
su - $PGUSER -c "psql -h 127.0.0.1 -p 6543 -U postgres -d pgbouncer -c 'shutdown;'"
su - $PGUSER -c "pgbouncer -d $PGDATA/pgbouncer.ini"
else
echo -e "Starting pgbouncer"
su - $PGUSER -c "pgbouncer -d $PGDATA/pgbouncer.ini"
fi
;;
stop)
if [ -f "$PGDATA/pgbouncer.pid" ]; then
echo -e "stop pgbouncer"
su - $PGUSER -c "psql -h 127.0.0.1 -p 6543 -U postgres -d pgbouncer -c 'shutdown;'"
else
echo -e "pgbouncer is stoped"
fi
;;
restart)
if [ -f "$PGDATA/pgbouncer.pid" ]; then
echo -e "restart pgbouncer"
su - $PGUSER -c "pgbouncer -R -d $PGDATA/pgbouncer.ini"
else
echo -e "pgbouncer is stoped"
fi
;;
reload)
if [ -f "$PGDATA/pgbouncer.pid" ]; then
echo -e "reload pgbouncer"
su - $PGUSER -c "psql -h 127.0.0.1 -p 6543 -U postgres -d pgbouncer -c 'reload;'"
else
echo -e "pgbouncer is stoped"
fi
;;
status)
if [ -f "$PGDATA/pgbouncer.pid" ]; then
echo -e "pgbouncer is run:"
ps -ef | grep pgbouncer
else
echo -e "pgbouncer is stoped"
fi
;;
*)
# Print help
echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2
exit 1
;;
esac
exit 0
chown -R postgres:postgres /database/pgsql/*.sh
创建密码文件 .pgpass
vi /home/postgres/.pgpass
--格式
hostname:port:database:username:password
--范例
192.168.1.25:6432:Mydb:postgres:mypassword
--权限
chown -R postgres:postgres /home/postgres/
chmod 600 .pgpass
如果出现启动时目录无权限切换
chmod +r+x /home