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