使用PgBouncer连接池

PgBouncer工具可以管理PostgreSQL和Greenplum数据库连接的连接池。

在Greenplum数据库中使用pgBouncer作为连接池管理器是有益的，尤其是在高并发的场景下。pgBouncer作为一个轻量级的数据库连接池管理器，它可以有效减少数据库服务器建立和销毁连接的开销，提高系统性能和稳定性。

以下是在Greenplum中使用pgBouncer的一些好处：

1. 连接复用：pgBouncer能够维持少量的持久连接，并让多个客户端请求共享这些连接，从而避免了频繁创建和销毁连接的开销。
2. 资源管理：通过限制并发连接数，可以有效地管理Greenplum数据库的资源，防止因连接数过多而导致的资源耗尽问题。
3. 性能提升：减少TCP连接的建立和销毁次数，可以减少网络开销和内存管理的负担，提高数据库的整体响应速度。
4. 稳定性增强：通过连接池，pgBouncer能够更好地处理短连接请求，避免因为连接数过多而引起的数据库崩溃风险。
5. 安全性：pgBouncer支持SSL加密连接，增强了数据传输过程中的安全性。
6. 易用性：pgBouncer配置相对简单，易于集成到现有的部署和监控体系中。

以下话题描述了如何在Greenplum数据库上配置PgBouncer。 更多有关如何在PostgreSQL上使用PgBouncer的信息，请参考 PgBouncer网站 。

• 概述
• 迁移PgBouncer
• 配置PgBouncer
• 启动PgBouncer
• 管理PgBouncer

Parent topic: 访问数据库

概述

数据库连接池是一种数据库连接的缓存。一旦一个池子的连接被建立，连接池就能消除创建 数据库连接的开销，这样客户端能更快地连接并且服务器的负载也能被降低。

PgBouncer连接池来自于PostgreSQL社区，它被包括在Greenplum数据库中。PgBouncer 可以为多个数据库管理连接池，并且这些数据库可以位于不同的Greenplum数据库集群或者 PostgreSQL后端。PgBouncer会为每一种数据库用户与数据库的组合建立一个池。一个被 池化的连接只能被来自于同一个用户和数据库的另一个连接请求重用。当客户端断开连接后， PgBouncer会将连接归还给连接池以做下次重用。

PgBouncer有三种池模式来共享连接：

• 会话池化 – 当一个客户端连接时，只要它保持连接状态，就分配给它一个连接。 当该客户端断开连接时，该连接才被放回到池中。
• 事务池化 – 在一个事务运行期间，分配一个连接给客户端。当PgBouncer发现 事务完成，该连接就被放回到池中。这种模式只能被用于不使用依赖于会话的特性的应用。
• 语句池化 – 语句池化类似于事务池化，但是不允许多语句事务。这种模式的 目标是为了在客户端强制自动提交模式，且它的定位是PostgreSQL上的PL/Proxy。

gpbouncer有三种连接方式：

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

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

Statement pooling/语句连接池

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

可以为PgBouncer设置一种默认的池模式，并且该模式可以被单个数据库和用户覆盖。

通过连接到一个虚拟的pgbouncer数据库，用户可以使用类SQL命令监控和管理PgBouncer。 可以不重启PgBouncer就修改配置参数，只需要重新载入配置文件就能发现更改。 PgBouncer支持PostgreSQL和Greenplum数据库上的标准连接接口。Greenplum数据库客户端应用 （例如psql）可以连接到正在运行的PgBouncer主机和端口号，而不是 Greenplum的Master主机和端口号。

PgBouncer有一个类psql的管理控制台界面。授权用户可以通过该控制台 连接到一个虚拟数据库来监控和管理PgBouncer。您可以通过管理控制台来管理PgBouncer后台进程， 也可以用控制台在运行时更新和重载PgBouncer配置，而不停止和重启PgBouncer进程。

PgBouncer原生支持TLS。

迁移PgBouncer

当您迁移到一套新版本的Greenplum数据库时，必须迁移PgBouncer实例到新版本的Greenplum 数据库。

