PgBouncer安装

最新推荐文章于 2024-05-06 17:15:42 发布

阿莲-Aliana

最新推荐文章于 2024-05-06 17:15:42 发布

阅读量302

点赞数 1

文章标签：数据库服务器 postgresql

原文链接：https://pgfans.cn/a/2133

版权

PgBouncer 是一个轻量级的针对PostgreSQL的数据库连接池工具。数据库连接池在PgBouncer 中包括会话连接池、事务连接池、语句连接池三种方式。

连接方式	说明
会话池	当客户端连接时，服务器连接将在整个持续时间内分配给它保持联系。当客户端断开连接时，服务器连接将放回池中。此模式支持所有 PostgeSQL功能。
事务池	服务器连接仅在事务期间分配给客户端。当PgBouncer注意到事务结束或超过，服务器将被放回池中。这种模式破坏了PostgreSQL的一些基于会话的功能。仅当应用程序不配合时，才能使用它使用中断的功能。
语句池	最激进的方法。这是具有 twist：不允许多语句交易。这是旨在在客户端上强制实施“自动提交”模式，主要是针对PL/Proxy.。

PgBouncer 的特性：

内存要求低（默认情况下每个连接 2 kB）。这是因为 PgBouncer不需要一次看到完整的数据包。
PgBouncer不绑定到一个后端服务器。目标数据库可以驻留在不同的主机上。
支持大多数设置的在线重新配置。
支持在线重启/升级，无需断开客户端连接。

安装环境

系统版本： Scientific Linux release 7.9 (Nitrogen)
数据库版本： PostgreSQL 15.1
pgbouncer： PgBouncer 1.18.0

下载软件及依赖软件

PgBouncer 需要安装一下依赖：

GNU Make 3.81+
Libevent 2.0+
pkg-config
OpenSSL 1.0.1+

如果yum源可以直接安装的可以直接安装，没有RPM包的可以通过以下网址下载安装
下载pgbouncer
https://www.pgbouncer.org/downloads
下载libevent
https://libevent.org

安装依赖

[root@pg15_rh7_132 ~]# yum -y install make  libevent openssl
[root@pg15_rh7_132 ]# tar -xvf libevent-2.1.12-stable.tar.gz
[root@pg15_rh7_132 ]# cd libevent-2.1.12-stable/
[root@pg15_rh7_132 libevent-2.1.12-stable]# ./configure --prefix=/home/postgres/libevent
[root@pg15_rh7_132 libevent-2.1.12-stable]# make && make install

授权

[root@pg15_rh7_132 ]# chown -R postgres:postgres /home/postgres/

创建软连接

[root@pg15_rh7_132 ]#  ln -s /home/postgres/libevent/lib/libevent-2.1.so.7 /usr/lib64/libevent-2.1.so.7

设置环境变量

[postgres@pg15_rh7_132 ~]$ cat .bashrc
export PGDATA=/pg15data
export PGHOME=/usr/local/pg15/
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGHOME/lib
export PATH=$PATH:$PGHOME/bin/
export PGPORT=5432
export PKG_CONFIG_PATH=/home/postgres/libevent/lib/pkgconfig
[postgres@pg15_rh7_132 ~]$ source .bashrc

上传pgbouncer并解压安装

[root@pg15_rh7_132 ~]# tar -xvf pgbouncer-1.18.0.tar.gz -C /home/postgres/
[root@pg15_rh7_132 ~]# cd /home/postgres/pgbouncer-1.18.0/
[root@pg15_rh7_132 pgbouncer-1.18.0]# ./configure --prefix=/home/postgres/pgbouncer
[root@pg15_rh7_132 pgbouncer-1.18.0]# make && make install

再次授权

[root@pg15_rh7_132 ]# chown -R postgres:postgres /home/postgres/

查看版本

[root@pg15_rh7_132 ~]# pgbouncer -V
PgBouncer 1.18.0
libevent 2.0.21-stable
adns: evdns2
tls: OpenSSL 1.0.2k-fips  26 Jan 2017

如果这里没有pgbouncer命令，可以进到安装目录的bin目录下./pgbouncer执行，或者创建软连接，例如：

ln -s /home/postgres/pgbouncer/bin/pgbouncer /usr/bin/pgbouncer

配置pgbouncer.ini文件，完整的pgbouncer.ini文件在安装目录下share/doc/pgbouncer目录中

[postgres@pg15_rh7_132 ~]$ cd pgbouncer
[postgres@pg15_rh7_132 pgbouncer]$ cat pgbouncer.ini
[databases]
postgres = host=192.168.126.132 port=5432 dbname=postgres
[pgbouncer]
logfile = /home/postgres/pgbouncer/pgbouncer.log
pidfile = /home/postgres/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6543
unix_socket_dir = /tmp
auth_type = md5
auth_file =  /home/postgres/pgbouncer/userlist.txt
admin_users = postgres

配置userlist.txt文件

[postgres@pg15_rh7_132 pgbouncer]$ cat userlist.txt
"postgres" "qwe"

启动pgbouncer，查看进程

[postgres@pg15_rh7_132 bin]$ ./pgbouncer -d /home/postgres/pgbouncer/pgbouncer.ini 
[postgres@pg15_rh7_132 bin]$ ps -ef |grep pgbouncer
postgres  34945      1  0 18:25 ?        00:00:00 ./pgbouncer -d /home/postgres/pgbouncer/pgbouncer.ini
postgres  34964  33675  0 18:26 pts/1    00:00:00 grep --color=auto pgbouncer

