PostgreSQL分享|简易部署pgbouncer

一、环境准备

yum -y install libevent-devel

tar zxvf pgbouncer-1.18.0.tar.gz

启动数据库：pg_ctl -D /pgdata/data/ start

二、配置编译

cd pgbouncer-1.18.0

./configure

make && make install

三、创建用户

postgres=# create user test superuser  password 'test';
CREATE ROLE
postgres=# select usename, passwd from pg_shadow;
 usename  |                                                                passwd                                                                 
----------+---------------------------------------------------------------------------------------------------------------------------------------
 postgres | 
 test     | SCRAM-SHA-256$4096:3BqT1ICX/RtwAdQWsxa+ZQ==$KCpD3U2B2L9m+Al25oywhukvEzxZFe+2cxplP/c5UNg=:JVnXPWZbSXpQ70hpATLpddZJUhIagJ8Rn3FO+ybIYMI=

四、配置文件

编辑/etc/pgbouncer.ini 修改如下内容

[databases]

test = host=192.168.xx.xx port=5432 user=test password=test dbname=postgres client_encoding=UNICODE connect_query='select 1'

auth_type = md5
auth_file = /etc/userlist.txt

logfile = /pgdata/pgbouncer.log
pidfile = /pgdata/pgbouncer.pid

listen_addr = *
listen_port = 6432

pool_mode=session

编辑/etc/userlist.txt

"test","md505a671c66aefea124cc08b76ea6d30bb"

此时可能有同学要问了，auth_type设置的是md5，但是密码类型是SCRAM-SHA-256，我可不可以自作聪明用select md5('password');来获取md5的密码，答案是no！！如果非要使用md5类型的密码，需要调整password_encryption = md5参数，博主使用的pg版本为14.7，该参数默认为scram-sha-256。

参数调整完毕重建用户

postgres=# select usename, passwd from pg_shadow;
 usename  |               passwd                
----------+-------------------------------------
 postgres | 
 test     | md505a671c66aefea124cc08b76ea6d30bb

五、启动pgbouncer

[postgres data]$ pgbouncer -d /etc/pgbouncer.ini 
[postgres data]$ ps -ef|grep pgbo
postgres  21658      1  0 03:27 ?        00:00:00 pgbouncer -d /etc/pgbouncer.ini

六、登录测试

[postgres data]$ psql -Utest -p6432 -h192.168.x.x -d test
Password for user test: 
psql (14.7)
Type "help" for help.
test=#

[postgres data]$ psql  -p6432 -Upgbouncer

pgbouncer=# show clients;
-[ RECORD 1 ]----+------------------------
type             | C
user             | pgbouncer
database         | pgbouncer
state            | active
addr             | unix
port             | 6432
local_addr       | unix
local_port       | 6432
connect_time     | 2024-05-01 03:48:14 EDT
request_time     | 2024-05-01 03:49:47 EDT
wait             | 77
wait_us          | 178742
close_needed     | 0
ptr              | 0x1374890
link             | 
remote_pid       | 25308
tls              | 
application_name | psql
-[ RECORD 2 ]----+------------------------
type             | C
user             | test
database         | test
state            | active
addr             | 192.168.146.132
port             | 32986
local_addr       | 192.168.146.132
local_port       | 6432
connect_time     | 2024-05-01 03:49:14 EDT
request_time     | 2024-05-01 03:49:14 EDT
wait             | 0
wait_us          | 0
close_needed     | 0
ptr              | 0x1374af8
link             | 
remote_pid       | 0
tls              | 
application_name | psql

七、报错处理

[postgres data]$ psql -Utest -p6432 -d test
Password for user test: 
psql: error: connection to server on socket "/tmp/.s.PGSQL.6432" failed: FATAL:  no pg_hba.conf entry for host "192.168.xx", user "test", database "postgres", no encryption

vi pg_hba.conf添加如下内容

# IPv4 local connections:
host    all             all             0.0.0.0/0               md5

并重新载入配置文件 pg_ctl -D /pgdata/data/ reload