PostgreSQL学习随笔18 PgBouncer连接池安装、配置

tar zxvf pgbouncer-1.15.0.tar.gz
yum install libevent -y
yum install libevent-devel -y

以下使用root用户编译安装：
yum -y install systemd-devel-*
./configure --prefix=/usr/local --with-systemd
make 
make install

编译安装 默认PgBouncer是安装到 usr/local/bin 目录下的。
ll usr/local/bin/pgbounce*

 配置理解
使用系统自带工具安装的PgBouncer的配置文件路径是/etc/pgbouncer/pgbouncer.ini
源码安装后，在/usr/local/share/doc/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                         # 默认池大小，表示建立多少
pool_size = 20        #配置连接池的大小，如果没有配置此项，连接池的大小将使用default_pool_size配置项的值。

默认情况下不配置任何数据库信息，从上面还可以看到，配置主要分为两部分：
第一部分是[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

而这个认证文件默认情况下是没有的，因此需要手动生成。

生成这个认证文件有两种方式，如下：

1）SQL语句生成认证文件userlist.txt 
之前我们说过，用户密码默认是保存在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 '/home/postgres/auth_file';

我们打开这个auth_file文件，内容如下：
dbuser  md5baa6c789c3728a1a449b82005eb54a19
postgres        \N

然后保留可以连接数据库的用户账号和加密后的密码，将这个文件转移到上面配置项指定的位置。并且文件名称要和上面变量里定义的文件名一致，否则会提示找不到这个文件。
最后还要注意的一点是，默认导出的文件里用户名和密码的格式pgbouncer无法识别，需要用双引号引起来才能正确识别

如下所示：
"dbuser"  "md5baa6c789c3728a1a449b82005eb54a19"

手动构造文件
vi opt/userlist.txt
"postgres" "md5a3556571e93b0d20722ba62be61e8c2d"

配置实践
vi opt/pgbouncer.ini
[databases]
postgres=host=127.0.0.1 port=5432 user=postgres password=123456 connect_query='SELECT 1'
[pgbouncer]
logfile = opt/pgbouncer.log
pidfile = opt/pgbouncer.pid
listen_port = 1926
listen_addr = 127.0.0.1
auth_type = md5
auth_file =/opt/userlist.txt
admin_users = postgres
max_client_conn = 50
default_pool_size = 20

手动创建用户列表，生产环境建议限制postgres链接
vi home/pgbouncer/userlist.txt
"dbuser" "md5dcef1df7dcdff8faa6c25fcc48851de1"
"postgres" "md5648d63a3302b63ef6cacb6400725aad4"

启动
当用户文件配置好以后，就可以启动pgbouncer来使用了。

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

useradd pgbouncer
su - pgbouncer

启动命令
pgbouncer -d -v opt/pgbouncer.ini

重启命令
pgbouncer -R opt/pgbouncer.ini –v

cd opt
chown pgbouncer:pgbouncer pgbouncer.ini userlist.txt

查看进程
ps -ef |grep pgbouncer


查看端口状态
netstat -lanp|grep 1926


netstat -lanp|grep 1926
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 192.168.137.88:2926     0.0.0.0:*               LISTEN      31369/pgbouncer     
unix  2      [ ACC ]     STREAM     LISTENING     145671   31369/pgbouncer      /tmp/.s.PGSQL.1926
-d 表示是以后后台进程的方式运行，后面跟的是配置文件的路径。
启动完成，pgbouncer默认监听1926端口。


登录数据库
pgbouncer对外提供了一个虚拟数据库pgbouncer，之所以称为虚拟数据库，是因为它可以提供像PostgreSQL那样的数据库操作界面，但是这个数据库却并不是真实存在的，而是pgbouncer虚拟出来的一个命令行界面。如果修改了一些配置参数，可以不用重启 pgbouncer 而是 reload 使其生效。


注意：只有在配置了参数 admin_users 或者 stats_users才会连接到控制台。


postgres@pgexp1-> psql -h 127.0.0.1 -p 1926 -U postgres -d pgbouncer                
Password for user postgres: 
psql (12.10, server 1.15.0/bouncer)
Type "help" for help.


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


SHOW


如果修改了一些配置参数，可以不用重启 pgbouncer 而是 reload 使其生效
注意：只有在配置了参数 admin_users 或者 stats_users才会连接到控制台。


登录以后可以使用：
查看所有的帮助命令信息：show help;      
用来查看客户端连接信息：show clients ;   
 用来查看连接池信息：show pools; 


登录访问目标数据库
psql -h 127.0.0.1 -p 1926 -U postgres postgres


在装有psql的机器上使用命令：
psql -h 127.0.0.1 -p 1926 -U <用户名> <数据库名>(如数据库：testdb1、testdb2)


即通过pgbouncer登录到主机为127.0.0.1的数据库为postgres的机器上
pgbouncer的配置文件有映射关系，如上例所示。
1.4.1.7  停止
目前pgbouncer还没有自主停止的脚本或者命令，只能通过kill命令来停止。


格式是：
kill `cat /opt/pgbouncer.pid`
或cat /opt/pgbouncer.pid | xargs kill -9
1.4.1.8  日志信息
tail -f /opt/pgbouncer.log
……
2022-01-29 20:01:00.922 CST [31369] DEBUG pktbuf_send_func(11, 4, 0x225e4e0)
2022-01-29 20:01:00.922 CST [31369] DEBUG pktbuf_free(0x225e4e0)
2022-01-29 20:01:49.770 CST [31369] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-01-29 20:02:49.769 CST [31369] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-01-29 20:03:49.770 CST [31369] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
2022-01-29 20:04:49.769 CST [31369] LOG stats: 0 xacts/s, 0 queries/s, in 0 B/s, out 0 B/s, xact 0 us, query 0 us, wait 0 us
……


in b/s：这个比较好读懂，每秒读入字节数。
out b/s：和in b/s一样，表示每秒读出的字节数。
query us：平均每个查询话费的时间，单位微秒。us应该是used的缩写。
wait time : 等待耗时 微秒
xacts/s：每秒多少个事务操作
queries/s：每秒多少次请求数
xact  us：每个事务耗时多少微秒


复制