• 如果您正在迁移Greenplum 5.8.x版本数据库或更早版本，可以在不丢弃连接的 情况下迁移PgBouncer。采用带有-R选项和配置文件的命令拉起新的PgBouncer 进程。

  $ pgbouncer -R -d pgbouncer.ini

  1	$ pgbouncer -R -d pgbouncer.ini

  -R（重启）选项启动新进程以Unix套接字的方式连接到旧进程并发出以下命令：

  SUSPEND; SHOW FDS; SHUTDOWN;

  1 2 3

  SUSPEND; SHOW FDS; SHUTDOWN;

  当新进程检测到旧进程完成后，它接管旧连接的工作。最可能的原因是SHOW FDS 命令将实际的文件定位符发送新进程。如果事务因任何原因失败，新进程会被杀掉，旧进程仍然接管 原来的工作。

• 如果您正在迁移Greenplum 5.9.0版本数据库或更高版本，您必须关闭旧版本安装文件中 的PgBouncer实例，在新数据库安装文件中重新配置并启动PgBouncer实例。

• 如果您采用stunnel加密旧安装文件的PgBouncer连接，在新环境中必须使用与PgBouncer 1.8.1 及更新版本适配的内建TLS配置SSL/TLS。

• 如果您在旧环境中使用了LDAP授权，在新环境中必须使用与PgBouncer 1.8.1及更新版本适配的 内建集成PAM配置LDAP。必须移除或替换auth_file中所有 以ldap://开头的密码串。

配置PgBouncer

通过配置文件配置PgBouncer和它到Greenplum数据库的访问。配置文件通常命名为pgbouncer.ini， 提供Greenplum数据库的位置信息。pgbouncer.ini文件也指定PgBouncer的进程、连接池、 授权用户和授权配置。

pgbouncer.ini配置文件示例内容如下：

有关PgBouncer配置文件格式和其支持的配置参数列表等信息信息，请见 pgbouncer.ini参考页面。

当客户端连接到PgBouncer时，连接池从pgbouncer.ini配置文件中查找 受访数据库（可能是实际数据库的别名）对应的主机名、端口号和数据库名。该配置文件也定义数据库的 授权模式。

PgBouncer要求有一个授权文件，该文件是一个包含Greenplum数据库用户名和密码的文本文件。 文件内容与pgbouncer.ini中配置的auth_type 授权类型密切相关。密码可能是干净文本或MD5编码的字符串。用户也可以配置PgBouncer通过 查询目标数据库来获取授权文件中未定义的密码信息。

PgBouncer授权文件格式

PgBouncer有自己的用户授权文件。用户可以在pgbouncer.ini配置文件 的auth_file属性中定义文件名。auth_file文本文件 格式如下：

auth_file为每个用户占用一行。每行至少有两个域，两个都用双引号包裹 （” “）。第一部分定义Greenplum数据库用户名，第二部分为明文或 MD5编码的密码。PgBouncer忽略改行余下的部分。

（auth_file文件格式与Greenplum数据库所用的授权信息文件 pg_auth相似。PgBouncer可以直接使用Greenplum数据库授权文件。）

使用MD5编码的密码，密码格式如下：

可以从pg_shadow视图中获取所有Greenplum数据库用户的MD5编码密码。

为PgBouncer配置基于HBA的授权

PgBouncer支持基于HBA的授权。要为PgBouncer配置基于HBA的授权，在pgbouncer.ini 配置文件中设置auth_type=hba，并在pgbouncer.ini文件中配置 HBA格式文件参数auth_hba_file。

名称为hba_bouncer.conf的PgBouncer HBA文件的内容如下：

相关pgbouncer.ini配置文件中的对应配置部分：

有关PgBouncer支持的HBA授权文件格式的详细讨论，请参考HBA file format 。

启动PgBouncer

用户可以在Greenplum数据库master主机或其他服务器上运行PgBouncer。如果在一台单独的服务器上 安装PgBouncer，用户可以使用PgBouncer管理客户端，通过更新PgBouncer配置文件和重载配置很容易的 切换客户端连接到standby master。

