PG连接中间件PgBouncer快速入门

一、介绍

PgBouncer主要目标是为PostgreSQL提供轻量级的连接池
PgBouncer支持在不停机的情况下修改配置（大部分）
PgBouncer支持在不断开客户端连接的情况下升级或重启
PgBouncer采用libevent进行socket通信，效率很高
PgBouncer采用轻量级架构，每个连接仅仅消耗2KB内存

二、安装

GNU Make 3.81+
libevent 2.0
(optional) OpenSSL 1.0.1 for TLS support
(optional) c-ares as alternative to libevent’s evdns

```Plain Text ./configure --prefix=/usr/local/pgbouncer --with-libevent=libevent-prefix make make install

启动

```Plain Text
pgbouncer -d usr/local/pgbouncer/conf/pgbouncer.ini

停止

``Plain Text kill -9cat home/postgres/pgbouncer/pgbouncer.pid`

三、日常使用

> PgBouncer采用了基于Console的类似PG数据库的管理方式，当使用psql命令行连接到PgBouncer后就好像连接到虚拟的PG数据库，在PgBouncer上执行命令就像是在PG上执行SQL命令，这样PG的管理员就可以通过类似PG的方式监控和管理PgBouncer，具体操作如下：

```Plain Text
[postgres@node3 pgbouncer]$ psql -p 6432 -d pgbouncer
psql (9.6.3， server 1.7.2/bouncer)
Type "help" for help.
pgbouncer=#

3.1 show help;

查看命令的帮助信息，命令都必须以分号结尾

### 3.2 show config;
> 显示所有配置项的当前值，一个配置一行，字段如下：

> * key：配置项名称
> * value：配置值
> * changeable：yes 或 no，显示这个配置项是否可以在运行时修改，如果为no，那么这个配置项只能在启动时修改

### 3.3 show pools;
> 显示连接池的状态

> * database：数据库名
> * user：用户名
> * cl\_active：当前 active (活跃)的客户端连接的个数
> * cl\_waiting：当前 waiting (等待)的客户端连接个数
> * sv\_active：当前 active (活跃)的服务器连接个数
> * sv\_idle：当前 idle (空闲) 的服务器连接个数
> * sv\_used：当前 used (在使用)的服务器连接个数
> * sv\_tested：当前 tested (测试过)的服务器连接个数
> * sv\_login：当前 login (登录)到 PostgreSQL 服务器的个数
> * maxwait：队列中第一个(最老的那个)客户端等待的时间长度，单位是秒。如果这个数值开始上升，那么就意味着当前的连接池中的服务器处理请求的速度不够快。原因可能是服务器过载，也可能是 pool\_size 太小
> * pool\_mode：连接池模式

### 3.4 show stats;
> 显示每个PG数据库的统计信息

> * database：统计是根据每个数据库分比例的
> * total\_requests：连接池处理的SQL请求的总数
> * total\_received：接收到的网络流量的总字节数
> * total\_sent：发出的网络流量的总字节数
> * total\_query\_time：活跃在与数据库上面的时间开销总数，单位是微秒
> * avg\_req：在最后一次统计过程中的每秒平均请求数
> * avg\_recv：每秒(从客户端)接收到的平均数据量
> * avg\_sent：每秒发送(给客户端)的平均数据量
> * avg\_query：平均的查询时间，单位是微秒

### 3.5 show servers;
> 显示与PgBouncer建立连接的PG数据库信息

> * type：S，表示服务器
> * user：PgBouncer用于连接服务器的用户名
> * database：服务器端的数据库名
> * state：PgBouncer 服务器连接的状态 ，active、used或者idle之一
> * addr：PostgreSQL服务器的IP地址
> * port：PostgreSQL服务器的端口
> * local\_addr：本地机器上的发起连接地址
> * local\_port：本地机器上的发起连接端口
> * connect\_time：连接建立的时间
> * request\_time：请求发出的时间
> * ptr：这个连接的内部对象地址，用做唯一 ID
> * link：这个服务器对应的客户端地址

### 3.6    show clients;
> 显示客户端及客户端连接状态

