How To Setting Pg_Pool With Clustering EDB 10 On Different Server Database



test@rizky:~$ ssh dba2@IP_PC_DBA
Password:
Welcome to Ubuntu 16.04.1 LTS (GNU/Linux 4.4.0-130-generic x86_64)

* Documentation: https://help.ubuntu.com
* Management: https://landscape.canonical.com
* Support: https://ubuntu.com/advantage

381 packages can be updated.
0 updates are security updates.

Last login: Fri Aug 10 08:17:10 2018 from IP_PC_DBA
dba2@dba-Veriton-Z4620G:~$ ssh root@ IP _PGPOOL1_INTERNAL
root@IP _PGPOOL1_INTERNAL's password:
Last login: Fri Aug 10 10:47:26 2018 from dba
[root@haproxy1 ~]# yum install pgpool-II-pg96
[root@haproxy1 ~]# yum install pgpool-II-pg96-debuginfo
[root@haproxy1 ~]# yum install pgpool-II-pg96-devel
[root@haproxy1 ~]# yum install pgpool-II-pg96-extensions
[root@haproxy1 ~]# cd /etc/pgpool-II
[root@haproxy1 pgpool-II]# ls -lrt
total 196
-rw-r--r-- 1 root root 34370 Jul 31 07:57 pgpool.conf.sample-stream
-rw-r--r-- 1 root root 34331 Jul 31 07:57 pgpool.conf.sample-replication
-rw-r--r-- 1 root root 34347 Jul 31 07:57 pgpool.conf.sample-master-slave
-rwxrw-r-- 1 root root 858 Jul 31 07:57 pcp.conf
-rwxr--r-- 1 root root 35167 Agu 10 15:10 pgpool.conf
-rwxr-xr-x 1 root root 872 Agu 10 15:11 failover.sh
-rwxr--r-- 1 root root 3504 Agu 10 15:12 pool_hba.conf
[root@haproxy1 pgpool-II]# vi pgpool.conf

#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------
listen_addresses = '*'
port = 9999
socket_dir = '/tmp'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'

#------------------------------------------------------------------------------
# - Backend Connection Settings -
#------------------------------------------------------------------------------
backend_hostname0 = 'IP_A_Master'
backend_port0 = 5444
backend_weight0 = 1
backend_data_directory0 = '/DATA/EDB10/app/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'

backend_hostname1 = 'IP_B_Replication'
backend_port1 = 5444
backend_weight1 = 1
backend_data_directory1 = '/DATA/EDB10/app/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'

backend_hostname2 = 'IP_C_Replication'
backend_port2 = 5444
backend_weight2 = 1
backend_data_directory2 = '/DATA/EDB10/app/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'

backend_hostname3 = 'IP_D_Replication'
backend_port3 = 5444
backend_weight3 = 1
backend_data_directory3 = '/DATA/EDB10/app/data'
backend_flag3 = 'ALLOW_TO_FAILOVER'

#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------
num_init_children = 32
max_pool = 30

#------------------------------------------------------------------------------
# LOGS
#------------------------------------------------------------------------------
log_destination = 'syslog,stderr'
log_line_prefix = '%t: pid %p: '
log_connections = on
log_statement = on
log_per_node_statement = on
log_error_verbosity = verbose
client_min_messages = notice
log_min_messages = info

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/var/log/pgpool'

#------------------------------------------------------------------------------
# LOAD BALANCING MODE
#------------------------------------------------------------------------------
load_balance_mode = on
ignore_leading_white_space = on
white_function_list = 'get_.*,select_.*'
black_function_list = 'nextval,setval,set_.*,update_.*,delete_.*,insert_.*'



#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
#------------------------------------------------------------------------------
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_user = 'cekdb'
sr_check_password = 'Test1234'
sr_check_database = 'template1'

#------------------------------------------------------------------------------
# HEALTH CHECK
#------------------------------------------------------------------------------
health_check_period = 20
health_check_timeout = 20
health_check_user = 'cekdb'
health_check_password = 'Test1234'
health_check_database = 'template1'
health_check_max_retries = 2
health_check_retry_delay = 2
connect_timeout = 10000

#------------------------------------------------------------------------------
# FAILOVER AND FAILBACK
#------------------------------------------------------------------------------
failover_command = ''
search_primary_node_timeout = 300