参考以下配置设置PgBouncer。

1. 创建PgBouncer配置文件。例如，增加以下文本到文件pgbouncer.ini中：

   [databases] postgres = host=127.0.0.1 port=5432 dbname=postgres pgb_mydb = host=127.0.0.1 port=5432 dbname=mydb [pgbouncer] pool_mode = session listen_port = 6432 listen_addr = 127.0.0.1 auth_type = md5 auth_file = users.txt logfile = pgbouncer.log pidfile = pgbouncer.pid admin_users = gpadmin

   1 2 3 4 5 6 7 8 9 10 11 12

   [databases] postgres = host=127.0.0.1 port=5432 dbname=postgres pgb_mydb = host=127.0.0.1 port=5432 dbname=mydb [pgbouncer] pool_mode = session listen_port = 6432 listen_addr = 127.0.0.1 auth_type = md5 auth_file = users.txt logfile = pgbouncer.log pidfile = pgbouncer.pid admin_users = gpadmin

   该文件列出了数据库和它们连接的详细信息。该文件也配置了PgBouncer实例。 有关PgBouncer配置文件内容和格式的详细信息，请参考 Refer to the pgbouncer.ini参考页面。

2. 创建授权文件。文件名应该和pgbouncer.ini文件中定义的auth_file 参数名字一样，users.txt。每行包含一个用户名和一个密码。密码的格式应该匹配PgBouncer 配置文件中定义的auth_type。如果auth_type参数为plain， 密码串应该为干净的文本文件密码，例如：

   "gpadmin" "gpadmin1234"

   1	"gpadmin" "gpadmin1234"

   。

   如果auth_type如下面例子中为md5，授权部分必须是MD5编码格式。 MD5编码的密码格式如下：

   "md5" + MD5_encoded(<password><username>)

   1	"md5" + MD5_encoded(<password><username>)

3. 开始启动 pgbouncer:

   $ $GPHOME/bin/pgbouncer -d pgbouncer.ini

   1	$ $GPHOME/bin/pgbouncer -d pgbouncer.ini

   -d选项代表以后台进程的形式运行PgBouncer。pgbouncer命令的 语法和选项，请见pgbouncer参考页面。

4. 更新客户应用连接到pgbouncer，以代替直接连接到Greenplum数据库服务器。例如， 连接到上面配置的Greenplum数据库mydb，像如下方式一样运行psql：

   $ psql -p 6432 -U someuser pgb_mydb

   1	$ psql -p 6432 -U someuser pgb_mydb

   -p选项的值代表用户配置的PgBouncer实例的listen_port端口号。

管理PgBouncer

PgBouncer提供一个类psql的管理控制台，可以通过指定PgBouncer端口号 和虚拟数据库名称pgbouncer来登录到PgBouncer管理控制台。 该控制台接受类SQL命令，这些命令允许用户监控、重新配置和管理PgBouncer。

有关PgBouncer管理控制台命令的完整文档，请参考 PgBouncer管理控制台命令参考。

要开始使用PgBouncer管理控制台，请遵循下列步骤。

1. 用psql登录到pgbouncer虚拟数据库：

   $ psql -p 6432 -U username pgbouncer

   1	$ psql -p 6432 -U username pgbouncer

   username必须是pgbouncer.ini配置文件中 admin_users参数中所设置的值。 如果pgbouncer进程运行在用户登录的当前Unix用户的UID下，用户还可以用该用户名登录。

2. 要看PgBouncer管理控制台命令，运行SHOW help命令：

   pgbouncer=# SHOW help; NOTICE: Console usage DETAIL: SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM SHOW DNS_HOSTS|DNS_ZONES SHOW STATS|STATS_TOTALS|STATS_AVERAGES SET key = arg RELOAD PAUSE [<db>] RESUME [<db>] DISABLE <db> ENABLE <db> KILL <db> SUSPEND SHUTDOWN

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   pgbouncer=# SHOW help; NOTICE:  Console usage DETAIL:    SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION    SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM    SHOW DNS_HOSTS|DNS_ZONES    SHOW STATS|STATS_TOTALS|STATS_AVERAGES    SET key = arg    RELOAD    PAUSE [<db>]    RESUME [<db>]    DISABLE <db>    ENABLE <db>    KILL <db>    SUSPEND    SHUTDOWN

