[pgpool-general: 1574] Tool to create testing environment

Tatsuo Ishii ishii at postgresql.org
Fri Apr 5 12:49:36 JST 2013


Hi,

I have created a small tool called "pgpool_setup". You can create a
pgpool-II testing environment in a single box by using
"pgpool_setup"(absolutely not suitable for production environment,
though). All you need is just a Linux/UNIX box. The environment is
created under current directory. No other place will be touched. The
environment includes:

- pgpool-II instance
- two PostgreSQL instances
- fail over/online recovery scripts

So you can not only test pgpool-II, but also can try fail over (just
stop node: pg_ctl -D data1 stop or something like that) or online
recovery by using pcp_recovery_node.

You can create as many as different environments in your box. This
tool will be shipped with upcoming pgpool-II major release(3.3) and
already committed on master branch under "test" directory. However you
can use it with pgpool-II 3.2 as well(I have not tested with 3.1, but
should be no problem). pgpool-II and PostgreSQL(requires 9.1 or later)
installation must be completed beforehand and they should be in your
command search path.

Currently you have choice of streaming replication mode (the default)
or native replication mode(specify "-m r" option). You will become
PostgreSQL super user. The initial user name and password for pcp
command is your account.

After completion of the tool, you can start pgpool-II and PostgreSQL
by type:

./startall

and can connect to pgpool-II:

psql -p 11002 test
("test" database is automatically created by the tool)

