下载掌阅APP，畅读海量书库
立即打开
PgBouncer是由Skype的开发者在2007年开发的连接池软件。由于PgBouncer的连接开销远远小于PostgreSQL，使得它与数量庞大的客户端进行交互成为可能。尤其是与庞大的库、临时表、查询结果或其他昂贵的资源相比，它只是在队列中跟踪每个客户机的连接，然后根据配置创建若干 PostgreSQL 连接，并以先到先得的方式将连接分配给使用者。
安装PgBouncer可以从自身的软件源直接获取，为了使用其新的特性，也可以从源码编译部署。
从软件源安装PgBouncer非常简单。这里以CentOS操作系统为例，运行下面的命令：
安装后，将配置文件目录所属者分配给postgres系统账号。
需要使用一个安全的 Postgres 数据库，在主 Postgres 数据库中创建一个可以登录的PgBouncer 用户，可以随机使用一个密码并且在之后的配置中使用它。请在 PostgreSQL数据库中运行下面的SQL语句。
将PgBouncer加入系统服务中来管理启动和关闭，需要运行下面的命令：
从源码编译安装 PgBouncer。PgBouncer 官网中包含源码下载、文档、FAQ。在安装前需要提前安装依赖，安装命令如下。
下载源码。
解压。
进入目录并设置程序存放路径。
编译安装。
手动创建配置文件。
修改PgBouncer程序路径所属者并配置权限。
将编译后的PgBouncer程序加入systemctl服务中。
安装PgBouncer后，需要配置理想的池大小，要根据实际情况进行正确的配置。
在安装PgBouncer时，建议将配置目录修改为可由postgres系统用户写入，该用户是拥有 PostgreSQL 服务的同一用户。为了简单起见，建议设置此用户或者具有根用户权限的用户代表它修改文件。
需要在考虑连接成本和限制的情况下计算出池大小。
假设需要配置的池大小为25，最大连接数为350，请按照以下步骤正确配置PgBouncer。
第一步：创建一个 PgBouncer 可以使用的身份验证文件。作为第三方守护程序，它不能直接访问 PostgreSQL 身份验证文件。但是，它仍然可以在分配池资源之前对用户进行身份验证。这意味着需要创建当前用户及其加密密码的副本，PgBouncer才可以使用。此文件应在创建新用户或更改密码时重新生成。使用postgres用户连接到 PostgreSQL 数据库并执行以下SQL语句，将用户密码导出到userlist.txt文件中。
第二步：将生成的userlist.txt文件复制到etc目录下。
第三步：把pgbouncer.ini文件所属者分配给postgres用户，设置postgres用户编辑此配置文件。
第四步：编辑 pgbouncer.ini 文件修改配置。第一部分是[databases]部分，它跟踪PgBouncer映射的每个数据库。一个数据库既可以是一个主机的一对一关联，也可以是各种连接参数（如端口、主机或用户名）的别名。在标记为[databases]的部分内，加入此行。
第五步：在标记为[pgbouncer]的部分找到这些条目并修改为以下内容。
通过修改listen_addr，PgBouncer将监视分配给此服务器的所有IP地址。如果使用虚拟IP地址，则这一点尤其重要。确保auth_type设置为md5，以便使用导出的所有加密密码。将admin_users设置为postgres，因为PgBouncer有一个管理控制台，可以用来控制连接池的行为。
max_client_conn 设置了 PgBouncer 客户端连接数。这主要是为了防止客户端在分配连接之前等待太久。如果吞吐量总体上不错，则可以增加PgBouncer客户端连接数。
default_pool_size（默认池大小）和reserve_pool_size（保留池大小）是针对每个用户和每个数据库的。因此，即使实例中只有一个主数据库，在PgBouncer将它们放入等待队列之前，每个用户也可以有25个连接。如果PostgreSQL连接的数量太多，并且开始影响查询吞吐量，则可能需要减少这些设置。最好为需要它的应用程序保留池，这样可以更好地为这些应用程序创建PostgreSQL连接。
第六步：通过root用户启动PgBouncer服务。
保存配置后启动 PgBouncer，它默认使用 6432 端口。如果安装在专用的代理节点服务器上，则可以将端口设置为5432。
注意：在许多环境中，频繁地重新生成userlist.txt文件可能会非常不方便，建议自动化此过程，或者依赖轻量级目录访问协议（LDAP）、可插拔身份验证模块（PAM）等。除上面提到的基础配置外，PgBouncer还有一些非常重要的配置。
pool_mode配置包括如下内容。
Session 会话模式：将连接分配给客户端应用，直到客户端断开连接。这种方法被认为是最安全的方法，但某些应用程序会占据有限的连接，而永远不会释放。Session 会话模式是默认设置，不需要在配置文件中单独配置。
Transaction 事务模式：连接被分配给客户端，直到其完成一个事务。一旦事务被提交或中止，连接将重新进入池并分配给另一个客户端。对于那些保持持久数据库连接的应用程序来说，这是很好的设置，因为它仍然允许在池中进行连接循环。但是，一些使用游标的应用程序希望连接在事务之间保持不变，以便获取数据。由于事务之间的连接都会重置，这些游标也会被释放，应用程序将无法正常工作。
Statement语句模式：在每个SQL语句完成后，连接会重新进入池以重新分配给另一个客户端。使用这种模式的场景非常少，只有在从不使用事务、游标或预编译查询（Prepared Queries）等功能的情况下才应使用Statement语句模式。
在安装、配置和启动PgBouncer之后，就可以开始正式使用。如何连接到PgBouncer而不是PostgreSQL？
首先要确保 PgBouncer 已经配置完成且正常运行，检查配置文件是否已正确配置。然后在命令行执行如下命令查看进程是否正常运行。
执行后会看到类似这样的一行代码：
如果没有显示这行代码，则说明PgBouncer没有正常启动。
可以使用psql命令通过PgBouncer连接到PostgreSQL数据库。
若图形化管理工具或其他应用需要连接，则只需要将主机设置为pgha-proxy，端口设置为6432即可。
PgBouncer就像一个模拟的PostgreSQL服务器。任何标准PostgreSQL客户端或驱动程序都是完全兼容的，唯一的区别是默认端口是6432而不是5432，这使PgBouncer成为一个连接代理，或称为“伪数据库”。
现在需要连接到PgBouncer而不是PostgreSQL，请检查是否正确连接到PgBouncer。下面将使用所谓的伪数据库。为了方便访问管理控制台，需要在连接的时候使用pgbouncer 作为被连接的数据库名称。虽然这个数据库并不存在，但它仍然可以被PostgreSQL客户端程序用来连接。
按照以下步骤来获取从pga-proxy服务器到PostgreSQL的 PgBouncer服务器连接的状态。
通过连接到端口6432上的pgbouncer数据库名称，连接到PgBouncer并使用一个不存在的模拟数据库，或称为“伪数据库”。
执行 SHOW SERVERS 指令后会看到一个视图，PgBouncer 列出所有被接入的PostgreSQL服务器。其中比较重要的字段如下。
● user：此列为当前连接到数据库的用户。
● database：此列为当前连接被附加到的数据库。
● state：此列为当前连接的状态，取值范围为活动、使用、空闲。当连接被分配给客户端时，被标记为活动。当连接至少处理了一个查询时，则会被标记为使用。当连接被识别为未被分配时，则被标记为空闲。
● connect_time：显示PgBouncer创建到PostgreSQL的连接的时间。如果该时间大多数情况下都是最近的，则意味着连接的打开和关闭过于频繁。PostgreSQL 连接的分配成本相对较高，连接池在一定程度上是为了降低这一成本。可能需要考虑根据此字段的内容修改PgBouncer连接超时设置。
● request_time：此列为当前连接最后一次查询的处理时间。在繁忙的服务器上，这应该是一个非常近的时间戳；否则，维护不必要的空闲连接可能会浪费服务器资源。在这种情况下，需要检查池大小设置并考虑减少它，或者标记的 PostgreSQL连接可能有问题，或者指定的客户端可能会被冻结。
除了 PostgreSQL 服务器连接状态，PgBouncer 的管理控制台还可以提供有关其队列中客户机的详细信息。在有限的资源上保持一个健康且活跃的 PgBouncer 队列是实现高吞吐量的关键。在这种情况下，需要限制客户端到服务器的连接数，潜在的不健康客户端连接有可能被阻止。
从连接池中删除连接，会导致事务传输出现阻塞的瓶颈。按照以下步骤获取PgBouncer客户端的状态。
与前面一样，连接到6432端口上的pgbouncer数据库，以便使用管理控制台。通过show clients语句查询客户端信息，最常用到的字段如下。
● user：此列显示当前连接到数据库的用户。如果使用高级配置，则此列可能与连接到PgBouncer的用户不同。
● database：此列显示客户端连接到的数据库。一个 PostgreSQL 服务器上可以托管很多数据库，这是非常有用的信息。同样高级配置可以修改从 PgBouncer 创建的连接数据库名称。
● state：此列用来显示连接的状态。常用的状态有active（活动）、used（已使用）、waiting（等待）、idle（闲置）。当客户端正在使用连接时，它们被标记为活动。如果客户端在连接可用之前排队，则它们被标记为等待。
● connect_time：此列显示从客户端连接到PgBouncer的确切时间。虽然可以明确地查询客户端的状态，但是这个元素也关系到与PostgreSQL连接的相关性。由于连接是回收的，因此它们可能是几个小时甚至几天前的。
● request_time：此列显示客户端最后一次查询的时间。在繁忙的服务器上，这应该总是一个最近的时间戳；否则，维护不必要的空闲连接可能会浪费服务器资源。在这种情况下，需要检查池大小设置并考虑减少它，或者标记的PostgreSQL连接可能有问题，或者指定的客户端可能会被冻结。
PgBouncer 除了提供了服务器和客户端的数据库连接信息，还提供了每个池的运行状态和性能状况。PgBouncer 连接池配置由用户名、数据库名和服务器主机名分隔。因此，每个PostgreSQL服务器的连接池可能与用户通过PgBouncer访问的不同数据库的数量相同。
PgBouncer在查找服务器或客户端状态时提供了一些详细信息。但是，这些不是数据库视图，因此无法汇总或聚合输出以使其更可用。可能的话，需要监视聚合值，以监视错误配置或滥用的潜在模式。
由于PgBouncer充当代理，所以不能依赖pg_stat_activity系统视图获取摘要信息。这意味着PgBouncer及其管理控制台是调试和状态信息的主要来源。
通过以下步骤获取PgBouncer客户端的状态：作为postgres用户，连接到PgBouncer服务器端口6432上的pgbouncer数据库；如果查询连接池状态，则使用show pools查询语句；如果查询连接池统计信息，则使用show stats语句。
连接到端口6432上的pgbouncer数据库名称会使用实际上不存在的模拟数据库。如果配置了PgBouncer，则postgres用户是唯一允许使用管理控制台的数据库用户。
通过show pools发送查询，PgBouncer会为它充当代理的每个PostgreSQL数据库进行响应。每列是各种客户端和服务器指标的摘要，主要与活动或状态相关。
以下是各列的详细含义。
● cl_active：此列显示当前分配给服务器连接的客户端数。此数值不应超过从pgbouncer.ini default_pool_size中添加reserve_pool_size和应用程序来获取的值。如果总数经常低于最大值，则需要考虑减少池大小。
● cl_waiting：它表示等待服务器连接的客户端数。由于这是当前活动的快照，因此该数值可能存在剧烈波动。但是，如果它经常保持在零以上，并且最大等待列正在增加，则池可能太小。
● sv_active：此列详细介绍了分配给 PgBouncer 客户端的 PostgreSQL 服务器连接数。这些客户端不一定处于活动状态，只是与连接关联。cl_active 列与 sv_active列始终相等。
● sv_idle：此列提供一些未使用的 PostgreSQL 服务器连接。PgBouncer 在发送重置查询以清除分配的对象和设置后将连接标记为空闲。因此，连接不仅处于空闲状态，而且可以立即进行分配。
● sv_used：这表示PostgreSQL 服务器连接的计数。这些连接实际上处于空闲状态，但尚未被 PgBouncer 重置以进行重用。这意味着需要向用户 sv_used、sv_idle 获取此数据库和用户组合的空闲连接的实际计数。与 sv_idle 一样，sv_used 大量使用的连接表示连接池中可用资源的减少。
● maxwait：此列表示客户端等待连接的最大秒数。结合cl_waiting累积总数，可以根据连接可用性推断吞吐量的不足。此统计信息会不断更新，因此如果没有客户端等待，则显示为零。
通过show stats发送查询，PgBouncer 会为它充当代理的每个 PostgreSQL 数据库记录统计信息。每列是各种网络和时间指标的摘要。以下是这些列的详细摘要。
● total_xact_count：此列表示 PgBouncer 通过池指示的事务总数。由于事务比简单查询更昂贵，因此它们可能表示超额工作的比例更大。
● total_query_count：此处总结了SQL请求，这可以帮助确定每个事务的平均查询数。
● total_received：此列表示通过网络发送到 PgBouncer 的数据总量，用于此数据库和用户组合。为了有一个健康的池，需要说明高吞吐量，因此还必须检查下一列。
● total_sent：此列跟踪从 PgBouncer 发送到访问数据库的客户端的字节总数。此值与数据total_received处理太多大型查询任务，这降低了池连接吞吐量。也可能通过 PgBouncer 错误地访问数据库，配置错误批处理作业。
● total_query_time：这是 PgBouncer 在此池中与客户端通信所花费的时间（以微秒为单位）。这可能是一个特别难读的列，因为它是累积的，基于访问 PostgreSQL 连接的所有客户端。现在，我们建议忽略它。
● avg_req：此列显示自上次统计更新以来秒的请求数。与total_requests一样，这是PgBouncer 处理的事务数，不是查询数。
● avg_recv：此列详细介绍了自上次统计更新以来每个客户端发送到 PgBouncer 的平均字节数。
● avg_sent：此列指示自上次统计更新以来 PgBouncer已发送到每个客户端的平均字节数。
● avg_query_time：此列提供此池中所有连接的平均查询持续时间（微秒）。这是一个比total_query_time有用的指标，因为它实际上告诉我们池的平均吞吐量。例如，如果平均查询持续时间是 50 ms，那么可以预期每个 PostgreSQL 连接每天处理20 个客户端。这是正确调整连接池大小的重要数据。
● avg_wait_time：此列维护客户端会话在分配服务器后端之前等待的平均微秒数。在理想情况下，希望此值极低或接近零。较高的值可能表示池设置得过小，或者有太多的慢速查询限制会话重用。
在此方案中多次提到调整池大小。由于 Pgpool 充当多个数据库和用户组合的单个代理，因此在池需要更直接管理的情况下，实际上可以重写默认值。例如，假设将postgres数据库的条目更改为postgres=主机=pgha1 pool_size=5，则连接到postgres数据库的用户不能使用 5 个以上连接，即使默认值是每个池 50 个连接。在分析 PgBouncer 收集的池、客户端、服务器和其他统计信息时，请记住这一点。在达到理想状态之前，很可能需要进行几次调整，这种状态不会压倒PostgreSQL服务器，可以充分满足客户端需求。
PgBouncer的一个潜在问题是每个数据库映射只能有一个端点。也就是说，在配置中添加的每一行服务器的映射，只能代表一个 PostgreSQL 节点。在一般情况下，这样完全可以满足日常使用，但考虑到基于逻辑复制的双向复制技术，使具有多个主节点的高级集群场景成为一种有效选择。通常，当重新配置PgBouncer以将连接发送到新的数据库时，需要对配置文件进行修改，然后重启PgBouncer。
PgBouncer 1.9之后的版本进行了改进，使得在保留旧PostgreSQL服务连接的同时可以向新目标数据库发送新的数据。因为事务的完成不会因为切换数据库而中断，这让切换服务器变得十分平滑，从而有效提高多主系统的正常运行时间。
在默认情况下，PgBouncer使用一个配置文件维护所有允许连接的用户。已在前面的小节中解释过如何生成和维护这个文件。但是随着用户数量的增加，或者在某些情况下应用程序动态创建用户，这是非常不方便或有潜在安全风险的。
新的PgBouncer版本通过实现一个新的PgBouncer用户方位系统改进了这种身份验证过程，这意味着几乎不需要手动更新列表用户的数量。这不仅不再维护一个列表用户名和加密密码在一个潜在的不安全的位置，还可以使用PostgreSQLitself通过PgBouncer管理身份验证。
将启用新的PgBouncer身份验证系统，并探索它的一些其他功能。
在准备阶段，使用PgBouncer的高级用例，注意配置PgBouncer的安全方法，并将进一步修改pgbouncer.ini和userlist.txt文件。
本节将在 pgha-proxy 上实现一个 PgBouncer 服务器和一个标准 PostgreSQL server pgha1。
按照以下步骤改进PgBouncer身份验证过程。
第一步：postgres用户连接到pgha1，并连接到postgres数据库，执行下述SQL语句。
第二步：在PgBouncer使用的任何其他数据库中重复上述步骤，以便被允许连接。
第三步：删除/etc/pgbouncer/userlist.txt文件中除pgbouncer用户外的条目。
第四步：使用postgres系统用户打开/etc/pgbouncer/pgbouncer.ini文件。
第五步：在标记为[pgbouncer]的部分中，定位这些条目并将其更改如下。
第六步：postgres用户作为一个有root权限的用户，在命令行中执行以下命令，重新加载PgBouncer服务。
需要为PgBouncer创建一个安全的方法来检查密码。因为PgBouncer知道PostgreSQL如何存储密码，因此它可以将客户端身份验证请求中的任何值与数据库中的加密信息进行比较。通常利用get_auth（）函数，通过从PostgreSQL本身存储的地方检索请求的用户和加密密码实现加密信息的比较。
第七步：修改 userlist.txt 文件并删除 PgBouncer 用户本身的加密密码行外的所有条目。PgBouncer将使用刚刚创建的函数对该文件中未列出的任何用户进行身份验证。通过删除所有其他用户，它只能在本地验证自己，而所有其他用户必须由PostgreSQL验证。
第八步：启用用户验证功能。首先将auth_user设置为pgbouncer，因为这是希望用于身份验证目的的用户。既可以指定存在于userlist.txt文件中的任何用户，也可以执行特殊函数。然后，设置实际调用PgBouncer的查询。将get_auth（）函数转换为auth_query。这实际上可以是对 PostgreSQL 的任何查询，只要它总是返回两列，其中第一列包含用户名，第二列包含加密密码。PgBouncer将使用这些结果来验证客户端输入的任何密码。
第九步：告诉PgBouncer重新加载配置文件，以包含我们的修改。完成此操作后，应该能够作为之前在数据库实例中创建的任何用户进行身份验证，即使用户没有在userlist.txt文件中指定。
让功能更安全也许是可取的。为了避免允许超级用户通过 PgBouncer 访问，或者只允许用户分配到特定的组，所以通过这样修改验证查询来实现这两种功能。
不仅明确禁止超级用户通过代理连接，而且现在用户在被允许连接之前还需要一个补充授权声明。如果想让一个新用户 recent_hire 通过这个特定的代理连接，则可以这样管理用户：
如果该用户转移到公司的其他部分，或者不再访问应用程序的这个部分，则可以轻松撤销访问权限。