#------------------------------------------------------------------------------
# ONLINE RECOVERY
#------------------------------------------------------------------------------
recovery_user = 'enterprisedb'
recovery_1st_stage_command = ''

--------------------------Setting Watchdog IP PGPOOL2--------------------------

IP PGPOOL2
[root@haproxy2 pgpool-II]# vi pgpool.conf
#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
use_watchdog = on
wd_hostname = 'IP _PGPOOL2_INTERNAL'
wd_port = 9000
delegate_IP = 'IP_VIRTUAL'
if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'
wd_lifecheck_method = 'heartbeat'
wd_heartbeat_port = 9694
heartbeat_destination0 = 'IP _PGPOOL1_INTERNAL'
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'eth0'
other_pgpool_hostname0 = 'IP _PGPOOL1_INTERNAL'
other_pgpool_port0 = 9999
other_wd_port0 = 9000

--------------------------Setting Hba PgPool 2--------------------------
[root@haproxy2 pgpool-II]# vi pool_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all IP_A_Master/32 trust
host all all IP_B_Replication/32 trust
host all all IP_C_Replication/32 trust
host all all IP_D_Replication/32 trust
host all all IP _PGPOOL1_EXTERNAL/32 trust
host all all IP _PGPOOL1_INTERNAL/32 trust
host all all IP_VIRTUAL/32 trust
host all all ::1/128 trust

--------------------------Setting Watchdog IP PGPOOL1--------------------------
IP PGPOOL1
[root@haproxy1 pgpool-II]# vi pgpool.conf
#------------------------------------------------------------------------------
# WATCHDOG
#------------------------------------------------------------------------------
use_watchdog = on
wd_hostname = 'IP _PGPOOL1_INTERNAL'
wd_port = 9000
delegate_IP = 'IP_VIRTUAL'
if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'
wd_lifecheck_method = 'heartbeat'
wd_heartbeat_port = 9694
heartbeat_destination0 = 'IP _PGPOOL2_INTERNAL'
heartbeat_destination_port0 = 9694
heartbeat_device0 = 'eth0'
other_pgpool_hostname0 = ' IP _PGPOOL2_INTERNAL'
other_pgpool_port0 = 9999
other_wd_port0 = 9000

--------------------------Setting Hba PgPool 1--------------------------
[root@haproxy1 pgpool-II]# vi pool_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all IP_A_Master/32 trust
host all all IP_B_Replication/32 trust
host all all IP_C_Replication/32 trust
host all all IP_D_Replication/32 trust
host all all IP _PGPOOL2_EXTERNAL/32 trust
host all all IP _PGPOOL2_INTERNAL/32 trust
host all all IP_VIRTUAL/32 trust
host all all ::1/128 trust





Test di IP PGPOOL1
[root@haproxy1 pgpool-II]# /opt/PG10/app/bin/psql -h IP_VIRTUAL -p 9999 -U enterprisedb -d edb -c 'show pool_nodes;'
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+------------+------+--------+-----------+---------+------------+-------------------+-------------------
0 | IP A_Master | 5444 | up | 0.250000 | primary | 0 | true | 0
1 | IP B_Replication | 5444 | up | 0.250000 | standby | 0 | false | 0
2 | IP B_Replication | 5444 | up | 0.250000 | standby | 0 | false | 0
3 | IP B_Replication | up | 0.250000 | standby | 0 | false | 0
(4 rows)

Test di IP PGPOOL2
[root@haproxy2 ~]# /opt/PG10/app/bin/psql -h IP_VIRTUAL -p 9999 -U enterprisedb -d edb -c 'show pool_nodes;'
node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay
---------+------------+------+--------+-----------+---------+------------+-------------------+-------------------
0 | IP A_Master | 5444 | up | 0.250000 | primary | 6003 | false | 0
1 | IP B_Replication | 5444 | up | 0.250000 | standby | 0 | true | 0
2 | IP C_Replication | 5444 | up | 0.250000 | standby | 0 | false | 0
3 | IP D_Replication | 5444 | up | 0.250000 | standby | 0 | false | 0
(4 rows)

[root@haproxy1 ~]#

Comments

Post a Comment