Friday, August 14, 2026

.::: 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
# centos 9
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
or 
centos 10 
wget https://mirror.de.leaseweb.net/epel/10/Everything/x86_64/Packages/l/libmemcached-awesome-1.1.4-5.el10_0.x86_64.rpm 
wget https://www.pgpool.net/yum/rpms/4.7/redhat/rhel-10-x86_64/pgpool-II-release-4.7-1.noarch.rpm
yum install libmemcached-awesome-1.1.4-5.el10_0.x86_64.rpm -y

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
 
echo 'rahasia' > /root/.pgpoolkey
chmod 600 /root/.pgpoolkey 
cp /root/.pgpoolkey /var/lib/pgsql/.pgpoolkey
chown postgres:postgres /var/lib/pgsql/.pgpoolkey
chmod 600 /var/lib/pgsql/.pgpoolkey

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'
pcp_listen_addresses = '127.0.0.1'
pcp_port = 9898
pcp_socket_dir = '/var/run/postgresql'
 
grep -E '^(listen_addresses|port|backend_hostname|backend_port|backend_weight|backend_data_directory|backend_flag|load_balance_mode|sr_check_period|sr_check_user|sr_check_password|health_check_period|health_check_user|health_check_database|failover_on_backend_error|pool_passwd)|pool_passwd = '/etc/pgpool-II/pool_passwd'|pcp_listen_addresses|pcp_port|pcp_socket_dir' /etc/pgpool-II/pgpool.conf

7. restart pgpool  

chmod 640 /etc/pgpool-II/pgpool.conf 
chown postgres:postgres /etc/pgpool-II/pgpool.conf 
systemctl restart pgpool
systemctl enable pgpool 
 
journalctl -u pgpool --no-pager | grep -Ei 'error|fatal|warning|decrypt|pool_key|password|scram'

[root@ha01 pgpool-II]# journalctl -u pgpool --no-pager | grep -Ei 'error|fatal|warning|decrypt|pool_key|password|scram'
Aug 31 15:52:35 ha01 pgpool[1045]: 2026-08-31 15:52:35.272: main pid 1045: WARNING:  could not open configuration file: "/etc/pgpool-II/pgpool.conf"
Aug 31 15:53:44 ha01 pgpool[1148]: 2026-08-31 15:53:44.861: child pid 1148: FATAL:  pgpool is not accepting any new connections
Aug 31 15:55:25 ha01 pgpool[1273]: 2026-08-31 15:55:25.514: main pid 1273: WARNING:  could not open configuration file: "/etc/pgpool-II/pgpool.conf"
Aug 31 15:55:26 ha01 pgpool[1276]: 2026-08-31 15:55:26.555: main pid 1276: WARNING:  could not open configuration file: "/etc/pgpool-II/pgpool.conf"
[root@ha01 pgpool-II]#
[root@ha01 pgpool-II]# 

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;
 


 

SELECT
    CASE inet_server_addr()
        WHEN '10.10.10.31'::inet THEN 'teguhth01'
        WHEN '10.10.10.32'::inet THEN 'teguhth02'
        WHEN '10.10.10.33'::inet THEN 'teguhth03'
        ELSE 'UNKNOWN'
    END AS hostname,
    inet_server_addr() AS ip,
    inet_server_port() AS port,
    inet_client_addr() as ipclient,
    inet_client_port() as ipclientport,
    current_database() AS database,
    current_schema() as dbschema,
    current_user AS "user";
 


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
 

14. check all from pgpool
 
SELECT
    client_addr,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_size_pretty(
        pg_wal_lsn_diff(sent_lsn, replay_lsn)
    ) AS replication_lag
FROM pg_stat_replication; 


15. check all from master .31
      
master    
psql -x -c "select * from pg_stat_replication;" ;

slave 
psql -x -c "select * FROM pg_stat_wal_receiver;" ;


16.  check all from slave .32
      
master    
psql -x -c "select * from pg_stat_replication;" ;

slave 
psql -x -c "select * FROM pg_stat_wal_receiver;" ;

17. check all from slave .33 
     
master    
psql -x -c "select * from pg_stat_replication;" ;

slave 
psql -x -c "select * FROM pg_stat_wal_receiver;" ;
 
18. script to test
 
for i in {1..20}; do
  PGPASSWORD=admin psql \
    -h 10.10.10.25 \
    -p 9999 \
    -U userpgpool \
    -d teguhth \
    -Atc "SELECT inet_server_addr();"
done
 
for i in {1..30}; do
    PGPASSWORD=admin psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -Atc "SELECT inet_server_addr();"
done
 

 
19. script to test 2 with hostname
 
for i in {1..20}; do
    PGPASSWORD=admin psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -Atc "
        SELECT
            inet_server_addr(),
            CASE inet_server_addr()
                WHEN '10.10.10.31'::inet THEN 'teguhth01'
                WHEN '10.10.10.32'::inet THEN 'teguhth02'
                WHEN '10.10.10.33'::inet THEN 'teguhth03'
                ELSE 'UNKNOWN'
            END;
    "
done

for i in {1..20}; do
    PGPASSWORD=admin psql -h 10.10.10.25 -p 9999 -U userpgpool -d teguhth -Atc "SELECT inet_server_addr(), CASE inet_server_addr() WHEN '10.10.10.31'::inet THEN 'teguhth01' WHEN '10.10.10.32'::inet THEN 'teguhth02' WHEN '10.10.10.33'::inet THEN 'teguhth03' ELSE 'UNKNOWN' END;"
done 

 
Posted by at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts