.::: Install Pgpool-II for load balancer PostgreSQL EDB like Maxscale for MariaDB :::.

 

1. lab

ip master 10.10.10.31
port 5432
user pool access = userpgpool 
passwd pool access = admin
status Master

ip slave 10.10.10.33
port 5432
user pool access = userpgpool 
passwd pool access = admin
status Slave 1

ip slave 10.10.10.33
port 5432
user pool access = userpgpool 
passwd pool access = admin
status Slave 2
 
2. create user pool in master / 31

create user userpgpool ;
ALTER ROLE userpgpool SUPERUSER CREATEDB CREATEROLE REPLICATION;
ALTER ROLE userpgpool PASSWORD 'admin';
ALTER USER userpgpool WITH PASSWORD 'admin';

3. download Pgpool-II

wget https://mirror.stream.centos.org/9-stream/CRB/x86_64/os/Packages/libmemcached-awesome-1.1.0-12.el9.x86_64.rpm
wget https://www.pgpool.net/yum/rpms/4.7/redhat/rhel-9-x86_64/pgpool-II-release-4.7-1.noarch.rpm

4. Install Pgpool-II
yum install libmemcached-awesome-1.1.0-12.el9.x86_64.rpm -y
yum install -y pgpool-II-pg18 pgpool-II-pg18-extensions

5. create folder pgpool 

mkdir -p /var/run/postgresql
chown postgres:postgres /var/run/postgresql
chmod 775 /var/run/postgresql

6. File konfigurasi Pgpool-II

cp /etc/pgpool-II/pgpool.conf /etc/pgpool-II/back_pgpool.conf

listen_addresses = '*'
port = 9999

backend_hostname0 = '10.10.10.31'
backend_port0 = 5432
backend_weight0 = 0
backend_data_directory0 = '/var/lib/pgsql/18/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = '10.10.10.32'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/18/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = '10.10.10.33'
backend_port2 = 5432
backend_weight2 = 2
backend_data_directory2 = '/var/lib/pgsql/18/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'

load_balance_mode = on

sr_check_period = 10
sr_check_user = 'userpgpool'
sr_check_password = 'admin'

health_check_period = 10
health_check_user = 'userpgpool'
health_check_password = 'admin'

health_check_database = 'postgres'
failover_on_backend_error = on

pool_passwd = '/etc/pgpool-II/pool_passwd'

7. restart pgpool  

systemctl restart pgpool
systemctl enable pgpool 

8. write poll_passwd

[root@ha02 ~]# cat /etc/pgpool-II/pool_passwd
userpgpool:admin
admin:admin

#userpgpool:21232f297a57a5a743894a0e4a801fc3
#userpgpool:AESLxOxiMAbgnrehOwWjQByWQ==

[root@ha02 ~]#

9. test login direct 

psql "host=10.10.10.31 port=5432 user=userpgpool password=admin dbname=postgres"
psql "host=10.10.10.32 port=5432 user=userpgpool password=admin dbname=postgres"
psql "host=10.10.10.33 port=5432 user=userpgpool password=admin dbname=postgres"

or 

psql "host=10.10.10.31 port=5432 user=userpgpool password=admin dbname=teguhth"
psql "host=10.10.10.32 port=5432 user=userpgpool password=admin dbname=teguhth"
psql "host=10.10.10.33 port=5432 user=userpgpool password=admin dbname=teguhth"

or 

psql -h 10.10.10.31 -p 5432 -U userpgpool -d postgres
psql -h 10.10.10.32 -p 5432 -U userpgpool -d postgres
psql -h 10.10.10.33 -p 5432 -U userpgpool -d postgres

or 

psql -h 10.10.10.31 -p 5432 -U userpgpool -d teguhth
psql -h 10.10.10.32 -p 5432 -U userpgpool -d teguhth
psql -h 10.10.10.33 -p 5432 -U userpgpool -d teguhth
 

10. or login via pgpool 

psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth
psql -h 10.10.10.25 -p 9999 -U admin -d teguhth
 

 

11. run via pgpool 

SELECT inet_client_addr(), inet_client_port(), inet_server_addr(), inet_server_port();
SHOW pool_nodes;
 

 

12. if need check node info

pcp_node_info -h 127.0.0.1 -p 9898 -U pgpool -n 0
pcp_node_info -h 127.0.0.1 -p 9898 -U pgpool -n 1
pcp_node_info -h 127.0.0.1 -p 9898 -U pgpool -n 2
 

13. if atatch node 

pcp_attach_node -h 127.0.0.1 -p 9898 -U pgpool -n 0
pcp_attach_node -h 127.0.0.1 -p 9898 -U pgpool -n 1
pcp_attach_node -h 127.0.0.1 -p 9898 -U pgpool -n 2