• 首页
• 新闻中心
  • 内部新闻
  • 公共新闻
  • 业界新闻
  • 产品新闻
• 产品应用
  • 程序开发工具
  • 企业级应用
  • 服务器软件
  • 应用工具
  • 数字娱乐
  • 硬件
• 解决方案
  • 系统架构经验谈
  • 系统部署应用
  • 办公自动化
  • 案例集锦
  • 参考手册
• 开源应用支持
  • 认识Unix
  • 系统管理
  • 网络管理
  • 桌面应用
  • 存储备份
  • 引导安装
  • 服务应用
  • 群集应用
  • 数据库
  • 系统安全
• 程序设计与开发
  • Perl编程
  • 设计与开发
  • 应用编程开发
  • Web设计与编程
• 关于我们
  • 关于本站

PostgreSQL连接池-pgbouncer

2019-10-21 15:48:59 阿炯

PgBouncer是PostgreSQL的轻量的连接池，支持会话连接池、事务连接池和语句连接池三种模式。可以对客户端的连接做限制，防止恶意连接，另外也可以减少数据库的实际连接数，从而减少数据库的开销。采用C语言开发并在BSD协议下授权。其轻量级体现在：使用libevent进行socket通信，运行效率高；C语言编写执行效率高，每个连接仅消耗2kB内存。


Session pooling/会话连接池
最礼貌的方法。在客户端连接的时候，在它的连接生命期内，会给它赋予一个服务器连接。在客户端断开的时候，服务器连接会放回到连接池中。

Transaction pooling/事务连接池
服务器连接只有在一个事务里的时候才赋予客户端。在 PgBouncer 注意到事务结束的时候，服务器将会放回连接池中。这是一个 hack，因为它打破了应用对后段连接的看法。只有在应用配合这样的使用模式，没有使用会破坏这种使用模式的时候才能用这个连接方式。参阅下标获取会破坏 这种模式的特性。

Statement pooling/语句连接池
最激进的模式。这是事务连接池的一个扭曲的变种 - 不允许多语句的事务。这就意味着是在客户端强制“autocomit”模式，主要是给 PL/Proxy 用的。

内存需求低(缺省的时候每个连接 2k)。这事因为 PgBouncer 不需要一次就看到完整的包。
它不是和单个后端服务器绑定的，目标数据库可以位于不同的主机上。
对大多数设置，都支持在线的重新配置，无需重启。
支持在线的重启/升级，而不会退出客户端的连接。
只支持协议 V3 版本，因此后端版本必须 >= 7.4。

SQL feature map for pooling modes

The following table list various PostgreSQL features and whether they are compatible with PgBouncer pooling modes. Note that “transaction” pooling breaks client expectations of the server by design and can be used only if the application cooperates by not using non-working features.

Feature	Session pooling	Transaction pooling
Startup parameters 1	Yes	Yes
SET/RESET	Yes	Never
LISTEN/NOTIFY	Yes	Never
WITHOUT HOLD CURSOR	Yes	Yes
WITH HOLD CURSOR	Yes	Never
Protocol-level prepared plans	Yes	No 2
PREPARE / DEALLOCATE	Yes	Never
ON COMMIT DROP temp tables	Yes	Yes
PRESERVE/DELETE ROWS temp tables	Yes	Never
Cached plan reset	Yes	Yes
LOAD statement	Yes	Never

1.Startup parameters are: client_encoding, datestyle, timezone, and standard_conforming_strings. PgBouncer detects their changes and so it can guarantee they remain consistent for the client.

2.It is possible to add support for that into PgBouncer.


基本概念

支持三种连接池模型：
session，会话级连接，连接生命周期里，连接池分配一个数据库连接，客户端断开连接时，连接放回连接池。
transaction， 事务级连接，客户端的每个事务结束时，数据库连接就会重新释放回连接池，在执行一个事务时，就需要从连接池重新获得一个连接。
statement，语句级别连接 ，执行完一个SQL语句时，连接就会释放回连接池，再次执行一个SQL语句时，需要重新从连接池里获取连接，这种模式意味客户端需要强制“autocommit”模式。

安装方法