> * type：C，表示客户端
> * user：客户端连接的用户
> * database：数据库名
> * state：客户端连接的状态 ，active、used、waiting或者idle之一
> * addr：客户端的 IP 地址
> * port：客户端的端口
> * local\_addr：PgBouncer实例的IP地址
> * local\_port：PgBouncer实例的端口
> * connect\_time：客户端连接的最新时间戳
> * request\_time：客户端请求的最新时间戳
> * ptr：客户端连接内部对象的地址，用做唯一 ID
> * link：客户端连接对应的服务器的地址

### 3.7 show lists;
> 显示连接池的计数信息

> * databases：数据库的个数
> * users：用户的个数
> * pools：连接池的个数
> * free\_clients：空闲客户端的个数
> * used\_clients：已用的客户端的个数
> * login\_clients：处于登录状态的客户端个数
> * free\_servers：空闲服务器个数
> * used\_servers：已用服务器个数

### 3.8 show databases;
> 列出PgBouncer数据库别名及相关数据库

> * name：已配置的数据库名字记录
> * host：PgBouncer 连接到的主机名
> * port：PgBouncer 连接到的端口号
> * database：PgBouncer 实际连接的数据库名
> * force\_user：当用户是连接字串的一部分的时候，在 PgBouncer 和 PostgreSQL 之间的连接会强制成给出的用户，不管 client user 是什么
> * pool\_size：最大的服务器端连接数目

### 3.9 show fds;
> 显示正在使用的 fd 列表，如果连接的用户是 “pgbouncer”，那么通过 unix socket 建立的连接，就会和运行的进程拥有相同的 UID，这样 fd 就会被传递给连接，该机制用于在线重启

> * fd：文件描述符的数字值
> * task：pooler，client 或 server 之一
> * user：使用该 fd 的用户
> * database：使用该 fd 的的数据库
> * addr：使用该 fd 的连接的 IP 地址，如果使用的是 unix socket，就是 unix
> * port：使用该 fd 的连接的端口号
> * cancel：这个连接的取消键字
> * link：对应的服务器/客户端的 fd如果为 idle (空闲)则为 NULL

### 3.10 DISABLE db;
> 拒绝指定数据库上所有新客户端连接

### 3.11 ENALBLE db;
> 准许之前DISABLE命令之后的新客户端连接

### 3.12 PAUSE \[db\];
> 尝试从所有服务器断开连接(等待query完成)，在所有query完成之前，此命令不会返回，在数据库重新启动时使用。如果给出了数据库名字则只对该数据库有用

### 3.13 KILL db;
> 立即删除给定数据库上所有客户端以及数据库连接

### 3.14 SUPEND;
> 刷新所有socket缓存，并且停止监听，在缓存flush之前此命令不会有任何返回

### 3.15 RESUME \[db\];
> 从之前PAUSE或者SUPEND命令恢复之前状态

### 3.16 SHUTDOWN;
> PgBouncer进程退出

### 3.17 RELOAD;
> 重新加载其配置文件并更新可更改的配置

四、配置文件

4.1 **databases配置项**

> databases配置比较简单，每行由key=value对组成，其中key为对外数据库名称，value由多个以空格分割的key=value对的连接字符串及相关参数对组成，实例:

```Plain Text
postgres = host=127.0.0.1 dbname=postgres port=5432 user=postgres password=postgres client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'

连接串的各个参数说明:

dbname: 后端数据库名称

host: 后端数据库的主机名或者IP地址

port: 后端数据库监听端口

user: 后端数据库的用户名

password: 后端数据库的密码

pool_size: 连接池的大小，如果没有设置，连接池大小将使用 $pgbouncer$ 中的default_pool_size的值

connect_query: 在连接使用之前执行一个SQL语句用于探测连接是否正常。如果执行该语句出错，则选择另外一个连接

max_db_connections: 数据库提供的最大连接数（即数据库中的所有池都不会有这么多的服务器连接）

client_encoding: 客户端字符集编码

datestyle: 日志类型参数

timezone: 时区注意:如果在连接串中没有指定user和password，那么PgBouncer将使用给客户端连接PgBouncer时的用户名和密码来连接后端数据库，并为每个不同的用户建立一个连接池;如果连接中指定了user和password，PgBouncer将使用这里设置的用户名和密码来连接后端数据库，这样对使用这项配置的数据库来说，就只有一个连接池了

4.2 PgBouncer配置项

4.2.1 通用配置项

4.2.1.1 logfile

指定日志文件

4.2.1.2 pidfile

指定pidfile，文件中记录了PgBouncer的进程ID，如果加-d启动则此项是必须配置，如果未配置启动报错如下

```Plain Text 2017-07-25 12:26:27.875 1672 FATAL @src/main.c:517 in function go_daemon(): daemon needs pidfile configured

```

4.2.1.3 listen_addr

监听的IP地址

4.2.1.4 listen_port

监听的IP端口

4.2.1.5 unix_socket_dir

指定unix socket的文件目录，默认是/tmp

4.2.1.6 unix_socket_mode

指定unix socket文件属性，默认 0777

4.2.1.7 unix_socket_group

指定unix socket文件的组，默认未设置

4.2.1.8 admin_users

指定启动登录console的用户名

4.2.1.9 auth_file

指定连接PgBouncer的用户名和密码认证文件

4.2.1.10 auth_type

认证方法，可设置为any， trust， plain， crypt， md5

4.2.1.11 pool_mode

连接池模式，可为session， transaction， statement

4.2.1.12 max_client_conn

准许连接到PgBouncer上最大客户端数

4.2.1.13 default_pool_size

连接池与数据库默认大小，不同的用户或者数据库会有不同的连接池

4.2.1.14 min_pool_size

连接池最小连接，即每个连接池和数据库保持的连接数

4.2.1.15 reserve_pool_size

连接池保留连接数

4.2.1.16 reserve_pool_timeout

保留连接的超时时间

4.2.1.17 server_round_robin

负载均衡模式是否为 round robin，默认是关闭，采用LIFO(后进先出)

4.2.1.18 ignore_startup_parameters

默认PgBouncer会跟踪一些数据库参数，如client_encoding，datestyle，timezone，standard_conforming_strings，application_name等，PgBouncer能检测出这几个参数的变化并与客户端保持一致，所以默认情况下设置其他参数会导致PgBouncer抛出错误。通过该配置项指定一些数据库参数，PgBouncer就可以忽略对这些参数的检查，不同参数之间用逗号隔开

4.2.1.19 disable_pqexec

是否禁止简单查询协议，默认为0，表示禁用。简单查询协议准许一个请求发送多条SQL，容易导致SQL注入攻击

4.2.2 日志配置项

4.2.2.1 syslog

是否打开syslog，Windows下没有syslog，则使用eventlog。默认为0，表示不打开

4.2.2.2 syslog_facility

可配置为auth， authpriv， daemon， user， local0-7默认是daemon

4.2.2.3 syslog_ident

以什么名称发送日志到syslog，默认是pgbouncer

4.2.2.4 log_connections

是否记录连接成功的日志，默认值是1，记录

4.2.2.5 log_disconnections

是否记录断开连接的日志，默认值是1，记录

4.2.2.6 log_pooler_errors

连接池发往客户端的错误是否记录在日志中，默认值是1，记录

4.2.2.7 stats_period

将汇总的统计信息写入日志的时间周期，默认60

4.2.2.8 verbose

日志记录的详细程度，在启动命令行中 -v -v 与verbose=2是同样的含义

4.2.3 console访问控制配置项

4.2.3.1 admin_users

允许在console执行管理命令的用户列表，多个用户之间以逗号隔开。当设置auth_mod=any时，此配置可忽略，默认为空

4.2.3.2 stats_users

允许连接到console上查看连接池只读信息的用户列表。这些用户可以执行除show fds命令之外的其他show命令

4.2.4 健康检查和超时配置项

4.2.4.1 server_reset_query

当一个后端数据库连接会话被某一个客户端使用时，它的属性可能会改变，所以当该数据库连接被另一个客户端使用时，就可能会产生问题，因此在数据库连接使用后重新放回连接池时，需要对这个连接的属性进行复位。默认设置为DISCARD ALL。需要注意在连接池为事务模式时，此配置项应该为空，因为在事务模式下，客户端不应该设置连接会话的属性

4.2.4.2 server_check_delay

空闲连接需要多长时间进行一次健康检查，查看其是否可用.如果设置为0则立即检查，默认设置为30s

4.2.4.3 server_check_query

健康检查的SQL，如果为空则禁止健康检查。默认为SELECT 1

4.2.4.4 server_lifetime

连接存活时间。当一个连接存活时间超过此值时，就会被强制关闭，并创建建一个新连接。默认为3600s，模板配置文件中是1200s与默认值有冲突。如果设置为0，表示此连接只是用一次，使用后就关闭

4.2.4.5 server_idle_timeout

连接池中连接的idle时间，超过此时间，连接会被关闭，默认值为600s

4.2.4.6 server_connect_timeout

到后端数据库的login时间超过此值后，连接就会被关闭，默认为15s

4.2.4.7 server_login_retry

创建到后端数据库连接失败后，等待多长时间重试，默认值为15s

4.2.4.8 client_login_timeout

客户端与PgBouncer建立连接后，如果无法在这段时间内完成登录，那么连接将会被断开，默认为60s

4.2.4.9 autodb_idle_timeout

如果自动创建的数据库池已经使用了这个时间值，那么他们会被释放，不好的方面是相应的统计数据也会丢掉，默认3600s

4.2.4.10 suspend_timeout

在SUSPEND命令暂停或者用-R重新启动期间等待缓冲区刷新的秒数，如果在此时间内flush不成功，连接将被丢弃

4.2.5 关键的超时配置项

为了防止一些未知原因导致系统hang住，而设置的超时配置

4.2.5.1 query_timeout

运行时间超过该时间值的SQL会被终止。此值应该设置得比SQL的实际运行时间长一些，也应该比数据库的statement_timeout参数配置的值大一些。这个参数主要是为了便于应付一些未知网络问题。设置此值可防止查询被长时间hang住。默认值为0，表示禁止此功能

4.2.5.2 query_wait_timeout

一个请求在队列中等待被执行的最长时间，如果超过此时间还没有被分配到连接，则此客户端连接将会被断开。这主要为了防止数据库hang住后，客户端到PgBouncer的连接也一直被hang住，默认值为120s，如果设置为0则客户端无限排队等待

4.2.5.3 client_idle_timeout

如果客户端空闲该时间值后，一直不发送命令，则断开与此客户端的连接。这一般是为了防止客户端上的TCP连接实际上因为网络问题关闭，但是PgBouncer上相应的连接没有检测到客户端已经不存在而一直存在.默认值为0，表示禁止此功能

4.2.5.4 idle_transaction_timeout

客户端启动事务后，超过此时间值还不提交事务，则关闭这个客户端连接，防止客户端消耗PgBouncer及数据库的资源，默认为0，表示禁止此功能

4.2.6 底层网络配置项

4.2.6.1 pkt_buf

用于指定网络包的内部缓冲区大小，该值会影响发出的TCP包大小即内存使用大小。实际的libpq数据包可以比这个大，所以没有必要设置的太大。默认值为4096，一般保持这个值即可

4.2.6.2 max_packet_size

通过PgBouncer的最大包大小，这个包可以是一个SQL，也可以是一个SQL的返回结果集，有可能这个结果集非常大，默认为2147483647

4.2.6.3 listen_backlog

TCP监听函数listen的Backlog参数，默认是128，通过man 2 listen可查看backlog的含义backlog参数定义sockfd的等待连接队列可能增长的最大长度。如果该队列已满时连接请求到达，则客户端可能会收到带有ECONNREFUSED指示的错误；如果底层协议支持重传，则可能会忽略该请求，这样连接中的后续重新尝试才能成功

4.2.6.4 sbuf_loopcnt

在事件循环的处理过程中，每个连接处理多少数据包后切换到处理下一个连接的数据包。如果没有这个限制，就可能会出现在事件循环过程长时间处理某个连接的数据包，这样会导致其他连接的数据包得不到及时的处理。如果设置为0表示不限制，默认值是5

4.2.6.5 tcp_defer_accept

此选项值的详细说明从linux下 man 7 tcp中获取。在linux下次默认值为45，其他平台为0

4.2.6.6 tcp_socket_buffer

默认未设置

4.2.6.7 tcp_keepalive

是否以操作系统的默认值启用基本的keepalive设置。在linux操作系统下探活的相关默认设置为net.ipv4.tcp_keepalive_time = 7200， net.ipv4.tcp_keepalive_intvl = 75， net.ipv4.tcp_keepalive_probes = 9这些值默认偏大，一般根据实际情况调整