#!/usr/bin/env bash
#
# Copyright (c) 2013-2020 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]
# -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.
# -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.
#
# The user run this script will become the PostgreSQL super user as
# well.  Current directory must be empty.  Assume that appropreate
# pgpool and PostgreSQL binaries are in the command search path.
# The user must have passwordless access to localhost over SSH.
#
# Rayout 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=${BASEBOPRT:-"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 directory
PGSOCKET_DIR=${PGSOCKET_DIR:-"/tmp"}
# 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"}
#-------------------------------------------
# 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 localtion.
CONF=$BASEDIR/etc/pgpool.conf
# failover script
FAILOVER_SCRIPT=$BASEDIR/etc/failover.sh
# follow master script
FOLLOW_MASTER_SCRIPT=$BASEDIR/etc/follow_master.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=$PGSOCKET_DIR

#-------------------------------------------
# create failover script
#-------------------------------------------
function create_failover_script()
{
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 master node id
#                  %M = old master node id
#                  %H = new master node host name
#                  %P = old primary node id
#                  %R = new master database cluster path
#                  %r = new master port number
#                  %% = '%' character
failed_node_id=$1
failed_host_name=$2
failed_port=$3
failed_db_cluster=$4
new_master_id=$5
old_master_id=$6
new_master_host_name=$7
old_primary_node_id=$8
new_master_port_number=$9
new_master_db_cluster=${10}
mydir=__MYDIR__
log=$mydir/log/failover.log
pg_ctl=__PGBIN__/pg_ctl
cluster0=$mydir/data0
cluster1=$mydir/data1

date >> $log
echo "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

# check if all node is down
if [ $new_master_id = "-1" ];then
    echo "no new master node is available" >>$log
    exit 0
fi

if [ a"$failed_node_id" = a"$old_primary_node_id" ];then	# master failed
! 	new_primary_db_cluster=${mydir}/data"$new_master_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
EOF

#-------------------------------------------
# replace some variables in the script
#-------------------------------------------
/bin/sed -i \
	 -e "/__MYDIR__/s@__MYDIR__@$BASEDIR@" \
	 -e "/__PGBIN__/s@__PGBIN__@$PGBIN@" \
	 $FAILOVER_SCRIPT

chmod 755 $FAILOVER_SCRIPT
}

#-------------------------------------------
# create follow master script
#-------------------------------------------
function create_follow_master_script()
{
cat >> $FOLLOW_MASTER_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 master node id
#                  %M = old master node id
#                  %H = new master node host name
#                  %P = old primary node id
#                  %R = new master database cluster path
#                  %r = new master port number
#                  %% = '%' character
failed_node_id=$1
failed_host_name=$2
failed_port=$3
failed_db_cluster=$4
new_master_id=$5
old_master_id=$6
new_master_host_name=$7
old_primary_node_id=$8
new_master_port_number=$9
new_master_db_cluster=${10}
mydir=__MYDIR__
log=$mydir/log/failover.log
pg_ctl=__PGBIN__/pg_ctl
cluster0=$mydir/data0
cluster1=$mydir/data1
cluster2=$mydir/data2
PCP_PORT=__PCPPORT__
pgversion=__PGVERSION__
export PCPPASSFILE=__PCPPASSFILE__
PGPOOL_BIN=__PGPOOL_INSTALL_DIR__/bin

date >> $log
echo "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

# Stop standby node if it's running
$pg_ctl -D $failed_db_cluster status >/dev/null 2>&1
if [ $? = 0 ]
then
	$pg_ctl -w -m f -D $failed_db_cluster stop >> $log 2>&1
	sleep 10
	# recovery the node
	$PGPOOL_BIN/pcp_recovery_node -w -h localhost -p $PCP_PORT -n $failed_node_id >> $log 2>&1
else
	echo "$failed_db_cluster is not running. skipping follow master command." >> $log
fi
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_MASTER_SCRIPT

chmod 755 $FOLLOW_MASTER_SCRIPT
}