在Linux发行版中已经编译好的PgBouncer可以直接安装。RHEL/CentOS平台直接使用yum命令来安装；Debian/Ubuntu平台下，直接使用apt-get命令安装。在官网上下载源码编译安装，需要先安装libevent依赖。

简单使用

简单配置
简单介绍PgBouncer的配置文件pgbouncer.ini，后面附有详细介绍具体配置。使用系统自带工具安装的PgBouncer的配置文件路径是/etc/pgbouncer/pgbouncer.ini，默认的配置和含义如下：
[databases]
[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log  # 日志文件位置
pidfile = /var/run/pgbouncer/pgbouncer.pid  # pid文件位置
listen_addr = 127.0.0.1 # 监听的地址
listen_port = 6432  # 监听的端口
auth_type = trust   # 认证方式
auth_file = /etc/pgbouncer/userlist.txt #  认证文件
admin_users = postgres  # 管理员用户名
stats_users = stats, postgres   #  状态用户？stats和postgres
pool_mode = session # 池的模式，默认session级别
server_reset_query = DISCARD ALL#
max_client_conn = 100   # 最大连接用户数，客户端到pgbouncer的链接数量
default_pool_size = 20 # 默认池大小，表示建立多少个pgbouncer到数据库的连接

默认情况下配置任何数据库信息，需要添加一些简单的信息。从上面还可以看到，配置主要分为两部分，第一部分是[databases]区域，是用来配置数据库连接相关信息的。第二部分是[pgbouncer]，是pgbouncer自身的配置。

下面来看一个[databases]部分配置的示例：
forcedb = host=127.0.0.1  port=3000  user=baz  password=foo  client_encoding=UNIODE datestyle=ISO connect_query='SELECT 1'

它的基本格式是：
对外提供的数据库名 = host=主机IP port=端口 user=用户 password=密码
其他的规则都类似，数据库名后面的等号旁边要有空格隔开，后面每个成对的数值之间用空格隔开。这里面的主机和端口指的是PostgreSQL监听的地址和端口，而用户和密码就是用来连接PostgreSQL数据库的用户名和密码。

根据这个格式来建一个配置：
testdb=host=192.168.1.244  port=5433 user=dbuser  password=yourpassword connect_query='SELECT 1'

下面的[pgbouncer]区域就使用默认配置，这个默认配置里，需要说一下下面这两个配置：
auth_type = trust   # 认证方式
auth_file = /etc/pgbouncer/userlist.txt #  认证文件

第一行是用于配置登录pgbouncer的认证方式的，和PostgreSQL认证方式相同，默认是trust，即所有的都信任，还可以使用md5加密的形式。第二行是用于配置认证用户的，即连接pgbouncer的用户名都保存在该文件中。当第一行设置为md5加密时，则加密的密码也必须保存在第二行配置的文件中。如果这个文件不存在，那么登录的时候，无论是哪个用户，都会提示下面的错误：
-bash-4.2$ psql -p 6432 testdb -h 127.0.0.1
psql: ERROR:  No such user: postgres
-bash-4.2$ psql -p 6432 testdb -h 127.0.0.1 -U dbuser
psql: ERROR:  No such user: dbuser

而这个认证文件默认情况下是没有的，因此需要手动生成。在PostgreSQL的9.x版本中，所有的用户密码都是保存在pg_shadow表里。PostgreSQL 8.x版本则是保存在数据库目录下，可以直接复制过来使用。使用的9.5版本，因此需要手动生成这个文件。生成这个认证文件有两种方式，分别是SQL语句方式和pgbouncer自带的mkauth.py脚本生成。

我们来看下两种生成方式：
(1)SQL语句生成认证文件
之前我们说过，用户密码默认是保存在pg_shadow表里的，如下面所示：
postgres=# select usename, passwd from pg_shadow order by 1;
 usename  |   passwd
----------+-------------------------------------
 dbuser   | md5baa6c789c3728a1a449b82005eb54a19
 postgres |

usename和passwd两列里面保存的就是我们需要的账号和密码，使用copy命令将它们导出来：
postgres=# copy (select usename, passwd from pg_shadow order by 1) to '/var/lib/pgsql/9.5/auth_file';
COPY 2

auth_file文件内容如下：
dbuser  md5baa6c789c3728a1a449b82005eb54a19
postgres\N

里面保存有postgres的账号，一般不要使用这个超级管理员的身份，最好删掉。然后保留可以连接数据库的用户账号和加密后的密码，将这个文件转移到上面配置项指定的位置。并且文件名称要和上面变量里定义的文件名一致，否则会提示找不到这个文件。最后还要注意的一点是，默认导出的文件里用户名和密码的格式pgbouncer无法识别，需要用双引号引起来，如下所示：
"dbuser"  "md5baa6c789c3728a1a449b82005eb54a19"

这样才能正确识别。

(2)使用mkauth.py来生成文件
这个文件是使用python编写的一个小脚本，已经赋予了可执行权限。执行的时候需要两个参数，基本格式是：
/etc/pgbouncer/mkauth.py   用户列表文件   "数据库连接参数"

我们来看一个示例：
/etc/pgbouncer/mkauth.py  /etc/pgbouncer/userlist.txt   "host=127.0.0.1  user=postgres password=123456"

这里比较重要的是后面那一段参数，=号两边不能有空格，两个键值对之间要用空格隔开，不能用逗号，否则会报错。用户必须是有查询pg_shadow表权限的用户请记住这里的限制条件。

如果没有错误的话，就会在/etc/pgbouncer/目录下生成userlist.txt文件。文件内容如下所示：
"dbuser" "md5baa6c789c3728a1a449b82005eb54a19" ""
"postgres" "" ""

默认会备份出PostgreSQL数据库的pg_shadow表里的所有数据库，包括postgres用户。所有的用户名和密码都会用双引号引起来，比手动备份要方便的多。这里唯一麻烦的就是脚本后面的连接字符串。

启动pgbouncer
当用户文件配置好以后，就可以启动pgbouncer来使用了。使用linux发行版自带的包管理工具安装pgbouncer的时候，它会自动创建一个pgbouncer用户，而如果是自己编译的话，则需要手动创建这个用户。创建完成以后需要切换到这个用户下来启动pgbouncer，pgbouncer是不允许在root用户下启动的。

切换完成后，它的启动命令格式是：
pgbouncer -d /etc/pgbouncer/pgbouncer.ini
-d 表示是以后后台进程的方式运行，后面跟的是配置文件的路径。启动完成，pgbouncer默认监听6432端口。

然后就可以使用psql来登录了。

停止pgbouncer
目前pgbouncer还没有自主停止的脚本或者命令，只能通过kill命令来停止。格式是：
cat /var/run/pgbouncer/pgbouncer.pid | xargs kill -9

连接信息的查看
pgbouncer对外提供了一个虚拟数据库pgbouncer，之所以成为虚拟数据库，是因为它可以提供像PostgreSQL那样的数据库操作界面，但是这个数据库却并不是真实存在的。而是pgbouncer虚拟出来的一个命令行界面。登录命令是：
psql -p 6432  pgbouncer

登录以后可以使用show help命令查看所有的帮助命令信息，常用的两个命令是：
show clients 用来查看客户端连接信息
show pools 用来查看连接池信息

上节主要讲解了pgbouncer的基本介绍，基本配置以及简单的使用；接下来继续说pgbouncer的配置文件，熟悉它的高级配置。

配置详解

配置文件分为[databases] 和 [pgbouncer]两部分上面说过，现在来详细讲解一下里面的配置。

在上面的部分我们已经看到了一部分的[databases]部分的参数配置，都是以键值对的形式出现的，例如dbname、host、port、user、password，这几个参数都比较好理解。还有几个额外的配置我们接着看一下：
(1) pool_size 用来配置连接池的大小。连接池的含义上面说过，如果没有这个值则使用[pgbouncer]部分的default_pool_size的值。
(2)connect_query ：后面跟一个SQL语句字符串，用于探测这个连接是否正常，如果执行SQL语句出错，则换一个连接。
(3)client_encoding：指定连接的客户端使用的字符集编码
(4)datestyle：指定日期类型
(5)timezone：指定时区。

[pgbouncer]部分的配置
这个部分的配置项比较多，主要分为下面几类：
通用配置项
日志配置项
控制界面访问控制配置项
连接健康检查和超时配置项
危险的超时配置项
底层网络配置项

下面来依次说这些配置：

通用配置
logfile 指定日志文件，默认值是/var/log/pgbouncer/pgbouncer.log
pidfile 指定pid文件位置，默认值是/var/run/pgbouncer/pgbouncer.pid
listen_addr 监听的地址，默认值是127.0.0.1，可以使用*号表示监听所有IP地址。
listen_port 监听的端口，默认值是6432
unix_socket_dir 指定unix socket文件的目录，默认为/tmp目录
unix_socket_mode 指定unix socket文件的权限，默认值为0777
unix_socket_group 指定unix socket文件的组，默认无
user 指定启动PgBouncer的用户名，windows系统不支持此设置
auth_type 认证的类型，默认是trust，其他值包括md5，crypt，plain，any。用的较多的是md5
auth_file 认证文件的位置，默认值是/etc/pgbouncer/userlist.txt
pool_mode 指定池的模式，默认是session模式，还可以是transaction和statement
max_client_conn 允许的最大连接数
default_pool_size 默认的池大小，默认值20
min_pool_size 最小的池大小，每个连接池至少会向后端数据库保持多少个连接
reserve_pool_size 连接池的保留连接数
reserve_pool_timeout 保留连接的超时时间
server_round_robin 负载均衡的方式是否设置为“round robin”，默认为关闭，即后进先出
ignore_startup_parameters 默认情况PgBouncer只会跟踪一些默认参数，并且能检测这些参数的变化，保持这些参数和客户端的一致。在这个配置后面跟的配置后被PgBouncer忽略，不会被检查。
disable_pqexec 是否禁止简单查询协议，默认值为0。简单查询协议允许一个请求发送多个SQL，容易导致SQL注入攻击。

日志配置项：
syslog 是否打开syslog，windows下打开eventlog，默认值为0，表示不打开。
syslog_ident 默认为PgBouncer
syslog_facility
log_connections 是否记录连接成功的日志，默认值为1，表示记录
log_disconnections 是否记录断开连接的日志，默认值为1，表示记录
log_pooler_errors 连接池法网客户端的错误是否记录在日志中，默认值为1，表示记录
stats_period 把汇总的统计信息写入日志的时间周期，默认是60s

控制界面访问控制配置项：
admin_users 管理用户名，默认值是postgres
stats_users 允许连接到控制界面，查看连接池只读信息的用户列表。可以执行除“SHOW FDS”以外的其他“SHOW”命令
server_reset_query
server_check_delay 空闲的连接多长时间进行一次健康检测，判断连接是否可用。如果设置为0，则立即检测，默认值为30s
server_check_query，进行健康检查的SQL语句，如果为0，表示不检测，默认值为“select 1;”
server_lifetime 连接的存活时间，连接超过这个时间就会被关闭，默认为3600，设置为0表示只使用一次。
server_idle_timeout 连接的idle时间，超过此时间，连接会被关闭。默认为600
server_connect_timeout 后端数据库的login时间超过这个值就会被关闭。默认为15s
server_login_retry 传教到后端数据库的连接失败后，等多长时间后重试，默认为15s
client_login_timeout 客户端与PgBouncer建立连接后，如果无法在这段时间内完成登录，那么连接会断开，默认为60s

危险超时配置项
指的是为防止一些未知错误或者原因导致系统卡住的针对性配置。

query_timeout 允许超过该时间值的SQL会被断开，应该比SQL实际的执行时间稍长，也需要比数据库的statement_timeout参数值更大。为了应付一些未知的网络问题。默认为0.0，禁止使用
query_wait_timeout 请求在队列中等待被执行的最长时间，如果超过该时间还没有分配到连接，就会断开。默认为0，禁止使用。
client_idle_timeout 客户端连接空闲超过该时间，则断开连接。默认值为0，禁止使用
idle_transaction_timeout 客户端启动事务后，超过这个时间没有结束事务，则关闭这个客户端连接。默认值为0，禁止使用

底层网络连接配置
pkt_buf 用于网络包的内部缓冲区大小，会影响发出的TCP包的大小即内存的使用，默认值为2048，一般保持默认值
max_packet_size 通过PgBouncer最大的包大小，包可以是一个SQL，也可以是返回的结果，默认值是2147483647
listen_backlog TCP监听函数listen的Backlog参数，默认值为128
sbuf_loopcnt 处理过程中，每个连接处理多少数据就切换到下一个连接。默认为5，如果设置为0，表示不限制。不限制时，一个连接发送大量数据，另外的连接可能就会空闲，导致被结束掉。
tcp_defer_accept linux下，默认为45，其他平台为0。详细解释用man 7 tcp来查看
tcp_socket_buffer 默认没有设置
tcp_keepalive 是否以操作系统的默认值打开基本的keepalive 设置，在linux下，操作系统的keepalive里，默认值时tcp_keepidle=7200, tcp_keepintvl-75,tcp_keepcnt=9，其他操作系统类型，默认值为1
tcp_keepcnt 默认未设置
tcp_keepidle 默认未设置
tcp_keepintvl 默认未设置

上面的这些参数，大部分都是采用默认设置，常用的几个配置也就是pgbouncer.ini文件的基本默认设置：
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
stats_users = stats, postgres
pool_mode = session
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 20

剩下的配置在需要的时候，可以查询它们的含义，知道有这个配置即可。



最新版本：1.12
PgBouncer 1.12.0 发布了，新版本包含对 PgBouncer 1.11.0 中新的 SCRAM 支持的一些修复，从而改善了与较新的 PostgreSQL 版本的互操作性，因此特别建议 SCRAM 用户进行升级。更新内容如下：
添加一个设置以打开 SO_REUSEPORT 套接字选项。在某些操作系统上，这允许在侦听同一端口的同一主机上运行多个 PgBouncer 实例，并使内核自动分配连接。
添加一个设置以使用与操作系统分开的 resolv.conf 文件。这允许设置自定义 DNS 服务器以及其他 DNS 选项。
将 SHOW VERSION 的输出作为常规结果行而不是 NOTICE 消息发送。这使它更易于使用，并且与其他 SHOW 命令一致。
将统计信息列发送为数字而不是 bigint。这样可以避免某些客户端库在溢出 bigint 范围的值上失败。
修复 PAM 用户丢失密码的问题。
接受启用了 SCRAM 通道绑定的客户端。以前，在某些情况下，支持通道绑定的客户端(即 PostgreSQL 11+)在连接到 PgBouncer 时会发生连接失败(PgBouncer 不支持频道绑定。此更改仅修复了对提供该绑定的客户端的支持)。
使用较新版本的 musl-libc(由 Alpine Linux 使用)修复编译。
更多详情可查看更新列表。

最新版本：1.16
PgBouncer 1.16.0 正式版本于2021年8月中旬发布，更新内容如下：
支持热重载 TLS 设置。当配置文件被重新加载时，改变的 TLS 设置会自动生效；
密码和用户名的最大长度已分别增加到 996 和 128；
现在可以为每个数据库设置最小池大小；
在 SHOW POOLS 中显示了待定查询取消的数量。
配置解析现在在许多地方有更严格的错误处理。以前它可能会记录一个错误并继续进行，现在这些配置错误会导致启动失败；
查询取消处理已被修复。此前在某些情况下，取消请求似乎会被卡住很长时间；
通过 hba 混合使用 md5 和 scram 的问题已被修复；
在 Windows 上用 c-res 构建的问题已被修复；
"FIXME: query end, but query_start == 0" 信息已被修复；
修复了重新加载 default_pool_size、min_pool_size 和 res_pool_size 的问题。以前重新加载这些设置时，并不奏效。
现在使用 Cirrus CI 而不是 Travis CI；
跟以往一样，增加了许多测试；
不能再使用 "pgbouncer" 作为数据库名称；
在连接关闭前发送给客户端的错误现在被标记为 "FATAL"，而不仅仅是 "ERROR"；
修正了 GCC 11 的编译器警告；
更多详情可查看此处。


官方主页：http://www.pgbouncer.org/