Ports used for this environment is from 11000(for PostgreSQL instance
#1), 11001(for PostgreSQL instance #2), 11002(pgpool-II) and
11003(pcp).

After finishing testing/evaluation, type:

./shutodownall

to stop pgpool-II and PostgreSQL.

Here is the sample session:

----------------------------------------------------------------------
$ pgpool_setup 
Satrting set up in streaming replication mode
creating startall and shutdownall
creating failover script
creating database cluster /home/t-ishii/work/git.postgresql.org/tmp/data0...done.
update postgreql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
creating database cluster /home/t-ishii/work/git.postgresql.org/tmp/data1...done.
update postgreql.conf
creating pgpool_remote_start
creating basebackup.sh
creating recovery.conf
temporarily start data0 cluster to create extensions
temporarily start pgpool-II to create data1
waiting for failover happens...done.
recovery node 1...done.
shutdown all
start all
waiting for pgpool-II coming up...done.
Pager usage is off.
 node_id | hostname | port  | status | lb_weight |  role   
---------+----------+-------+--------+-----------+---------
 0       | /tmp     | 11000 | 2      | 0.500000  | primary
 1       | /tmp     | 11001 | 2      | 0.500000  | standby
(2 rows)

shutdown all

pgpool-II setting for streaming replication mode is done.
To start the whole system, use /home/t-ishii/work/git.postgresql.org/tmp/startall.
To shutdown the whole system, use /home/t-ishii/work/git.postgresql.org/tmp/shutdownall.
pcp command user name is "t-ishii", password is "t-ishii".
Each PostgreSQL, pgpool-II and pcp port is as follows:
#1 port is 11000
#2 port is 11001
pgpool port is 11002
pcp port is 11003
The info above is in README.port.
----------------------------------------------------------------------

Hope this small tool help you in testing, evaluating and debugging
pgpool-II!
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
-------------- next part --------------
#! /bin/sh
# Copyright (c) 2013 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]
# -m s: create an installation as streaming replication mode.
# (the default)
# -m r: create an installation as native replication 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.
#
# 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, $BASEPORT+1 and so on.
# pgpool port and pcp_port will be assigned afterwards.
BASEPORT=11000
# Number of PostgreSQL database clusters
# XXX: more than two PostgreSQL clusters can be created but some of
# part like failover script will not work correctly.
NUMCLUSTERS=2
# Where to look for pgpool.conf.sample*
PGPOOLDIR=/usr/local/etc
# LD_LIBRARY_PATH 
LPATH=/usr/local/pgsql/lib
# initdb args
INITDBARG="--no-locale -E UTF_8"
#-------------------------------------------
# End of configuration section
#-------------------------------------------
#
# user name
WHOAMI=`whoami`

# our root directory
BASEDIR=`pwd`

# PostgreSQL bin directory
PGBIN=`pg_config --bindir` || (echo "$0: cannot locate pg_config";exit 1)
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
# 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

#-------------------------------------------
# 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
#                  %% = '%' character
#                  %R = new master database cluster path
#                  %% = '%' 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 [ $failed_node_id = $old_primary_node_id ];then	# master failed
    if [ $failed_node_id = 0 ];then
		echo $pg_ctl -D $cluster1 promote >>$log	# let standby take over
		$pg_ctl -D $cluster1 promote >>$log	# let standby take over
    else
		echo $pg_ctl -D $cluster0 promote >>$log	# let standby take over
		$pg_ctl -D $cluster0 promote >>$log	# let standby take over
   fi
fi
EOF

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

chmod 755 $FAILOVER_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 at __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

	if [ $MODE = "s" ];then
		echo "hot_standby = on" >> $PGCONF
		echo "wal_level = hot_standby" >> $PGCONF
		echo "max_wal_senders = 2" >> $PGCONF
		echo "archive_mode = on" >> $PGCONF
		echo "archive_command = 'cp %p $BASEDIR/archivedir/%f </dev/null'" >> $PGCONF
	else
		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
DATA0_PORT=__DATA0_PORT__
DATA1_PORT=`expr $DATA0_PORT + 1`
DATA2_PORT=`expr $DATA1_PORT + 1`
DATADIR_BASE=__DATADIR_BASE__
DATA0_DIR=${DATADIR_BASE}"/data0"
DATA1_DIR=${DATADIR_BASE}"/data1"
DATA2_DIR=${DATADIR_BASE}"/data2"
PGSUPERUSER=__PGSUPERUSER__

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

log=$DATADIR_BASE/log/recovery.log

if [ $master_db_cluster = $DATA0_DIR ];then
  PORT=$DATA0_PORT
  SOURCE_CLUSTER=$DATA0_DIR
elif [ $master_db_cluster = $DATA1_DIR ];then
  PORT=$DATA1_PORT
  SOURCE_CLUSTER=$DATA1_DIR
else
  PORT=$DATA2_PORT
  SOURCE_CLUSTER=$DATA2_DIR
fi

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

echo "source: $SOURCE_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 \
$SOURCE_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 at __PGBIN__@$PGBIN@
/__DATA0_PORT__/s/__DATA0_PORT__/$BASEPORT/
/__DATADIR_BASE__/s at __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
DATA0_PORT=__DATA0_PORT__
DATA1_PORT=`expr $DATA0_PORT + 1`
DATA2_PORT=`expr $DATA1_PORT + 1`
DATADIR_BASE=__DATADIR_BASE__
DATA0_DIR=${DATADIR_BASE}"/data0"
DATA1_DIR=${DATADIR_BASE}"/data1"
DATA2_DIR=${DATADIR_BASE}"/data2"
PGSUPERUSER=__PGSUPERUSER__

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

log=$DATADIR_BASE/log/recovery.log

if [ $master_db_cluster = $DATA0_DIR ];then
  PORT=$DATA0_PORT
  SOURCE_CLUSTER=$DATA0_DIR
elif [ $master_db_cluster = $DATA1_DIR ];then
  PORT=$DATA1_PORT
  SOURCE_CLUSTER=$DATA1_DIR
else
  PORT=$DATA2_PORT
  SOURCE_CLUSTER=$DATA2_DIR
fi

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

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

cat > $SOURCE_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 \
$SOURCE_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 at __PGBIN__@$PGBIN@
/__DATA0_PORT__/s/__DATA0_PORT__/$BASEPORT/
/__DATADIR_BASE__/s at __DATADIR_BASE__@$BASEDIR@
/__PGSUPERUSER__/s/__PGSUPERUSER__/$WHOAMI/
/__ARCHDIR__/s at __ARCHDIR__@$BASEDIR/archivedir at 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
DATA0_PORT=__DATA0_PORT__
DATA1_PORT=`expr $DATA0_PORT + 1`
DATA2_PORT=`expr $DATA1_PORT + 1`
DATADIR_BASE=__DATADIR_BASE__
DATA0_DIR=${DATADIR_BASE}"/data0"
DATA1_DIR=${DATADIR_BASE}"/data1"
DATA2_DIR=${DATADIR_BASE}"/data2"
PGSUPERUSER=__PGSUPERUSER__

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

log=$DATADIR_BASE/log/recovery.log

if [ $master_db_cluster = $DATA0_DIR ];then
  PORT=$DATA0_PORT
  SOURCE_CLUSTER=$DATA0_DIR
elif [ $master_db_cluster = $DATA1_DIR ];then
  PORT=$DATA1_PORT
  SOURCE_CLUSTER=$DATA1_DIR
else
  PORT=$DATA2_PORT
  SOURCE_CLUSTER=$DATA2_DIR
fi

# 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 at __PGBIN__@$PGBIN at p
/__DATA0_PORT__/s/__DATA0_PORT__/$BASEPORT/p
/__DATADIR_BASE__/s at __DATADIR_BASE__@$BASEDIR at p
/__PGSUPERUSER__/s/__PGSUPERUSER__/$WHOAMI/p
/__ARCHDIR__/s at __ARCHDIR__@$BASEDIR/archivedir at 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

	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
}