#-------------------------------------------
# create pgpool_remote_start script
# argument: PostgreSQL database cluster directory
#-------------------------------------------
function create_pgpool_remote_start_script()
{
cat >> $1/$PGPOOL_REMOTE_START_SCRIPT <<'EOF'
#! /bin/sh
#
# start postmaster on the recoveried 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 %t '" >> $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 

	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
		echo "max_wal_senders = $NUMCLUSTERS" >> $PGCONF

		if [ $USE_REPLICATION_SLOT = "false" ];then
		    echo "archive_mode = on" >> $PGCONF
		    echo "archive_command = 'cp %p $BASEDIR/archivedir/`basename $1`/%f </dev/null'" >> $PGCONF
		else
		    num_slots=`expr $NUMCLUSTERS + 10`
		    echo "max_replication_slots = $num_slots" >> $PGCONF
		fi

	elif [ $MODE = 'r' -o $MODE = 'l' -o $MODE = 'y' ];then
		echo "wal_level = archive" >> $PGCONF
		echo "archive_mode = on" >> $PGCONF
		echo "archive_command = 'cp %p $BASEDIR/archivedir/`basename $1`/%f </dev/null'" >> $PGCONF
		if [ $MODE = 'l' ];then
		    echo "wal_level = logical" >> $PGCONF
		fi
	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     0/0    scram-sha-256" >> $PGHBACONF
	echo "host      all   md5_user       0/0    md5" >> $PGHBACONF
    fi

    echo "host      all   all       0/0    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 <<EOF
/^#local *replication/s/^#//p
/^#host *replication/s/^#//p
/^#host *replication/s/^#//p
w
q
EOF
}

#-------------------------------------------
# create basebackup.sh for streaming replication mode
# argument1: PostgreSQL database cluster
# argument2: cluster node number
#-------------------------------------------
function create_basebackup_stream {
SCRIPT=basebackup.sh
cat >> $1/$SCRIPT <<'EOF'
#! /bin/sh
psql=__PGBIN__/psql
pg_rewind=__PGBIN__/pg_rewind
DATADIR_BASE=__DATADIR_BASE__
PGSUPERUSER=__PGSUPERUSER__

master_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
EOF

echo "export PGHOST=$PGSOCKET_DIR" >> $1/$SCRIPT

pg_rewind_failed="true"

if [ $USE_PG_REWIND = "true" ];then
cat >> $1/$SCRIPT <<'EOF'    

# First try pg_rewind

# Make backup copy of postgresql.conf since pg_rewind blindly copies
# $master_db_cluster/postgresql.conf.
cp $DEST_CLUSTER/postgresql.conf /tmp/
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 rsync.
   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

$psql -p $PORT -c "SELECT pg_start_backup('Streaming Replication', true)" postgres

echo "source: $master_db_cluster dest: $DEST_CLUSTER" >> $log

rsync -C -a -c --delete --exclude postgresql.conf --exclude postmaster.pid \
--exclude postmaster.opts --exclude pg_log \
--exclude recovery.conf --exclude recovery.done \
--exclude pg_xlog --exclude pg_wal \
--exclude log \
$master_db_cluster/ $DEST_CLUSTER/

rm -fr $DEST_CLUSTER/pg_xlog 
mkdir $DEST_CLUSTER/pg_xlog
chmod 700 $DEST_CLUSTER/pg_xlog

rm -fr $DEST_CLUSTER/pg_wal 
mkdir $DEST_CLUSTER/pg_wal
chmod 700 $DEST_CLUSTER/pg_wal

rm $DEST_CLUSTER/recovery.done
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 <<REOF
primary_conninfo      = 'host=localhost port=$PORT user=$PGSUPERUSER application_name=''server$recovery_node'''
recovery_target_timeline='latest'
EOF

else

    cat >> $1/$SCRIPT <<'EOF'
cat > $DEST_CLUSTER/recovery.conf <<REOF
standby_mode          = 'on'
primary_conninfo      = 'host=localhost port=$PORT user=$PGSUPERUSER application_name=''server$recovery_node'''
recovery_target_timeline='latest'
EOF
fi

if [ $USE_REPLICATION_SLOT = "false" ];then
    cat >> $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'
if [ $pg_rewind_failed = "true" ];then
$psql -p $PORT -c "SELECT pg_stop_backup()" postgres
fi

if [ $pg_rewind_failed = "false" ];then
    cp /tmp/postgresql.conf $DEST_CLUSTER/
fi
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/" \
$1/$SCRIPT

chmod 755 $1/$SCRIPT
}

