pgBouncer管理和使用案例

[转]PostgreSQL数据库连接池Pgbouncer的搭建

最近有些项目我们是采用PostgreSQL进行后端数据库，由于要对客户端的连接使用类型和连接数进行控制，因此我们采用PgBouncer来进行实施。

PgBouncer相关的基本文档pgsqldb中文站已经有不少文档，这里我就不再作过多的说明，我将集中对其中的几个要点和我实践中遇到的问题和大家分享一下。

OK，肺话也不多少，开始正题吧;D

 

 

 

PGBouncer介绍

• 什么是PgBouncer?

PgBouncer是一个轻量级的数据库连接池。download

• PgBouncer的作用

1. PgBouncer可以在后端数据库和前端应用间建立连接的桥梁，由PgBouncer去处理和后端数据库的连接关系。

2. 对客户端连接进行限制，预防过多或者恶意的连接请求。

• PgBouncer的特点

1. 内存消耗低(默认为2k/连接)，因为Bouncer不需要每次都接受完整的数据包

2. 可以把不同的数据库连接到一个机器上，而对客户端保持透明

3. 支持在线的重新配置而无须重启

4. 仅支持V3协议，因此后端版本须>=7.4

 

 

开始使用PgBouncer

• 基本ini文件配置（假设我们创建了新的ini文件/usr/local/pgsql/conf/pgbouncer.ini）

\1. 添加目标数据库的连接字符串，这个表示PgBouncer将会在哪些后端数据库中建立连接，比如：

 template1 = host=127.0.0.1 port=5432 dbname=template1

\2. 设定PgBouncer的监听端口, port=5555，默认为6000

\3. 创建用户列表文件并添加用户信息，此用户为允许客户端使用的连接用户名，比如：

 A. zhaoyi@zhaoyi-laptop:[~]$ echo "user" "password" > /usr/local/pgsql/user.txt
 B. 在ini设定：auth_file = /usr/local/pgsql/user.txt

\4. 创建admin用户，在配置中添加：admin_users = user，用户可以使用此用户名连接pgbouncer并查看运行状况等，注意：此用户必须为user.txt文件中已经存在的用户。

• 启动并测试连接/查看运行状况

\1. 启动:pgbouncer -d pgbouncer.ini

\2. 测试连接：psql -h 127.0.0.1 -p 6000 -U user template1

\3. 通过admin用户连接pgbouncer查看配置：

 psql -h 127.0.0.1 -p 6000 -U user pgbouncer
 pgbouncer=# show config;

\3. 通过admin用户连接pgbouncer查看运行情况：

 pgbouncer=# show stats;
 pgbouncer=# show lists; 
 pgbouncer=# show pools;
 pgbouncer=# show databases;
 #其余运行参数可以通过如下命令查看
 pgbouncer=# show help;

\4. 参数修改：如果修改了ini文件中相关参数，需要通过命令告知bouncer重新读取配置内容：

 pgbouncer=# reload;
   

 

PgBouncer连接类型

1. 会话池连接：即以一个session为生命期，当客户端断开的时候，才断开并释放连接回连接池

2. 事务连接：服务器连接只是在一个事务的过程里赋予客户端的

3. 语句连接：在每个查询结束之后，服务器的连接都会立即放回连接池。在这种模式下将不允许多语句的事务。否则，会给出如下提示，比如

 test=# begin;
  ERROR:  Long transactions not allowed
    server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
  The connection to the server was lost. Attempting reset: Succeeded.
 ​

 

客户端连接方式

这里我简单说明一下客户端不同连接方式时服务器端的处理方式，以perl中为例：

 $dbh = DBI -> connect('dbi:Pg:dbname=test;host=127.0.0.1;port=6000', 'postgres', ' ' ) or die $DBI::errstr;

1. 客户端为AutoCommit时，transaction类型 = statements类型

2. pool类型为transaction类型，如果客户端不为AutoCommit时，必须显示Commit，否则连接不释放

3. pool类型为statements类型时，连接不支持长语句，因此客户端必须AutoCommit，否则依然会报错

AutoCommit配置为：

 {'AutoCommit' => 0}
 ​

 

连接参数配置及客户端状态

• max_client_conn

1. 这个为允许客户端连接的最大数目

2. 你可以通过show clients进行查看，当客户段连接大于此设置时，客户端会得到类似如下信息：

  psql: ERROR:  no more connections allowed

• default_pool_size

1. 这个为当前连接池中允许的最大处理连接

2. 比如default_pool_size=3，如果在连接池没有空余连接的情况下，如果已经连接的客户端尝试通过PgBouncer向后端执行查询请求，则客户端会一直处理等待状态，当有空余的连接释放时，比如其他的客户端断开连接时，PgBouncer才会把此请求放如连接池中，向后端提出服务请求，后端收到请求后，才执行请求返回结果，比如

  template1=# select * from test;
  --no result

以上测试都是基于 pgbouncer version 1.1.1 + psql (PostgreSQL) 8.3.3

 

 

相关文档

• Config file documentation

• Usage document

• 用户手册

• 管理员手册

 

gBouncer安装方法

1. 在Linux发行版中，包含已经编译好的PgBouncer，可以直接安装。RHEL/CentOS平台直接使用yum命令来安装。Debian/Ubuntu平台下，直接使用apt-get命令安装。

2. 在官网http://pgfoundry.org上下载源码编译安装，需要先安装libevent依赖。

PgBouncer的简单使用

1. 简单配置 简单介绍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用户。所有的用户名和密码都会用双引号引起来，比手动备份要方便的多。这里唯一麻烦的就是脚本后面的连接字符串。

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

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

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

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

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

 

 psql -p 6432  pgbouncer

登录以后可以使用show help命令查看所有的帮助命令信息，常用的两个命令是：

1. show clients 用来查看客户端连接信息

2. show pools 用来查看连接池信息

这篇文章主要讲解了pgbouncer的基本介绍，基本配置以及简单的使用。下一篇文章继续说pgbouncer的配置文件，熟悉它的高级配置。

 

作者：shark_tear 链接：https://www.jianshu.com/p/8650c3298f7f 来源：简书 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

相关参考： http://pgfoundry.org/projects/pgbouncer/