pgpool2 3.6.1-1 / usr / sbin / pgpool_setup

#!/usr/bin/env bash
#
# Copyright (c) 2013-2016 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][-n num_clusters][-p base_port][-pg pg_base_port][--no-stop][-d]
# -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.
# -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
#
# 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"}
# 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:-"/usr/local"}
PGPOOLDIR=${PGPOOLDIR:-${PGPOOL_INSTALL_DIR}/etc}
# PostgreSQL commands(initdb, pg_ctl, psql) install dir
PGBIN=${PGBIN:-"/usr/local/pgsql/bin"}
# LD_LIBRARY_PATH 
LPATH=${PGLIB:-"/usr/local/pgsql/lib"}
# unix socket directory
PGSOCKET_DIR=${PGSOCKET_DIR:-"/tmp"}
# initdb args
INITDBARG="--no-locale -E UTF_8"
#-------------------------------------------
# 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

# 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

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

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

#-------------------------------------------
# replace some variables in the script
#-------------------------------------------
/bin/ed $FAILOVER_SCRIPT <<EOF
/__MYDIR__/s@__MYDIR__@$BASEDIR@
/__PGBIN__/s@__PGBIN__@$PGBIN@
w
q
EOF

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__
export PCPPASSFILE=__PCPPASSFILE__

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
if $pg_ctl -D $failed_db_cluster status|grep "is running" >/dev/null 2>&1
then
	$pg_ctl -w -m f -D $failed_db_cluster stop >> $log 2>&1
	sleep 10
	# recovery the node
	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/ed $FOLLOW_MASTER_SCRIPT <<EOF
/__MYDIR__/s@__MYDIR__@$BASEDIR@
/__PGBIN__/s@__PGBIN__@$PGBIN@
/__PCPPASSFILE__/s@__PCPPASSFILE__@$PCP_PASS_FILE@
/__PCPPORT__/s/__PCPPORT__/$PCP_PORT/
w
q
EOF

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/ed $1/$PGPOOL_REMOTE_START_SCRIPT <<EOF
/__PGBIN__/s@__PGBIN__@$PGBIN@
w
q
EOF

chmod 755 $1/$PGPOOL_REMOTE_START_SCRIPT

}

#-------------------------------------------
# set postgresql.conf
# argument: PostgreSQL database cluster directory
#-------------------------------------------
function set_postgresql_conf
{
	PGCONF=$1/postgresql.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 [ $MODE = "s" ];then
		echo "hot_standby = on" >> $PGCONF
		echo "wal_level = hot_standby" >> $PGCONF
		echo "max_wal_senders = $NUMCLUSTERS" >> $PGCONF
		echo "archive_mode = on" >> $PGCONF
		echo "archive_command = 'cp %p $BASEDIR/archivedir/%f </dev/null'" >> $PGCONF
	elif [ $MODE = 'r' ];then
		echo "wal_level = archive" >> $PGCONF
		echo "archive_mode = on" >> $PGCONF
		echo "archive_command = 'cp %p $BASEDIR/archivedir/%f </dev/null'" >> $PGCONF
	fi

	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
# argument: PostgreSQL database cluster directory
#-------------------------------------------
function create_basebackup_stream {
SCRIPT=basebackup.sh
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

$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 \
$master_db_cluster/ $DEST_CLUSTER/

rm -fr $DEST_CLUSTER/pg_xlog 
mkdir $DEST_CLUSTER/pg_xlog
chmod 700 $DEST_CLUSTER/pg_xlog
rm $DEST_CLUSTER/recovery.done
cat > $DEST_CLUSTER/recovery.conf <<REOF
standby_mode          = 'on'
primary_conninfo      = 'port=$PORT user=$PGSUPERUSER'
recovery_target_timeline='latest'
REOF

$psql -p $PORT -c "SELECT pg_stop_backup()" postgres
EOF

#-------------------------------------------
# replace some variables in the script
#-------------------------------------------
/bin/ed $1/$SCRIPT <<EOF
/__PGBIN__/s@__PGBIN__@$PGBIN@
/__DATADIR_BASE__/s@__DATADIR_BASE__@$BASEDIR@
/__PGSUPERUSER__/s/__PGSUPERUSER__/$WHOAMI/
w
q
EOF

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__

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

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

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

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

$psql -p $PORT -c "SELECT pg_stop_backup()" postgres
EOF

#-------------------------------------------
# replace some variables in the script
#-------------------------------------------
/bin/ed $1/$SCRIPT <<EOF
/__PGBIN__/s@__PGBIN__@$PGBIN@
/__DATADIR_BASE__/s@__DATADIR_BASE__@$BASEDIR@
/__PGSUPERUSER__/s/__PGSUPERUSER__/$WHOAMI/
/__ARCHDIR__/s@__ARCHDIR__@$BASEDIR/archivedir@p
w
q
EOF

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

# Force to flush current value of sequences to xlog 
$psql -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/ed $1/$SCRIPT <<EOF
/__PGBIN__/s@__PGBIN__@$PGBIN@p
/__DATADIR_BASE__/s@__DATADIR_BASE__@$BASEDIR@p
/__PGSUPERUSER__/s/__PGSUPERUSER__/$WHOAMI/p
/__ARCHDIR__/s@__ARCHDIR__@$BASEDIR/archivedir@p
w
q
EOF

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 [ $2 = "0" ];then
	fname=recovery.done
else
	fname=recovery.conf
fi

cat > $1/$fname <<EOF
standby_mode          = 'on'
primary_conninfo      = 'port=$BASEPORT user=$WHOAMI'
recovery_target_timeline='latest'
restore_command = 'cp $BASEDIR/archivedir/pg_xlog/%f "%p" 2> /dev/null'
EOF
}

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

	echo "health_check_period = 10" >> $CONF
	echo "health_check_user = '$WHOAMI'" >> $CONF
	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] [-n num_clusters] [-p base_port] [-pg pg_base_port][--no-stop] [-d]";exit 1
}

#-------------------------------------------
# Argument check
# usage: $0  [-m r|s|n][-n num_clusters][-p base_port][-pg pg_base_port][--no-stop][-d]
#-------------------------------------------
#
# 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";;
			* ) usage;;
		esac
	elif [ $1 = "-n" ];then
		shift
		NUMCLUSTERS=$1
	elif [ $1 = "-p" ];then
		shift
		BASEPORT=$1
	elif [ $1 = "-pg" ];then
		shift
		PGBASEPORT=$1
	elif [ $1 = "--no-stop" ];then
		shift
		NO_STOP="true"
	elif [ $1 = "-d" ];then
		PGPOOLDEBUG="true"
		shift;
	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
		;;
esac

#-------------------------------------------
# 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 "Satrting 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

#-------------------------------------------
# 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
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 postgreql.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 >&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

done

set_pgpool_conf $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

# create archive directory
mkdir archivedir

#-------------------------------------------
# if streaming replication mode, we need to create data1 and so on, by
# using on line 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

	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

# 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
		# 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
			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`
		if [ $n -lt $NUMCLUSTERS ];then
			wait_for_pgpool_startup
#			sleep 10
		fi
	done

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

	while [ $n -lt $NUMCLUSTERS ]
	do
	# set up pgpool.conf
		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
		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