#-------------------------------------------
# create basebackup.sh for native replication mode
# argument: PostgreSQL database cluster directory
#-------------------------------------------
function create_basebackup_replication {
SCRIPT=basebackup.sh
cat >> $1/$SCRIPT <<'EOF'
#! /bin/sh
psql=__PGBIN__/psql
DATADIR_BASE=__DATADIR_BASE__
PGSUPERUSER=__PGSUPERUSER__
EOF

echo "export PGHOST=$PGSOCKET_DIR" >> $1/$SCRIPT

cat >> $1/$SCRIPT <<'EOF'
master_db_cluster=$1
recovery_node_host_name=$2
DEST_CLUSTER=$3
PORT=$4

log=$DATADIR_BASE/log/recovery.log

$psql -p $PORT -c "SELECT pg_start_backup('Native Replication', true)" postgres

echo "source: $master_db_cluster dest: $DEST_CLUSTER" > $log
EOF

if [ $PGVERSION -ge 120 ];then
    cat >> $1/$SCRIPT <<'EOF'
cat > $master_db_cluster/myrecovery.conf <<REOF
restore_command = 'cp __ARCHDIR__/%f %p'
REOF
EOF

else

    cat >> $1/$SCRIPT <<'EOF'
cat > $master_db_cluster/recovery.conf <<REOF
restore_command = 'cp __ARCHDIR__/%f %p'
REOF
EOF
fi

cat >> $1/$SCRIPT <<'EOF'
rsync -C -a -c --delete --exclude postgresql.conf --exclude postmaster.pid \
--exclude postmaster.opts --exclude pg_log \
--exclude recovery.done \
--exclude pg_xlog pg_wal \
$master_db_cluster/ $DEST_CLUSTER/

rm -fr $DEST_CLUSTER/pg_xlog 
mkdir $DEST_CLUSTER/pg_xlog
chmod 700 $DEST_CLUSTER/pg_xlog

rm -fr $DEST_CLUSTER/pg_wal
mkdir $DEST_CLUSTER/pg_wal
chmod 700 $DEST_CLUSTER/pg_wal
EOF

cat >> $1/$SCRIPT <<'EOF'
$psql -p $PORT -c "SELECT pg_stop_backup()" postgres
EOF

if [ $PGVERSION -ge 120 ];then
    cat >> $1/$SCRIPT <<'EOF'
touch $DEST_CLUSTER/recovery.signal
EOF
fi

#-------------------------------------------
# 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 pgpool_recovery_pitr (2nd stage script)for native replication mode
# argument: PostgreSQL database cluster directory
#-------------------------------------------
function create_pgpool_recovery_pitr {
SCRIPT=pgpool_recovery_pitr
cat >> $1/$SCRIPT <<'EOF'
#! /bin/sh
psql=__PGBIN__/psql
DATADIR_BASE=__DATADIR_BASE__
PGSUPERUSER=__PGSUPERUSER__

master_db_cluster=$1
recovery_node_host_name=$2
DEST_CLUSTER=$3
PORT=$4

log=$DATADIR_BASE/log/recovery.log
EOF

echo "export PGHOST=$PGSOCKET_DIR" >> $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
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 <<EOF
standby_mode          = 'on'
EOF
    fi

    cat > $1/$fname <<EOF
primary_conninfo      = 'host=localhost port=$BASEPORT user=$WHOAMI application_name=''server$2'''
recovery_target_timeline='latest'
EOF
    if [ $USE_REPLICATION_SLOT = true ];then
	echo "primary_slot_name = 'pgpool_setup_slot$2'" >> $1/$fname
    else
	cat >> $1/$fname <<EOF
restore_command = 'cp $BASEDIR/archivedir/`basename $1`/%f "%p" 2> /dev/null'
EOF
    fi
}

function set_pool_hba_conf {
    POOL_HBACONF=$1
    sed -i '/host.*all.*all.*trust$/s/^/#/g' $POOL_HBACONF
    sed -i '/local.*all.*all.*trust$/s/^/#/g' $POOL_HBACONF

    if [ $PGVERSION -ge 100 ];
    then
	echo "host    all         scram_user  0/0                   scram-sha-256" >> $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 "sr_check_user = '$WHOAMI'" >> $CONF
	echo "recovery_user = '$WHOAMI'" >> $CONF
	echo "recovery_password = ''"  >> $CONF
	echo "recovery_1st_stage_command = 'basebackup.sh'" >> $CONF

	if [ $MODE = "r" ];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 %M %H %P %r %R'" >> $CONF
	fi

	echo "socket_dir = '$PGSOCKET_DIR'" >> $CONF
	echo "pcp_socket_dir = '$PGSOCKET_DIR'" >> $CONF
}

#-------------------------------------------
# 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
}

