clone postgres DB

clonedb.sh

################################################################################################
##--GEO--C-- this script copies a database to another database on the same machine or different machine 
##            
##
################################################################################################

echo "To run this script supply fromhost, fromdbe, user,  port, toHost,todb "
echo "E.g int503(fromhost) mydb(fromdb) myuser(user)  (5432)port To: int503 newdb(todb) "
echo "-------------------------------------------------------------- "

################################################################################################
if [ -z "$1" ]
then echo "Paramter %1(host) is empty";
     exit 1
else
   bash hh $1;
   #if [ $count -eq 0 ]
  # then exit 1
  # else
           if [ -z "$2" ]
           then echo "Paramter %2(db) is empty"
                exit 1
           else
                if [ -z "$3" ]
                then echo  "Paramter %3(user) is empty"
                     exit 1
                else
                        if [ -z "$4" ]
                        then echo  "Paramter %4(port) is empty"
                             exit 1
                        else
                                if [ -z "$5" ]
                                then  echo "Paramter %5(tohost) is empty. "
                                fi
                        fi
                fi
         #  fi
        fi
fi
################################################################################################
## Set Variables
frmhost=$1
frmdb=$2
tohost=$5
todb=$6
user=$3
port=$4

################################################################################################
### set connection
export frmconn="-h $frmhost -d $frmdb -p $port -U $user"
export toconn="-h $tohost -d $todb -p $port -U $user"

echo "Start time: $( date +%T ) "

##Write to tempfile
fname="clone_$frmhost$frmdb"
cmd="set role sysdba;"
cmd="$cmd  SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$todb';"
cmd="$cmd drop database if exists $todb;"
cmd="$cmd create database  $todb  with template template1 owner dba;"
cmd="$cmd GRANT CONNECT ON DATABASE $todb  TO devgrp, sysdba,pghc;revoke CONNECT, TEMPORARY ON DATABASE $todb  from public;"

echo $cmd >$fname
################################################################################################
## execute 
echo "executing psql $toconn -f $fname "
psql $toconn -f $fname
echo "Create DB time: $( date +%T ) "
pg_dump  $frmconn -F custom | pg_restore $toconn --role=sysdba -c >$fname$tohost$todb$port$user 2>$fname$tohost$todb$port$user

echo "Finished time: $( date +%T ) "