#################################################################################
#
# main script
#
################################################################################

#-------------------------------------------
# Argument check
# usage: $0 [-m r|s]"
#-------------------------------------------
#
# default mode is streaming replication mode
MODE="s"

while [ $# -gt 0 ]
do
	if [ $1 = "-m" ];then
		shift
		case $1 in
			r ) MODE="r";;
			s ) MODE="s";;
			* ) echo "usage: $0 [-m r|s]";exit 1;;
		esac
	elif [ $1 = "--help" -o $1 = "-o" ];then
		echo "usage: $0 [-m r|s]"
		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
		;;
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"

#-------------------------------------------
# install pgpool.conf
#-------------------------------------------
test ! -d etc && mkdir etc
cp $SAMPLE_CONF $CONF

#-------------------------------------------
# create startall and shutdownall
#-------------------------------------------
echo "creating startall and shutdownall"
echo "LD_LIBRARY_PATH=$LPATH" > $STARTALL
echo 'dir=`pwd`' >> $STARTALL
chmod 755 $STARTALL
echo 'dir=`pwd`' > $SHUTDOWNALL
echo 'pgpool -f $dir/etc/pgpool.conf -m f stop' >> $SHUTDOWNALL
chmod 755 $SHUTDOWNALL

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

	# set up pgpool.conf
	echo "backend_hostname$n = ''" >> $CONF
	echo "backend_port$n = $PORT" >> $CONF
	echo "backend_weight$n = 1" >> $CONF
	echo "backend_data_directory$n = '$CLUSTERDIR'" >> $CONF

	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=`expr $PORT + 1`
echo "port = $PGPOOL_PORT" >> $CONF
PCP_PORT=`expr $PORT + 2`
echo "pcp_port = $PCP_PORT" >> $CONF

test ! -d run && mkdir run
echo "pid_file_name = '$BASEDIR/run/pgpool.pid'" >> $CONF
test ! -d log && mkdir log
echo "logdir = '$BASEDIR/log'" >> $CONF

echo 'pgpool -D -n -f $dir/etc/pgpool.conf -F $dir/etc/pcp.conf -a $dir/etc/pool_hba.conf > $dir/log/pgpool.log 2>&1 &' >> $STARTALL

# create pcp.conf
cp $PGPOOLDIR/pcp.conf.sample etc/pcp.conf
echo -n "${WHOAMI}:" >> etc/pcp.conf
pg_md5 $WHOAMI >> etc/pcp.conf

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

# temporarily start pgpool
	echo "temporarily start pgpool-II to create data1"
	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 &

	echo -n "waiting for failover happens..."
# wait for failover happens
	sleep 5
	echo "done."

# recovery data1 and so on
	n=1
	while [ $n -lt $NUMCLUSTERS ]
	do
		echo -n "recovery node $n..."
		pcp_recovery_node 1 localhost $PCP_PORT $WHOAMI $WHOAMI $n
		echo "done."
		n=`expr $n + 1`
	done

	echo "shutdown all"
	$SHUTDOWNALL >&5 2>&1
fi

echo "start all"
$STARTALL >&5 2>&1

echo -n "waiting for pgpool-II coming up..."
sleep 20
echo "done."

if [ $MODE = "r" ];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

$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

echo "shutdown all"
$SHUTDOWNALL >&5 2>&1

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."


More information about the pgpool-general mailing list