CNDBA社区
1 PgBouncer 概述
PgBouncer是为PostgreSQL数据库提供的一个轻量级连接池工具，与其他存在于PostgreSQL的连接池中间件不同，PgBouncer仅作为一个连接池和代理层为PostgreSQL和应用之间提供服务。
Pgbouncer 官网地址如下：
http://www.pgbouncer.org/
PgBouncer的作用如下：
- 如果应用程序直接与PostgreSQL连接，则每次连接时PostgreSQL都会克隆（Linux及unix下通过fork系统调用）出一个服务进程来为应用程序服务，在关闭连接后，PostgreSQL则会自动把服务进程停掉。但频繁地创建和销毁进程，会耗费比较多的资源。使用PgBouncer后，PgBouner会把与后端PostgreSQL数据库的连接缓存住，当有前端请求来时，只是分配一个空闲的连接给前端程序使用，这样就降低了资源的消耗。
- 允许前端创建多个连接，把前端的连接聚合到适量的数据库连接上。从理论上来说，后台的服务进程数与这台主机上的CPU核数相同时，其CPU有效利用率是最高的，因为这时CPU不需要在多个进程中来回切换。通常的机器其CPU的核数在4～32 个之间，很明显，限制用户的数据库连接数与CPU核数相同是不现实的。从经验看，如果连接数超过CPU核数4倍时，CPU有效利用率会大大下降。而即使允许的连接数是CPU核数的4倍，对多数应用来说，连接数还是不够的，所以这时使用连接池是一个很明智的选择。
- 能对客户端连接进行限制，预防过多或恶意的连接请求。
PgBouncer是一个轻量级的连接池，这主要体现在以下几个特点上：
- PgBouncer使用libevent进行socket通信，这种通信方式效率很高。
- PgBouncer是用C语言写的，实现得很精巧，每个连接仅消耗2KB的内存。
PgBouncer目前支持以下三种连接池模型。
- session：会话级连接，在它的连接生命期内，连接池分配给它一个数据库连接。客户端断开时，数据库连接会放回连接池中。
- transaction：事务级别连接，当客户端的每个事务结束时，数据库连接就会重新释放回连接池中，再次执行一个事务时，需要再从连接池加获得一个连接。
- statement：每执行完一个SQL语句时，连接就会重新释放回连接池中，再次执行一个SQL语句时，需要再次从连接池中获得连接。这种模式意味着在客户端强制“autocomit”模式。
2 PgBouncer 安装
Linux 发行的ISO 镜像中自带pgbounder工具，直接yum 安装即可：
[dave@www.cndba.cn yum.repos.d]# yum search pgbouncer
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
dave | 2.8 kB 00:00:00
(1/2): dave/group | 628 kB 00:00:00
(2/2): dave/primary | 2.1 MB 00:00:00
dave 5230/5230
========================================================= N/S matched: pgbouncer ==========================================================
pgbouncer.x86_64 : Lightweight connection pooler for PostgreSQL
Name and summary matches only, use "search all" for everything.
[dave@www.cndba.cn yum.repos.d]#
[dave@www.cndba.cn mnt]# yum install pgbouncer -y
查看版本：
[dave@www.cndba.cn yum.repos.d]# pgbouncer -V
PgBouncer 1.18.0
libevent 2.0.21-stable
adns: evdns2
tls: OpenSSL 1.0.2k-fips 26 Jan 2017
[dave@www.cndba.cn yum.repos.d]#
3 PgBouncer 使用示例
3.1 修改配置文件
安装pgbouncer之后会自动创建配置文件，里面也有一些模板，如下：
[dave@www.cndba.cn yum.repos.d]# find / -name pgbouncer.ini
/etc/pgbouncer/pgbouncer.ini
/usr/share/doc/pgbouncer/pgbouncer.ini
[dave@www.cndba.cn yum.repos.d]#
[dave@www.cndba.cn ~]# cd /etc/pgbouncer/
[dave@www.cndba.cn pgbouncer]# ll
total 24
-rwx------. 1 root root 823 Dec 12 17:35 mkauth.py
-rw-r--r--. 2 root root 1132 Dec 13 23:05 mkauth.pyc
-rw-r--r--. 2 root root 1132 Dec 13 23:05 mkauth.pyo
-rw-r--r--. 1 root root 9360 Dec 13 23:05 pgbouncer.ini
默认参数：
[dave@www.cndba.cn yum.repos.d]# cat /etc/pgbouncer/pgbouncer.ini| grep -Ev "^$|^[#;]"
[databases]
[users]
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = localhost
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
stats_users = stats, postgres
[dave@www.cndba.cn yum.repos.d]#
我们这里修改成如下参数：
[databases]
postgres = host=192.168.56.102 port=5432 dbname=postgres
[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5 #PG 14之前
auth_type = scram-sha-256 #PG 14之后
auth_file = /etc/pgbouncer/userlist.txt
logfile = /etc/pgbouncer/pgbouncer.log
pidfile = /etc/pgbouncer/pgbouncer.pid
admin_users = postgres
stats_users = postgres
server_reset_query = DISCARD ALL
server_check_query = select 1
server_check_delay = 30
max_client_conn = 5000
default_pool_size = 20
reserve_pool_size = 5
dns_max_ttl = 15
pool_mode=transaction
这里参数部分不再说明，需要了解可以查看帮助：
[dave@www.cndba.cn pgbouncer]# man pgbouncer.ini
另外不同版本的pgbouncer 参数有出入，以当前版本的模板为准，否则可能会导致pgbouncer无法连接。
3.2 修改用户密码文件
PgBouncer认证文件的格式如下：”usernamel” “password” .
“username2” “md5abcdef012342345” …
每行是一个用户。每行必须至少有两列，每列的内容必须以英文双引号括起来。
对于加密的密码,可以从 PG 中”pg_shadow”表查询，从PostgreSQL 14开始默认的密码加密由原先的MD5修改成了scram-sha-256， 我们在配置文件得auth_type参数修改成对应算法即可。
postgres=# select usename,passwd from pg_shadow order by 1;
usename | passwd
----------+--------------------------------------------------------------------------------------------------------------------------------
-------
app | SCRAM-SHA-256$4096:ceVAg2FDYhwkk6bi8WYAwQ==$fi9uDk60v7+mMR0DAWdh9YKJn71wGSAc3j+UUyN+f4E=:TxNKuHKbY8EbVQZ4zdZt7zlkr3BQFoOaWNKdRc
N6mrw=
backup | SCRAM-SHA-256$4096:PAeviqvI9J+GwWj4e5lI7w==$cmzp9MUj+4ozdh/yIQ2vBmhElKOl2H7qUkYywtwbl/Q=:mnBdigGKT9whaz3VxXFBb7fZR/sKUFLcLpK2rJ
k6XWc=
cndba | SCRAM-SHA-256$4096:sf9nnp5tkI1L+hQPfRJzAw==$E6SaQdWYGRBO0lZ1HbiZ8iCAzJbg4mtg1hpTqEIkqsc=:4o9gICYnvskFkkdV5DqJh1b8TMD/vDqoy3LNss
581Ug=
dave | SCRAM-SHA-256$4096:s2BytipDRzYwKFC1fPW7RQ==$wwtPBXJQonuVfEs0EOIE2zBr86vd811hkPhwnHybTds=:cCtoaL4xa3QBnbfyBNeA7WXfD2HJGtF7diVmUV
SvOJw=
postgres | SCRAM-SHA-256$4096:LpLN5mzvDNoclc9HHQd8ww==$HZtpbMSDls0J2uCWHX3tkV2VgmWf5a91Ph07vpZQvJo=:+xjJW9LCCLnYtV1WhbU9lzBZ/tiWtr0GdKVpGE
MZEmY=
repl | SCRAM-SHA-256$4096:HElMX94nU635i4jAwFJzMA==$11ZBtos2pop2ktHp0gSuFFk6PXfqOTyJAfCsPkLT+CI=:jyC9AzBfWLOZ9RD2122dTJugGBZ/6s4rJ+dGot
NiSZ4=
(6 rows)
postgres=#
我们这里只添加一个用户：
[dave@www.cndba.cn yum.repos.d]# cat /etc/pgbouncer/userlist.txt
"postgres" "SCRAM-SHA-256$4096:LpLN5mzvDNoclc9HHQd8ww==$HZtpbMSDls0J2uCWHX3tkV2VgmWf5a91Ph07vpZQvJo=:+xjJW9LCCLnYtV1WhbU9lzBZ/tiWtr0GdKVpGE
MZEmY="
[dave@www.cndba.cn yum.repos.d]#
实际上，在PgBouncer有一个Python脚本”mkauth.py”可以自动从”pe_shadow”表中读取数据自动生成的密码文件。但依赖比较多，所以简单点就是手工复制一下。
[dave@www.cndba.cn pgbouncer]# pwd
/etc/pgbouncer
[dave@www.cndba.cn pgbouncer]# ll
total 28
-rwx------. 1 root root 823 Dec 12 17:35 mkauth.py
-rw-r--r--. 2 root root 1132 Dec 13 23:05 mkauth.pyc
-rw-r--r--. 2 root root 1132 Dec 13 23:05 mkauth.pyo
-rw-r--r--. 1 root root 9360 Dec 13 23:05 pgbouncer.ini
-rw-r--r--. 1 root root 148 Dec 16 13:31 userlist.txt
[dave@www.cndba.cn pgbouncer]#
[dave@www.cndba.cn pgbouncer]# python mkauth.py
Traceback (most recent call last):
File "mkauth.py", line 6, in <module>
import psycopg2
ImportError: No module named psycopg2
[dave@www.cndba.cn pgbouncer]#
3.3 启动PgBouncer
Pgbouncer 不能以root用户启动：
[dave@www.cndba.cn pgbouncer]# pgbouncer -d /etc/pgbouncer/pgbouncer.ini
2022-12-16 14:07:00.009 CST [10684] FATAL PgBouncer should not run as root
[dave@www.cndba.cn pgbouncer]#
[dave@www.cndba.cn etc]# chown postgres:postgres pgbouncer –R
[dave@www.cndba.cn yum.repos.d]# su - postgres
Last login: Fri Dec 16 13:23:02 CST 2022 on pts/2
-bash-4.2$ pgbouncer -d /etc/pgbouncer/pgbouncer.ini
-bash-4.2$ ps -ef|grep pgb
postgres 10867 1 0 14:08 ? 00:00:00 pgbouncer -d /etc/pgbouncer/pgbouncer.ini
postgres 10895 10794 0 14:08 pts/1 00:00:00 grep --color=auto pgb
-bash-4.2$
查看日志：
-bash-4.2$ tail /etc/pgbouncer/pgbouncer.log
2022-12-16 14:07:00.009 CST [10684] FATAL PgBouncer should not run as root
2022-12-16 14:08:24.675 CST [10867] LOG kernel file descriptor limit: 1024 (hard: 4096); max_client_conn: 5000, max expected fd use: 5032
2022-12-16 14:08:24.677 CST [10867] LOG listening on 127.0.0.1:6432
2022-12-16 14:08:24.677 CST [10867] LOG listening on unix:/tmp/.s.PGSQL.6432
2022-12-16 14:08:24.677 CST [10867] LOG process up: PgBouncer 1.18.0, libevent 2.0.21-stable (epoll), adns: evdns2, tls: OpenSSL 1.0.2k-fips 26 Jan 2017
-bash-4.2$
3.4 使用pgbouncer 连接PG
-bash-4.2$ psql -p 6432 -h localhost -U postgres
Password for user postgres:
psql (14.6)
Type "help" for help.
postgres=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
cndba | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
dave | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
ustc | cndba | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(6 rows)
postgres=#
3.5 查看连接池信息
PgBouncer提供了类似连接到虚拟数据库“pgbouncer”，然后执行一些PgBouncer特殊命令的功能，这些特殊命令就像是执行一个真正的SQL命令，让管理者能查询和管理PgBouncer 的连接池信息，这个界面称为PgBouncer的console控制界面。
一般使用 psql 命令连接到这个虚拟的数据库”pgbouncer”上，就能执行PgBouncer的这些管理命令，如下：
-bash-4.2$ psql -p 6432 -h localhost -U postgres -d pgbouncer
Password for user postgres:
psql (14.6, server 1.18.0/bouncer)
Type "help" for help.
pgbouncer=# show help;
NOTICE: Console usage
DETAIL:
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
SET key = arg
RELOAD
PAUSE [<db>]
RESUME [<db>]
DISABLE <db>
ENABLE <db>
RECONNECT [<db>]
KILL <db>
SUSPEND
SHUTDOWN
WAIT_CLOSE [<db>]
SHOW
pgbouncer=# show clients;
type | user | database | state | addr | port | local_addr | local_port | connect_time | request_time | w
ait | wait_us | close_needed | ptr | link | remote_pid | tls | application_name
------+----------+-----------+--------+-----------+-------+------------+------------+-------------------------+-------------------------+--
----+---------+--------------+-----------+------+------------+-----+------------------
C | postgres | pgbouncer | active | 127.0.0.1 | 53482 | 127.0.0.1 | 6432 | 2022-12-16 15:21:22 CST | 2022-12-16 15:21:41 CST |
5 | 908626 | 0 | 0x1bd8810 | | 0 | | psql
(1 row)
pgbouncer=# show pools;
database | user | cl_active | cl_waiting | cl_active_cancel_req | cl_waiting_cancel_req | sv_active | sv_active_cancel | sv_being_ca
nceled | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
-----------+-----------+-----------+------------+----------------------+-----------------------+-----------+------------------+------------
-------+---------+---------+-----------+----------+---------+------------+-------------
pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 | statement
postgres | postgres | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 1 | 0 | 0 | 0 | 0 | transaction
(2 rows)
pgbouncer=#
3.6 停止pgbouncer
-bash-4.2$ kill -9 `cat /etc/pgbouncer/pgbouncer.pid`
-bash-4.2$ cat /etc/pgbouncer/pgbouncer.pid
10867
-bash-4.2$ ps -ef|grep pgb
postgres 15186 7631 0 15:05 pts/2 00:00:00 grep --color=auto pgb
-bash-4.2$
版权声明：本文为博主原创文章，未经博主允许不得转载。
