1#!/usr/bin/env bash 2# 3# Copyright (c) 2013-2020 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 pgpool-II and PostgreSQL temporary installation in current 17# directory for *testing* purpose. 18# Do not use this tool for production environment! 19# 20# usage: pgpool_setup [-m r|s|n][-n num_clusters][-p base_port][-pg pg_base_port][--no-stop][-d] 21# -m s: create an installation as streaming replication mode. 22# (the default) 23# -m r: create an installation as native replication mode. 24# -m n: create an installation as raw mode. 25# -n num_clusters: create num_clusters PostgreSQL database cluster nodes 26# -p base_port: specify base port. pgpool port is base_port. 27# pcp port is base_port + 1. The first PostgreSQL node's port is 28# base_port + 2, second PostgreSQL node's port is base_port + 3 29# and so on. 30# if -pg option is specified, the first PostgreSQL node's port is 31# assigned to pg_pase_port, the second PostgreSQL node's port is 32# pg_base_port + 1 and so on. 33# --no-stop: do not stop pgpool and PostgreSQL after the work 34# -d: start pgpool with debug mode 35# 36# The user run this script will become the PostgreSQL super user as 37# well. Current directory must be empty. Assume that appropreate 38# pgpool and PostgreSQL binaries are in the command search path. 39# The user must have passwordless access to localhost over SSH. 40# 41# Rayout after executing this script: 42# data[0-]: PostgreSQL database clusters 43# log: pgpool.log and pgpool_status resides here 44# run: pgpool-II pid file resides here(generated by pgpool-II) 45# etc/pgpool.conf: pgpool-II configuration file 46# etc/pool_passwd: generated by pgpool-II 47# ./startall: a script to start pgpool-II and all PostgreSQL servers 48# ./shutdownall: a script to shutdown pgpool-II and all PostgreSQL servers 49# 50# test database "test" is created. 51# pcp username and password is set to the person who executed this script. 52# 53#------------------------------------------- 54# Configuration section 55#------------------------------------------- 56# Starting port number to be used. Each PostgreSQL is assigned 57# $BASEPORT + 2, $BASEPORT + 3 and so on. 58# pgpool port and pcp_port will be assigned to $BASEPORT and $BASEPORT +1 . 59BASEPORT=${BASEBOPRT:-"11000"} 60ORIGBASEPORT=$BASEPORT 61# PostgreSQL startig port number. 62PGBASEPORT=`expr $BASEPORT + 2` 63# Default number of PostgreSQL database clusters 64NUMCLUSTERS=${NUMCLUSTERS:-"2"} 65# Where to look for pgpool.conf.sample* 66PGPOOL_INSTALL_DIR=${PGPOOL_INSTALL_DIR:-"@@PGPOOL_INSTALL_DIR@@"} 67PGPOOLDIR=${PGPOOLDIR:-"@@PGPOOL_CONFIG_DIR@@"} 68# PostgreSQL commands(initdb, pg_ctl, psql) install dir 69PGBIN=${PGBIN:-"@@PGSQL_BIN_DIR@@"} 70# LD_LIBRARY_PATH 71LPATH=${PGLIB:-"@@PGSQL_LIB_DIR@@"} 72# unix socket directory 73PGSOCKET_DIR=${PGSOCKET_DIR:-"/tmp"} 74# initdb args 75INITDBARG="--no-locale -E UTF_8" 76# Check TIME_WAIT in shutdownall script 77CHECK_TIME_WAIT=${CHECK_TIME_WAIT:-"false"} 78#------------------------------------------- 79# End of configuration section 80#------------------------------------------- 81# 82# user name 83WHOAMI=`whoami` 84 85# our root directory 86BASEDIR=`pwd` 87 88# PostgreSQL bin directory 89INITDB=$PGBIN/initdb 90PG_CTL=$PGBIN/pg_ctl 91PSQL=$PGBIN/psql 92 93# get PostgreSQL major version 94vstr=`$INITDB -V|awk '{print $3}'|sed 's/\./ /g'` 95#vstr="12beta1" 96#vstr="9.1.24" 97#vstr="11.1" 98 99# check if alpha or beta 100echo $vstr|egrep "[a-z]" > /dev/null 101if [ $? = 0 ];then 102 vstr=`echo $vstr|sed 's/\([0-9]*\).*/\1/'` 103 major1=`echo $vstr|awk '{print $1}'` 104 major2=`echo $vstr|awk '{print $2}'` 105 if [ -z $major2 ];then 106 major2=0 107 fi 108else 109 vstr=`echo $vstr|sed 's/\./ /g'` 110 major1=`echo $vstr|awk '{print $1}'` 111 112 if [ $major1 -ge 10 ];then 113 major2=0 114 else 115 major2=`echo $vstr|awk '{print $2}'` 116 fi 117fi 118major1=`expr $major1 \* 10` 119PGVERSION=`expr $major1 + $major2` 120echo PostgreSQL major version: $PGVERSION 121 122if [ $PGVERSION -gt 91 ];then 123 INITDBARG="$INITDBARG --data-checksums" 124fi 125 126# pgpool-II configuration file localtion. 127CONF=$BASEDIR/etc/pgpool.conf 128# failover script 129FAILOVER_SCRIPT=$BASEDIR/etc/failover.sh 130# follow master script 131FOLLOW_MASTER_SCRIPT=$BASEDIR/etc/follow_master.sh 132# pgpool_remote_start 133PGPOOL_REMOTE_START_SCRIPT=pgpool_remote_start 134# Start script name. This will be generated in this script. 135STARTALL=$BASEDIR/startall 136# Shutdown script name. This will be generated in this script. 137SHUTDOWNALL=$BASEDIR/shutdownall 138PCP_PASS_FILE=$BASEDIR/pcppass 139# pgpool reload script name. This will be generated in this script. 140PGPOOL_RELOAD=$BASEDIR/pgpool_reload 141 142export PGHOST=$PGSOCKET_DIR 143 144#------------------------------------------- 145# create failover script 146#------------------------------------------- 147function create_failover_script() 148{ 149cat >> $FAILOVER_SCRIPT <<'EOF' 150#! /bin/sh 151# Execute command by failover. 152# special values: %d = node id 153# %h = host name 154# %p = port number 155# %D = database cluster path 156# %m = new master node id 157# %M = old master node id 158# %H = new master node host name 159# %P = old primary node id 160# %R = new master database cluster path 161# %r = new master port number 162# %% = '%' character 163failed_node_id=$1 164failed_host_name=$2 165failed_port=$3 166failed_db_cluster=$4 167new_master_id=$5 168old_master_id=$6 169new_master_host_name=$7 170old_primary_node_id=$8 171new_master_port_number=$9 172new_master_db_cluster=${10} 173mydir=__MYDIR__ 174log=$mydir/log/failover.log 175pg_ctl=__PGBIN__/pg_ctl 176cluster0=$mydir/data0 177cluster1=$mydir/data1 178 179date >> $log 180echo "failed_node_id $failed_node_id failed_host_name $failed_host_name failed_port $failed_port failed_db_cluster $failed_db_cluster new_master_id $new_master_id old_master_id $old_master_id new_master_host_name $new_master_host_name old_primary_node_id $old_primary_node_id new_master_port_number $new_master_port_number new_master_db_cluster $new_master_db_cluster" >> $log 181 182# check if all node is down 183if [ $new_master_id = "-1" ];then 184 echo "no new master node is available" >>$log 185 exit 0 186fi 187 188if [ a"$failed_node_id" = a"$old_primary_node_id" ];then # master failed 189! new_primary_db_cluster=${mydir}/data"$new_master_id" 190 echo $pg_ctl -D $new_primary_db_cluster promote >>$log # let standby take over 191 $pg_ctl -D $new_primary_db_cluster promote >>$log # let standby take over 192fi 193EOF 194 195#------------------------------------------- 196# replace some variables in the script 197#------------------------------------------- 198/bin/sed -i \ 199 -e "/__MYDIR__/s@__MYDIR__@$BASEDIR@" \ 200 -e "/__PGBIN__/s@__PGBIN__@$PGBIN@" \ 201 $FAILOVER_SCRIPT 202 203chmod 755 $FAILOVER_SCRIPT 204} 205 206#------------------------------------------- 207# create follow master script 208#------------------------------------------- 209function create_follow_master_script() 210{ 211cat >> $FOLLOW_MASTER_SCRIPT <<'EOF' 212#! /bin/sh 213# Execute command by failover. 214# special values: %d = node id 215# %h = host name 216# %p = port number 217# %D = database cluster path 218# %m = new master node id 219# %M = old master node id 220# %H = new master node host name 221# %P = old primary node id 222# %R = new master database cluster path 223# %r = new master port number 224# %% = '%' character 225failed_node_id=$1 226failed_host_name=$2 227failed_port=$3 228failed_db_cluster=$4 229new_master_id=$5 230old_master_id=$6 231new_master_host_name=$7 232old_primary_node_id=$8 233new_master_port_number=$9 234new_master_db_cluster=${10} 235mydir=__MYDIR__ 236log=$mydir/log/failover.log 237pg_ctl=__PGBIN__/pg_ctl 238cluster0=$mydir/data0 239cluster1=$mydir/data1 240cluster2=$mydir/data2 241PCP_PORT=__PCPPORT__ 242export PCPPASSFILE=__PCPPASSFILE__ 243 244date >> $log 245echo "failed_node_id $failed_node_id failed_host_name $failed_host_name failed_port $failed_port failed_db_cluster $failed_db_cluster new_master_id $new_master_id old_master_id $old_master_id new_master_host_name $new_master_host_name old_primary_node_id $old_primary_node_id new_master_port_number $new_master_port_number new_master_db_cluster $new_master_db_cluster" >> $log 246 247# Stop standby node if it's running 248$pg_ctl -D $failed_db_cluster status >/dev/null 2>&1 249if [ $? = 0 ] 250then 251 $pg_ctl -w -m f -D $failed_db_cluster stop >> $log 2>&1 252 sleep 10 253 # recovery the node 254 pcp_recovery_node -w -h localhost -p $PCP_PORT -n $failed_node_id >> $log 2>&1 255else 256 echo "$failed_db_cluster is not running. skipping follow master command." >> $log 257fi 258EOF 259 260#------------------------------------------- 261# replace some variables in the script 262#------------------------------------------- 263 264/bin/sed -i \ 265 -e "/__MYDIR__/s@__MYDIR__@$BASEDIR@" \ 266 -e "/__PGBIN__/s@__PGBIN__@$PGBIN@" \ 267 -e "/__PCPPASSFILE__/s@__PCPPASSFILE__@$PCP_PASS_FILE@" \ 268 -e "/__PCPPORT__/s/__PCPPORT__/$PCP_PORT/" \ 269 $FOLLOW_MASTER_SCRIPT 270 271chmod 755 $FOLLOW_MASTER_SCRIPT 272} 273 274#------------------------------------------- 275# create pgpool_remote_start script 276# argument: PostgreSQL database cluster directory 277#------------------------------------------- 278function create_pgpool_remote_start_script() 279{ 280cat >> $1/$PGPOOL_REMOTE_START_SCRIPT <<'EOF' 281#! /bin/sh 282# 283# start postmaster on the recoveried node 284# 285if [ $# -ne 2 ] 286then 287 echo "pgpool_remote_start remote_host remote_datadir" 288 exit 1 289fi 290 291DEST=$1 292DESTDIR=$2 293PGCTL=__PGBIN__/pg_ctl 294 295ssh -T $DEST $PGCTL -w -D $DESTDIR start 2>/dev/null 1>/dev/null < /dev/null & 296EOF 297 298#------------------------------------------- 299# replace some variables in the script 300#------------------------------------------- 301/bin/sed -i \ 302 -e "/__PGBIN__/s@__PGBIN__@$PGBIN@" \ 303 $1/$PGPOOL_REMOTE_START_SCRIPT 304 305chmod 755 $1/$PGPOOL_REMOTE_START_SCRIPT 306} 307 308#------------------------------------------- 309# set postgresql.conf 310# argument: PostgreSQL database cluster directory 311#------------------------------------------- 312function set_postgresql_conf 313{ 314 PGCONF=$1/postgresql.conf 315 316 echo "listen_addresses = '*'" >> $PGCONF 317 echo "port = $PORT" >> $PGCONF 318 echo "logging_collector = on" >> $PGCONF 319 echo "log_filename = '%A.log'" >> $PGCONF 320 echo "log_line_prefix = '%p %t '" >> $PGCONF 321 echo "log_truncate_on_rotation = on" >> $PGCONF 322 echo "log_statement = 'all'" >> $PGCONF 323 echo "max_prepared_transactions = 10" >> $PGCONF 324 echo "unix_socket_directories = '$PGSOCKET_DIR'" >> $PGCONF 325 326 if [ $PGVERSION -ge 120 ];then 327 echo "include_if_exists = 'myrecovery.conf'" >> $PGCONF 328 fi 329 330 if [ $MODE = "s" ];then 331 echo "hot_standby = on" >> $PGCONF 332 echo "wal_level = hot_standby" >> $PGCONF 333 echo "max_wal_senders = $NUMCLUSTERS" >> $PGCONF 334 echo "archive_mode = on" >> $PGCONF 335 echo "archive_command = 'cp %p $BASEDIR/archivedir/`basename $1`/%f </dev/null'" >> $PGCONF 336 elif [ $MODE = 'r' ];then 337 echo "wal_level = archive" >> $PGCONF 338 echo "archive_mode = on" >> $PGCONF 339 echo "archive_command = 'cp %p $BASEDIR/archivedir/`basename $1`/%f </dev/null'" >> $PGCONF 340 fi 341 342 ed $1/pg_hba.conf <<EOF 343/^#local *replication/s/^#//p 344/^#host *replication/s/^#//p 345/^#host *replication/s/^#//p 346w 347q 348EOF 349 350} 351 352#------------------------------------------- 353# create basebackup.sh for streaming replication mode 354# argument: PostgreSQL database cluster directory 355#------------------------------------------- 356function create_basebackup_stream { 357SCRIPT=basebackup.sh 358cat >> $1/$SCRIPT <<'EOF' 359#! /bin/sh 360psql=__PGBIN__/psql 361DATADIR_BASE=__DATADIR_BASE__ 362PGSUPERUSER=__PGSUPERUSER__ 363 364master_db_cluster=$1 365recovery_node_host_name=$2 366DEST_CLUSTER=$3 367PORT=$4 368 369log=$DATADIR_BASE/log/recovery.log 370touch standby.signal 371EOF 372 373echo "export PGHOST=$PGSOCKET_DIR" >> $1/$SCRIPT 374 375cat >> $1/$SCRIPT <<'EOF' 376$psql -p $PORT -c "SELECT pg_start_backup('Streaming Replication', true)" postgres 377 378echo "source: $master_db_cluster dest: $DEST_CLUSTER" > $log 379 380rsync -C -a -c --delete --exclude postgresql.conf --exclude postmaster.pid \ 381--exclude postmaster.opts --exclude pg_log \ 382--exclude recovery.conf --exclude recovery.done \ 383--exclude pg_xlog --exclude pg_wal \ 384--exclude log \ 385$master_db_cluster/ $DEST_CLUSTER/ 386 387rm -fr $DEST_CLUSTER/pg_xlog 388mkdir $DEST_CLUSTER/pg_xlog 389chmod 700 $DEST_CLUSTER/pg_xlog 390rm $DEST_CLUSTER/recovery.done 391 392rm -fr $DEST_CLUSTER/pg_wal 393mkdir $DEST_CLUSTER/pg_wal 394chmod 700 $DEST_CLUSTER/pg_wal 395 396rm $DEST_CLUSTER/recovery.done standby.signal 397rm standby.signal 398EOF 399 400if [ $PGVERSION -ge 120 ];then 401 cat >> $1/$SCRIPT <<'EOF' 402cat > $DEST_CLUSTER/myrecovery.conf <<REOF 403primary_conninfo = 'host=localhost port=$PORT user=$PGSUPERUSER' 404recovery_target_timeline='latest' 405restore_command = 'cp $DATADIR_BASE/archivedir/%f "%p" 2> /dev/null' 406REOF 407EOF 408 409else 410 cat >> $1/$SCRIPT <<'EOF' 411cat > $DEST_CLUSTER/recovery.conf <<REOF 412standby_mode = 'on' 413primary_conninfo = 'host=localhost port=$PORT user=$PGSUPERUSER' 414recovery_target_timeline='latest' 415restore_command = 'cp $DATADIR_BASE/archivedir/`basename $1`/%f "%p" 2> /dev/null' 416REOF 417EOF 418fi 419 420cat >> $1/$SCRIPT <<'EOF' 421$psql -p $PORT -c "SELECT pg_stop_backup()" postgres 422EOF 423 424#------------------------------------------- 425# replace some variables in the script 426#------------------------------------------- 427/bin/sed -i \ 428 -e "/__PGBIN__/s@__PGBIN__@$PGBIN@" \ 429 -e "/__DATADIR_BASE__/s@__DATADIR_BASE__@$BASEDIR@" \ 430 -e "/__PGSUPERUSER__/s/__PGSUPERUSER__/$WHOAMI/" \ 431$1/$SCRIPT 432 433chmod 755 $1/$SCRIPT 434} 435 436#------------------------------------------- 437# create basebackup.sh for native replication mode 438# argument: PostgreSQL database cluster directory 439#------------------------------------------- 440function create_basebackup_replication { 441SCRIPT=basebackup.sh 442cat >> $1/$SCRIPT <<'EOF' 443#! /bin/sh 444psql=__PGBIN__/psql 445DATADIR_BASE=__DATADIR_BASE__ 446PGSUPERUSER=__PGSUPERUSER__ 447EOF 448 449echo "export PGHOST=$PGSOCKET_DIR" >> $1/$SCRIPT 450 451cat >> $1/$SCRIPT <<'EOF' 452master_db_cluster=$1 453recovery_node_host_name=$2 454DEST_CLUSTER=$3 455PORT=$4 456 457log=$DATADIR_BASE/log/recovery.log 458 459$psql -p $PORT -c "SELECT pg_start_backup('Native Replication', true)" postgres 460 461echo "source: $master_db_cluster dest: $DEST_CLUSTER" > $log 462EOF 463 464if [ $PGVERSION -ge 120 ];then 465 cat >> $1/$SCRIPT <<'EOF' 466cat > $master_db_cluster/myrecovery.conf <<REOF 467restore_command = 'cp __ARCHDIR__/%f %p' 468REOF 469EOF 470 471else 472 473 cat >> $1/$SCRIPT <<'EOF' 474cat > $master_db_cluster/recovery.conf <<REOF 475restore_command = 'cp __ARCHDIR__/%f %p' 476REOF 477EOF 478fi 479 480cat >> $1/$SCRIPT <<'EOF' 481rsync -C -a -c --delete --exclude postgresql.conf --exclude postmaster.pid \ 482--exclude postmaster.opts --exclude pg_log \ 483--exclude recovery.done \ 484--exclude pg_xlog pg_wal \ 485$master_db_cluster/ $DEST_CLUSTER/ 486 487rm -fr $DEST_CLUSTER/pg_xlog 488mkdir $DEST_CLUSTER/pg_xlog 489chmod 700 $DEST_CLUSTER/pg_xlog 490 491rm -fr $DEST_CLUSTER/pg_wal 492mkdir $DEST_CLUSTER/pg_wal 493chmod 700 $DEST_CLUSTER/pg_wal 494EOF 495 496cat >> $1/$SCRIPT <<'EOF' 497$psql -p $PORT -c "SELECT pg_stop_backup()" postgres 498EOF 499 500if [ $PGVERSION -ge 120 ];then 501 cat >> $1/$SCRIPT <<'EOF' 502touch $DEST_CLUSTER/recovery.signal 503EOF 504fi 505 506#------------------------------------------- 507# replace some variables in the script 508#------------------------------------------- 509/bin/sed -i \ 510 -e "/__PGBIN__/s@__PGBIN__@$PGBIN@" \ 511 -e "/__DATADIR_BASE__/s@__DATADIR_BASE__@$BASEDIR@" \ 512 -e "/__PGSUPERUSER__/s/__PGSUPERUSER__/$WHOAMI/" \ 513 -e "/__ARCHDIR__/s@__ARCHDIR__@$BASEDIR/archivedir/\`basename \$1\`@" \ 514 $1/$SCRIPT 515 516chmod 755 $1/$SCRIPT 517} 518 519#------------------------------------------- 520# create pgpool_recovery_pitr (2nd stage script)for native replication mode 521# argument: PostgreSQL database cluster directory 522#------------------------------------------- 523function create_pgpool_recovery_pitr { 524SCRIPT=pgpool_recovery_pitr 525cat >> $1/$SCRIPT <<'EOF' 526#! /bin/sh 527psql=__PGBIN__/psql 528DATADIR_BASE=__DATADIR_BASE__ 529PGSUPERUSER=__PGSUPERUSER__ 530 531master_db_cluster=$1 532recovery_node_host_name=$2 533DEST_CLUSTER=$3 534PORT=$4 535 536log=$DATADIR_BASE/log/recovery.log 537EOF 538 539echo "export PGHOST=$PGSOCKET_DIR" >> $1/$SCRIPT 540 541cat >> $1/$SCRIPT <<'EOF' 542# Force to flush current value of sequences to xlog 543$psql -q -p $PORT -t -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn' template1| 544while read i 545do 546 if [ "$i" != "" ];then 547 $psql -p $PORT -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind = 'S'" $i 548 fi 549done 550 551$psql -p $PORT -c "SELECT pgpool_switch_xlog('__ARCHDIR__')" template1 552EOF 553#------------------------------------------- 554# replace some variables in the script 555#------------------------------------------- 556/bin/sed -i \ 557 -e "/__PGBIN__/s@__PGBIN__@$PGBIN@" \ 558 -e "/__DATADIR_BASE__/s@__DATADIR_BASE__@$BASEDIR@" \ 559 -e "/__PGSUPERUSER__/s/__PGSUPERUSER__/$WHOAMI/" \ 560 -e "/__ARCHDIR__/s@__ARCHDIR__@$BASEDIR/archivedir/\`basename \$1\`@" \ 561 $1/$SCRIPT 562 563chmod 755 $1/$SCRIPT 564} 565 566#------------------------------------------- 567# create initial recovery.conf 568# argument1: PostgreSQL database cluster directory 569# argument2: cluster No. we assume that data0 is primary 570#------------------------------------------- 571function create_recovery_conf { 572 if [ $PGVERSION -ge 120 ];then 573 fname=myrecovery.conf 574 elif [ $2 = "0" ];then 575 fname=recovery.done 576 else 577 fname=recovery.conf 578 fi 579 580 if [ $PGVERSION -lt 120 ];then 581 cat > $1/$fname <<EOF 582standby_mode = 'on' 583EOF 584 fi 585 586 cat > $1/$fname <<EOF 587primary_conninfo = 'host=localhost port=$BASEPORT user=$WHOAMI' 588recovery_target_timeline='latest' 589restore_command = 'cp $BASEDIR/archivedir/`basename $1`/%f "%p" 2> /dev/null' 590EOF 591} 592 593function set_pool_hba_conf { 594 POOL_HBACONF=$1 595 sed -i '/host.*all.*all.*trust$/s/^/#/g' $POOL_HBACONF 596 sed -i '/local.*all.*all.*trust$/s/^/#/g' $POOL_HBACONF 597 598 if [ $PGVERSION -gt 100 ]; 599 then 600 echo "host all scram_user 0/0 scram-sha-256" >> $POOL_HBACONF 601 echo "host all md5_user 0/0 md5" >> $POOL_HBACONF 602 603 echo "local all scram_user scram-sha-256" >> $POOL_HBACONF 604 echo "local all md5_user md5" >> $POOL_HBACONF 605 fi 606 607 echo "local all all trust" >> $POOL_HBACONF 608 609 echo "host all all 127.0.0.1/32 trust" >> $POOL_HBACONF 610 echo "host all all ::1/128 trust" >> $POOL_HBACONF 611} 612 613#------------------------------------------- 614# set pgpool.conf 615# argument: absolute path to pgpool.conf 616#------------------------------------------- 617function set_pgpool_conf { 618 echo "sr_check_user = '$WHOAMI'" >> $CONF 619 echo "recovery_user = '$WHOAMI'" >> $CONF 620 echo "recovery_password = ''" >> $CONF 621 echo "recovery_1st_stage_command = 'basebackup.sh'" >> $CONF 622 623 if [ $MODE = "r" ];then 624 echo "recovery_2nd_stage_command = 'pgpool_recovery_pitr'" >> $CONF 625 fi 626 627 echo "health_check_period = 10" >> $CONF 628 echo "health_check_user = '$WHOAMI'" >> $CONF 629 OIDDIR=$BASEDIR/log/pgpool/oiddir 630 mkdir -p $OIDDIR 631 echo "memqcache_oiddir = '$OIDDIR'" >> $CONF 632 echo "log_per_node_statement = on" >> $CONF 633 634 if [ $MODE = "s" ];then 635 echo "failover_command = '$FAILOVER_SCRIPT %d %h %p %D %m %M %H %P %r %R'" >> $CONF 636 fi 637 638 echo "socket_dir = '$PGSOCKET_DIR'" >> $CONF 639 echo "pcp_socket_dir = '$PGSOCKET_DIR'" >> $CONF 640} 641 642#------------------------------------------- 643# wait for pgpool comes up 644#------------------------------------------- 645function wait_for_pgpool_startup { 646 timeout=20 647 648 while [ $timeout -gt 0 ] 649 do 650 $PSQL -p $PGPOOL_PORT -c "show pool_nodes" postgres >/dev/null 2>&1 651 if [ $? = 0 ];then 652# echo "pgpool-II comes up after `expr 20 - $timeout` seconds" 653 break; 654 fi 655 timeout=`expr $timeout - 1` 656 sleep 1 657 done 658} 659 660#------------------------------------------- 661# wait for pgpool reload finished 662#------------------------------------------- 663function wait_for_pgpool_reload { 664 timeout=20 665 num_node=$1 666 667 while [ $timeout -gt 0 ] 668 do 669 N=`$PSQL -p $PGPOOL_PORT -c "show pool_status" test | grep backend_data | wc -l` 670 if [ $N = $num_node ];then 671 break; 672 fi 673 timeout=`expr $timeout - 1` 674 sleep 1 675 done 676} 677 678################################################################################# 679# 680# main script 681# 682################################################################################ 683function usage() 684{ 685 echo "usage: $0 [-m r|s|n] [-n num_clusters] [-p base_port] [-pg pg_base_port][--no-stop] [-d]";exit 1 686} 687 688#------------------------------------------- 689# Argument check 690# usage: $0 [-m r|s|n][-n num_clusters][-p base_port][-pg pg_base_port][--no-stop][-d] 691#------------------------------------------- 692# 693# default mode is streaming replication mode 694MODE="s" 695NO_STOP="false" 696 697while [ $# -gt 0 ] 698do 699 if [ $1 = "-m" ];then 700 shift 701 case $1 in 702 r ) MODE="r";; 703 s ) MODE="s";; 704 n ) MODE="n";; 705 * ) usage;; 706 esac 707 elif [ $1 = "-n" ];then 708 shift 709 NUMCLUSTERS=$1 710 elif [ $1 = "-p" ];then 711 shift 712 BASEPORT=$1 713 ORIGBASEPORT=$1 714 elif [ $1 = "-pg" ];then 715 shift 716 PGBASEPORT=$1 717 elif [ $1 = "--no-stop" ];then 718 shift 719 NO_STOP="true" 720 elif [ $1 = "-d" ];then 721 PGPOOLDEBUG="true" 722 shift; 723 elif [ $1 = "--help" -o $1 = "-o" ];then 724 usage 725 exit 726 else 727 usage 728 exit 729 fi 730 shift 731done 732 733case $MODE in 734 r) MODENAME="native replication mode" 735 SAMPLE_CONF=$PGPOOLDIR/pgpool.conf.sample-replication 736 ;; 737 s ) MODENAME="streaming replication mode" 738 SAMPLE_CONF=$PGPOOLDIR/pgpool.conf.sample-stream 739 ;; 740 n ) MODENAME="raw mode" 741 SAMPLE_CONF=$PGPOOLDIR/pgpool.conf.sample 742 ;; 743esac 744 745#------------------------------------------- 746# Make sure that current directory is empty 747#------------------------------------------- 748if [ "`/bin/ls`" != "" ] 749then 750 echo "$0: Current directory is not empty. Please remove files and directories then try again." 751 exit 1 752fi 753 754exec 5> $BASEDIR/pgpool_setup.log 755 756#------------------------------------------- 757# everything looks good. starting setup... 758#------------------------------------------- 759echo "Starting set up in $MODENAME" 760 761#------------------------------------------- 762# assign base port for PostgreSQL 763#------------------------------------------- 764ORIG_BASEPORT=$BASEPORT 765BASEPORT=$PGBASEPORT 766 767#------------------------------------------- 768# install pgpool.conf 769#------------------------------------------- 770test ! -d etc && mkdir etc 771cp $SAMPLE_CONF $CONF 772 773#------------------------------------------- 774# create startall, shutdownall and pgpool_reload 775#------------------------------------------- 776echo "creating startall and shutdownall" 777echo "LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$LPATH" > $STARTALL 778echo 'dir=`pwd`' >> $STARTALL 779echo "PGPOOL_INSTALL_DIR=$PGPOOL_INSTALL_DIR" >> $STARTALL 780chmod 755 $STARTALL 781echo 'dir=`pwd`' > $SHUTDOWNALL 782echo "PGPOOL_INSTALL_DIR=$PGPOOL_INSTALL_DIR" >> $SHUTDOWNALL 783echo '$PGPOOL_INSTALL_DIR/bin/pgpool -f $dir/etc/pgpool.conf -m f stop && while [ -f $dir/run/pgpool.pid ];do sleep 1;done' >> $SHUTDOWNALL 784if [ $CHECK_TIME_WAIT != "false" ];then 785 echo "while netstat -a|grep $ORIGBASEPORT ;do sleep 1;done" >> $SHUTDOWNALL 786fi 787chmod 755 $SHUTDOWNALL 788echo 'dir=`pwd`' > $PGPOOL_RELOAD 789echo "PGPOOL_INSTALL_DIR=$PGPOOL_INSTALL_DIR" >> $PGPOOL_RELOAD 790echo '$PGPOOL_INSTALL_DIR/bin/pgpool -f $dir/etc/pgpool.conf reload' >> $PGPOOL_RELOAD 791chmod 755 $PGPOOL_RELOAD 792 793#------------------------------------------- 794# create failover script 795#------------------------------------------- 796echo "creating failover script" 797create_failover_script >&5 2>&1 798 799#------------------------------------------- 800# create each PostgreSQL cluster 801#------------------------------------------- 802n=0 803while [ $n -lt $NUMCLUSTERS ] 804do 805 CLUSTER="data"`expr $n` 806 CLUSTERDIR=$BASEDIR/$CLUSTER 807 PORT=`expr $BASEPORT + $n` 808 809 echo -n "creating database cluster $CLUSTERDIR..." 810 $INITDB -D $CLUSTERDIR $INITDBARG >&5 2>&1 811 echo "done." 812 813 # set postgresql.conf 814 echo "update postgresql.conf" 815 set_postgresql_conf $CLUSTERDIR >&5 2>&1 816 817 # create pgpool_remote_start script under cluster directory 818 echo "creating pgpool_remote_start" 819 create_pgpool_remote_start_script $CLUSTERDIR >&5 2>&1 820 821 echo "creating basebackup.sh" 822 # create basebackup.sh 823 if [ $MODE = 's' ];then 824 create_basebackup_stream $CLUSTERDIR >&5 2>&1 825 elif [ $MODE = 'r' ];then 826 create_basebackup_replication $CLUSTERDIR >&5 2>&1 827 create_pgpool_recovery_pitr $CLUSTERDIR >&5 2>&1 828 fi 829 830 # create recovery.conf or recovery.done if streaming replication 831 # mode 832 if [ $MODE = "s" ];then 833 echo "creating recovery.conf" 834 create_recovery_conf $CLUSTERDIR $n >&5 2>&1 835 fi 836 837 echo "$PG_CTL -D $CLUSTERDIR -m f stop" >> $SHUTDOWNALL 838 echo "$PG_CTL -w -D $CLUSTERDIR start" >> $STARTALL 839 840 n=`expr $n + 1` 841 842 echo "#$n port is $PORT" >> README.port 843 844 # create archive directory 845 test ! -d archivedir/$CLUSTER && mkdir -p archivedir/$CLUSTER 846 847done 848 849set_pgpool_conf $CONF 850 851PGPOOL_PORT=$ORIG_BASEPORT 852echo "port = $PGPOOL_PORT" >> $CONF 853PCP_PORT=`expr $PGPOOL_PORT + 1` 854echo "pcp_port = $PCP_PORT" >> $CONF 855 856#------------------------------------------- 857# create password file for pcp 858#------------------------------------------- 859echo "localhost:${PCP_PORT}:${WHOAMI}:${WHOAMI}" >> $PCP_PASS_FILE 860chmod 0600 $PCP_PASS_FILE 861 862test ! -d run && mkdir run 863echo "pid_file_name = '$BASEDIR/run/pgpool.pid'" >> $CONF 864test ! -d log && mkdir log 865echo "logdir = '$BASEDIR/log'" >> $CONF 866 867if [ "$PGPOOLDEBUG" = "true" ];then 868 echo '$PGPOOL_INSTALL_DIR/bin/pgpool -d -D -n -f $dir/etc/pgpool.conf -F $dir/etc/pcp.conf -a $dir/etc/pool_hba.conf 2>&1 | cat > $dir/log/pgpool.log &' >> $STARTALL 869else 870 echo '$PGPOOL_INSTALL_DIR/bin/pgpool -D -n -f $dir/etc/pgpool.conf -F $dir/etc/pcp.conf -a $dir/etc/pool_hba.conf 2>&1 | cat > $dir/log/pgpool.log &' >> $STARTALL 871fi 872 873# create pcp.conf 874 875if [ -f $PGPOOLDIR/pcp.conf.sample ];then 876 cp $PGPOOLDIR/pcp.conf.sample etc/pcp.conf 877fi 878echo -n "${WHOAMI}:" >> etc/pcp.conf 879$PGPOOL_INSTALL_DIR/bin/pg_md5 $WHOAMI >> etc/pcp.conf 880 881# create pool_passwd 882$PGPOOL_INSTALL_DIR/bin/pg_md5 -m -f etc/pgpool.conf -u $WHOAMI $WHOAMI 883 884#------------------------------------------- 885# if streaming replication mode, we need to create data1 and so on, by 886# using online recovery. 887#------------------------------------------- 888 889if [ $MODE = 's' ];then 890# temporarily start data0 cluster to create extensions 891 echo "temporarily start data0 cluster to create extensions" 892 $PG_CTL -w -D data0 start >&5 2>&1 893 $PSQL -p $BASEPORT template1 >&5 2>&1 <<EOF 894CREATE EXTENSION pgpool_regclass; 895CREATE EXTENSION pgpool_recovery; 896CREATE DATABASE test; 897EOF 898 899 n=0 900 PORT=$BASEPORT 901 CLUSTER="data"`expr $n` 902 CLUSTERDIR=$BASEDIR/$CLUSTER 903 echo "backend_hostname$n = '$PGSOCKET_DIR'" >> $CONF 904 echo "backend_port$n = $PORT" >> $CONF 905 echo "backend_weight$n = 1" >> $CONF 906 echo "backend_data_directory$n = '$CLUSTERDIR'" >> $CONF 907 908# temporarily start pgpool 909 echo "temporarily start pgpool-II to create standby nodes" 910 $PGPOOL_INSTALL_DIR/bin/pgpool -D -n -f $BASEDIR/etc/pgpool.conf -F $BASEDIR/etc/pcp.conf -a $BASEDIR/etc/pool_hba.conf > $BASEDIR/log/pgpool.log 2>&1 & 911 912 wait_for_pgpool_startup 913 914 if [ $NUMCLUSTERS -gt 1 ];then 915 n=1 916 while [ $n -lt $NUMCLUSTERS ] 917 do 918 # create archive directory 919 test ! -d $BASEDIR/archivedir/`basename $CLUSTER` && mkdir $BASEDIR/archivedir/`basename $CLUSTER` 920 921 # set up pgpool.conf 922 PORT=`expr $PORT + 1` 923 echo "backend_hostname$n = '$PGSOCKET_DIR'" >> $CONF 924 echo "backend_port$n = $PORT" >> $CONF 925 echo "backend_weight$n = 1" >> $CONF 926 CLUSTER="data"`expr $n` 927 CLUSTERDIR=$BASEDIR/$CLUSTER 928 echo "backend_data_directory$n = '$CLUSTERDIR'" >> $CONF 929 n=`expr $n + 1` 930 done 931 $PGPOOL_INSTALL_DIR/bin/pgpool -f $BASEDIR/etc/pgpool.conf reload 932 fi 933 934 wait_for_pgpool_reload $NUMCLUSTERS 935$PSQL -p $PGPOOL_PORT -c "show pool_nodes" test 936export PCPPASSFILE=$PCP_PASS_FILE 937# recovery data1 and so on 938 n=1 939 while [ $n -lt $NUMCLUSTERS ] 940 do 941 echo -n "recovery node $n..." 942 $PGPOOL_INSTALL_DIR/bin/pcp_recovery_node -w -h localhost -p $PCP_PORT -n $n 943 echo "done." 944 n=`expr $n + 1` 945 wait_for_pgpool_startup 946 done 947 948# 949# replication mode 950# 951else 952 n=0 953 PORT=$BASEPORT 954 CLUSTER="data"`expr $n` 955 CLUSTERDIR=$BASEDIR/$CLUSTER 956 957 while [ $n -lt $NUMCLUSTERS ] 958 do 959 # set up pgpool.conf 960 echo "backend_hostname$n = '$PGSOCKET_DIR'" >> $CONF 961 echo "backend_port$n = $PORT" >> $CONF 962 echo "backend_weight$n = 1" >> $CONF 963 CLUSTER="data"`expr $n` 964 CLUSTERDIR=$BASEDIR/$CLUSTER 965 echo "backend_data_directory$n = '$CLUSTERDIR'" >> $CONF 966 PORT=`expr $PORT + 1` 967 n=`expr $n + 1` 968 done 969 970 echo "start all" 971 $STARTALL >&5 2>&1 972 echo -n "waiting for pgpool-II coming up..." 973 wait_for_pgpool_startup 974# sleep 20 975 echo "done." 976fi 977 978if [ $MODE = "r" -o $MODE = "n" ];then 979 echo "create extensions" 980 $PSQL -p $PGPOOL_PORT template1 >&5 2>&1 <<EOF 981CREATE EXTENSION pgpool_regclass; 982CREATE EXTENSION pgpool_recovery; 983CREATE DATABASE test; 984EOF 985fi 986 987#------------------------------------------- 988# create follow_masterfailover script 989#------------------------------------------- 990if [ $MODE = "s" ];then 991 echo "creating follow master script" 992 create_follow_master_script >&5 2>&1 993 echo "follow_master_command = '$FOLLOW_MASTER_SCRIPT %d %h %p %D %m %M %H %P %r %R'" >> $CONF 994fi 995 996$PSQL -p $PGPOOL_PORT test <<EOF 997show pool_nodes; 998EOF 999 1000echo "pgpool port is $PGPOOL_PORT" >> README.port 1001echo "pcp port is $PCP_PORT" >> README.port 1002 1003if [ $NO_STOP = "false" ];then 1004 echo "shutdown all" 1005 $SHUTDOWNALL >&5 2>&1 1006fi 1007 1008echo "export PGPOOL_PORT=$PGPOOL_PORT" > bashrc.ports 1009echo "export PCP_PORT=$PCP_PORT" >> bashrc.ports 1010echo "export PCPPASSFILE=$PCP_PASS_FILE" >> bashrc.ports 1011chmod 755 bashrc.ports 1012 1013echo 1014echo "pgpool-II setting for $MODENAME is done." 1015echo "To start the whole system, use ${STARTALL}." 1016echo "To shutdown the whole system, use ${SHUTDOWNALL}." 1017echo "pcp command user name is \"$WHOAMI\", password is \"$WHOAMI\"." 1018echo "Each PostgreSQL, pgpool-II and pcp port is as follows:" 1019cat README.port 1020echo "The info above is in README.port." 1021 1022if [ $NO_STOP = "true" ];then 1023 echo "CAUTION: whole system is still running." 1024fi 1025