Pgdoucer best practices: Series 4

author ： Wang Zhibin , Once won the Chinese PostgreSQL Database Management Engineer （PGCE）, yes PostgreSQL Officially certified lecturer , Pan Guyun is a specially invited gold medal lecturer in the classroom .

Finally, let's talk about Pgbouncer The deployment form of , Including single application scenarios 、 Multiple application scenarios 、 Cluster scenario and multi instance scenario , These methods are based on different business scenarios , There is no better or worse , What's right is what's right . Single application and multi application scenarios come from the official .

Single application scenario ：

Single application scenarios are mainly short connection scenarios , Frequent database connection operations , But the operation time is shorter , All are short connections , So will pgbouncer The application server is deployed on the same server , Reduce application servers and pgbouncer Overhead between .

The configuration file

[databases]
test1 =
test =
[pgbouncer]
listen_port = 6688
listen_addr = 192.168.165.3
auth_type = md5
auth_file = /home/postgres/pgbouncer/bin/userlist.txt
logfile = /home/postgres/pgbouncer/pgbouncer1.log
pidfile =/home/postgres/pgbouncer/pgbouncer1.pid
unix_socket_dir = /tmp
;;unix_socket_mode = 0777
admin_users = wzb
stats_users = wzb
pool_mode = session
max_client_conn=1000
default_pool_size=30

Export the user name and password in the database to userslist.txt
userslist.txt, The format is user name password

"testuser" "md54d15115d8bebd3188c1ae09c4a9848af"
"testuser1" "md5f8386abbae413786661ee5a5cfb5593c"
"wzb" "md53d57c4bc9a647385e6916efd0b44db46"

start-up Pgbouncer
pgbouncer -d pgbouncer.ini

Client connection mode
psql -dtest1 -Utestuser1 -p6688

Multiple application scenarios :

Multiple application scenarios , It generally refers to multiple application servers connecting to the database , So you can choose to pgbouncer Deployed on the same server as the database service , Reduce pgbouncer And database overhead .

To configure PgBouncer.ini file

[databases]
a1 =  host=127.0.0.1 port=5432 dbname=test
a2 =  host=127.0.0.1 port=5432 dbname=test1
[pgbouncer]
listen_port = 6688
listen_addr = *
auth_type = md5
auth_file = /home/postgres/pgbouncer/bin/userlist.txt
logfile = /home/postgres/pgbouncer/pgbouncer.log
pidfile =/home/postgres/pgbouncer/pgbouncer.pid
admin_users = wzb
stats_users = wzb
pool_mode = session
max_client_conn=1000
default_pool_size=30

Export the user name and password in the database to userslist.txt
userslist.txt, The format is user name password

"testuser" "md54d15115d8bebd3188c1ae09c4a9848af"
"testuser1" "md5f8386abbae413786661ee5a5cfb5593c"
"wzb" "md53d57c4bc9a647385e6916efd0b44db46"

start-up Pgbouncer
pgbouncer -d pgbouncer.ini

Connect to the back-end database
$ psql -p 6688 -U testuser a1
$ psql -p 6688 -U testuser1 a2

Connect pgbouncer database
psql -p 6688 pgbouncer -U wzb
pgbouncer=# show help;
NOTICE: Console usage
DETAIL:
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
SET key = arg
RELOAD
PAUSE []
RESUME []
DISABLE
ENABLE
RECONNECT []
KILL
SUSPEND
SHUTDOWN
SHOW
pgbouncer=# show clients;

type| C
user| pgbouncer
database| pgbouncer
state| active
addr| unix
port| 6432
local_addr| unix
local_port| 6432
connect_time| 2020-10-09 20:41:32 CST
request_time| 2020-10-09 20:41:32 CST
wait| 5
wait_us| 483185
close_needed| 0
ptr| 0x9ec340
link|
remote_pid| 23567
tls |

pgbouncer=# show pools;

