CentOS 7 建置 PostgreSQL連接池 — pgBouncer

好處：
https://scalegrid.io/blog/postgresql-connection-pooling-part-4-pgbouncer-vs-pgpool/
https://medium.com/pgsql-tw/you-need-pgbouncer-e62fa329b209

安裝：
https://mydbops.wordpress.com/2020/03/25/getting-started-with-pgbouncer-in-postgresql/

# 安裝
yum install pgbouncer
# 編輯配置 -  這邊只列出需要注意部分
vi /etc/pgbouncer/pgbouncer.ini
[databases]
* = host=localhost
[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
# 產生檔案 /etc/pgbouncer/userlist.txt
sudo -u postgres psql
Password for user postgres:
psql (12.4)
Type "help" for help.
postgres=# select rolname,rolpassword from pg_authid where rolname='postgres';
 rolname  |             rolpassword
----------+-------------------------------------
 postgres | md5fc5fa78dbffef80dc29f2b89fdb45aba
(1 row)
# 寫入檔案
echo '"postgres" "md5fc5fa78dbffef80dc29f2b89fdb45aba"' > /etc/pgbouncer/userlist.txt

啟動服務

systemctl enable pgbouncer
systemctl start pgbouncer
# 連接測試
[root@vm postgresql]# psql --port 6432 -Upostgres -h127.0.0.1
Password for user postgres:
psql (12.4)
Type "help" for help.

連接問題排除

vi /var/lib/pgsql/12/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the

補充：在完成連接池配置後，若使用pgPool2作為負載平衡記得將port改為pgBouncer的連接配置。

pgPool2連接pgBouncer

— 完成 —