pgbouncer 的学习与使用之二 make
os : centos 7.2
pgbouncer: 1.8.1
libevent: 2.0.21-4
libevent-dev: 2.0.21-4
本次使用编译安装pgbouncer 1.8.1，官网的介绍如下
https://wiki.postgresql.org/wiki/PgBouncer
下载pgbouncer的安装包
旧版本地址
http://pgfoundry.org/projects/pgbouncer/
https://www.postgresql.org/ftp/projects/pgFoundry/pgbouncer/pgbouncer/
新版本地址
https://pgbouncer.github.io/downloads/
https://github.com/pgbouncer/pgbouncer
下载libevent包,centos 有 yum 源安装,也可以编译安装
http://libevent.org/
http://monkey.org/~provos/libevent/
官网给的建议
PgBouncer depends on few things to get compiled:
GNU Make 3.81+
libevent 2.0
(optional) OpenSSL 1.0.1 for TLS support.
(optional) c-ares as alternative to libevent’s evdns.
os层创建用户,目录
# groupadd postgres
# useradd -g postgres postgres
# passwd postgres
# mkdir -p /var/run/postgresql
# chown postgres:postgres /var/run/postgresql
# mkdir -p /usr/pgbouncer/pgbouncer1.8
# chown -R postgres:postgres /usr/pgbounceros 层修改ulimit
# vi /etc/security/limits.conf
root soft nofile 655360
root hard nofile 655360
root soft nproc 655360
root hard nproc 655360
postgres soft nofile 655360
postgres hard nofile 655360
postgres soft nproc 655360
postgres hard nproc 655360安装 pgbouncer 的依赖包
# yum install libevent
# yum install libevent-devel
# rpm -q libevent
# rpm -q libevent-devel
# rpm -ql libevent
/usr/lib64/libevent-2.0.so.5
/usr/lib64/libevent-2.0.so.5.1.9
/usr/lib64/libevent_core-2.0.so.5
/usr/lib64/libevent_core-2.0.so.5.1.9
/usr/lib64/libevent_extra-2.0.so.5
/usr/lib64/libevent_extra-2.0.so.5.1.9
/usr/lib64/libevent_openssl-2.0.so.5
/usr/lib64/libevent_openssl-2.0.so.5.1.9
/usr/lib64/libevent_pthreads-2.0.so.5
/usr/lib64/libevent_pthreads-2.0.so.5.1.9
/usr/share/doc/libevent-2.0.21
/usr/share/doc/libevent-2.0.21/ChangeLog
/usr/share/doc/libevent-2.0.21/LICENSE
/usr/share/doc/libevent-2.0.21/README
# rpm -ql libevent-devel
/usr/bin/event_rpcgen.py
/usr/include/evdns.h
/usr/include/event.h
/usr/include/event2/buffer.h
/usr/include/event2/buffer_compat.h
/usr/include/event2/bufferevent.h
/usr/include/event2/bufferevent_compat.h
/usr/include/event2/bufferevent_ssl.h
/usr/include/event2/bufferevent_struct.h
/usr/include/event2/dns.h
/usr/include/event2/dns_compat.h
/usr/include/event2/dns_struct.h
/usr/include/event2/event-config.h
/usr/include/event2/event.h
/usr/include/event2/event_compat.h
/usr/include/event2/event_struct.h
/usr/include/event2/http.h
/usr/include/event2/http_compat.h
/usr/include/event2/http_struct.h
/usr/include/event2/keyvalq_struct.h
/usr/include/event2/listener.h
/usr/include/event2/rpc.h
/usr/include/event2/rpc_compat.h
/usr/include/event2/rpc_struct.h
/usr/include/event2/tag.h
/usr/include/event2/tag_compat.h
/usr/include/event2/thread.h
/usr/include/event2/util.h
/usr/include/evhttp.h
/usr/include/evrpc.h
/usr/include/evutil.h
/usr/lib64/libevent.so
/usr/lib64/libevent_core.so
/usr/lib64/libevent_extra.so
/usr/lib64/libevent_openssl.so
/usr/lib64/libevent_pthreads.so
/usr/lib64/pkgconfig/libevent.pc
/usr/lib64/pkgconfig/libevent_openssl.pc
/usr/lib64/pkgconfig/libevent_pthreads.pc此处是yum install libevent, 如果手动编译的需要指定路径
# vi /ect/profile
export LD_LIBRARY_PATH=/usr/local/libevent/lib:$LD_LIBRARY_PATH安装 psql，方便本地登录管理
# yum install postgresql.x86_64编译安装 pgbouncer
# su - postgres
$ cd /tmp
$ ls -l pgbouncer-1.8.1.tar.gz
$ tar -zxvf /tmp/pgbouncer-1.8.1.tar.gz
$ cd /tmp/pgbouncer-1.8.1
$ ./configure --prefix=/usr/pgbouncer/pgbouncer1.8
如果是手动编译安装的libevent,添加 --with-libevent=libevent-prefix 来指定 libevent
$ make
$ make install配置 pgbouncer
$ cd /usr/pgbouncer/pgbouncer1.8/
$ mkdir log
$ mkdir run
$ cp /usr/pgbouncer/pgbouncer1.8/share/doc/pgbouncer/pgbouncer.ini /usr/pgbouncer/pgbouncer1.8/
$ cp ./pgbouncer.ini ./pgbouncer.ini.bak
$ touch userlist.txt
$ touch pg_hba.conf目录结构如下
$ pwd
/usr/pgbouncer/pgbouncer1.8
$ ls -l
total 24
drwxrwxr-x. 2 postgres postgres 22 Feb 5 19:47 bin
drwxrwxr-x. 2 postgres postgres 26 Feb 5 20:21 log
-rw-r--r--. 1 postgres postgres 2471 Feb 27 16:32 pgbouncer.ini
-rw-r--r--. 1 postgres postgres 8766 Feb 5 20:11 pgbouncer.ini.bak
-rw-rw-r--. 1 postgres postgres 1794 Feb 27 16:35 pg_hba.conf
drwxrwxr-x. 2 postgres postgres 26 Feb 27 16:36 run
drwxrwxr-x. 4 postgres postgres 26 Feb 5 19:47 share
-rw-rw-r--. 1 postgres postgres 74 Feb 5 20:21 userlist.txt修改 pgbouncer.ini
$ vi /usr/pgbouncer/pgbouncer1.8/pgbouncer.ini
[databases]
rw_pg01_peiybdb = host=192.168.56.100 port=5432 user=peiyb password=peiyb dbname=peiybdb pool_size=500 client_encoding=UNICODE connect_query='SELECT 1'
[pgbouncer]
logfile = /usr/pgbouncer/pgbouncer1.8/log/pgbouncer.log
pidfile = /usr/pgbouncer/pgbouncer1.8/run/pgbouncer.pid
listen_addr = 0.0.0.0
listen_port = 6432
auth_hba_file = /usr/pgbouncer/pgbouncer1.8/pg_hba.conf
auth_type = hba
auth_file = /usr/pgbouncer/pgbouncer1.8/userlist.txt
pool_mode = session
unix_socket_dir = /var/run/postgresql
unix_socket_mode = 0777
admin_users = root
stats_users = stat_collector
server_reset_query = DISCARD ALL
max_client_conn = 20000
default_pool_size = 100
reserve_pool_size = 3
ignore_startup_parameters = extra_float_digits修改 pg_hba.conf
$ vi pg_hba.conf
# Database administrative login by Unix domain socket
local all root peer
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# TYPE DATABASE USER ADDRESS METHOD
#####################################################################
##### for person
#####################################################################
#peiyb
host all all 192.168.50.1/32 md5修改 userlist.txt
$ vi /usr/pgbouncer/pgbouncer1.8/userlist.txt
"root" "root"
"stat_collector" "stat_collector"
"usrpeiyb" "usrpeiyb"启动 pgbouncer
$ /usr/pgbouncer/pgbouncer1.8/bin/pgbouncer --help
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
$ /usr/pgbouncer/pgbouncer1.8/bin/pgbouncer -d /usr/pgbouncer/pgbouncer1.8/pgbouncer.ini
或者
# /usr/pgbouncer/pgbouncer1.8/bin/pgbouncer -d -u postgres /usr/pgbouncer/pgbouncer1.8/pgbouncer.ini
详细日志
# /usr/pgbouncer/pgbouncer1.8/bin/pgbouncer -v -d -u postgres /usr/pgbouncer/pgbouncer1.8/pgbouncer.ini
重新加载,online restart pgbouncer
# /usr/pgbouncer/pgbouncer1.8/bin/pgbouncer -R -d -u postgres /usr/pgbouncer/pgbouncer1.8/pgbouncer.ini随机启动, 修改 /etc/rc.local
# vi /etc/rc.local
/usr/pgbouncer/pgbouncer1.8/bin/pgbouncer -d -u postgres /usr/pgbouncer/pgbouncer1.8/pgbouncer.iniadmin_users 用户登录 pgbouncer
$ psql -h 127.0.0.1 -p 6432 -U root pgbouncer
pgbouncer=# show help;
NOTICE: Console usage
DETAIL:
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION
SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SHOW STATS|STATS_TOTALS|STATS_AVERAGES
SET key = arg
RELOAD
PAUSE [<db>]
RESUME [<db>]
DISABLE <db>
ENABLE <db>
KILL <db>
SUSPEND
SHUTDOWN
SHOW
pgbouncer=# reload 加载修改后的配置文件
$ psql -h 127.0.0.1 -p 6432 -U root pgbouncer
pgbouncer=# reload;
RELOAD
pgbouncer=#
浙公网安备 33010602011771号