如果需要配置开机自启则可以按以下操作

[root@pg15_rh7_132 ~]# cat /etc/systemd/system/pgbouncer.service  
[Unit]
Description=pgBouncer connection pooling for PostgreSQL
After=syslog.target network.target
[Service]
Type=forking
User=postgres
Group=postgres
PermissionsStartOnly=true
ExecStartPre=-/bin/mkdir -p /var/run/pgbouncer /var/log/pgbouncer
ExecStartPre=/bin/chown -R postgres:postgres /var/run/pgbouncer /var/log/pgbouncer
ExecStart=/home/postgres/pgbouncer/bin/pgbouncer -d /home/postgres/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -SIGHUP $MAINPID
PIDFile=/home/postgres/pgbouncer/pgbouncer.pid
LimitNOFILE=1000
[Install]
WantedBy=multi-user.target

添加可执行权限

[root@pg15_rh7_132 ~]# chmod +x /etc/systemd/system/pgbouncer.service

启动服务，查看进程

[root@pg15_rh7_132 ~]# systemctl start pgbouncer
[root@pg15_rh7_132 ~]# ps -ef |grep pgbouncer
postgres  51706      1  0 22:56 ?        00:00:00 /home/postgres/pgbouncer/bin/pgbouncer -d /home/postgres/pgbouncer/pgbouncer.ini
root      51714  51183  0 22:56 pts/2    00:00:00 grep --color=auto pgbouncer

通过pgbouncer连接PG

[postgres@pg15_rh7_132 bin]$ psql -p 6543 -U postgres 
Password for user postgres:
psql: error: connection to server on socket "/tmp/.s.PGSQL.6543" failed: FATAL:  no pg_hba.conf entry for host "192.168.126.132", user "postgres", database "postgres", no encryption

配置白名单后重新连接

[postgres@pg15_rh7_132 ~]$ cat >>/pg15data/pg_hba.conf 
host    postgres     postgres           192.168.126.0/24        md5
[postgres@pg15_rh7_132 ~]$ pg_ctl reload
server signaled
[postgres@pg15_rh7_132 ~]$ psql -p 6543 -U postgres
Password for user postgres:
psql (15.1)
Type "help" for help.
postgres=# select user;
   user
----------
 postgres
(1 row)

创建测试表进行增删改查

postgres=# create table t1(id int,name text);
CREATE TABLE
postgres=# insert into t1 values (1,'jshdf');
INSERT 0 1
postgres=# insert into t1 SELECT generate_series(1,10000) ,repeat( chr(int4(random()*26)+65),4);
INSERT 0 10000
postgres=# select count(*) from t1;
 count 
-------
 10001
(1 row)
postgres=# select * from t1 limit 5;
 id | name  
----+-------
  1 | jshdf
  1 | SSSS
  2 | KKKK
  3 | DDDD
  4 | CCCC
(5 rows)
postgres=# delete from t1 where name='CCCC';
DELETE 401

登陆控制台

[postgres@pg15_rh7_132 ~]$ psql -p 6543 -U postgres pgbouncer
Password for user postgres: 
psql (15.1, server 1.18.0/bouncer)
WARNING: psql major version 15, server major version 1.18.
         Some psql features might not work.
Type "help" for help.
pgbouncer=#
--这里看到提示PG15版本和pgbouncer1.18版本搭配可能会导致一些psql特性无法工作，可能是PG版本较新，部分新功能pgbouncer还未兼容，只是暂未看到具体说明。

遇到问题

[postgres@pg15_rh7_132 ~]$ psql -p 6543 -U test pgbouncer
Password for user test: 
psql: error: connection to server on socket "/tmp/.s.PGSQL.6543" failed: FATAL:  password authentication failed

--以上报错是没有在userlist.txt文件中添加test用户的登陆信息
--添加之后用户信息后需重新加载

[postgres@pg15_rh7_132 pgbouncer]$ cat >>userlist.txt 
"test" "qwe"
[root@pg15_rh7_132 ~]# systemctl reload pgbouncer.service

--添加之后又出现新的报错，not allowed

[postgres@pg15_rh7_132 ~]$ psql -p 6543 -U test pgbouncer
psql: error: connection to server on socket "/tmp/.s.PGSQL.6543" failed: FATAL:  not allowed

--这个需要在pgbouncer.ini文件中配置admin_users或者stats_users去指定用户登录到控制台，再或者将auth_type设置为any，这样任何用户都可以作为stats_user进入。

[postgres@pg15_rh7_132 pgbouncer]$ cat >>pgbouncer.ini
stats_users = test
[root@pg15_rh7_132 ~]# systemctl reload pgbouncer.service
[postgres@pg15_rh7_132 pgbouncer]$ psql -p 6543 -U test pgbouncer
Password for user test: 
psql (15.1, server 1.18.0/bouncer)
WARNING: psql major version 15, server major version 1.18.
         Some psql features might not work.
Type "help" for help.
pgbouncer=# SHOW CLIENTS;
 type | user | database  | state  | addr | port | local_addr | local_port |      connect_time       |      request_time       | wait | wait_us | close_needed |   ptr    | link | remote_pid | tls | application_name 
------+------+-----------+--------+------+------+------------+------------+-------------------------+-------------------------+------+---------+--------------+----------+------+------------+-----+------------------
 C    | test | pgbouncer | active | unix | 6543 | unix       |       6543 | 2023-03-29 23:34:07 CST | 2023-03-29 23:36:14 CST |    0 |       0 |            0 | 0x72b650 |      |      54227 |     | psql
(1 row)