3. 如果用户对PgBouncer配置文件pgbouncer.ini做了修改， 用户可以用RELOAD命令重载它：

   pgbouncer=# RELOAD;

   1	pgbouncer=# RELOAD;

映射PgBouncer客户端到Greenplum数据库服务器连接

要映射PgBouncer客户端到Greenplum数据库服务器连接，可以使用PgBouncer管理控制台 命令SHOW CLIENTS和SHOW SERVERS：

1. 使用ptr和link把本地客户端连接映射到服务器连接。
2. 使用客户端连接的addr和port标识来自客户端的TCP连接。
3. 使用local_addr和local_port标识到服务器的TCP连接。

pgbouncer.ini

PgBouncer的配置文件。

官网：http://www.pgbouncer.org/config.html

概要

描述

可以在配置文件pgbouncer.ini中指定PgBouncer配置参数并指定用户相关的配置参数。

PgBouncer的配置文件（通常是pgbouncer.ini）是.ini格式。 .ini格式的文件通常由小节、参数和参数值组成。小节名称封闭在方括号中， 例如，[section_name]。参数和值通常以 key\=value的方式展示。 以”;”或”#”开头的行被注释或者忽略。当该”;”和”#” 字符出现在行后面时候不被识别。

PgBouncer配置文件包含%include路由符，它指定了用来读取和处理的其他文件。 这让我们可以将配置文件拆分为多个部分。例如：

如果提供的文件名不是绝对路径，当前操作系统工作路径被作为相对路径。

PgBouncer的配置文件包括以下小节，详细信息如下：

• [databases]小节
• [pgbouncer]小节
• [users]小节

[databases]小节

[databases]小节包含key\=value 键值对，其中键（key）是数据库名称，值（value） 是key\=value对的libpq连接字符串列表。

数据库名称可以包含不带引号的字符[0-9A-Za-z_.-]。包含其他字符的名称必须使用标准SQL标识符引用：

• 名字用双引号括起来（” “）。
• 用两个连续的双引号字符在一个标识符内表示一个双引号。

数据库名*是回退数据库。此键的值是所请求数据库的连接字符串。 如果自动创建的数据库项的空闲时间超过autodb_idle_timeout参数中指定的时间， 则会自动清理这些数据库项。

数据库连接参数

该值中可能包含以下参数以指定数据库的位置。

dbname

目标数据库名称。

默认值：与客户端数据库名称相同。

host

Greenplum的Master主机的名称或IP地址。主机名在连接时被解析。如果DNS返回多个结果， 则以循环方式使用它们。DNS结果会被缓存，并且dns_max_ttl参数确定 缓存项何时过期。

默认值：未设置，表示通过Unix套接字进行连接。

port

Greenplum数据库的Master端口。

默认：5432

user, password

如果设置了user=，则用指定的用户建立到目标数据库的所有连接。 这意味着数据库将只有一个池。

如果未设置user=参数，将尝试使用客户端传递的用户名登录到目标数据库。 这意味着每个连接到数据库的用户都会有一个池。

auth_user

如果设置了auth_user，任何未在auth_file文件中指定的用户， 通过以auth_user身份查询数据库中的pg_shadow表来认证。 auth_user的口令必须在auth_file中设置。

client_encoding

从服务器要求特定的client_encoding。

datestyle

从服务器要求特定的datestyle。

timezone

从服务器要求特定的timezone。

连接池配置

可以使用以下数据库连接池配置

pool_size

设置该数据库的最大池数。如果没有设置，则使用default_pool_size。

connect_query

在任何用户连接建立之后，允许执行查询。如果查询引发错误，则会被记录，否则将被忽略。

