pgbouncer安装、配置、使用方法

--安装
[root@pg14lgm ~]# yum install pgbouncer -y

--配置
# cat /etc/pgbouncer/pgbouncer.ini
[databases]
postgres = host=127.0.0.1 port=5432 dbname=postgres user=postgres password=postgres  connect_query='SELECT 1'
pubdb = host=127.0.0.1 port=5432 dbname=pubdb user=postgres password=postgres  connect_query='SELECT 1'

[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

listen_addr = 0.0.0.0
listen_port = 6432

auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = admin          ;;登录管理console的用户
stats_users = admin
pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 100        ;;表示最多允许用户建多少个连接到pgbouncer
default_pool_size = 20       ;;表示默认连接池建多少个到后端数据库的连接

--配置登录pgbouncer用户和密码
# vi /etc/pgbouncer/userlist.txt 

"marko" "asdasd"
"postgres" "admin"
"pgbouncer" "fake"
"szr" "szr"
"admin" "admin"



--启动
[root@pg14lgm ~]# systemctl enable pgbouncer
Created symlink from /etc/systemd/system/multi-user.target.wants/pgbouncer.service to /usr/lib/systemd/system/pgbouncer.service.

[root@pg14lgm ~]# systemctl start pgbouncer
[root@pg14lgm ~]# systemctl status pgbouncer
● pgbouncer.service - A lightweight connection pooler for PostgreSQL
   Loaded: loaded (/usr/lib/systemd/system/pgbouncer.service; disabled; vendor preset: disabled)
   Active: active (running) since Tue 2023-01-17 16:39:55 CST; 3s ago
     Docs: man:pgbouncer(1)
 Main PID: 478 (pgbouncer)
   CGroup: /docker/cdeaa764cf950e31f1fa079f9b849549447b65104c7a5bc42d4a366597bc782a/system.slice/pgbouncer.service
           └─478 /usr/bin/pgbouncer -q /etc/pgbouncer/pgbouncer.ini
           ‣ 478 /usr/bin/pgbouncer -q /etc/pgbouncer/pgbouncer.ini

Jan 17 16:39:55 pg14lgm systemd[1]: Started A lightweight connection pooler for PostgreSQL.

--连接pgbouncer
[postgres@pg14lgm ~]$ psql -p 6432 pubdb
Password for user postgres: 
psql (14.4)
Type "help" for help.

postgres=# \d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | tb   | table | rep
 public | tt   | table | rep
(2 rows)

--查看连接池信息
需要连接到虚拟数据库pgbouncer
[postgres@pg14lgm ~]$ psql -p 6432 pgbouncer -U admin
Password for user admin: 
psql (14.4, server 1.14.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

SHOW
--查看客户端连接情况：
pgbouncer=# show clients;
 type |   user   | database  | state  | addr | port | local_addr | local_port |      connect_time       |      request_time       | wait | wait_us | close_needed |      ptr       | link | remote_pid | tls 
------+----------+-----------+--------+------+------+------------+------------+-------------------------+-------------------------+------+---------+--------------+----------------+------+------------+-----
 C    | admin    | pgbouncer | active | unix | 6432 | unix       |       6432 | 2023-01-17 16:59:25 CST | 2023-01-17 17:05:03 CST |  185 |  464511 |            0 | 0x55715fa25ca0 |      |        627 | 
 C    | postgres | postgres  | active | unix | 6432 | unix       |       6432 | 2023-01-17 17:05:01 CST | 2023-01-17 17:05:01 CST |    0 |       0 |            0 | 0x55715fa25ed8 |      |        670 | 
(2 rows)

--查看连接池：
pgbouncer=# show pools;
 database  |   user    | cl_active | cl_waiting | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us |  pool_mode  
-----------+-----------+-----------+------------+-----------+---------+---------+-----------+----------+---------+------------+-------------
 pgbouncer | pgbouncer |         1 |          0 |         0 |       0 |       0 |         0 |        0 |       0 |          0 | statement
 postgres  | postgres  |         1 |          0 |         0 |       0 |       1 |         0 |        0 |       0 |          0 | transaction
(2 rows)