1#!/usr/bin/env bash 2# 3# Copyright (c) 2013-2021 PgPool Global Development Group 4# 5# Permission to use, copy, modify, and distribute this software and 6# its documentation for any purpose and without fee is hereby 7# granted, provided that the above copyright notice appear in all 8# copies and that both that copyright notice and this permission 9# notice appear in supporting documentation, and that the name of the 10# author not be used in advertising or publicity pertaining to 11# distribution of the software without specific, written prior 12# permission. The author makes no representations about the 13# suitability of this software for any purpose. It is provided "as 14# is" without express or implied warranty. 15#------------------------------------------------------------------- 16# Set up watchdog enabled pgpool-II and PostgreSQL temporary 17# installation in current directory for *testing* purpose. Do not use 18# this tool for production environment! 19# Note that this script uses pgpool_setup as a work horse. 20# 21# usage: watchdog_setup [-wn num_pgpool][-wp watchdog_base_port][-m r|s|n|i][-n num_clusters][-p base_port][--no-stop][-d] 22# -wn num_pgpool: create num_pgpool pgpool nodes. The default is 3. Must be greater than 1. 23# -wp watchdog_base_port: starting port number. The default is 24# 50000. 25#------------------------------------------- 26# Configuration section 27#------------------------------------------- 28# Number of Pgpool-II installations. 29W_NUM_PGPOOL=${W_NUM_PGPOOL:-"3"} 30 31# Starting port number. 32W_BASE_PORT=${W_BASE_PORT:-"50000"} 33 34# Number of default PostgreSQL clusters 35NUMCLUSTERS=2 36 37# PostgreSQL startig port number. 38export PGBASEPORT=`expr $W_BASE_PORT + 1000` 39 40# number of ports used in a single pgpool-II installation. 41# (port, pcp_port, wd_port, wd_heartbeat_port) 42num_ports_per_node=4 43#------------------------------------------- 44# End of configuration section 45#------------------------------------------- 46# 47# user name 48WHOAMI=`whoami` 49 50# our root directory 51BASEDIR=`pwd` 52 53# location of pgpool_setup 54PGPOOL_SETUP=${PGPOOL_SETUP:-"$(dirname $0)/pgpool_setup"} 55 56# PostgreSQL bin directory 57PGBIN=${PGBIN:-"@@PGSQL_BIN_DIR@@"} 58INITDB=$PGBIN/initdb 59PG_CTL=$PGBIN/pg_ctl 60PSQL=$PGBIN/psql 61 62#------------------------------------------- 63# set postgresql.conf 64# argument: PostgreSQL database cluster directory 65#------------------------------------------- 66function set_postgresql_conf 67{ 68 PGCONF=$1/postgresql.conf 69 70 echo "listen_addresses = '*'" >> $PGCONF 71 echo "port = $PORT" >> $PGCONF 72 echo "logging_collector = on" >> $PGCONF 73 echo "log_filename = '%A.log'" >> $PGCONF 74 echo "log_line_prefix = '%p %t '" >> $PGCONF 75 echo "log_truncate_on_rotation = on" >> $PGCONF 76 echo "log_statement = 'all'" >> $PGCONF 77 echo "max_prepared_transactions = 10" >> $PGCONF 78 echo "unix_socket_directories = '$PGSOCKET_DIR'" >> $PGCONF 79 80 if [ $MODE = "s" ];then 81 echo "hot_standby = on" >> $PGCONF 82 echo "wal_level = hot_standby" >> $PGCONF 83 echo "max_wal_senders = $NUMCLUSTERS" >> $PGCONF 84 echo "archive_mode = on" >> $PGCONF 85 echo "archive_command = 'cp %p $BASEDIR/archivedir/%f </dev/null'" >> $PGCONF 86 else 87 echo "wal_level = archive" >> $PGCONF 88 echo "archive_mode = on" >> $PGCONF 89 echo "archive_command = 'cp %p $BASEDIR/archivedir/%f </dev/null'" >> $PGCONF 90 fi 91 92 ed $1/pg_hba.conf <<EOF 93/^#local *replication/s/^#//p 94/^#host *replication/s/^#//p 95/^#host *replication/s/^#//p 96w 97q 98EOF 99 100} 101 102#------------------------------------------- 103# set pgpool.conf 104# argument: absolute path to pgpool.conf 105#------------------------------------------- 106function set_pgpool_conf { 107 echo "sr_check_user = '$WHOAMI'" >> $CONF 108 echo "recovery_user = '$WHOAMI'" >> $CONF 109 echo "recovery_password = ''" >> $CONF 110 echo "recovery_1st_stage_command = 'basebackup.sh'" >> $CONF 111 112 if [ $MODE = "r" || $MODE = "i" ];then 113 echo "recovery_2nd_stage_command = 'pgpool_recovery_pitr'" >> $CONF 114 fi 115 116 echo "health_check_period = 10" >> $CONF 117 echo "health_check_user = '$WHOAMI'" >> $CONF 118 OIDDIR=$BASEDIR/log/pgpool/oiddir 119 mkdir -p $OIDDIR 120 echo "memqcache_oiddir = '$OIDDIR'" >> $CONF 121 echo "log_per_node_statement = on" >> $CONF 122 123 if [ $MODE = "s" ];then 124 echo "failover_command = '$FAILOVER_SCRIPT %d %h %p %D %m %M %H %P %r %R'" >> $CONF 125 fi 126 127 echo "socket_dir = '$PGSOCKET_DIR'" >> $CONF 128 echo "pcp_socket_dir = '$PGSOCKET_DIR'" >> $CONF 129} 130 131#------------------------------------------- 132# wait for pgpool comes up 133#------------------------------------------- 134function wait_for_pgpool_startup { 135 timeout=20 136 137 while [ $timeout -gt 0 ] 138 do 139 $PSQL -p $PGPOOL_PORT -c "show pool_nodes" postgres >/dev/null 2>&1 140 if [ $? = 0 ];then 141 # echo "pgpool-II comes up after `expr 20 - $timeout` seconds" 142 break; 143 fi 144 timeout=`expr $timeout - 1` 145 sleep 1 146 done 147} 148 149#------------------------------------------- 150# wait for pgpool reload finished 151#------------------------------------------- 152function wait_for_pgpool_reload { 153 timeout=20 154 num_node=$1 155 156 while [ $timeout -gt 0 ] 157 do 158 N=`$PSQL -p $PGPOOL_PORT -c "show pool_status" test | grep backend_data | wc -l` 159 if [ $N = $num_node ];then 160 break; 161 fi 162 timeout=`expr $timeout - 1` 163 sleep 1 164 done 165} 166 167#------------------------------------------- 168# Set watchdog params to pgpool.conf 169#------------------------------------------- 170function set_watchdog_params { 171 id=$1 172 num_pgpool=$2 173 base_port=$3 174 priority=`expr $num_pgpool - $id` 175 n=0 176 conf=etc/pgpool.conf 177 node_id_file=etc/pgpool_node_id 178 179 cat >> $conf <<EOF 180use_watchdog = on 181wd_interval = 1 182wd_priority = $priority 183EOF 184 185 while [ $n -lt $num_pgpool ] 186 do 187 pgpool_port=`expr $base_port + \( $n \* $num_ports_per_node \)` 188 pcp_port=`expr $pgpool_port + 1` 189 wd_port=`expr $pcp_port + 1` 190 191 wd_heartbeat_port=`expr $wd_port + 1` 192 echo "hostname$n = 'localhost'" >> $conf 193 echo "pgpool_port$n = $pgpool_port" >> $conf 194 echo "wd_port$n = $wd_port" >> $conf 195 echo "heartbeat_hostname$n = 'localhost'" >> $conf 196 echo "heartbeat_port$n = $wd_heartbeat_port" >> $conf 197 n=`expr $n + 1` 198 done 199 200 echo "$id" >> $node_id_file 201} 202 203################################################################################# 204# 205# main script 206# 207################################################################################ 208function usage() 209{ 210 echo "usage: $0 [-wn num_pgpool][-wp watchdog_base_port][-m r|s|n|i] [-n num_clusters] [-p base_port] [-pg pg_base_port][--no-stop] [-d]";exit 1 211} 212 213#------------------------------------------- 214# Argument check 215# usage: $0 [-wn num_pgpool][-wp watchdog_base_port][-m r|s|n][-n num_clusters][-p base_port][-pg pg_base_port][--no-stop][-d] 216#------------------------------------------- 217# 218# default mode is streaming replication mode 219MODE="s" 220NO_STOP="false" 221 222while [ $# -gt 0 ] 223do 224 if [ $1 = "-wn" ];then 225 shift 226 W_NUM_PGPOOL=$1 227 if [ "$W_NUM_PGPOOL" -le 1 ];then 228 echo "number of Pgpool-II nodes must be greater than 1" 229 exit 1 230 fi 231 elif [ $1 = "-wp" ];then 232 shift 233 W_BASE_PORT=$1 234 PGBASEPORT=`expr $W_BASE_PORT + 1000` 235 236 # rest are pgpool_setup args 237 238 elif [ $1 = "-m" ];then 239 shift 240 case $1 in 241 r ) MODE="r";; 242 s ) MODE="s";; 243 n ) MODE="n";; 244 i ) MODE="i";; 245 * ) usage;; 246 esac 247 elif [ $1 = "-n" ];then 248 shift 249 export NUMCLUSTERS=$1 250 elif [ $1 = "-p" ];then 251 shift 252 export BASEPORT=$1 253 elif [ $1 = "-pg" ];then 254 shift 255 export PGBASEPORT=$1 256 elif [ $1 = "--no-stop" ];then 257 shift 258 NO_STOP="true" 259 elif [ $1 = "-d" ];then 260 export PGPOOLDEBUG="true" 261 shift; 262 elif [ $1 = "--help" -o $1 = "-o" ];then 263 usage 264 exit 265 else 266 usage 267 exit 268 fi 269 shift 270done 271 272#------------------------------------------- 273# Make sure that current directory is empty 274#------------------------------------------- 275if [ "`/bin/ls`" != "" ] 276then 277 echo "$0: Current directory is not empty. Please remove files and directories then try again." 278 exit 1 279fi 280 281#------------------------------------------- 282# everything looks good. starting setup... 283#------------------------------------------- 284echo "Starting set up " 285 286#------------------------------------------- 287# Run pgpool_setup 288#------------------------------------------- 289 290STARTALL=$BASEDIR/startall 291SHUTDOWNALL=$BASEDIR/shutdownall 292 293cnt=0 294 295while [ $cnt -lt $W_NUM_PGPOOL ] 296do 297 echo "============= setting up pgpool $cnt =============" 298 mkdir pgpool$cnt 299 cd pgpool$cnt 300 port=`expr $W_BASE_PORT + \( $cnt \* $num_ports_per_node \)` 301 $PGPOOL_SETUP -m $MODE -p $port -pg $PGBASEPORT 302 set_watchdog_params $cnt $W_NUM_PGPOOL $W_BASE_PORT 303 304 if [ $cnt != 0 ] 305 then 306 # remove "pg_ctl start" line from startall/shutdownall script in other than pgpool0 307 sed -i '/.*data.*/d' startall 308 sed -i '/.*data.*/d' shutdownall 309 310 # change database cluster directory symlink to pgpool0. 311 # The database cluster entity only resides in pgpool0. 312 n=0 313 while [ $n -lt $NUMCLUSTERS ] 314 do 315 rm -fr data$n 316 ln -s ../pgpool0/data$n . 317 n=`expr $n + 1` 318 done 319 fi 320 321 echo "cd pgpool$cnt" >> $STARTALL 322 echo "./startall" >> $STARTALL 323 echo "cd .." >> $STARTALL 324 echo "cd pgpool$cnt" >> $SHUTDOWNALL 325 echo "./shutdownall" >> $SHUTDOWNALL 326 echo "cd .." >> $SHUTDOWNALL 327 328 cd .. 329 cnt=`expr $cnt + 1` 330done 331 332chmod 755 $STARTALL 333chmod 755 $SHUTDOWNALL 334