pgbouncer 的学习与使用之一 apt-get
os : debian 8.2
pgbouncer: 1.5.4
libevent: 2.0.21
libevent-dev: 2.0.21
本次采用的时 apt-get 方式安装
安装 libevent
# apt-get install libevent-2.0-5
正在读取软件包列表... 完成
正在分析软件包的依赖关系树
正在读取状态信息... 完成
libevent-2.0-5 已经是最新的版本。
升级了 0 个软件包，新安装了 0 个软件包，要卸载 0 个软件包，有 1 个软件包未被升级。安装 libevent-dev
# apt-get install libevent-dev
正在读取软件包列表... 完成
正在分析软件包的依赖关系树
正在读取状态信息... 完成
将会安装下列额外的软件包：
libevent-core-2.0-5 libevent-extra-2.0-5 libevent-openssl-2.0-5 libevent-pthreads-2.0-5
下列【新】软件包将被安装：
libevent-core-2.0-5 libevent-dev libevent-extra-2.0-5 libevent-openssl-2.0-5 libevent-pthreads-2.0-5
升级了 0 个软件包，新安装了 5 个软件包，要卸载 0 个软件包，有 1 个软件包未被升级。
需要下载 541 kB 的软件包。
解压缩后会消耗掉 2,120 kB 的额外空间。
您希望继续执行吗？ [Y/n] y
获取：1 http://mirrors.163.com/debian/ jessie/main libevent-core-2.0-5 amd64 2.0.21-stable-2+deb8u1 [108 kB]
获取：2 http://mirrors.163.com/debian/ jessie/main libevent-extra-2.0-5 amd64 2.0.21-stable-2+deb8u1 [90.7 kB]
获取：3 http://mirrors.163.com/debian/ jessie/main libevent-openssl-2.0-5 amd64 2.0.21-stable-2+deb8u1 [50.0 kB]
获取：4 http://mirrors.163.com/debian/ jessie/main libevent-pthreads-2.0-5 amd64 2.0.21-stable-2+deb8u1 [44.1 kB]
获取：5 http://mirrors.163.com/debian/ jessie/main libevent-dev amd64 2.0.21-stable-2+deb8u1 [248 kB]
下载 541 kB，耗时 5秒 (93.7 kB/s)
正在选中未选择的软件包 libevent-core-2.0-5:amd64。
(正在读取数据库 ... 系统当前共安装有 142086 个文件和目录。)
正准备解包 .../libevent-core-2.0-5_2.0.21-stable-2+deb8u1_amd64.deb ...
正在解包 libevent-core-2.0-5:amd64 (2.0.21-stable-2+deb8u1) ...
正在选中未选择的软件包 libevent-extra-2.0-5:amd64。
正准备解包 .../libevent-extra-2.0-5_2.0.21-stable-2+deb8u1_amd64.deb ...
正在解包 libevent-extra-2.0-5:amd64 (2.0.21-stable-2+deb8u1) ...
正在选中未选择的软件包 libevent-openssl-2.0-5:amd64。
正准备解包 .../libevent-openssl-2.0-5_2.0.21-stable-2+deb8u1_amd64.deb ...
正在解包 libevent-openssl-2.0-5:amd64 (2.0.21-stable-2+deb8u1) ...
正在选中未选择的软件包 libevent-pthreads-2.0-5:amd64。
正准备解包 .../libevent-pthreads-2.0-5_2.0.21-stable-2+deb8u1_amd64.deb ...
正在解包 libevent-pthreads-2.0-5:amd64 (2.0.21-stable-2+deb8u1) ...
正在选中未选择的软件包 libevent-dev。
正准备解包 .../libevent-dev_2.0.21-stable-2+deb8u1_amd64.deb ...
正在解包 libevent-dev (2.0.21-stable-2+deb8u1) ...
正在设置 libevent-core-2.0-5:amd64 (2.0.21-stable-2+deb8u1) ...
正在设置 libevent-extra-2.0-5:amd64 (2.0.21-stable-2+deb8u1) ...
正在设置 libevent-openssl-2.0-5:amd64 (2.0.21-stable-2+deb8u1) ...
正在设置 libevent-pthreads-2.0-5:amd64 (2.0.21-stable-2+deb8u1) ...
正在设置 libevent-dev (2.0.21-stable-2+deb8u1) ...
正在处理用于 libc-bin (2.19-18+deb8u10) 的触发器 ...安装 pgbouncer
# apt-get install pgbouncer
正在读取软件包列表... 完成
正在分析软件包的依赖关系树
正在读取状态信息... 完成
将会安装下列额外的软件包：
postgresql-client-common postgresql-common
建议安装的软件包：
python-psycopg2
下列【新】软件包将被安装：
pgbouncer postgresql-client-common postgresql-common
升级了 0 个软件包，新安装了 3 个软件包，要卸载 0 个软件包，有 1 个软件包未被升级。
需要下载 400 kB 的软件包。
解压缩后会消耗掉 1,234 kB 的额外空间。
您希望继续执行吗？ [Y/n] y
获取：1 http://mirrors.163.com/debian/ jessie/main postgresql-client-common all 165+deb8u3 [73.8 kB]
获取：2 http://mirrors.163.com/debian/ jessie/main postgresql-common all 165+deb8u3 [203 kB]
获取：3 http://mirrors.163.com/debian/ jessie/main pgbouncer amd64 1.5.4-6+deb8u1 [123 kB]
下载 400 kB，耗时 5秒 (73.5 kB/s)
正在预设定软件包 ...
正在选中未选择的软件包 postgresql-client-common。
(正在读取数据库 ... 系统当前共安装有 141812 个文件和目录。)
正准备解包 .../postgresql-client-common_165+deb8u3_all.deb ...
正在解包 postgresql-client-common (165+deb8u3) ...
正在选中未选择的软件包 postgresql-common。
正准备解包 .../postgresql-common_165+deb8u3_all.deb ...
正在添加 postgresql-common 导致 /usr/bin/pg_config 转移到 /usr/bin/pg_config.libpq-dev
正在解包 postgresql-common (165+deb8u3) ...
正在选中未选择的软件包 pgbouncer。
正准备解包 .../pgbouncer_1.5.4-6+deb8u1_amd64.deb ...
正在解包 pgbouncer (1.5.4-6+deb8u1) ...
正在处理用于 man-db (2.7.0.2-5) 的触发器 ...
正在处理用于 systemd (215-17+deb8u7) 的触发器 ...
正在设置 postgresql-client-common (165+deb8u3) ...
正在设置 postgresql-common (165+deb8u3) ...
正在将用户“postgres”加入到“ssl-cert”组中
Creating config file /etc/postgresql-common/createcluster.conf with new version
Creating config file /etc/logrotate.d/postgresql-common with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
en_us
Removing obsolete dictionary files:
正在设置 pgbouncer (1.5.4-6+deb8u1) ...
正在处理用于 systemd (215-17+deb8u7) 的触发器 ...验证安装
# dpkg -l pgbouncer
期望状态=未知(u)/安装(i)/删除(r)/清除(p)/保持(h)
| 状态=未安装(n)/已安装(i)/仅存配置(c)/仅解压缩(U)/配置失败(F)/不完全安装(H)/触发器等待(W)/触发器未决(T)
|/ 错误?=(无)/须重装(R) (状态，错误：大写=故障)
||/ 名称 版本 体系结构： 描述
+++-=========================================-=========================-=========================-=======================================================================================
ii pgbouncer 1.5.4-6+deb8u1 amd64 lightweight connection pooler for PostgreSQL
# dpkg -l libevent-2.0-5
期望状态=未知(u)/安装(i)/删除(r)/清除(p)/保持(h)
| 状态=未安装(n)/已安装(i)/仅存配置(c)/仅解压缩(U)/配置失败(F)/不完全安装(H)/触发器等待(W)/触发器未决(T)
|/ 错误?=(无)/须重装(R) (状态，错误：大写=故障)
||/ 名称 版本 体系结构： 描述
+++-========================================-=========================-=========================-======================================================================================
ii libevent-2.0-5:amd64 2.0.21-stable-2+deb8u1 amd64 Asynchronous event notification library
# dpkg -l libevent-dev
期望状态=未知(u)/安装(i)/删除(r)/清除(p)/保持(h)
| 状态=未安装(n)/已安装(i)/仅存配置(c)/仅解压缩(U)/配置失败(F)/不完全安装(H)/触发器等待(W)/触发器未决(T)
|/ 错误?=(无)/须重装(R) (状态，错误：大写=故障)
||/ 名称 版本 体系结构： 描述
+++-========================================-=========================-=========================-======================================================================================
ii libevent-dev 2.0.21-stable-2+deb8u1 amd64 Asynchronous event notification library (development files)pgbouncer相关的路径、文件
# dpkg -L pgbouncer
/.
/etc
/etc/init.d
/etc/init.d/pgbouncer
/etc/pgbouncer
/etc/pgbouncer/pgbouncer.ini
/etc/pgbouncer/userlist.txt
/etc/default
/etc/default/pgbouncer
/usr
/usr/sbin
/usr/sbin/pgbouncer
/usr/share
/usr/share/man
/usr/share/man/man5
/usr/share/man/man5/pgbouncer.5.gz
/usr/share/man/man1
/usr/share/man/man1/pgbouncer.1.gz
/usr/share/doc
/usr/share/doc/pgbouncer
/usr/share/doc/pgbouncer/NEWS.gz
/usr/share/doc/pgbouncer/README.Debian
/usr/share/doc/pgbouncer/changelog.Debian.gz
/usr/share/doc/pgbouncer/AUTHORS
/usr/share/doc/pgbouncer/faq.html
/usr/share/doc/pgbouncer/examples
/usr/share/doc/pgbouncer/examples/userlist.txt
/usr/share/doc/pgbouncer/examples/pgbouncer.ini.gz
/usr/share/doc/pgbouncer/todo.html
/usr/share/doc/pgbouncer/copyright
/usr/share/doc/pgbouncer/config.html
/usr/share/doc/pgbouncer/README
/usr/share/doc/pgbouncer/usage.html
/usr/share/doc/pgbouncer/README.html
/usr/share/pgbouncer
/usr/share/pgbouncer/mkauth.py重点关注的几个路径、文件
/etc/init.d/pgbouncer
/etc/init.d/$NAME {start|stop|reload|force-reload|restart|status}
/etc/pgbouncer/
/etc/pgbouncer/pgbouncer.ini
/etc/pgbouncer/userlist.txt
/etc/default/pgbouncer
/usr/sbin/pgbouncer
/var/run/postgresql
/usr/share/pgbouncer
/usr/share/doc/pgbouncer
两个重要的文件
/etc/pgbouncer/pgbouncer.ini
/etc/pgbouncer/userlist.txt配置pgbouncer的cfg文件 pgbouncer.ini
# vi pgbouncer.ini
[databases]
trade_peiybdb = host=192.168.56.101 port=5432 user=usr_peiyb password=usr_peiyb dbname=peiybdb pool_size=50 client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 192.168.56.220
listen_port = 6432
#unix_socket_dir = /var/run/postgresql
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
admin_users = root
stats_users = stat_collector
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 20配置用户密码的文件 userlist.txt
# vi userlist.txt
"root" "root"
"usr_abc" "usr_abc"启动pgbouncer
# 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
# service pgbouncer start
或者
$ /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini
2018-01-22 21:58:26.622 4715 LOG File descriptor limit: 65536 (H:65536), max_client_conn: 100, max fds possible: 160查看进程
$ ps -fu postgres
UID PID PPID C STIME TTY TIME CMD
postgres 4712 4711 0 21:58 pts/1 00:00:00 -su
postgres 4717 1 0 21:58 ? 00:00:00 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini
postgres 4720 4712 0 22:00 pts/1 00:00:00 ps -fu postgres查看端口
# netstat -antp|grep -i 6432
tcp 0 0 192.168.56.220:6432 0.0.0.0:* LISTEN 6158/pgbouncer 连接pgbouncer
$ psql -h 192.168.56.220 -p 6432 -U usr_abc trade_peiybdb
用户 usr_abc 的口令：
psql (9.4.15, 服务器 9.1.24)
输入 "help" 来获取帮助信息.
trade_peiybdb=> 查看6432端口
# netstat -antp|grep -i 6432
tcp 0 0 192.168.56.220:6432 0.0.0.0:* LISTEN 6158/pgbouncer
tcp 0 0 192.168.56.220:58877 192.168.56.220:6432 ESTABLISHED 6163/psql
tcp 0 0 192.168.56.220:58876 192.168.56.220:6432 TIME_WAIT -
tcp 0 0 192.168.56.220:6432 192.168.56.220:58877 ESTABLISHED 6158/pgbouncer 上面输出留意 6163/psql 这个进程
# netstat -antp|grep -i "192.168.56"
tcp 0 0 192.168.56.220:6432 0.0.0.0:* LISTEN 6158/pgbouncer
tcp 0 0 192.168.56.220:22 192.168.56.1:57230 ESTABLISHED 4051/sshd: peiyb [p
tcp 0 0 192.168.56.220:22 192.168.56.1:49987 ESTABLISHED 6085/sshd: peiyb [p
tcp 0 52 192.168.56.220:22 192.168.56.1:63625 ESTABLISHED 968/sshd: peiyb [pr
tcp 0 0 192.168.56.220:58877 192.168.56.220:6432 ESTABLISHED 6163/psql
tcp 0 0 192.168.56.220:6432 192.168.56.220:58877 ESTABLISHED 6158/pgbouncer
tcp 0 0 192.168.56.220:38510 192.168.56.101:5432 ESTABLISHED 6158/pgbouncer 上面输出留意 6158/pgbouncer 这个进程
# netstat -antp|grep -i 6158 |grep -i "192.168.56.101"
tcp 0 0 192.168.56.220:38510 192.168.56.101:5432 ESTABLISHED 6158/pgbouncer可以看到 本地 6158/pgbouncer 进程使用 38510 端口 和 远程主机 192.168.56.10 的5432 端口已经建立了连接。
在本地开个 dbeaver 连接看看，结果提示错误
org.postgresql.util.PSQLException: ERROR: Unsupported startup parameter: extra_float_digits这个错误可以问下baidu，很多人都碰到了。这里列出了一个参考过的url，就是修改一个配置
ignore_startup_parameters = extra_float_digits重启 pgbouncer 服务 或者 reload 一下
当时参考的文章
https://my.oschina.net/Kenyon/blog/95550
https://www.talendforge.org/forum/viewtopic.php?pid=152966
http://blog.163.com/digoal@126/blog/static/1638770402010539112355/
查看6432端口
# netstat -antp|grep -i 6432
tcp 0 0 192.168.56.220:6432 0.0.0.0:* LISTEN 6442/pgbouncer
tcp 0 0 192.168.56.220:6432 192.168.56.220:58885 ESTABLISHED 6442/pgbouncer
tcp 0 0 192.168.56.220:58885 192.168.56.220:6432 ESTABLISHED 6466/psql
tcp 0 0 192.168.56.220:6432 192.168.56.1:57514 ESTABLISHED 6442/pgbouncer
tcp 0 0 192.168.56.220:6432 192.168.56.1:57513 ESTABLISHED 6442/pgbouncer
tcp 0 0 192.168.56.220:58884 192.168.56.220:6432 TIME_WAIT - # netstat -antp|grep -i "192.168.56"
tcp 0 0 192.168.56.220:6432 0.0.0.0:* LISTEN 6442/pgbouncer
tcp 0 0 192.168.56.220:38515 192.168.56.101:5432 ESTABLISHED 6442/pgbouncer
tcp 0 0 192.168.56.220:38514 192.168.56.101:5432 ESTABLISHED 6442/pgbouncer
tcp 0 0 192.168.56.220:6432 192.168.56.220:58885 ESTABLISHED 6442/pgbouncer
tcp 0 0 192.168.56.220:58885 192.168.56.220:6432 ESTABLISHED 6466/psql
tcp 0 0 192.168.56.220:6432 192.168.56.1:57514 ESTABLISHED 6442/pgbouncer
tcp 0 0 192.168.56.220:6432 192.168.56.1:57513 ESTABLISHED 6442/pgbouncer 上面观察最后两行 本地台式机 192.168.56.1 到 192.168.56.220 的 dbeaver 创建的连接
上面观察第2、3两行，是对应连接到 192.168.56.101 的 5432 端口的信息
可以在 pgbouncer 的管理界面求证
# psql -h 192.168.56.220 -p 6432 -U root pgbouncer
用户 root 的口令：
psql (9.4.15, 服务器 1.5.4/bouncer)
输入 "help" 来获取帮助信息.
pgbouncer=# show clients;
type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | ptr | link
------+---------+---------------+--------+----------------+-------+----------------+------------+---------------------+---------------------+-----------+-----------
C | root | pgbouncer | active | 192.168.56.220 | 58887 | 192.168.56.220 | 6432 | 2018-01-23 02:54:36 | 2018-01-23 02:55:20 | 0x17f9e98 |
C | usr_abc | trade_peiybdb | active | 192.168.56.1 | 57513 | 192.168.56.220 | 6432 | 2018-01-23 02:45:46 | 2018-01-23 02:46:21 | 0x17f9bc8 | 0x1817410
C | usr_abc | trade_peiybdb | active | 192.168.56.1 | 57514 | 192.168.56.220 | 6432 | 2018-01-23 02:45:46 | 2018-01-23 02:46:16 | 0x17f9a60 | 0x1817578
C | usr_abc | trade_peiybdb | active | 192.168.56.220 | 58885 | 192.168.56.220 | 6432 | 2018-01-23 02:47:56 | 2018-01-23 02:47:56 | 0x17f9d30 |
(4 行记录)上面观察第2、3两行，addr、port、link列
pgbouncer=# show servers;
type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | ptr | link
------+-----------+---------------+--------+----------------+------+----------------+------------+---------------------+---------------------+-----------+-----------
S | usr_peiyb | trade_peiybdb | active | 192.168.56.101 | 5432 | 192.168.56.220 | 38514 | 2018-01-23 02:44:32 | 2018-01-23 02:46:21 | 0x1817410 | 0x17f9bc8
S | usr_peiyb | trade_peiybdb | active | 192.168.56.101 | 5432 | 192.168.56.220 | 38515 | 2018-01-23 02:44:32 | 2018-01-23 02:46:16 | 0x1817578 | 0x17f9a60
(2 行记录)上面观察第1、2两行，local_addr、local_port、ptr列
pgbouncer=# show sockets;
type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | ptr | link | recv_pos | pkt_pos | pkt_remain | send_pos | send_remain | pkt_avail | send_avail
------+-----------+---------------+-----------+----------------+-------+----------------+------------+---------------------+---------------------+-----------+-----------+----------+---------+------------+----------+-------------+-----------+------------
C | root | pgbouncer | cl_active | 192.168.56.220 | 58887 | 192.168.56.220 | 6432 | 2018-01-23 02:54:36 | 2018-01-23 03:02:37 | 0x17f9e98 | | 19 | 0 | 0 | 0 | 0 | 19 | 0
C | usr_abc | trade_peiybdb | cl_active | 192.168.56.1 | 57513 | 192.168.56.220 | 6432 | 2018-01-23 02:45:46 | 2018-01-23 02:46:21 | 0x17f9bc8 | 0x1817410 | 0 | 0 | 0 | 0 | 0 | 0 | 0
C | usr_abc | trade_peiybdb | cl_active | 192.168.56.1 | 57514 | 192.168.56.220 | 6432 | 2018-01-23 02:45:46 | 2018-01-23 02:46:16 | 0x17f9a60 | 0x1817578 | 0 | 0 | 0 | 0 | 0 | 0 | 0
C | usr_abc | trade_peiybdb | cl_active | 192.168.56.220 | 58885 | 192.168.56.220 | 6432 | 2018-01-23 02:47:56 | 2018-01-23 02:47:56 | 0x17f9d30 | | 0 | 0 | 0 | 0 | 0 | 0 | 0
S | usr_peiyb | trade_peiybdb | sv_active | 192.168.56.101 | 5432 | 192.168.56.220 | 38514 | 2018-01-23 02:44:32 | 2018-01-23 02:46:21 | 0x1817410 | 0x17f9bc8 | 0 | 0 | 0 | 0 | 0 | 0 | 0
S | usr_peiyb | trade_peiybdb | sv_active | 192.168.56.101 | 5432 | 192.168.56.220 | 38515 | 2018-01-23 02:44:32 | 2018-01-23 02:46:16 | 0x1817578 | 0x17f9a60 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(6 行记录)上面观察第1、2两行
管理pgbouncer
pgbouncer其实有一个虚拟的db存在，名称就是pgbouncer
当以admin_users 登记的那个用户登录pgbouncer 时，pgbouncer认为用户是要对pgbouncer本身进行管理，就不会把去请求真实的DB了。
# psql -h 192.168.56.220 -p 6432 -U root pgbouncer
用户 root 的口令：
psql (9.4.15, 服务器 1.5.4/bouncer)
输入 "help" 来获取帮助信息.
pgbouncer=# show help;
NOTICE: Console usage
描述:
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 [<db>]
RESUME [<db>]
KILL <db>
SUSPEND
SHUTDOWN
SHOW
pgbouncer=# show version;
NOTICE: pgbouncer version 1.5.4 (compiled by <root@lehmann> at 2015-05-23 23:31:01)
SHOW
pgbouncer=# show databases;
name | host | port | database | force_user | pool_size | reserve_pool
---------------+----------------+------+-----------+------------+-----------+--------------
pgbouncer | | 6432 | pgbouncer | pgbouncer | 2 | 0
trade_peiybdb | 192.168.56.101 | 5432 | peiybdb | usr_peiyb | 50 | 0
(2 行记录)
pgbouncer=# show stats;
database | total_requests | total_received | total_sent | total_query_time | avg_req | avg_recv | avg_sent | avg_query
---------------+----------------+----------------+------------+------------------+---------+----------+----------+-----------
pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0
trade_peiybdb | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(2 行记录)
