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
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 default
pcp_socket_dir = '/var/run/postgresql'
#------------------------------------------------------------------------------
# - Backend Connection Settings -
#------------------------------------------------------------------------------
backend_hostname0 = 'SERVER A hostname'
# Host name or IP address to connect to for backend 0
backend_port0 = 5432
# Port number for backend 0
backend_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_FAILOVER
backend_hostname1 = 'SERVER B hostname'
backend_port1 = 5432 # SERVER B is on a different server and the port is default
backend_weight1 = 1 # Not sure here too
backend_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 authentication
pool_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 exit
connection_life_time = 0 # Any advise will help
# Connection to backend closes after being idle for this many seconds
# 0 means no close
client_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
