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