PGPool-II is an Open Source tool used for PostgreSQL, it is a connection pooler, a load balancer, and a high availability solution.
It provides high availability by replicating data between multiple nodes, allowing for automatic failover in the event of a node failure.
Why PGPool-II
Connection Pooling – Once a connection is established to a PostgreSQL server, pgpool reuses the connections with same properties which reduces the connection overhead.
Replication –
Best Practices for Implementing PGPool for Postgres High Availability
Implementing PGPool for Postgres High Availability is an effective way to ensure reliable performance of a database. It can provide scalability, redundancy and resilience against failure.
To ensure best practices are followed, there are several key steps to ensure a successful implementation.
#pgpool=II installation
yum install -y http://www.pgpool.net/yum/rpms/4.1/redhat/rhel-7-x86_64/pgpool-II-release-4.1-2.noarch.rpm
yum install -y pgpool-II-pg14-*
#Next Steps
# Configuration of PGPool-II:
# /etc/pgpool-II/pgpool.conf
# CREATE USER monitor WITH ENCRYPTED PASSWORD '*****';
# CREATE USER infuser WITH ENCRYPTED PASSWORD '*****';
# create database monitor;
# add pgpool host address to pg_hba.conf on Primary and Secondary
# host all all 10.0.0.0/32 md5
# reload config
# select pg_reload_conf();
# when config is done restart pgpool
systemctl restart pgpool-II.service
#On Primary and secondary, make sure the replication is in place
alter system set listen_addresses to '*';
select pg_reload_conf;
#Then restart the servers
####################pgpool server####################
## Connection Details
backend_clustering_mode = 'streaming_replication'
listen_addresses='*'
port=5433
socket_dir = '/var/run/postgresql'
## Streaming checks
sr_check_period = 10
sr_check_user = 'monitor'
sr_check_password = '*********'
sr_check_database = 'monitor'
delay_threshold = 14240
## Health Check
health_check_period = 40
health_check_timeout = 10
health_check_user = 'monitor'
health_check_password = '*********'
health_check_database = 'postgres'
health_check_max_retries = 3
health_check_retry_delay = 1
## Backend Settings
# Primary
backend_hostname0 = '<primary ip>'
backend_port0 = 5433
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/14/data/'
backend_flag0 = 'ALLOW_TO_FAILOVER'<c/ode>
# Secondary
backend_hostname1 = '<secondary ip>'
backend_port1 = 5433
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/14/data/'
backend_flag1 = 'ALLOW_TO_FAILOVER'
#failover
failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'
follow_primary_command = '/etc/pgpool-II/follow_primary.sh %d %h %p %D %m %H %M %P %r %R'
failover_on_backend_error = 'off'<c/ode>
## Logging
logging_collector='on'
log_destination = 'stderr'
log_directory = '/var/log/pgpool'
log_line_prefix = '%t: pid %p: ' # printf-style string to output at beginning of each log line.
log_connections = 'on'
#Pooling
#num_init_children = 32
num_init_children = 1000 # Number of concurrent sessions allowed
# (change requires restart)
#max_pool = 4
max_pool = 2 # Number of connection pool caches per connection
# (change requires restart)
# - Life time -
child_life_time = 300
# Pool exits after being idle for this many seconds
child_max_connections = 2000
# Pool exits after receiving that many connections
# 0 means no exit
connection_life_time = 300
# Connection to backend closes after being idle for this many seconds
# 0 means no close
client_idle_limit = 3000
## Load Balancing
#load_balance_mode = 'ON'
disable_load_balance_on_write='always'
## Replication Responsibilty
master_slave_mode = 'ON'
master_slave_sub_mode = 'stream'
## Client Authentication
allow_clear_text_frontend_auth = 'ON'<//code>
#search_primary_node_timeout = 1min
search_primary_node_timeout = 10
#restart services
systemctl restart pgpool-II.service
#confirm psql
sudo -iu postgres psql -h <pgpool ip> -U infuser -p 5433 postgres -c "show pool_nodes;"