PostgreSQL系列- 6 - PgBouncer连接池
目录
1、为什么要使用连接池
1.1 能够缓存和PostgreSQL的连接，当有连接请求进来的时候，直接分配空闲进程，而不需要PostgreSQL fork出新进程来建立连接，以节省创建新进程，创建连接的资源消耗。
1.2 能够有效提高连接的利用率，避免过多的无效连接，导致数据库消耗资源过大，CPU占用过高。
1.3 对客户端连接进行限制，预防过多或恶意的连接请求。
2、软件安装
yum install pgbouncer
安装完成以后，修改配置：
3、连接池的配置文件
PgBouncer连接池需要两个配置文件， pgbouncer.ini 和 auth_file文件。
3.1 pgbouncer.ini
文件分为三部分内容：
a. 需要连接到的数据库信息，包括数据库主机、端口、数据库名称等
b. 连接池信息，包括连接池模式、监听端口、监听主机、用户连接认证类型、日志文件、管理用户等。（
支持三种连接池模型：
session，会话级连接，连接生命周期里，连接池分配一个数据库连接，客户端断开连接时，连接放回连接池。
transaction， 事务级连接，客户端的每个事务结束时，数据库连接就会重新释放回连接池，在执行一个事务时，就需要从连接池重新获得一个连接。
statement，语句级别连接 ，执行完一个SQL语句时，连接就会释放回连接池，再次执行一个SQL语句时，需要重新从连接池里获取连接，这种模式意味客户端需要强制“autocommit”模式。
）
c. 用户认证信信息，指定用户认证文件的名称和用户信息加密方式
详细配置信息
使用系统自带工具安装的PgBouncer的配置文件路径是/etc/pgbouncer/pgbouncer.ini，默认的配置和含义如下：
[databases] 配置 ，添加需要代理的数据库链接
代理的数据库名称 = host=127.0.0.1 port=5432 user=db_admin_user dbname=test_db password=123456 connect_query='SELECT 1' pool_size=50
如：
dctadb = host=master port=5432 dbname=dbname dctsdb = host=master port=5432 dbname=dbname user=xx_xx
[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
3.2 auth_file
修改/etc/pgbouncer/userlist.txt
首先去数据库获得数据库相关用户的帐号以及密码（md5值）
postgres=# select usename, passwd from pg_shadow order by 1; vim /etc/pgbouncer/userlist.txt“dbuser” “md5baa6c789c82005eb54a19”注意，双引号是不可少的，里面分别为帐号和密码。
3.3检查pg数据库是否开启md5认证
检查pg_hba.conf 然后看到：
这是默认的配置，没有开启md5认证，刚才我们的认证都是md5的，请在：
将下面的该一改
host all all 127.0.0.1/32 ident
改为：
host all all 127.0.0.1/32 md5
3.4 示例
pgbouncer.ini示例
[databases] dctadb = host=master port=5432 dbname=dbname dctsdb = host=master port=5432 dbname=dbname user=xx_xx [pgbouncer] pool_mode = session listen_port = 7000 listen_addr = smaster auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt logfile = pgbouncer.log pidfile = pgbouncer.pid admin_users = dbuser max_client_conn = 100
auth_file 示例
"xx_xx" "md50159db6e66x35f2ffd59" "dbuser" "123456"
4、PgBouncer启动和管理
4.1 启动
$ $GPHOME/bin/pgbouncer -d pgbouncer.ini
#热加载
$ pgbouncer -R -d /etc/pgbouncer/pgbouncer.ini
4.2 连接测试
连接时指定的是PgBouncer的服务器和监听端口，数据库要使用pgbouncer中配置的数据库名称
$ psql -h smaster -p 7000 -d dbname -U xx_xx Password for user xx_xx: psql (9.4.24) Type "help" for help. dctsdb=> \q
4.3 管理
使用配置中指定的管理用户，登录虚拟的pgbouncer数据库，可以进行一系列管理工作
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 # 重载配置文件 pgbouncer=# RELOAD; RELOAD pgbouncer=# #查看当前里连接池里的连接 pgbouncer=# show CLIENTS;