PostgreSQL数据库使用系列连接池pgbouncer

caitxj_BC
955 阅读3分钟

PostgreSQL数据库使用系列连接池pgbouncer

PgBouncer - lightweight connection pooler for PostgreSQL

PgBouncer 是一个基于Postgresql的一个轻量级的连接池. 对于postgresql的connection实际上是通过进程模式,对于较多的连接实际上是非常消耗资源.而PgBouncer实际上是通过线程模式进行连接管理,大大的降低资源的消耗。并且通过连接池进行数据操作可以提高数据库的操作效率。

PG进程连接模式 image.png pgbouncer连接池连接 image.png

使用连接池模式 image.png

连接池高可用模式

image.png

pgbouncer 使用

pgbouncer 安装

在线安装

# pg源安装(centos7为例)
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

#pgbouncer安装
yum install pgbouncer

#连接池管理
systemctl stop pgbouncer.service
systemctl status pgbouncer.service
systemctl start pgbouncer.service

pgbouncer 配置

核心配置

[databases]
;; fallback connect string
* = host=localhost port=5432 user=postgres

;;连接池核心配置,日志文件位置、监听ip端口及访问连接池的认证方式
[pgbouncer]
;;;
;;; Administrative settings
;;;
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
;;;
;;; Where to wait for clients
;;;
;; IP address or * which means all IPs
listen_addr = *
listen_port = 6432
;; any, trust, plain, md5, cert, hba, pam
auth_type = md5
;;auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
;; Total number of clients that can connect
max_client_conn = 150

[databases]

database 模块配置连接池上的数据库路由映射:

;;表示连接连接池readdb数据库,实际路由到pg的数据库为test。
;;格式 routerDbName= key=value  (key=value是数据库连接的相关参数,host,port,user,dbname等)
;;*表示未明确指明映射的一个default配置
readdb = host=172.16.20.178 port=5432 user=postgres dbname=test
* = host=localhost port=5432 user=postgres

[pgbouncer]

连接池的核心参数配置,包括监听的ip、端口及其他核心参数

;;核心配置(pid文件及日志文件)
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

;;监听端口、ip
listen_addr = *
listen_port = 6432

;;连接认证方式
;; any, trust, plain, md5, cert, hba, pam
auth_type = md5
;;auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt

;;连接数配置
max_client_conn = 150

连接池认证方式(auth_type)

image.png

认证方式与文件配置

;; any, trust, plain, md5, cert, hba, pam

;;认证方式与认证文件配置
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

认证文件存储格式

#auth_file 账号密码存储格式 "账号"  "密码"
#查询账号及密码信息
#SELECT usename, passwd FROM pg_shadow
"username1" "password" ...
"username2" "md5abcdef012342345" ...
"username2" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>"

连接池模式(pool model)

  • session connection
    服务器在客户端断开连接后释放回池(默认方式)。

  • transacton connection
    服务器在客户端事务完成后释放资源

  • statement connection
    服务器在查询完成后被释放回池。在此模式下不允许跨多个语句的事务。

pgbouncer console管理台

命令行管理台提供了对pgbouncer连接池的运行情况及服务管理提供一个统一的入口进行管

# 1.连接console(-h:ip地址,p:连接池暴露端口,U 连接用户)
psql -h ip  -p 6432 -U postgres pgbouncer

# 2.console相关命令使用

### Show 命令
SHOW HELP
SHOW DATABASES
SHOW STATS
SHOW SERVERS
SHOW CLIENTS
SHOW POOLS
SHOW LISTS
SHOW USERS
SHOW FDS
SHOW SOCKETS, SHOW ACTIVE_SOCKETS
SHOW MEM
SHOW DNS_HOSTS
### Process controlling commands 服务操作命令
PAUSE [db]
DISABLE db
ENABLE db
RECONNECT [db]
SHUTDOWN
RELOAD #刷新配置

使用pgbouncer连接池常见问题处理

  • 使用druid链接pgbouncer时候 unsupported startup parameter: extra_float_digits
;; Comma-separated list of parameters to ignore when given in startup
;; packet.  Newer JDBC versions require the extra_float_digits here.
ignore_startup_parameters = extra_float_digits
  • org.postgresql.util.PSQLException: FATAL: SASL authentication failed(使用SCRAM认证方式) github.com/pgbouncer/p…
    To be able to use SCRAM on server connections, use plain-text passwords.
注意认证方式的处理,对于添加了连接池后，现在对数据库的访问模式为:
client --明文[auth_type方式]----> pgbouncer(userlist.txt) ---明文[pg_hba.conf认证方式]---> pgdb
对于使用的认证模式为:scram-sha-256,需要再userlist.txt配合文件中使用明文密码,否则报在进行认证的时候会报错SASL authentication failed、password type 认证不通过
avatar
文章
阅读
粉丝