database| pgbouncer
user| pgbouncer
cl_active| 1
cl_waiting| 0
sv_active|0
sv_idle|0
sv_used|0
sv_tested|0
sv_login|0
maxwait|0
maxwait_us|0
pool_mode|  transaction

Cluster scenario （ Read / write separation ）:

In the context of read-write separation pgbouncer The configuration of is basically the same as the previous configuration , It is mainly different from deploying read and write separately pgbouncer, because pgbouncer It's just a database connection pool , No load balancing , Or high availability ,IP Drift and other characteristics , It needs to be combined with other mature products .

Multiple instance scenarios ：

Multi instance scenarios mainly use linux System port reuse technology , This feature depends on Linux Support on the kernel （Linux3.6 Above version ）, And combine pgbouncer Self support （ Set up so_reuseport=1） Combined to form a multi instance scenario pgbouncer Use , Think of it as pgbouncer Highly reliable or available , In the case of an instance process failure , Other instance integrations can still handle database connection requests from outside . At the operating system level , Belong to multiple processes sharing the same port .

The instance configuration 1

[databases]
a2 =  host=127.0.0.1 port=5432 dbname=test1 pool_size=50
;;a1 =  host=127.0.0.1 port=5432 dbname=test pool_size=30
[pgbouncer]
listen_port = 6688
listen_addr = 192.168.165.3
auth_type = md5
auth_file = /home/postgres/pgbouncer/bin/userlist.txt
logfile = /home/postgres/pgbouncer/pgbouncer1.log
pidfile =/home/postgres/pgbouncer/pgbouncer1.pid
unix_socket_dir = /tmp/pg1
#unix_socket_mode = 0777
admin_users = wzb
stats_users = wzb
pool_mode = session
max_client_conn=1000
default_pool_size=30
so_reuseport = 1

The instance configuration 2

[databases]
a2 =  host=127.0.0.1 port=5432 dbname=test1 pool_size=50
;;a1 =  host=127.0.0.1 port=5432 dbname=test pool_size=30
[pgbouncer]
listen_port = 6688
listen_addr = 192.168.165.3
auth_type = md5
auth_file = /home/postgres/pgbouncer/bin/userlist.txt
logfile = /home/postgres/pgbouncer/pgbouncer2.log
pidfile =/home/postgres/pgbouncer/pgbouncer2.pid
unix_socket_dir = /tmp/pg2
#unix_socket_mode = 0777
admin_users = wzb
stats_users = wzb
pool_mode = session
max_client_conn=1000
default_pool_size=30
so_reuseport = 1

Export the user name and password in the database to userslist.txt
userslist.txt, The format is user name password

"testuser" "md54d15115d8bebd3188c1ae09c4a9848af"
"testuser1" "md5f8386abbae413786661ee5a5cfb5593c"
"wzb" "md53d57c4bc9a647385e6916efd0b44db46"

Start multiple instances
./pgbouncer pgbouncer.ini
./pgbouncer pgbouncer1.ini

Reference resources
[1]Pgbouncer Official website
[2]PgBouncer Configuration
[3]Tuning PostgreSQL for sysbench-tpcc
[4]understanding-user-management-in-pgbouncer
[5]performance-best-practices-for-using-azure-database-for-postgresql-connection-pooling
[6]guide-using-pgbouncer
[7]azure-database-for-postgresql/connection-handling-best-practice-with-postgresql
[8]steps-to-install-and-setup-pgbouncer-connection-pooling-proxy
[9]pg-phriday-securing-pgbouncer

Learn more about PostgreSQL Hot news 、 news information 、 Wonderful activities , Please visit China PostgreSQL Official website
Solve more PostgreSQL Related knowledge 、 technology 、 Work problems , Please visit China PostgreSQL Official Q & a community
Download more PostgreSQL Related information 、 Tools 、 Plug in problems , Please visit China PostgreSQL Official download site