#################################################################################
#
# main script
#
################################################################################
function usage()
{
	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
}

#-------------------------------------------
# Argument check
# usage: $0  [-m r|s|n][-n num_clusters][-p base_port][-pg pg_base_port][--no-stop][-d][-s][-r]
#-------------------------------------------
#
# default mode is streaming replication mode
MODE="s"
NO_STOP="false"

while [ $# -gt 0 ]
do
	if [ $1 = "-m" ];then
		shift
		case $1 in
			r ) MODE="r";;
			s ) MODE="s";;
			n ) MODE="n";;
			l ) MODE="l";;
			y ) MODE="y";;
			* ) usage;;
		esac
	elif [ $1 = "-n" ];then
		shift
		NUMCLUSTERS=$1
	elif [ $1 = "-p" ];then
		shift
		BASEPORT=$1
		ORIGBASEPORT=$1
	elif [ $1 = "-pg" ];then
		shift
		PGBASEPORT=$1
	elif [ $1 = "--no-stop" ];then
		shift
		NO_STOP="true"
	elif [ $1 = "-d" ];then
		PGPOOLDEBUG="true"
	elif [ $1 = "-s" ];then
	    USE_REPLICATION_SLOT="true"
	elif [ $1 = "-r" ];then
	    USE_PG_REWIND="true"
	elif [ $1 = "--help" -o $1 = "-o" ];then
		usage
		exit
	else
		usage
		exit
	fi
	shift
done

case $MODE in
	r) MODENAME="native replication mode"
		SAMPLE_CONF=$PGPOOLDIR/pgpool.conf.sample-replication
		;;
	s ) MODENAME="streaming replication mode"
		SAMPLE_CONF=$PGPOOLDIR/pgpool.conf.sample-stream
		;;
	n ) MODENAME="raw mode"
		SAMPLE_CONF=$PGPOOLDIR/pgpool.conf.sample
		;;
	l ) MODENAME="logical replication mode"
	    SAMPLE_CONF=$PGPOOLDIR/pgpool.conf.sample-logical
	    ;;
	y ) MODENAME="slony mode"
	    SAMPLE_CONF=$PGPOOLDIR/pgpool.conf.sample-master-slave
	    ;;
esac

# If USE_REPLICATION_SLOT is provided as an environment variable, turn on -s
if [ a$USE_REPLICATION_SLOT = a"true" ];then
    USE_REPLICATION_SLOT="true"
fi

# If USE_PG_REWIND is provided as an environment variable, turn on -r
if [ a$USE_PG_REWIND = a"true" ];then
    USE_PG_REWIND="true"
fi

#-------------------------------------------
# Make sure that current directory is empty
#-------------------------------------------
if [ "`/bin/ls`" != "" ]
then
	echo "$0: Current directory is not empty. Please remove files and directories then try again."
	exit 1
fi

exec 5> $BASEDIR/pgpool_setup.log

#-------------------------------------------
# everything looks good. starting setup...
#-------------------------------------------
echo "Starting set up in $MODENAME"

#-------------------------------------------
# assign base port for PostgreSQL
#-------------------------------------------
ORIG_BASEPORT=$BASEPORT
BASEPORT=$PGBASEPORT

#-------------------------------------------
# install pgpool.conf
#-------------------------------------------
test ! -d etc && mkdir etc
cp $SAMPLE_CONF $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' >> $SHUTDOWNALL
echo 'while [ -f $dir/run/pgpool.pid ];do sleep 1;done' >> $SHUTDOWNALL
if [ $CHECK_TIME_WAIT != "false" ];then
   echo "while  netstat -a|grep $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

#-------------------------------------------
# create failover script
#-------------------------------------------
echo "creating failover script"
create_failover_script >&5 2>&1

