一、安装配置
1、下载pgbouncer程序包和libevent依赖包
http://www.pgbouncer.org/downloads/files/1.17.0/pgbouncer-1.17.0.tar.gz
2、安装编译所需的环境依赖
yum install gcc gcc-c++ make openssl-devel3、解压并编译安装
tar -xf libevent-2.1.11-stable.tar.gz4、编译安装libevent
./configure --prefix=/usr/local/libevent
make -j 2
make install5、配置环境变量(编译用户pg14下配置)
echo "export PKG_CONFIG_PATH=/usr/local/libevent/lib/pkgconfig" >> ~/.bashrc
cat ~/.bashrc
source ~/.bashrc6、授权pg14用户访问libevent-2.1.so.7权限
[root@cdh01 ~]# setfacl -Rm u:pg14:rwx /usr/7、创建软连接
[root@cdh01 ~]# ln -s /usr/local/libevent/lib/libevent-2.1.so.7 /usr/lib64/libevent-2.1.so.78、编译安装pgbouncer
[pg14@cdh01 ~]$ tar -xf pgbouncer-1.17.0.tar.gz
./configure --prefix=/home/pg14/pgbouncer
make -j 2
make install9、创建pgbouncer.ini配置文件
cat /home/pg14/pgbouncer/pgbouncer.ini
[databases]
appdb = host= 10.111.2.10 port=5666 dbname=appdb
[pgbouncer]
logfile = /home/pg14/pgbouncer/pgbouncer.log
pidfile = /home/pg14/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 5766
unix_socket_dir = /tmp
auth_type = md5
auth_file = /home/pg14/pgbouncer/userlist.txt
admin_users = postgres
pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 1000
default_pool_size = 2010、创建用户名及密码认证文件
确保仅postgres用户可以访问，权限600。
cat /home/pg14/pgbouncer/userlist.txt
"appuser" "1qaz@WSX"
"postgres" "1qaz@WSX"
[pg14@cdh01 pgbouncer]$ ls -l userlist.txt
-rw------- 1 pg14 pg14 43 Oct 23 21:31 userlist.txt11、配置开机自启服务
由systemctl管理，创建pgbouncer.service文件。
[root@cdh01 ~]# cat /etc/systemd/system/pgbouncer.service
[Unit]
Description=pgBouncer connection pooling for PostgreSQL
After=syslog.target network.target
[Service]
Type=forking
User=pg14
Group=pg14
PermissionsStartOnly=true
ExecStartPre=-/bin/mkdir -p /var/run/pgbouncer /var/log/pgbouncer
ExecStartPre=/bin/chown -R pg14:pg14 /var/run/pgbouncer /var/log/pgbouncer
ExecStart=/home/pg14/pgbouncer/bin/pgbouncer -d /home/pg14/pgbouncer/pgbouncer.ini
ExecReload=/bin/kill -SIGHUP $MAINPID
PIDFile=/home/pg14/pgbouncer/pgbouncer.pid
LimitNOFILE=100000
[Install]
WantedBy=multi-user.target12、授权可执行权限
[root@cdh01 ~]# chmod +x /etc/systemd/system/pgbouncer.service13、启动服务
[root@cdh01 ~]# systemctl start pgbouncer二、遇到的问题
1、登录控制台报错：failed: FATAL: not allowed.
[pg14@cdh01 ~]$ psql -p 5766 -Uappuser pgbouncer
psql: error: connection to server on socket "/tmp/.s.PGSQL.5766" failed: FATAL: not allowed从官网查询到，在pgbouncer.ini中需要添加参数admin_users or stats_users。官网原文如下：
Admin console
The console is available by connecting as normal to the database pgbouncer:
$ psql -p 6432 pgbouncer
Only users listed in the configuration parameters admin_users or stats_users are allowed to log in to the console. (Except when auth_type=any, then any user is allowed in as a stats_user.)
Additionally, the user name pgbouncer is allowed to log in without password, if the login comes via the Unix socket and the client has same Unix user UID as the running process.
The admin console currently only supports the simple query protocol. Some drivers use the extended query protocol for all commands; these drivers will not work for this.2、处理方法
pgbouncer.ini文件中添加如下配置，管理员为postgres可以登录console控制台。其它用户无权限登录，登录报failed: FATAL: not allowed.
admin_users = postgres[pg14@cdh01 ~]$ psql -p 5766 -Upostgres pgbouncer
Password for user postgres:
psql (14.5, server 1.17.0/bouncer)
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
------+----------+-----------+--------+------+------+------------+------------+-------------------------+-------------------------+------+---------+--------------+-----------+------+------------+-----
C | postgres | pgbouncer | active | unix | 5766 | unix | 5766 | 2022-10-24 00:07:01 CST | 2022-10-24 00:07:31 CST | 0 | 0 | 0 | 0x15d8770 | | 8602 |
(1 row)
[pg14@cdh01 ~]$ psql -p 5766 -Uappuser pgbouncer
psql: error: connection to server on socket "/tmp/.s.PGSQL.5766" failed: FATAL: not allowed可以指定登录pgbouncer控制台的用户，如指定appuser。pgbouncer.ini文件中调整配置为：
stats_users = appuser[pg14@cdh01 pgbouncer]$ psql -p 5766 -Uappuser pgbouncer
Password for user appuser:
psql (14.5, server 1.17.0/bouncer)
Type "help" for help.
pgbouncer=# show pools;
database | user | cl_active | cl_waiting | cl_cancel_req | sv_active | sv_idle | sv_used | sv_tested | sv_login | maxwait | maxwait_us | pool_mode
-----------+-----------+-----------+------------+---------------+-----------+---------+---------+-----------+----------+---------+------------+-----------
pgbouncer | pgbouncer | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | statement
(1 row)
