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