pool_mode

为此数据库设置池模式。如果未设置，则使用默认的pool_mode。

max_db_connections

为此数据库设置数据库范围内的最大连接数。此数据库的所有池连接总数不会超过此值。

[pgbouncer]小节

一般设置

logfile

指定日志文件的位置。日志文件保持打开状态。日志轮转后在PgBouncer管理控制台中执行 kill -HUP pgbouncer或运行RELOAD;命令。

默认：未设置。

pidfile

pid文件的名字。没有pid文件，PgBouncer不能作为后台进程（守护进程）运行。

默认：未设置。

listen_addr

PgBouncer侦听TCP连接的接口地址列表。用户也可以使用*， 表示在所有接口上进行监听。如果没有设置，则仅允许Unix套接字连接。

地址可以用数字（IPv4/IPv6）或名称来指定。

默认：未设置。

listen_port

要监听的端口。应用到TCP和UNIX套接字。

默认：6432

unix_socket_dir

指定Unix套接字的位置。适用于监听套接字和服务器连接。如果设置为空字符串， 则禁用Unix套接字。在线重新启动（-R选项）才能正常工作。

默认值：/tmp

unix_socket_mode

Unix套接字的文件系统模式。

默认为：0777

unix_socket_group

用于Unix套接字的组名。

默认：未设置。

user

如果设置，则指定启动后要更改的Unix用户。这只有在PgBouncer作为root启动或 user与当前用户相同时才有效。

默认：未设置。

auth_file

包含要加载的用户名和口令的文件的名称。文件格式与Greenplum数据库的 pg_auth文件相同，因此可以将此参数设置为其中一个后端文件。

默认：未设置

auth_hba_file

当auth_type为hba时使用的HBA配置文件。 HBA文件格式请参阅https://pgbouncer.github.io/config.html#hba-file-formatPgBouncer文档中PgBouncer支持的HBA授权文件格式的讨论部分。

默认：未设置

auth_type

如何认证用户- pam

auth_query

从数据库加载用户口令的查询。如果用户不存在于auth_file中并且数据库项包含 auth_user，则该查询作为auth_user在数据库中运行以查找用户。

注意查询会在每个数据库中都执行，如果要运行函数，那么该函数需要在每个数据库中都安装。

默认值：SELECT usename, passwd FROM pg_shadow WHERE usename=$1

auth_user

如果设置了auth_user，任何未在auth_file文件中指定的用户， 通过以auth_user身份查询数据库中的pg_shadow表来认证。 auth_user的口令必须在auth_file中设置。

直接访问pg_shadow要求有Greenplum数据库的管理员权限。 推荐使用非管理员用户并调用函数来代替。

pool_mode

指定服务器连接何时可以被其他客户端重用。- session

max_client_conn

允许的最大客户端连接数。当增加时，文件描述符限制也应该增加。使用的文件描述符的实际数量大于 max_client_conn。当每个用户使用自己的用户名连接到服务器时， 使用的理论最大值是：

如果在连接字符串中指定了数据库用户，则所有用户使用相同的用户名连接。那么理论上的最大连接是：

（应该永远不要达到理论上的最大值，除非有人刻意为它加上负荷。但是，这意味着用户应该将文件 描述符的数量设置为一个安全的较高数字。在用户的操作系统文档中搜索ulimit。）

默认：100

default_pool_size

允许每个用户/数据库对的服务器连接数量。这可以在每个数据库配置中被覆盖。

默认：20

min_pool_size

当池中连接数低于这个数字时，将更多的服务器连接添加到池中。当平常的负载下降然后在一段时间 的不活跃期之后突然恢复到平常的负载时，这会改善行为。

默认值：0（禁用）

reserve_pool_size

对池允许的额外连接的数量。0 禁用。

默认： 0（禁用）

reserve_pool_timeout

如果客户端在这段时间内没有被服务，PgBouncer允许使用来自保留池的额外连接。0禁用。

默认值：5.0

max_db_connections

