From 86306410f8609da1e13121794e3088a46faa9b20 Mon Sep 17 00:00:00 2001 From: Dobrica Pavlinusic Date: Tue, 29 Aug 2017 14:25:05 +0200 Subject: [PATCH] try to config two pgpools and watchdog, non-working for now --- Makefile | 20 +++++++++++++++----- pgpool.conf | 35 ++++++++++++++++++----------------- t/1-init-cluster.sh | 2 +- t/2-init-pgpool.sh | 37 +++++++++++++++++++++++++++++++++---- 4 files changed, 67 insertions(+), 27 deletions(-) diff --git a/Makefile b/Makefile index c746248..7423358 100644 --- a/Makefile +++ b/Makefile @@ -21,10 +21,17 @@ pull: scp root@10.200.1.61:/var/lib/pgsql/9.6/data/postgresql.conf.slave . scp root@10.200.1.61:/var/lib/pgsql/9.6/data/recovery.conf . -push: - #scp pgpool.conf failover.sh online-recovery.sh root@10.200.1.60:/etc/pgpool-II/ +push: push-pg push-pgpool +push-pgpool: + # replace names in config with real ones + cat pgpool.conf | sed -e 's/__HOSTNAME__THIS__/edozvola-db-01/' -e 's/__HOSTNAME__OTHER__/edozvola-db-02/' > /tmp/pgpool.conf + scp /tmp/pgpool.conf failover.sh online-recovery.sh root@10.200.1.61:/etc/pgpool-II/ + cat pgpool.conf | sed -e 's/__HOSTNAME__THIS__/edozvola-db-02/' -e 's/__HOSTNAME__OTHER__/edozvola-db-01/' > /tmp/pgpool.conf + scp /tmp/pgpool.conf failover.sh online-recovery.sh root@10.200.1.62:/etc/pgpool-II/ + +push-pg: scp streaming-replication.sh root@10.200.1.61:/var/lib/pgsql/ scp postgresql.conf.master postgresql.conf.slave recovery.done pg_hba.conf root@10.200.1.61:/var/lib/pgsql/9.6/data/ ssh root@10.200.1.61 chown -R postgres:postgres /var/lib/pgsql/9.6/data/ @@ -43,11 +50,14 @@ push: restart: #ssh root@10.200.1.61 systemctl restart postgresql-9.6 #ssh root@10.200.1.62 systemctl restart postgresql-9.6 - ssh root@10.200.1.60 rm /var/log/pgpool/pgpool_status - ssh root@10.200.1.60 systemctl restart pgpool + ssh root@10.200.1.61 rm /var/log/pgpool/pgpool_status + ssh root@10.200.1.61 systemctl restart pgpool + ssh root@10.200.1.62 rm /var/log/pgpool/pgpool_status + ssh root@10.200.1.62 systemctl restart pgpool status: - ssh root@10.200.1.60 systemctl status pgpool + ssh root@10.200.1.61 systemctl status pgpool + ssh root@10.200.1.62 systemctl status pgpool ssh root@10.200.1.61 systemctl status postgresql-9.6 ssh root@10.200.1.62 systemctl status postgresql-9.6 diff --git a/pgpool.conf b/pgpool.conf index 2986bb9..2df4aa5 100644 --- a/pgpool.conf +++ b/pgpool.conf @@ -28,7 +28,7 @@ listen_addresses = '*' # Host name or IP address to listen on: # '*' for all, '' for no TCP/IP connections # (change requires restart) -port = 5432 +port = 9999 # Port number # (change requires restart) socket_dir = '/tmp' @@ -181,6 +181,7 @@ syslog_ident = 'pgpool' # - Debug - #log_error_verbosity = default # terse, default, or verbose messages +log_error_verbosity = verbose #client_min_messages = notice # values in order of decreasing detail: # debug5 @@ -463,13 +464,13 @@ client_idle_limit_in_recovery = 0 # - Enabling - -use_watchdog = off +use_watchdog = on # Activates watchdog # (change requires restart) # -Connection to up stream servers - -trusted_servers = '' +trusted_servers = 'edozvola-db-01,edozvola-db-02' # trusted server list which are used # to confirm network connection # (hostA,hostB,hostC,...) @@ -480,7 +481,7 @@ ping_path = '/bin' # - Watchdog communication Settings - -wd_hostname = 'edozvola-db-pgpool' +wd_hostname = '__HOSTNAME__THIS__' # Host name or IP address of this watchdog # (change requires restart) wd_port = 9000 @@ -503,20 +504,20 @@ wd_ipc_socket_dir = '/var/run/postgresql' # - Virtual IP control Setting - -delegate_IP = '' +delegate_IP = '10.210.1.60' # delegate IP address # If this is empty, virtual IP never bring up. # (change requires restart) -if_cmd_path = '/sbin' +if_cmd_path = '/etc/pgpool-II/sbin' # path to the directory where if_up/down_cmd exists # (change requires restart) -if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0' +if_up_cmd = 'ifconfig eth0:0 $_IP_$ netmask 255.255.255.0 up' # startup delegate IP command # (change requires restart) -if_down_cmd = 'ip addr del $_IP_$/24 dev eth0' +if_down_cmd = 'ifconfig eth0:0 down' # shutdown delegate IP command # (change requires restart) -arping_path = '/usr/sbin' +arping_path = '/etc/pgpool-II/sbin' # arping command path # (change requires restart) arping_cmd = 'arping -U $_IP_$ -w 1' @@ -553,7 +554,7 @@ wd_monitoring_interfaces_list = '' # Comma separated list of interfaces names t wd_lifecheck_method = 'heartbeat' # Method of watchdog lifecheck ('heartbeat' or 'query' or 'external') # (change requires restart) -wd_interval = 10 +wd_interval = 3 # lifecheck interval (sec) > 0 # (change requires restart) @@ -568,7 +569,7 @@ wd_heartbeat_keepalive = 2 wd_heartbeat_deadtime = 30 # Deadtime interval for heartbeat signal (sec) # (change requires restart) -heartbeat_destination0 = 'host0_ip1' +heartbeat_destination0 = 'edozvola-db-01' # Host name or IP address of destination 0 # for sending heartbeat signal. # (change requires restart) @@ -585,9 +586,9 @@ heartbeat_device0 = '' # and pgpool has root privilege. # (change requires restart) -#heartbeat_destination1 = 'host0_ip2' -#heartbeat_destination_port1 = 9694 -#heartbeat_device1 = '' +heartbeat_destination1 = 'edozvola-db-02' +heartbeat_destination_port1 = 9694 +heartbeat_device1 = '' # -- query mode -- @@ -618,9 +619,9 @@ wd_lifecheck_password = '' #other_wd_port0 = 9000 # Port number for othet watchdog 0 # (change requires restart) -#other_pgpool_hostname1 = 'host1' -#other_pgpool_port1 = 5432 -#other_wd_port1 = 9000 +other_pgpool_hostname0 = '__HOSTNAME__OTHER__' +other_pgpool_port0 = 5432 +other_wd_port0 = 9000 #------------------------------------------------------------------------------ diff --git a/t/1-init-cluster.sh b/t/1-init-cluster.sh index ae71391..ebced6d 100755 --- a/t/1-init-cluster.sh +++ b/t/1-init-cluster.sh @@ -19,7 +19,7 @@ ssh root@10.200.1.61 sh -xe /tmp/1.sh ssh root@10.200.1.62 sh -xe /tmp/1.sh -make push +make push-pg ssh root@10.200.1.61 ln -sf /var/lib/pgsql/9.6/data/postgresql.conf.master /var/lib/pgsql/9.6/data/postgresql.conf ssh root@10.200.1.61 sh -xe /tmp/2.sh diff --git a/t/2-init-pgpool.sh b/t/2-init-pgpool.sh index e8a1431..20c5e44 100755 --- a/t/2-init-pgpool.sh +++ b/t/2-init-pgpool.sh @@ -2,9 +2,38 @@ export PAGER='' +install() { + ssh root@$1 'test -d /var/log/pgpool || mkdir -p /var/log/pgpool' + cat <<__SHELL__ > /tmp/3.sh +if [ ! -d /etc/pgpool-II/sbin ] ; then + mkdir /etc/pgpool-II/sbin + cp /usr/sbin/ifconfig /etc/pgpool-II/sbin/ + cp /usr/sbin/arping /etc/pgpool-II/sbin/ + chmod -v 4755 /etc/pgpool-II/sbin/* +fi +__SHELL__ + scp /tmp/3.sh root@$1:/tmp/ + ssh root@$1 sh -x /tmp/3.sh + + # must be at end + ssh root@$1 rpm -q pgpool-II-pg96 && return + yes | ssh root@$1 yum install $2 http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-x86_64/pgpool-II-pg96-3.6.5-1pgdg.rhel7.x86_64.rpm +} + +install 10.200.1.61 +install 10.200.1.62 + +# create pcp.conf +#echo -n postgres:`pg_md5 postgres` >> /etc/pgpool-II/pcp.conf + +make push-pgpool + # init pgpool -ssh root@10.200.1.60 rm -f /var/log/pgpool/pgpool_status -echo 0 | ssh root@10.200.1.60 'cat > /tmp/postgres_master' -ssh root@10.200.1.60 systemctl restart pgpool -psql -h 10.200.1.60 -c 'show pool_nodes' postgres postgres +#ssh root@10.200.1.60 rm -f /var/log/pgpool/pgpool_status +#echo 0 | ssh root@10.200.1.60 'cat > /tmp/postgres_master' + +ssh root@10.200.1.61 systemctl restart pgpool +ssh root@10.200.1.62 systemctl restart pgpool + +psql -h 10.200.1.61 -p 9999 -c 'show pool_nodes' postgres postgres -- 2.20.1