Pgpool for Pooling and Load Balancing

Hi Everyone,

So I am looking to leverage *pg-pool* for the first time in my environment
and I am hoping to get help here on each step as I go along.

My current environment, sometimes we go high on connections. So I decided
to resolve it by using pgpool for connection pooling and load balancing.
Currently the environment is set up with a Master postgresql version 10,
with 2 direct streaming replication replicas and these 2 replicas have 3
other replicas cascaded.

So at the end of this project I should have pgpool configured on 2 servers
with all *SELECT *queries diverted to the read replica. All the other
slaves will not act as read replicas but as normal replicas using streaming
replication.

So I came up with a diagram below on how the architecture will look like
for the 2 servers with pgpool* server A *and* server B*

[image: image.png]

*Step 1 Configuration*
*-------------------------------------------------------------------------------------------------------------------------*

*#------------------------------------------------------------------------------#
CONNECTIONS#------------------------------------------------------------------------------#
- pgpool Connection Settings -listen_addresses = 'IP of server A'
port = 9999 #
i will keep this as default 9999
socket_dir = '/var/run/postgresql'
listen_backlog_multiplier = 2 # Am not sure what value to put here
serialize_accept = off #
Am not sure what to do here
reserved_connections = 3 # I put 3 here assuming it works like
postgresql superuser reserved
connections#-------------------------------------------------------------------------------#
- pgpool Communication Manager Connection Settings
-#-------------------------------------------------------------------------------pcp_listen_addresses
= '*' # should i just put SERVER A IP or leave (*)
pcp_port = 9898 # I will keep
this port as defaultpcp_socket_dir =
'/var/run/postgresql'#------------------------------------------------------------------------------
# - Backend Connection Settings
-#------------------------------------------------------------------------------backend_hostname0
= 'SERVER A hostname'
# Host name or IP address to connect to for backend 0backend_port0 = 5432
# Port number for backend
0backend_weight0 = 1 # Not sure what the weights do and what
value to choose # Weight
for backend 0 (only in load balancing mode)backend_data_directory0 =
'/var/lib/pgsql/10/data_serverA'
backend_flag0 = 'ALLOW_TO_FAILOVER'
# Not sure what this
does and what option to take
# Controls various backend behavior
#
ALLOW_TO_FAILOVER or DISALLOW_TO_FAILOVERbackend_hostname1 = 'SERVER B
hostname'backend_port1 = 5432 # SERVER B
is on a different server and the port is defaultbackend_weight1 = 1
# Not sure here toobackend_data_directory1 =
'//var/lib/pgsql/10/data_serverB'backend_flag1 = 'ALLOW_TO_FAILOVER' #
Does this mean failover will occur in server
B#------------------------------------------------------------------------------------#
- Authentication
-#------------------------------------------------------------------------------------enable_pool_hba
= on # So i turn this to (on)..then i will add all client IPs
in pgpool_hba file #
Use pool_hba.conf for client authenticationpool_passwd = 'pool_passwd'
# Not sure i understand what this pool_passwd is
authentication_timeout = 60
allow_clear_text_frontend_auth = off
# please advice if this should be on or off
# Allow Pgpool-II to use clear text password
authentication
# with clients, when pool_passwd does not
# contain the user
password#------------------------------------------------------------------------------#
POOLS#------------------------------------------------------------------------------#
- Concurrent session and pool size -num_init_children = 32 # my
DB max_connection configuration is 500. Any idea what value i should put
here. # Number of
concurrent sessions allowed max_pool = 4
# Any advice on this
# Number of connection pool caches per connection
# (change requires restart)# - Life
time -child_life_time = 300
child_max_connections = 0 # Any advise will help
# Pool exits after receiving that
many connections # 0
means no exitconnection_life_time = 0 # Any advise will help
# Connection to backend
closes after being idle for this many seconds
# 0 means no closeclient_idle_limit = 0 #
Any advise will do # Client
is disconnected after being idle for that many seconds
# (even inside an explicit transactions!)
# 0 means no disconnection*
*----------------------------------------------------------------------------------------------------------------------------------------------------------*

Thanks