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/pgbouncer

os 层修改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.ini

admin_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=#