pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPgpool for Pooling and Load Balancing
2+ messages / 2 participants
[nested] [flat]
* Pgpool for Pooling and Load Balancing
@ 2020-11-12 23:09 Walters Che Ndoh <chendohw@gmail.com>
2020-11-12 23:33 ` Re: Pgpool for Pooling and Load Balancing MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 2+ messages in thread
From: Walters Che Ndoh @ 2020-11-12 23:09 UTC (permalink / raw)
To: pgsql-admin
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
Attachments:
image.png (image/png, 14.5K) download
view image
* Re: Pgpool for Pooling and Load Balancing
2020-11-12 23:09 Pgpool for Pooling and Load Balancing Walters Che Ndoh <chendohw@gmail.com>
@ 2020-11-12 23:33 ` MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 0 replies; 2+ messages in thread
From: MichaelDBA @ 2020-11-12 23:33 UTC (permalink / raw)
To: Walters Che Ndoh <chendohw@gmail.com>; +Cc: pgsql-admin
Hi Walters,
You said, "...to get help here on each step as I go along."
That's what usually happens when you hire someone to setup a HA
environment for ya. It might be better served for you and us to ask
specific questions as you go along.
Thanks for the picture. I'm a bit confused by it. Is pgpool2 and
postres installed on server A and B respectfully? If so that is a
normal setup, and the one I'm most familiar with.
Regards,
Michael Vitale
Walters Che Ndoh wrote on 11/12/2020 6:09 PM:
> to get help here on each step as I go along.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-11-12 23:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-12 23:09 Pgpool for Pooling and Load Balancing Walters Che Ndoh <chendohw@gmail.com>
2020-11-12 23:33 ` MichaelDBA <MichaelDBA@sqlexec.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox