CentOS 7 建置 PostgreSQL連接池 — pgBouncer
Oct 18, 2020
好處：
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 = 6432auth_type = md5
auth_file = /etc/pgbouncer/userlist.txtpool_mode = transaction# 產生檔案 /etc/pgbouncer/userlist.txtsudo -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.1Password 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的連接配置。
— 完成 —