#-------------------------------------------
# create each PostgreSQL cluster
#-------------------------------------------
n=0
while [ $n -lt $NUMCLUSTERS ]
do
	CLUSTER="data"`expr $n`
	CLUSTERDIR=$BASEDIR/$CLUSTER
	PORT=`expr $BASEPORT + $n`

	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' ];then
		create_basebackup_replication $CLUSTERDIR >&5 2>&1
		create_pgpool_recovery_pitr $CLUSTERDIR >&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

	n=`expr $n + 1`

	echo "#$n port is $PORT" >> README.port

	# create archive directory
	test ! -d archivedir/$CLUSTER && mkdir -p archivedir/$CLUSTER

done

set_pgpool_conf $CONF
set_pool_hba_conf $BASEDIR/etc/pool_hba.conf

PGPOOL_PORT=$ORIG_BASEPORT
echo "port = $PGPOOL_PORT" >> $CONF
PCP_PORT=`expr $PGPOOL_PORT + 1`
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.
#-------------------------------------------

if [ $MODE = 's' ];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 <<EOF
CREATE EXTENSION pgpool_regclass;
CREATE EXTENSION pgpool_recovery;
CREATE DATABASE test;
EOF
	if [ $USE_REPLICATION_SLOT = "true" ];then
	    n=0
	    while [ $n -lt $NUMCLUSTERS ]
	    do
		$PSQL -p $BASEPORT template1 -c "SELECT * FROM pg_create_physical_replication_slot('pgpool_setup_slot$n')" >&5 2>&1
		n=`expr $n + 1`
	    done
	fi

	n=0
	PORT=$BASEPORT
	CLUSTER="data"`expr $n`
	CLUSTERDIR=$BASEDIR/$CLUSTER
	echo "backend_hostname$n = '$PGSOCKET_DIR'" >> $CONF
	echo "backend_port$n = $PORT" >> $CONF
	echo "backend_weight$n = 1" >> $CONF
	echo "backend_data_directory$n = '$CLUSTERDIR'" >> $CONF
	echo "backend_application_name$n = 'server$n'" >> $CONF

# 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

	if [ $NUMCLUSTERS -gt 1 ];then
		n=1
		while [ $n -lt $NUMCLUSTERS ]
		do
		    # create archive directory
		    test ! -d $BASEDIR/archivedir/`basename $CLUSTER` && mkdir $BASEDIR/archivedir/`basename $CLUSTER`

		# set up pgpool.conf
			PORT=`expr $PORT + 1`
			echo "backend_hostname$n = '$PGSOCKET_DIR'" >> $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_application_name$n = 'server$n'" >> $CONF
			n=`expr $n + 1`
		done
		$PGPOOL_INSTALL_DIR/bin/pgpool -f $BASEDIR/etc/pgpool.conf reload
	fi

	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

#	
#	replication mode
#
else
	n=0
	PORT=$BASEPORT
	CLUSTER="data"`expr $n`
	CLUSTERDIR=$BASEDIR/$CLUSTER

	while [ $n -lt $NUMCLUSTERS ]
	do
	    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 <<EOF
CREATE EXTENSION pgpool_regclass;
CREATE EXTENSION pgpool_recovery;
CREATE DATABASE test;
EOF
		$PG_CTL -m f -D data${n} stop >&5 2>&1
	    fi

	# set up pgpool.conf
		echo "backend_hostname$n = '$PGSOCKET_DIR'" >> $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_MASTER" >> $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

	echo "start all"
	$STARTALL >&5 2>&1
	echo -n "waiting for pgpool-II coming up..."
	wait_for_pgpool_startup
#	sleep 20
	echo "done."
fi

if [ $MODE = "r" -o $MODE = "n" ];then
	echo "create extensions"
	$PSQL -p $PGPOOL_PORT template1 >&5 2>&1 <<EOF
CREATE EXTENSION pgpool_regclass;
CREATE EXTENSION pgpool_recovery;
CREATE DATABASE test;
EOF
fi

#-------------------------------------------
# create follow_masterfailover script
#-------------------------------------------
if [ $MODE = "s" ];then
	echo "creating follow master script"
	create_follow_master_script >&5 2>&1
	echo "follow_master_command = '$FOLLOW_MASTER_SCRIPT %d %h %p %D %m %M %H %P %r %R'" >> $CONF
fi

$PSQL -p $PGPOOL_PORT test <<EOF
show pool_nodes;
EOF

echo "pgpool port is $PGPOOL_PORT" >> 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