#!/usr/bin/env bash # # Copyright (c) 2013-2022 PgPool Global Development Group # # Permission to use, copy, modify, and distribute this software and # its documentation for any purpose and without fee is hereby # granted, provided that the above copyright notice appear in all # copies and that both that copyright notice and this permission # notice appear in supporting documentation, and that the name of the # author not be used in advertising or publicity pertaining to # distribution of the software without specific, written prior # permission. The author makes no representations about the # suitability of this software for any purpose. It is provided "as # is" without express or implied warranty. #------------------------------------------------------------------- # Set up pgpool-II and PostgreSQL temporary installation in current # directory for *testing* purpose. # Do not use this tool for production environment! # # usage: pgpool_setup [-m r|s|n|l][-n num_clusters][-p base_port][-pg pg_base_port][--no-stop][-d][-s][-r][-e][-t][-c] # -m s: create an installation as streaming replication mode. # (the default) # -m r: create an installation as native replication mode. # -m n: create an installation as raw mode. # -m l: create an installation as logical replication mode. # -m y: create an installation as slony mode. # -m i: create an installation as snapshot isolation mode. # -n num_clusters: create num_clusters PostgreSQL database cluster nodes # -p base_port: specify base port. pgpool port is base_port. # pcp port is base_port + 1. The first PostgreSQL node's port is # base_port + 2, second PostgreSQL node's port is base_port + 3 # and so on. # if -pg option is specified, the first PostgreSQL node's port is # assigned to pg_pase_port, the second PostgreSQL node's port is # pg_base_port + 1 and so on. # --no-stop: do not stop pgpool and PostgreSQL after the work # -d: start pgpool with debug mode # -s: use replication slot, rather than wal archive. # -r: use pg_rewind for base backup if possible. # -e: do not create PostgreSQL clusters # -t: enable testing facility # # The user run this script will become the PostgreSQL super user as # well. Current directory must be empty. Assume that appropriate # pgpool and PostgreSQL binaries are in the command search path. # The user must have passwordless access to localhost over SSH. # # Layout after executing this script: # data[0-]: PostgreSQL database clusters # log: pgpool.log and pgpool_status resides here # run: pgpool-II pid file resides here(generated by pgpool-II) # etc/pgpool.conf: pgpool-II configuration file # etc/pool_passwd: generated by pgpool-II # ./startall: a script to start pgpool-II and all PostgreSQL servers # ./shutdownall: a script to shutdown pgpool-II and all PostgreSQL servers # # test database "test" is created. # pcp username and password is set to the person who executed this script. # #------------------------------------------- # Configuration section #------------------------------------------- # Starting port number to be used. Each PostgreSQL is assigned # $BASEPORT + 2, $BASEPORT + 3 and so on. # pgpool port and pcp_port will be assigned to $BASEPORT and $BASEPORT +1 . BASEPORT=${BASEPORT:-"11000"} ORIGBASEPORT=$BASEPORT # PostgreSQL startig port number. PGBASEPORT=`expr $BASEPORT + 2` # Default number of PostgreSQL database clusters NUMCLUSTERS=${NUMCLUSTERS:-"2"} # Where to look for pgpool.conf.sample PGPOOL_INSTALL_DIR=${PGPOOL_INSTALL_DIR:-"@@PGPOOL_INSTALL_DIR@@"} PGPOOLDIR=${PGPOOLDIR:-"@@PGPOOL_CONFIG_DIR@@"} # PostgreSQL commands(initdb, pg_ctl, psql) install dir PGBIN=${PGBIN:-"@@PGSQL_BIN_DIR@@"} # LD_LIBRARY_PATH LPATH=${PGLIB:-"@@PGSQL_LIB_DIR@@"} # unix socket directories PGSOCKET_DIR=${PGSOCKET_DIR:-"/tmp,/var/run/postgresql"} # remove inaccessible diretories from PGSOCKET_DIR tmp=`echo $PGSOCKET_DIR|sed "s/,/ /"` dir="" first=true for i in $tmp do if [ -w $i ];then if [ $first != "true" ];then dir="$dir,$i" else dir=$i fi fi first=false done PGSOCKET_DIR=$dir # initdb args INITDBARG="--no-locale -E UTF_8" # Use replication slot USE_REPLICATION_SLOT=${USE_REPLICATION_SLOT:-"false"} # Use pg_rewind USE_PG_REWIND=${USE_PG_REWIND:-"false"} # Check TIME_WAIT in shutdownall script CHECK_TIME_WAIT=${CHECK_TIME_WAIT:-"false"} # Test RPMs using sample scripts and config files TEST_SAMPLES=${TEST_SAMPLES:-"false"} #------------------------------------------- # End of configuration section #------------------------------------------- # # user name WHOAMI=`whoami` # our root directory BASEDIR=`pwd` # PostgreSQL bin directory INITDB=$PGBIN/initdb PG_CTL=$PGBIN/pg_ctl PSQL=$PGBIN/psql # get PostgreSQL major version vstr=`$INITDB -V|awk '{print $3}'|sed 's/\./ /g'` #vstr="12beta1" #vstr="9.1.24" #vstr="11.1" # check if alpha or beta echo $vstr|egrep "[a-z]" > /dev/null if [ $? = 0 ];then vstr=`echo $vstr|sed 's/\([0-9]*\).*/\1/'` major1=`echo $vstr|awk '{print $1}'` major2=`echo $vstr|awk '{print $2}'` if [ -z $major2 ];then major2=0 fi else vstr=`echo $vstr|sed 's/\./ /g'` major1=`echo $vstr|awk '{print $1}'` if [ $major1 -ge 10 ];then major2=0 else major2=`echo $vstr|awk '{print $2}'` fi fi major1=`expr $major1 \* 10` PGVERSION=`expr $major1 + $major2` echo PostgreSQL major version: $PGVERSION if [ $PGVERSION -gt 91 ];then INITDBARG="$INITDBARG --data-checksums" fi # pgpool-II configuration file location. CONF=$BASEDIR/etc/pgpool.conf # failover script FAILOVER_SCRIPT=$BASEDIR/etc/failover.sh # follow primary script FOLLOW_PRIMARY_SCRIPT=$BASEDIR/etc/follow_primary.sh # pgpool_remote_start PGPOOL_REMOTE_START_SCRIPT=pgpool_remote_start # Start script name. This will be generated in this script. STARTALL=$BASEDIR/startall # Shutdown script name. This will be generated in this script. SHUTDOWNALL=$BASEDIR/shutdownall PCP_PASS_FILE=$BASEDIR/pcppass # pgpool reload script name. This will be generated in this script. PGPOOL_RELOAD=$BASEDIR/pgpool_reload export PGHOST=localhost #------------------------------------------- # create failover script #------------------------------------------- function create_failover_script() { # If $TEST_SAMPLES = "true", use sample scripts and config files if [ $TEST_SAMPLES = "true" ]; then cp -p $PGPOOLDIR/sample_scripts/failover.sh.sample $FAILOVER_SCRIPT # Rewrite script PGHOMEVER=`expr $PGVERSION / 10` /bin/sed -i \ -e "s/^REPLUSER=.*/REPLUSER=$WHOAMI/" \ -e "s/^PCP_USER=.*/PCP_USER=$WHOAMI/" \ -e "s/^PGHOME=.*/PGHOME=\/usr\/pgsql-$PGHOMEVER/" \ -e "s/^PCP_PORT=.*/PCP_PORT=$PCP_PORT/" \ -e "s/^POSTGRESQL_STARTUP_USER=.*/POSTGRESQL_STARTUP_USER=$WHOAMI/" \ -e "s/^SSH_KEY_FILE=.*/SSH_KEY_FILE=id_rsa/" \ -e "s/^REPL_SLOT_NAME=.*/REPL_SLOT_NAME=pgpool_setup_slot\${FAILED_NODE_ID}/" \ -e "/^SSH_KEY_FILE=/a source $BASEDIR/bashrc.ports" \ $FAILOVER_SCRIPT chmod 755 $FAILOVER_SCRIPT return; fi cat >> $FAILOVER_SCRIPT <<'EOF' #! /bin/sh # Execute command by failover. # special values: %d = node id # %h = host name # %p = port number # %D = database cluster path # %m = new main node id # %M = old main node id # %H = new main node host name # %P = old primary node id # %R = new main database cluster path # %r = new main port number # %% = '%' character failed_node_id=$1 failed_host_name=$2 failed_port=$3 failed_db_cluster=$4 new_main_id=$5 new_main_host_name=$6 old_main_id=$7 old_primary_node_id=$8 new_main_port_number=$9 new_main_db_cluster=${10} mydir=__MYDIR__ log=$mydir/log/failover.log pg_ctl=__PGBIN__/pg_ctl PCP_PORT=__PCPPORT__ pgversion=__PGVERSION__ export PCPPASSFILE=__PCPPASSFILE__ PGPOOL_BIN=__PGPOOL_INSTALL_DIR__/bin date >> $log echo "failover script started for node: $failed_node_id" >> $log echo "failed_node_id: $failed_node_id failed_host_name: $failed_host_name failed_port: $failed_port" >> $log echo "failed_db_cluster: $failed_db_cluster new_main_id: $new_main_id old_main_id: $old_main_id" >> $log echo "new_main_host_name: $new_main_host_name old_primary_node_id: $old_primary_node_id" >> $log echo "new_main_port_number: $new_main_port_number new_main_db_cluster: $new_main_db_cluster" >> $log # check if all node is down if [ $new_main_id = "-1" ];then echo "no new main node is available" >>$log exit 0 fi if [ a"$failed_node_id" = a"$old_primary_node_id" -o a"$old_primary_node_id" = a"-1" ];then # main node failed new_primary_db_cluster=${mydir}/data"$new_main_id" echo $pg_ctl -D $new_primary_db_cluster promote >>$log # let standby take over $pg_ctl -D $new_primary_db_cluster promote >>$log # let standby take over sleep 2 fi date >> $log echo "failover script ended" >> $log EOF #------------------------------------------- # replace some variables in the script #------------------------------------------- /bin/sed -i \ -e "/__MYDIR__/s@__MYDIR__@$BASEDIR@" \ -e "/__PGBIN__/s@__PGBIN__@$PGBIN@" \ -e "/__PCPPASSFILE__/s@__PCPPASSFILE__@$PCP_PASS_FILE@" \ -e "/__PCPPORT__/s/__PCPPORT__/$PCP_PORT/" \ -e "/__PGVERSION__/s/__PGVERSION__/$PGVERSION/" \ -e "/__PGPOOL_INSTALL_DIR__/s@__PGPOOL_INSTALL_DIR__@$PGPOOL_INSTALL_DIR@" \ $FAILOVER_SCRIPT chmod 755 $FAILOVER_SCRIPT } #------------------------------------------- # create follow primary script #------------------------------------------- function create_follow_primary_script() { # If $TEST_SAMPLES = "true", use sample scripts and config files if [ $TEST_SAMPLES = "true" ]; then cp -p $PGPOOLDIR/sample_scripts/follow_primary.sh.sample $FOLLOW_PRIMARY_SCRIPT # Rewrite script PGHOMEVER=`expr $PGVERSION / 10` /bin/sed -i \ -e "s/^REPLUSER=.*/REPLUSER=$WHOAMI/" \ -e "s/^PCP_USER=.*/PCP_USER=$WHOAMI/" \ -e "s/^PGHOME=.*/PGHOME=\/usr\/pgsql-$PGHOMEVER/" \ -e "s/^PCP_PORT=.*/PCP_PORT=$PCP_PORT/" \ -e "s/^POSTGRESQL_STARTUP_USER=.*/POSTGRESQL_STARTUP_USER=$WHOAMI/" \ -e "s/^SSH_KEY_FILE=.*/SSH_KEY_FILE=id_rsa/" \ -e "s/^REPL_SLOT_NAME=.*/REPL_SLOT_NAME=pgpool_setup_slot\${NODE_ID}/" \ -e "/^SSH_KEY_FILE=/a source $BASEDIR/bashrc.ports" \ -e "s@^ARCHIVEDIR=.*@ARCHIVEDIR=${BASEDIR}/archivedir/\`basename \$NODE_PGDATA\`@" \ -e "/^ARCHIVEDIR=.*/a NEW_PRIMARY_ARCHIVEDIR=${BASEDIR}/archivedir/\`basename \$NEW_PRIMARY_NODE_PGDATA\`" \ -e "/restore_command =.*/s/\${ARCHIVEDIR}/\${NEW_PRIMARY_ARCHIVEDIR}/g" \ $FOLLOW_PRIMARY_SCRIPT chmod 755 $FOLLOW_PRIMARY_SCRIPT return; fi cat >> $FOLLOW_PRIMARY_SCRIPT <<'EOF' #! /bin/sh # Execute command by failover. # special values: %d = node id # %h = host name # %p = port number # %D = database cluster path # %m = new primary node id # %M = old main node id # %H = new primary node host name # %P = old primary node id # %R = new primary database cluster path # %r = new primary port number # %N = old primary node host name # %S = old primary node port number # %% = '%' character node_id=$1 host_name=$2 port=$3 db_cluster=$4 new_primary_id=$5 new_primary_host_name=$6 old_main_id=$7 old_primary_node_id=$8 new_primary_port_number=$9 new_primary_db_cluster=${10} mydir=__MYDIR__ log=$mydir/log/failover.log pg_ctl=__PGBIN__/pg_ctl PCP_PORT=__PCPPORT__ pgversion=__PGVERSION__ export PCPPASSFILE=__PCPPASSFILE__ PGPOOL_BIN=__PGPOOL_INSTALL_DIR__/bin echo "follow primary script started for node: $node_id" >> $log date >> $log echo "node_id: $node_id host_name: $host_name port: $port db_cluster: $db_cluster" >> $log echo "new_primary_id: $new_primary_id old_main_id: $old_main_id new_main_host_name: $new_main_host_name" >> $log echo "old_primary_node_id: $old_primary_node_id: new_primary_port_number: $new_primary_port_number new_primary_db_cluster: $new_primary_db_cluster" >> $log # Skip the target standby node if it's not running $pg_ctl -D $db_cluster status >/dev/null 2>&1 if [ $? = 0 ] then # change primary node connection info so that it points to the new primary if [ $pgversion -ge 120 ];then sed -i "s/port=[0-9]*/port=$new_primary_port_number/" $db_cluster/myrecovery.conf sed -i "/restore_command/s/data[0-9]/`basename $new_primary_db_cluster`/" $db_cluster/myrecovery.conf else # if recovery.conf is not found, rename recovery.done. if [ ! -f $db_cluster/recovery.conf ];then mv $db_cluster/recovery.done $db_cluster/recovery.conf fi sed -i "s/port=[0-9]*/port=$new_primary_port_number/" $db_cluster/recovery.conf sed -i "/restore_command/s/data[0-9]/`basename $new_primary_db_cluster`/" $db_cluster/recovery.conf fi touch $db_cluster/standby.signal echo "restart the target server" >> $log $pg_ctl -w -m f -D $db_cluster restart >> $log 2>&1 $pg_ctl -D $db_cluster status >>$log 2>&1 if [ $? != 0 ] then echo "restarting $db_cluster failed" >>$log echo "fallback to pcp_recovery_node" >>$log $PGPOOL_BIN/pcp_recovery_node -w -h localhost -p $PCP_PORT -n $node_id >> $log 2>&1 else # attach the node $PGPOOL_BIN/pcp_attach_node -w -h localhost -p $PCP_PORT -n $node_id >> $log 2>&1 fi else echo "$db_cluster is not running. skipping follow primary command." >> $log fi echo "follow primary script ended." >> $log date >> $log EOF #------------------------------------------- # replace some variables in the script #------------------------------------------- /bin/sed -i \ -e "/__MYDIR__/s@__MYDIR__@$BASEDIR@" \ -e "/__PGBIN__/s@__PGBIN__@$PGBIN@" \ -e "/__PCPPASSFILE__/s@__PCPPASSFILE__@$PCP_PASS_FILE@" \ -e "/__PCPPORT__/s/__PCPPORT__/$PCP_PORT/" \ -e "/__PGVERSION__/s/__PGVERSION__/$PGVERSION/" \ -e "/__PGPOOL_INSTALL_DIR__/s@__PGPOOL_INSTALL_DIR__@$PGPOOL_INSTALL_DIR@" \ $FOLLOW_PRIMARY_SCRIPT chmod 755 $FOLLOW_PRIMARY_SCRIPT } #------------------------------------------- # create pgpool_remote_start script # argument: PostgreSQL database cluster directory #------------------------------------------- function create_pgpool_remote_start_script() { # If $TEST_SAMPLES = "true", use sample scripts and config files if [ $TEST_SAMPLES = "true" ]; then cp -p $PGPOOLDIR/sample_scripts/pgpool_remote_start.sample $1/$PGPOOL_REMOTE_START_SCRIPT # Rewrite script PGHOMEVER=`expr $PGVERSION / 10` /bin/sed -i \ -e "s/^REPLUSER=.*/REPLUSER=$WHOAMI/" \ -e "s/^PCP_USER=.*/PCP_USER=$WHOAMI/" \ -e "s/^PGHOME=.*/PGHOME=\/usr\/pgsql-$PGHOMEVER/" \ -e "s/^PCP_PORT=.*/PCP_PORT=$PCP_PORT/" \ -e "s/^POSTGRESQL_STARTUP_USER=.*/POSTGRESQL_STARTUP_USER=$WHOAMI/" \ -e "s/^SSH_KEY_FILE=.*/SSH_KEY_FILE=id_rsa/" \ -e "/^SSH_KEY_FILE=/a source $BASEDIR/bashrc.ports" \ $1/$PGPOOL_REMOTE_START_SCRIPT chmod 755 $1/$PGPOOL_REMOTE_START_SCRIPT return; fi cat >> $1/$PGPOOL_REMOTE_START_SCRIPT <<'EOF' #! /bin/sh # # start postmaster on the recovered node # if [ $# -ne 2 ] then echo "pgpool_remote_start remote_host remote_datadir" exit 1 fi DEST=$1 DESTDIR=$2 PGCTL=__PGBIN__/pg_ctl ssh -T $DEST $PGCTL -w -D $DESTDIR start 2>/dev/null 1>/dev/null < /dev/null & EOF #------------------------------------------- # replace some variables in the script #------------------------------------------- /bin/sed -i \ -e "/__PGBIN__/s@__PGBIN__@$PGBIN@" \ $1/$PGPOOL_REMOTE_START_SCRIPT chmod 755 $1/$PGPOOL_REMOTE_START_SCRIPT } #------------------------------------------- # set postgresql.conf # argument: PostgreSQL database cluster directory #------------------------------------------- function set_postgresql_conf { PGCONF=$1/postgresql.conf PGHBACONF=$1/pg_hba.conf echo "listen_addresses = '*'" >> $PGCONF echo "port = $PORT" >> $PGCONF echo "logging_collector = on" >> $PGCONF echo "log_filename = '%A.log'" >> $PGCONF echo "log_line_prefix = '%p %m '" >> $PGCONF echo "log_truncate_on_rotation = on" >> $PGCONF echo "log_statement = 'all'" >> $PGCONF echo "max_prepared_transactions = 10" >> $PGCONF echo "unix_socket_directories = '$PGSOCKET_DIR'" >> $PGCONF echo "max_wal_senders = `expr $NUMCLUSTERS + 10`" >> $PGCONF if [ $PGVERSION -ge 120 ];then echo "include_if_exists = 'myrecovery.conf'" >> $PGCONF fi if [ $MODE = "s" ];then echo "hot_standby = on" >> $PGCONF echo "wal_level = 'hot_standby'" >> $PGCONF if [ $USE_REPLICATION_SLOT = "false" ];then echo "archive_mode = on" >> $PGCONF echo "archive_command = 'cp %p $BASEDIR/archivedir/`basename $1`/%f > $PGCONF # If $TEST_SAMPLES = "true", always enable replication slot if [ $TEST_SAMPLES = "true" ];then num_slots=`expr $NUMCLUSTERS + 10` echo "max_replication_slots = $num_slots" >> $PGCONF fi else num_slots=`expr $NUMCLUSTERS + 10` echo "max_replication_slots = $num_slots" >> $PGCONF fi elif [ $MODE = 'r' -o $MODE = 'n' -o $MODE = 'l' -o $MODE = 'y' -o $MODE = 'i' ];then echo "wal_level = 'archive'" >> $PGCONF echo "archive_mode = on" >> $PGCONF echo "archive_command = 'cp %p $BASEDIR/archivedir/`basename $1`/%f > $PGCONF if [ $MODE = 'l' ];then echo "wal_level = 'logical'" >> $PGCONF fi fi # Snapshot isolation mode requires REPEATABLE READ transaction isolation mode. if [ $MODE = 'i' ];then echo "default_transaction_isolation = 'repeatable read'" >> $PGCONF fi sed -i '/host.*all.*all.*trust$/s/^/#/g' $PGHBACONF sed -i '/local.*all.*all.*trust$/s/^/#/g' $PGHBACONF if [ $PGVERSION -ge 100 ]; then echo "host all scram_user all scram-sha-256" >> $PGHBACONF echo "host all md5_user all md5" >> $PGHBACONF fi echo "host all all all trust" >> $PGHBACONF if [ $PGVERSION -ge 100 ]; then echo "local all scram_user scram-sha-256" >> $PGHBACONF echo "local all md5_user md5" >> $PGHBACONF fi echo "local all all trust" >> $PGHBACONF ed $1/pg_hba.conf <> $1/$SCRIPT <<'EOF' #! /bin/sh psql=__PGBIN__/psql pg_rewind=__PGBIN__/pg_rewind pg_basebackup=__PGBIN__/pg_basebackup PG_CTL=__PGBIN__/pg_ctl DATADIR_BASE=__DATADIR_BASE__ PGSUPERUSER=__PGSUPERUSER__ MAX_DURATION=60 pgversion=__PGVERSION__ main_db_cluster=$1 recovery_node_host_name=$2 DEST_CLUSTER=$3 PORT=$4 recovery_node=$5 pg_rewind_failed="true" log=$DATADIR_BASE/log/recovery.log echo >> $log date >> $log # Make backup copy of postgresql.conf since pg_rewind/pg_basebackup blindly copies # $main_db_cluster/postgresql.conf. cp $DEST_CLUSTER/postgresql.conf /tmp/ pg_rewind_failed="true" EOF echo "export PGHOST=localhost" >> $1/$SCRIPT if [ $USE_PG_REWIND = "true" ];then cat >> $1/$SCRIPT <<'EOF' # First try pg_rewind echo "pg_rewind starts" >> $log $pg_rewind -P -D $DEST_CLUSTER --source-server="port=$PORT user=$PGSUPERUSER dbname=postgres" >> $log 2>&1 if [ $? != 0 ];then # pg_rewind failed. Fallback to pg_basebackup. echo "pg_rewind failed. Fall back to rsync" >> $log pg_rewind_failed="true" else pg_rewind_failed="false" fi EOF fi cat >> $1/$SCRIPT <<'EOF' if [ $pg_rewind_failed = "true" ];then echo "source: $main_db_cluster dest: $DEST_CLUSTER" >> $log rm -fr $DEST_CLUSTER/* test -d $DEST_CLUSTER || mkdir $DEST_CLUSTER $pg_basebackup -w -U $PGSUPERUSER -p $PORT -D $DEST_CLUSTER -c fast fi EOF if [ $USE_REPLICATION_SLOT = "true" ];then cat >> $1/$SCRIPT <<'EOF' $psql -p $PORT -c "SELECT * FROM pg_create_physical_replication_slot('pgpool_setup_slot$recovery_node')" postgres EOF fi if [ $PGVERSION -ge 120 ];then cat >> $1/$SCRIPT <<'EOF' cat > $DEST_CLUSTER/myrecovery.conf <> $1/$SCRIPT <<'EOF' cat > $DEST_CLUSTER/recovery.conf <> $1/$SCRIPT <<'EOF' restore_command = 'cp $DATADIR_BASE/archivedir/`basename $1`/%f "%p" 2> /dev/null' REOF EOF else cat >> $1/$SCRIPT << 'EOF' primary_slot_name = 'pgpool_setup_slot$recovery_node' REOF EOF fi cat >> $1/$SCRIPT <<'EOF' cp /tmp/postgresql.conf $DEST_CLUSTER/ touch $DEST_CLUSTER/standby.signal EOF #------------------------------------------- # replace some variables in the script #------------------------------------------- /bin/sed -i \ -e "/__PGBIN__/s@__PGBIN__@$PGBIN@" \ -e "/__DATADIR_BASE__/s@__DATADIR_BASE__@$BASEDIR@" \ -e "/__PGSUPERUSER__/s/__PGSUPERUSER__/$WHOAMI/" \ -e "/__PGVERSION__/s/__PGVERSION__/$PGVERSION/" \ $1/$SCRIPT chmod 755 $1/$SCRIPT } #------------------------------------------- # create basebackup.sh for native replication mode and snapshot isolation mode. # argument1: full path to database cluster directory of main node # argument2: node number of target node #------------------------------------------- function create_basebackup_replication { CLUSTERDIR=$1 n=$2 SCRIPT=basebackup.sh # If $TEST_SAMPLES = "true", use sample scripts and config files if [ $TEST_SAMPLES = "true" ]; then cp -p $PGPOOLDIR/sample_scripts/replication_mode_recovery_1st_stage.sample $1/$SCRIPT # Rewrite script PGHOMEVER=`expr $PGVERSION / 10` /bin/sed -i \ -e "s/^REPLUSER=.*/REPLUSER=$WHOAMI/" \ -e "s/^PCP_USER=.*/PCP_USER=$WHOAMI/" \ -e "s/^PGHOME=.*/PGHOME=\/usr\/pgsql-$PGHOMEVER/" \ -e "s/^PCP_PORT=.*/PCP_PORT=$PCP_PORT/" \ -e "s/^POSTGRESQL_STARTUP_USER=.*/POSTGRESQL_STARTUP_USER=$WHOAMI/" \ -e "s/^SSH_KEY_FILE=.*/SSH_KEY_FILE=id_rsa/" \ -e "s/^REPL_SLOT_NAME=.*/REPL_SLOT_NAME=pgpool_setup_slot\${DEST_NODE_ID}/" \ -e "/^SSH_KEY_FILE=/a source $BASEDIR/bashrc.ports" \ -e "s@^ARCHIVEDIR=.*@ARCHIVEDIR=${BASEDIR}/archivedir/\`basename \$DEST_NODE_PGDATA\`@" \ -e "/^ARCHIVEDIR=.*/a NEW_PRIMARY_ARCHIVEDIR=${BASEDIR}/archivedir/\`basename \$MAIN_NODE_PGDATA\`" \ -e "/restore_command =.*/s/\${ARCHIVEDIR}/\${NEW_PRIMARY_ARCHIVEDIR}/g" \ $1/$SCRIPT chmod 755 $1/$SCRIPT return; fi create_basebackup_stream $CLUSTERDIR $n cat >> $1/$SCRIPT <<'EOF' # start target server as a streaming replication standby server $PG_CTL -w -D $DEST_CLUSTER start # wait till the standby catches up primary server or # $MAX_DURATION seconds passes sec=0 while [ $sec -lt $MAX_DURATION ] do sec=`expr $sec + 1` if [ $pgversion -ge 100 ];then result=`psql -p $4 -c "SELECT sent_lsn = replay_lsn FROM pg_stat_replication where application_name = 'server$recovery_node'" postgres|sed -n 3p|sed 's/ //'` else result=`psql -p $4 -c "SELECT sent_location = replay_location FROM pg_stat_replication where application_name = 'server$recovery_node'" postgres|sed -n 3p|sed 's/ //'` fi # echo "result: $result" if [ "$result" = "t" ];then sec=$MAX_DURATION fi sleep 1 done EOF #------------------------------------------- # replace some variables in the script #------------------------------------------- /bin/sed -i \ -e "/__PGBIN__/s@__PGBIN__@$PGBIN@" \ -e "/__DATADIR_BASE__/s@__DATADIR_BASE__@$BASEDIR@" \ -e "/__PGSUPERUSER__/s/__PGSUPERUSER__/$WHOAMI/" \ -e "/__ARCHDIR__/s@__ARCHDIR__@$BASEDIR/archivedir/\`basename \$1\`@" \ -e "/__PGVERSION__/s/__PGVERSION__/$PGVERSION/" \ $1/$SCRIPT chmod 755 $1/$SCRIPT } #------------------------------------------- # create pgpool_recovery_pitr (2nd stage script)for native replication mode # argument: PostgreSQL database cluster directory #------------------------------------------- function create_pgpool_recovery_pitr { SCRIPT=pgpool_recovery_pitr # If $TEST_SAMPLES = "true", use sample scripts and config files if [ $TEST_SAMPLES = "true" ]; then cp -p $PGPOOLDIR/sample_scripts/replication_mode_recovery_2nd_stage.sample $1/$SCRIPT # Rewrite script PGHOMEVER=`expr $PGVERSION / 10` /bin/sed -i \ -e "s/^REPLUSER=.*/REPLUSER=$WHOAMI/" \ -e "s/^PCP_USER=.*/PCP_USER=$WHOAMI/" \ -e "s/^PGHOME=.*/PGHOME=\/usr\/pgsql-$PGHOMEVER/" \ -e "s/^PCP_PORT=.*/PCP_PORT=$PCP_PORT/" \ -e "s/^POSTGRESQL_STARTUP_USER=.*/POSTGRESQL_STARTUP_USER=$WHOAMI/" \ -e "s/^SSH_KEY_FILE=.*/SSH_KEY_FILE=id_rsa/" \ -e "s/^REPL_SLOT_NAME=.*/REPL_SLOT_NAME=pgpool_setup_slot\${DEST_NODE_ID}/" \ -e "/^SSH_KEY_FILE=/a source $BASEDIR/bashrc.ports" \ -e "s@^ARCHIVEDIR=.*@ARCHIVEDIR=${BASEDIR}/archivedir/\`basename \$DEST_NODE_PGDATA\`@" \ -e "/^ARCHIVEDIR=.*/a NEW_PRIMARY_ARCHIVEDIR=${BASEDIR}/archivedir/\`basename \$MAIN_NODE_PGDATA\`" \ -e "/restore_command =.*/s/\${ARCHIVEDIR}/\${NEW_PRIMARY_ARCHIVEDIR}/g" \ $1/$SCRIPT chmod 755 $1/$SCRIPT return; fi cat >> $1/$SCRIPT <<'EOF' #! /bin/sh psql=__PGBIN__/psql DATADIR_BASE=__DATADIR_BASE__ PGSUPERUSER=__PGSUPERUSER__ PGCTL=__PGBIN__/pg_ctl main_db_cluster=$1 recovery_node_host_name=$2 DEST_CLUSTER=$3 PORT=$4 log=$DATADIR_BASE/log/recovery.log EOF echo "export PGHOST=localhost" >> $1/$SCRIPT cat >> $1/$SCRIPT <<'EOF' # Force to flush current value of sequences to xlog $psql -q -p $PORT -t -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn' template1| while read i do if [ "$i" != "" ];then $psql -p $PORT -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind = 'S'" $i fi done $psql -p $PORT -c "SELECT pgpool_switch_xlog('__ARCHDIR__')" template1 $PGCTL -D $DEST_CLUSTER promote EOF #------------------------------------------- # replace some variables in the script #------------------------------------------- /bin/sed -i \ -e "/__PGBIN__/s@__PGBIN__@$PGBIN@" \ -e "/__DATADIR_BASE__/s@__DATADIR_BASE__@$BASEDIR@" \ -e "/__PGSUPERUSER__/s/__PGSUPERUSER__/$WHOAMI/" \ -e "/__ARCHDIR__/s@__ARCHDIR__@$BASEDIR/archivedir/\`basename \$1\`@" \ $1/$SCRIPT chmod 755 $1/$SCRIPT } #------------------------------------------- # create initial recovery.conf # argument1: PostgreSQL database cluster directory # argument2: cluster No. We assume that data0 is primary #------------------------------------------- function create_recovery_conf { if [ $PGVERSION -ge 120 ];then fname=myrecovery.conf elif [ $2 = "0" ];then fname=recovery.done else fname=recovery.conf fi if [ $PGVERSION -lt 120 ];then cat > $1/$fname < $1/$fname <> $1/$fname else cat >> $1/$fname <> $POOL_HBACONF echo "host all md5_user 0/0 md5" >> $POOL_HBACONF echo "local all scram_user scram-sha-256" >> $POOL_HBACONF echo "local all md5_user md5" >> $POOL_HBACONF fi echo "local all all trust" >> $POOL_HBACONF echo "host all all 127.0.0.1/32 trust" >> $POOL_HBACONF echo "host all all ::1/128 trust" >> $POOL_HBACONF } #------------------------------------------- # set pgpool.conf # argument: absolute path to pgpool.conf #------------------------------------------- function set_pgpool_conf { echo "listen_addresses = '*'" >> $CONF echo "sr_check_user = '$WHOAMI'" >> $CONF echo "sr_check_password = ''" >> $CONF echo "recovery_user = '$WHOAMI'" >> $CONF echo "recovery_password = ''" >> $CONF echo "recovery_1st_stage_command = 'basebackup.sh'" >> $CONF if [ $MODE = "r" -o $MODE = "i" ];then echo "recovery_2nd_stage_command = 'pgpool_recovery_pitr'" >> $CONF fi n=0 while [ $n -lt $NUMCLUSTERS ] do echo "health_check_period$n = 10" >> $CONF echo "health_check_timeout$n = 20" >> $CONF echo "health_check_user$n = '$WHOAMI'" >> $CONF echo "health_check_password$n = ''" >> $CONF echo "health_check_database$n = 'postgres'" >> $CONF echo "health_check_max_retries$n = 3" >> $CONF echo "health_check_retry_delay$n = 1" >> $CONF echo "connect_timeout$n = 1000" >> $CONF n=`expr $n + 1` done OIDDIR=$BASEDIR/log/pgpool/oiddir mkdir -p $OIDDIR echo "memqcache_oiddir = '$OIDDIR'" >> $CONF echo "log_per_node_statement = on" >> $CONF if [ $MODE = "s" ];then echo "failover_command = '$FAILOVER_SCRIPT %d %h %p %D %m %H %M %P %r %R %N %S'" >> $CONF fi echo "unix_socket_directories = '$PGSOCKET_DIR'" >> $CONF echo "pcp_socket_dir = '$PGSOCKET_DIR'" >> $CONF echo "logging_collector = off" >> $CONF echo "log_line_prefix = '%m: %a pid %p: '" >> $CONF if [ $ENABLE_TEST = "true" ];then echo "health_check_test = on" >> $CONF fi } #------------------------------------------- # wait for pgpool comes up #------------------------------------------- function wait_for_pgpool_startup { timeout=20 while [ $timeout -gt 0 ] do $PSQL -p $PGPOOL_PORT -c "show pool_nodes" postgres >/dev/null 2>&1 if [ $? = 0 ];then # echo "pgpool-II comes up after `expr 20 - $timeout` seconds" break; fi timeout=`expr $timeout - 1` sleep 1 done } #------------------------------------------- # wait for pgpool reload finished #------------------------------------------- function wait_for_pgpool_reload { timeout=20 num_node=$1 while [ $timeout -gt 0 ] do N=`$PSQL -p $PGPOOL_PORT -c "show pool_status" test | grep backend_data | wc -l` if [ $N = $num_node ];then break; fi timeout=`expr $timeout - 1` sleep 1 done } #------------------------------------------- # create each PostgreSQL cluster #------------------------------------------- function create_postgresql_clusters { n=0 while [ $n -lt $NUMCLUSTERS ] do CLUSTER="data"`expr $n` CLUSTERDIR=$BASEDIR/$CLUSTER PORT=`expr $BASEPORT + $n` if [ $NO_CREATE_PGCLUSTER = "false" ];then echo -n "creating database cluster $CLUSTERDIR..." $INITDB -D $CLUSTERDIR $INITDBARG >&5 2>&1 echo "done." # set postgresql.conf echo "update postgresql.conf" set_postgresql_conf $CLUSTERDIR >&5 2>&1 # create pgpool_remote_start script under cluster directory echo "creating pgpool_remote_start" create_pgpool_remote_start_script $CLUSTERDIR >&5 2>&1 echo "creating basebackup.sh" # create basebackup.sh if [ $MODE = 's' ];then create_basebackup_stream $CLUSTERDIR $n >&5 2>&1 elif [ $MODE = 'r' -o $MODE = 'i' ];then create_basebackup_replication $CLUSTERDIR $n >&5 2>&1 create_pgpool_recovery_pitr $CLUSTERDIR $n >&5 2>&1 fi # create recovery.conf or recovery.done if streaming replication # mode if [ $MODE = "s" ];then echo "creating recovery.conf" create_recovery_conf $CLUSTERDIR $n >&5 2>&1 fi echo "$PG_CTL -D $CLUSTERDIR -m f stop" >> $SHUTDOWNALL echo "$PG_CTL -w -D $CLUSTERDIR start" >> $STARTALL fi n=`expr $n + 1` echo "#$n port is $PORT" >> README.port # create archive directory test ! -d archivedir/$CLUSTER && mkdir -p archivedir/$CLUSTER done } #------------------------------------------- # if streaming replication mode, we need to create data1 and so on, by # using online recovery. #------------------------------------------- function create_followers { BACKEND_HOSTNAME=localhost if [ $MODE = 's' ];then if [ $NO_CREATE_PGCLUSTER = "false" ];then # temporarily start data0 cluster to create extensions echo "temporarily start data0 cluster to create extensions" $PG_CTL -w -D data0 start >&5 2>&1 $PSQL -p $BASEPORT template1 >&5 2>&1 <&5 2>&1 n=`expr $n + 1` done fi fi n=0 PORT=$BASEPORT CLUSTER="data"`expr $n` CLUSTERDIR=$BASEDIR/$CLUSTER echo "backend_hostname$n = '$BACKEND_HOSTNAME'" >> $CONF echo "backend_port$n = $PORT" >> $CONF echo "backend_weight$n = 1" >> $CONF echo "backend_data_directory$n = '$CLUSTERDIR'" >> $CONF echo "backend_flag$n = 'ALLOW_TO_FAILOVER'" >> $CONF echo "backend_application_name$n = 'server$n'" >> $CONF if [ $NO_CREATE_PGCLUSTER = "false" ];then # temporarily start pgpool echo "temporarily start pgpool-II to create standby nodes" $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 & wait_for_pgpool_startup fi if [ $NUMCLUSTERS -gt 1 ];then n=1 while [ $n -lt $NUMCLUSTERS ] do # create archive directory test ! -d $BASEDIR/archivedir/`basename $CLUSTER` && mkdir -p $BASEDIR/archivedir/`basename $CLUSTER` # set up pgpool.conf PORT=`expr $PORT + 1` echo "backend_hostname$n = '$BACKEND_HOSTNAME'" >> $CONF echo "backend_port$n = $PORT" >> $CONF echo "backend_weight$n = 1" >> $CONF CLUSTER="data"`expr $n` CLUSTERDIR=$BASEDIR/$CLUSTER echo "backend_data_directory$n = '$CLUSTERDIR'" >> $CONF echo "backend_flag$n = 'ALLOW_TO_FAILOVER'" >> $CONF echo "backend_application_name$n = 'server$n'" >> $CONF n=`expr $n + 1` done if [ $NO_CREATE_PGCLUSTER = "false" ];then $PGPOOL_INSTALL_DIR/bin/pgpool -f $BASEDIR/etc/pgpool.conf reload fi fi if [ $NO_CREATE_PGCLUSTER = "false" ];then wait_for_pgpool_reload $NUMCLUSTERS $PSQL -p $PGPOOL_PORT -c "show pool_nodes" test export PCPPASSFILE=$PCP_PASS_FILE # recovery data1 and so on n=1 while [ $n -lt $NUMCLUSTERS ] do echo -n "recovery node $n..." $PGPOOL_INSTALL_DIR/bin/pcp_recovery_node -w -h localhost -p $PCP_PORT -n $n echo "done." n=`expr $n + 1` wait_for_pgpool_startup done fi # # replication mode # else n=0 PORT=$BASEPORT CLUSTER="data"`expr $n` CLUSTERDIR=$BASEDIR/$CLUSTER while [ $n -lt $NUMCLUSTERS ] do if [ $NO_CREATE_PGCLUSTER = "false" ];then if [ $MODE = 'l' -o $MODE = 'y' ] then # temporarily start data$n cluster to create extensions echo "temporarily start data${n} cluster to create extensions" $PG_CTL -w -D data${n} start >&5 2>&1 $PSQL -p `expr $BASEPORT + $n` template1 >&5 2>&1 <&5 2>&1 fi fi # set up pgpool.conf echo "backend_hostname$n = '$BACKEND_HOSTNAME'" >> $CONF echo "backend_port$n = $PORT" >> $CONF echo "backend_weight$n = 1" >> $CONF if [ $n -eq 0 -a $MODE = "l" ] then echo "backend_flag$n = ALWAYS_PRIMARY" >> $CONF fi CLUSTER="data"`expr $n` CLUSTERDIR=$BASEDIR/$CLUSTER echo "backend_data_directory$n = '$CLUSTERDIR'" >> $CONF PORT=`expr $PORT + 1` n=`expr $n + 1` done if [ $NO_CREATE_PGCLUSTER = "false" ];then echo "start all" $STARTALL >&5 2>&1 echo -n "waiting for pgpool-II coming up..." wait_for_pgpool_startup # sleep 20 echo "done." fi fi if [ $NO_CREATE_PGCLUSTER = "false" ];then if [ $MODE = "r" -o $MODE = "n" -o $MODE = "i" ];then echo "create extensions" $PSQL -p $PGPOOL_PORT template1 >&5 2>&1 < /dev/null 2>&1 if [ $? != 0 ]; then echo "Pgpool-II packages must be installed if \"-c\" option or TEST_SAMPLES is specified." exit 1 fi if [ ! -r $PGPOOLDIR/pgpool.conf.sample ]; then echo "\"$WHOAMI\" user could not read sample config files unter $PGPOOLDIR" exit 1 fi fi exec 5> $BASEDIR/pgpool_setup.log #------------------------------------------- # everything looks good. starting setup... #------------------------------------------- echo "Starting set up in $MODENAME" if [ $TEST_SAMPLES = "true" ]; then echo "Test Pgpool-II RPMs using sample scripts and config files" fi #------------------------------------------- # assign base port for PostgreSQL #------------------------------------------- ORIG_BASEPORT=$BASEPORT BASEPORT=$PGBASEPORT #------------------------------------------- # install pgpool.conf #------------------------------------------- test ! -d etc && mkdir etc cp $PGPOOLDIR/pgpool.conf.sample $CONF echo "backend_clustering_mode = $CLUSTERING_MODE_STR" >> $CONF cp $PGPOOLDIR/pool_hba.conf.sample $BASEDIR/etc/pool_hba.conf #------------------------------------------- # create startall, shutdownall and pgpool_reload #------------------------------------------- echo "creating startall and shutdownall" echo "LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$LPATH" > $STARTALL echo 'dir=`pwd`' >> $STARTALL echo "PGPOOL_INSTALL_DIR=$PGPOOL_INSTALL_DIR" >> $STARTALL chmod 755 $STARTALL echo 'dir=`pwd`' > $SHUTDOWNALL echo "PGPOOL_INSTALL_DIR=$PGPOOL_INSTALL_DIR" >> $SHUTDOWNALL echo '$PGPOOL_INSTALL_DIR/bin/pgpool -f $dir/etc/pgpool.conf -m f stop && while [ -f $dir/run/pgpool.pid ];do sleep 1;done' >> $SHUTDOWNALL if [ $CHECK_TIME_WAIT != "false" ];then echo "while netstat -tulpn 2>/dev/null|grep pgpool|grep -w $ORIGBASEPORT ;do sleep 1;done" >> $SHUTDOWNALL fi chmod 755 $SHUTDOWNALL echo 'dir=`pwd`' > $PGPOOL_RELOAD echo "PGPOOL_INSTALL_DIR=$PGPOOL_INSTALL_DIR" >> $PGPOOL_RELOAD echo '$PGPOOL_INSTALL_DIR/bin/pgpool -f $dir/etc/pgpool.conf reload' >> $PGPOOL_RELOAD chmod 755 $PGPOOL_RELOAD #------------------------------------------- # setup ports #------------------------------------------- PGPOOL_PORT=$ORIG_BASEPORT PCP_PORT=`expr $PGPOOL_PORT + 1` #------------------------------------------- # create failover script #------------------------------------------- echo "creating failover script" create_failover_script >&5 2>&1 #------------------------------------------- # create each PostgreSQL cluster #------------------------------------------- create_postgresql_clusters #------------------------------------------- # create pgpool.conf #------------------------------------------- set_pgpool_conf $CONF set_pool_hba_conf $BASEDIR/etc/pool_hba.conf echo "port = $PGPOOL_PORT" >> $CONF echo "pcp_port = $PCP_PORT" >> $CONF #------------------------------------------- # create password file for pcp #------------------------------------------- echo "localhost:${PCP_PORT}:${WHOAMI}:${WHOAMI}" >> $PCP_PASS_FILE chmod 0600 $PCP_PASS_FILE test ! -d run && mkdir run echo "pid_file_name = '$BASEDIR/run/pgpool.pid'" >> $CONF test ! -d log && mkdir log echo "logdir = '$BASEDIR/log'" >> $CONF if [ "$PGPOOLDEBUG" = "true" ];then 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 else 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 fi # create pcp.conf if [ -f $PGPOOLDIR/pcp.conf.sample ];then cp $PGPOOLDIR/pcp.conf.sample etc/pcp.conf fi echo -n "${WHOAMI}:" >> etc/pcp.conf $PGPOOL_INSTALL_DIR/bin/pg_md5 $WHOAMI >> etc/pcp.conf # create pool_passwd $PGPOOL_INSTALL_DIR/bin/pg_md5 -m -f etc/pgpool.conf -u $WHOAMI $WHOAMI #------------------------------------------- # if streaming replication mode, we need to create data1 and so on, by # using online recovery. #------------------------------------------- create_followers #------------------------------------------- # create follow_primary failover script #------------------------------------------- if [ $MODE = "s" ];then echo "creating follow primary script" create_follow_primary_script >&5 2>&1 echo "follow_primary_command = '$FOLLOW_PRIMARY_SCRIPT %d %h %p %D %m %H %M %P %r %R'" >> $CONF fi if [ $NO_CREATE_PGCLUSTER = "false" ];then $PSQL -p $PGPOOL_PORT test <> README.port echo "pcp port is $PCP_PORT" >> README.port if [ $NO_STOP = "false" ];then echo "shutdown all" $SHUTDOWNALL >&5 2>&1 fi echo "export PGPOOL_PORT=$PGPOOL_PORT" > bashrc.ports echo "export PCP_PORT=$PCP_PORT" >> bashrc.ports echo "export PCPPASSFILE=$PCP_PASS_FILE" >> bashrc.ports chmod 755 bashrc.ports echo echo "pgpool-II setting for $MODENAME is done." echo "To start the whole system, use ${STARTALL}." echo "To shutdown the whole system, use ${SHUTDOWNALL}." echo "pcp command user name is \"$WHOAMI\", password is \"$WHOAMI\"." echo "Each PostgreSQL, pgpool-II and pcp port is as follows:" cat README.port echo "The info above is in README.port." if [ $NO_STOP = "true" ];then echo "CAUTION: whole system is still running." fi