每个数据库的最大连接数。如果达到限制，关闭到池的客户端连接不会立即允许对另一个池建立服务器连接， 因为第一个池的服务器连接仍处于打开状态。一旦服务器连接关闭（由于空闲超时）， 将为等待池打开一个新的服务器连接。

默认：没限制

max_user_connections

每个用户的最大连接数。当用户达到限制时，关闭到池的客户端连接不会立即允许对另一个池建立服务器连接， 因为第一个池的服务器连接仍处于打开状态。一旦服务器连接关闭（由于空闲超时）， 将为等待池打开一个新的服务器连接。

server_round_robin

默认情况下，PgBouncer以LIFO（后进先出）顺序重用服务器连接，这样少数连接获得最大的负载。 这在单个服务器提供数据库时提供最佳性能。但是如果数据库IP后面有TCP循环，那么最好是PgBouncer 也以这种方式使用连接来实现均衡的负载。

默认值：0

ignore_startup_parameters

默认情况下，PgBouncer只允许它在启动包中可以跟踪的参数： client_encoding、datestyle、 timezone和 standard_conforming_strings。

所有其他参数都会产生错误。 要允许其他参数，在这里指定它们，这样PgBouncer可以忽略它们。

默认值：空

disable_pqexec

禁用简单查询协议（PQexec）。与扩展查询协议不同，简单查询协议允许在一个数据包中进行多个查询， 这允许某些种类的SQL注入攻击。禁用它可以提高安全性。这意味着只有专门使用扩展查询协议的客户端 才能工作。

默认值：0

application_name_add_host

将客户端主机地址和端口添加到连接启动时设置的应用程序名称设置。这有助于识别不良查询的来源。 如果应用程序在连接后执行了SET application_name，则设置将被覆盖而不被检测到。

默认值：1

conffile

显示当前配置文件位置。改变该参数会导致PgBouncer在下次RELOAD/SIGHUP后使用另外的配置文件。

默认值：文件来自命令行

service_name

仅在win32服务注册时使用。

默认值：pgbouncer

job_name

service_name的代称。

日志设置

syslog

切换syslog开启和关闭。

默认值：0

syslog_ident

以什么名字发送日志到syslog。

默认值： pgbouncer

syslog_facility

在什么设施下发送日志到系统日志。一些可能是： auth、authpriv、daemon、 user、local0-7

默认值： daemon

log_connections

记录成功的登陆。

默认值：1

log_disconnections

记录连接失败的原因。

默认值：1

log_pooler_errors

记录池中发送给客户端的错误消息。

默认值：1

stats_period

将汇总的统计信息写入日志的频率。

默认值：60

控制台访问控制

admin_users

允许连接并在控制台上运行所有命令的数据库用户列表，用逗号分隔。 当auth_type=any时候，忽略该列表。在这种情况下，任何用户名都被允许作为admin。

默认值：空

stats_users

逗号分隔的数据库用户列表，允许它们在控制台上连接并运行只读查询。这意味着除了SHOW FDS 之外的所有SHOW命令。

默认值：空

C连接健康检查、超时

server_reset_query

在连接释放时且没有对其他客户端可用之前，发送到服务器的查询。在那一刻没有事务正在进行， 所以它不应该包括ABORT或ROLLBACK。

该查询会清理任何改变模式为数据库会话，以保证下一个客户端连接获取更好的状态。默认为 DISCARD ALL

当使用事务池时，server_reset_query应该是空的， 因为客户端不能使用任何事务特性。如果客户端使用了事务特性，操作将会失败，因为事务池 不能保证下次运行相同的连接。

默认为： DISCARD ALL;

server_reset_query_always

server_reset_query是否应该在所有池模式下运行。如果此设置处于关闭状态 （默认），则server_reset_query将仅在处于会话池模式的池中运行。 事务池模式下的连接不应该有任何重置查询的需要。

默认值：0

server_check_delay

保持被释放连接可重用而不运行健康检查查询的时间。如果为0，则查询始终运行。

默认值：30.0

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。