公司最近在使用postgresql数据库,开发给了我们很多ddl/dml语句,于是我就花了2天时间完成了这套脚本,用来创建表空间,用户,并且自动导入数据的脚本




#!/bin/bash
# 2013/07/26, DD.
# Usage: install_postgreSQL_db.sh --dbname <database name> [ --userid <user name> ] [ --passwd <user passwrod> ] [ --port <database connection port> ] [ --ctlfile <control file path > ] [ --datadir <data file store path> ] [ -l <log direcotry> ] [ --init ] [ --create ] [--help ]
# FinShare DB SQL (DDL/DML) installation script for Postgre
# postgre database install script
# must use account postgres to login linux to run this script
# must add account postgres to /etc/sudoers, and can execute mkdir、chown commands
# must special one control file for this script, script will read this file to complete database initialization or execute DDL/DML script
#
Usage() {
  echo "Usage:"
  echo "   $0 -d <dbname>                     (to run DDL/DML script)"
  echo "   $0 -d <dbname> --create            (to create a database)"
  echo "   $0 -d <dbname> --init -D <datadir> (to initialize the database)"
  echo " "
  echo "Commands:"
  echo "Either long or short options are allowed."
  echo "    -d, --dbname.    database name"
  echo "    -u, --userid.    database user name, default is (postgres)"
  echo "    -p, --passwd.    user postgres's password"
  echo "    -P, --port.      database connection port, default is 5432"
  echo "    -f, --ctlfile.   control file. default is .ctl in current directory"
  echo "    -l, --logdir.    log file directory. default is /tmp"
  echo "    -c, --cerate.    if the database does not exist, add this parameter to create"
  echo "                     a database"
  echo "    -i, --init.      to initialize the database"
  echo "    -D, --datadir.   directory to store data"
  echo "    -h, --help.      print help information"
  echo " "
  if [ "X$1" != "X" ]; then
    echo $1
  fi
  if [ "$help" == "true" ]
  then
    echo "  Control file can have comment lines which start with # and empty lines."
    echo "  if run script has special --init option, script will read lines start wiht (tablespace:) in control file to create table space and account, other lines will be temporary ignored. after finished create, it will execute in order. "
    echo " if cannot find lines start with tablespace: in control file, then it fails."
    echo "   To initial database, use following line:"
    echo "     tablespace:tablespaceName1:tablespaceName2:tablespaceName3:tablespaceName{n}:SchemaName"
    echo " if not special --init option, it will ignore lines start with (tablespace:), and then execute sql (DDL\DML)files in order."
    echo " each line can only contains two fields, if contains more fields ,then it fails."
    echo "   To install ddl/dml, use following line:"
    echo "      filePath:Schemaname"
    echo "  If control file is not provided in -F, then it will find the file with extension .ctl"
    echo "    in current directory. if there are more than one .ctl files, then it fails."
    echo "  The control file directory is the scripts root directory."
    echo "  Command is to run a single sql script. It is the line in control file for example."
    echo "    the command script root directory is current directory."
    echo " "
    echo "Note:"
    echo "  In control file, all directory path use / (don't use \)."
    echo " "
    fi
  exit 1
}
func_CheckError() {
  sqlErrFound=0
  if [ -n "`grep -E '^psql|^ERROR:|does not exist$|already exists$|No such file$' ${logfileTmp}`" ]
  then
    sqlErrFound=1
  fi
}
func_PorcessCtl() {
  line=`echo $line | tr -d '\136\015\010'`
  if [ "X$line" != "X" ]
  then
    if [ "$1" == "yes" ]
    then
      params=`echo $line | awk -F: '{ for (i=2; i<=NF-1; i++) printf "%s ", $i}'`
      ##parmsNUM=`echo $parmas {'print NF'}`
      schema=`echo $line | awk -F: {'print $NF'}`
      if [ "X$params" != "X" ]
      then
        func_createSchema
        for m in $params
        do
          tablespaceName=$m
          func_createTabspa
        done
      fi
    fi
    if [ "$1" == "no" ]
    then
      filePath=$scriptdir/`echo $line | awk -F: {'print $1'}`
      schema=`echo $line | awk -F: {'print $2'}`
      if [ ! -f $filePath ]
      then
        echo Error: $filePath : no sush file or directory | tee -a $logfile
        exit 1
      fi
      if [ "X$schema" == "X" ]
      then
        func_runSqlfile
      else
        func_changeSchema yes
        func_runSqlfile
        func_changeSchema
      fi
    fi
  fi  
}
func_createSchema() {
  totalschema=`expr $totalschema + 1`
  #drop current schmea
  echo "Drop schema $schema if exists"
  $psqlCMD -h $hostname -p $port -d $dbname -U $userid -w -a -e -c "drop schema IF EXISTS $schema cascade;" >> $logfile 2>&1
  #recreate current schema
  echo "***** create schema $schema" | tee -a $logfile
  $psqlCMD -h $hostname -p $port -d $dbname -U $userid -w -a -e -c "create schema $schema;" >> $logfile 2>&1
}
func_createTabspa() {
  #change search_path to current schema
  totalspace=`expr $totalspace + 1`
  #echo "change $userid's default search_path to $schema" | tee -a $logfile
  #$psqlCMD -h $hostname -p $port -d $dbname -U $userid -w -a -e -c "ALTER ROLE $userid SET search_path to $schema;">>$logfile 2>&1
  echo "***** create data directory $datadir/$tablespaceName" | tee -a $logfile
  sudo mkdir -p $datadir/$tablespaceName
  echo "***** change data directory ownership to $userid"
  sudo chown -R $userid:$userid $datadir/$tablespaceName
  echo "***** drop tablespace if already exists"
  $psqlCMD -h $hostname -p $port -d $dbname -U $userid -w -a -e -c "DROP TABLESPACE IF EXISTS $tablespaceName;" >> $logfile 2>&1
  echo "***** create tablespace $tablespaceName" | tee -a $logfile
  $psqlCMD -h $hostname -p $port -d $dbname -U $userid -w -a -e -c "CREATE TABLESPACE $tablespaceName LOCATION '$datadir/$tablespaceName';" >> $logfile 2>&1
  if [ $? -eq 0 ]; then
     echo "---------------------- $tablespaceName created" | tee -a $logfile
  else
     echo "---------------------- $tablespaceName create failed" | tee -a $logfile
  fi
}
func_changeSchema() {
  if [ "$1" == "yes" ]
  then
    echo "---------------------------------------------" | tee -a $logfile
    echo "change $userid's default search_path to $schema" | tee -a $logfile
    $psqlCMD -h $hostname -p $port -d $dbname -U $userid -w -a -e -c "ALTER ROLE $userid SET search_path to $schema;" >> $logfile 2>&1
  else
    echo "---------------------------------------------" | tee -a $logfile
    echo "change default search_path back to public" | tee -a $logfile
    $psqlCMD -h $hostname -p $port -d $dbname -U $userid -w -a -e -c "ALTER ROLE $userid SET search_path to public;" >> $logfile 2>&1
  fi
}
func_runSqlfile() {
  totalfiles=`expr $totalfiles + 1`
  echo "=== Executing file $filePath" | tee -a $logfile
  $psqlCMD -h $hostname -p $port -d $dbname -U $userid -w -a -e -f $filePath >> $logfileTmp 2>&1
  errorSqlFile=$?
  func_CheckError
  if [ $errorSqlFile -ne 0 ] || [ $sqlErrFound -ne 0 ]
  then
    errfiles=`expr $errfiles + 1`
    echo "Error in $filePath" >> $logfileTmp
    echo "Error in $filePath. Check details in file - $logfile"
  fi
  if [ -f $logfileTmp ]
  then
    cat $logfileTmp >> $logfile
    rm -f $logfileTmp
  fi
}
func_createDatadir() {
  while true
  do
    read -p "Speciel the data directory: " datadir
    if [ -d $datadir ]
    then
      if [ `ls $datadir | wc -l` -ne 0 ]
      then
         echo "$datadir is already exist, but it is not empty" | tee -a $logfile
         echo "please select a another directory"
      else
        datadir=$datadir
        break
      fi
    else
      echo "create data directoy $datadir" | tee -a >> $logfile
      sudo mkdir -p $datadir
      break 
    fi
  done
}
# ========================================
#echo Parsing command line arguments
numargs=$#
i=1
scriptname=`basename "$0"`
scriptdir=`pwd "$0"`
psqlCMD=psql
createdbCMD=createdb
hostname="localhost"
initdb="no"
createdb="no"
help="false"
dbname=""
userid=""
port=""
controlfile=""
controlcmd=""
logdir=""
if [ "$USER" == "root" ]
then
  echo "User is "root", running this script must use "postgres""
  exit 1
fi
while [ $i -le $numargs ]
do
  j=$1
  if [ $j = "--dbname" ] || [ $j = "-d" ]
  then
    dbname=$2
    shift 1
    i=`expr $i + 1`
  fi
  if [ $j = "--userid" ] || [ $j = "-u" ]
  then
    userid=$2
    shift 1
    i=`expr $i + 1`
  fi
  if [ $j = "--ctlfile" ] || [ $j = "-f" ]
  then
    userid=$2
    shift 1
     i=`expr $i + 1`
  fi
  if [ $j = "--port" ] || [ $j = "-p" ]
  then
    port=$2
    shift 1
    i=`expr $i + 1`
  fi
  if [ $j = "--passwd" ] || [ $j = "-p" ]
  then
    port=$2
    shift 1
    i=`expr $i + 1`
  fi
  if [ $j = "--logfile" ] || [ $j = "-l" ]
  then
    logdir=$2
    shift 1
    i=`expr $i + 1`
  fi
  if [ $j = "--datadir" ] || [ $j = "-D" ]
  then
    datadir=$2
    shift 1
    i=`expr $i + 1`
  fi
  if [ $j = "--init" ] || [ $j = "-i" ]
  then
    initdb=yes
  fi
  if [ $j = "--create" ] || [ $j = "-c" ]
  then
    createdb=yes
  fi
  if [ $j = "--help" ] || [ $j = "-h" ]
  then
    help=true
  fi
  i=`expr $i + 1`
  shift 1
done
if [ $help = "ture" ]
then
   Usage
fi
if [ "X$dbname" == "X" ]
then
  Usage "ERROR: dbname is empty."
fi
if [ "X$userid" == "X" ]
then
  userid=postgres
fi
if [ "X$port" == "X" ]
then
  port=5432
fi
if [ "X$logdir" == "X" ]
then
  logdir=/tmp
else
  if [ ! -d $logdir ]
  then
    echo create log dirctory $logdir
    sudo mkdir -p $logdir
  fi
fi
logfile=$logdir/${scriptname}_${dbname}_`date +%Y-%m-%d_%H_%M_%S`.log
logfileTmp=${logfile}.tmp
if [ "X$pgpasswd" == "X" ]
then
  while true
  do
    stty -echo  
    read -p "Enter $userid's password: " PGPASSWORD
    stty echo
    if [ ! -z $PGPASSWORD ] || [ "X$PGPASSWORD" != "X" ]
    then
      export PGPASSWORD=$PGPASSWORD
      break
    fi
  done
else
  export PGPASSWORD=$PGPASSWORD
fi
if [ "$createdb" == "yes" ]
then
  echo -n "Special the owner of database $dbname, default user is "fscs": "
  read isFSCS
  echo "Special the owner of database $dbname, default user is "fscs": $isFSCS " >> $logfile
  if [ -z $isFSCS ] || [ "$isFSCS" == "X" ]
  then
    dbuser=fscs
  else
    dbuser=$isFSCS
  fi
  createuser -s $dbuser
  isCreate=$?
  if [ "$isCreate" -ne "0" ]
  then
    echo "create user $dbuser faied"
    exit 1
  else
    echo User $dbuser created | tee -a $logfile
  fi
  $createdbCMD $dbname -O $dbuser
  isCreate=$?
  if [ $isCreate -eq 0 ]
  then
    echo The owner of the database $dbname is $dbuser | tee -a $logfile
    echo Database $dbname created | tee -a $logfile
    echo "------------------------------------------------" | tee -a $logfile
    echo "You can enter (y/Y) to initialize the $dbname database, enter any key to exit script"
    echo "Confrim there has initialize information in (*.ctl) control file"
    echo -n "Do you want to initialize the $dbname[y]:  "
    read initial
    if [ "$initial" == "y" ] || [ "$initial" == "Y" ]
    then
      if [ "X$datadir" == "X" ]
      then
        func_createDatadir  
        initdb=yes
      else
        datadir=$datadir
      fi
    else
      echo "You can use $0 -d $dbname --init to initialize the database"
      exit 0
    fi
  else
    echo create database $dbname faied | tee -a $logfile
    echo check whether $dbname database is already exist or not? | tee -a $logfile
    exit 1
  fi
fi
if [ $initdb = "yes" ]
then
  if [ "X$datadir" == "X" ]
  then
    func_createDatadir
  else
    if [ -d $datadir ]
      then
      if [ `ls $datadir | wc -l` -ne 0 ]
      then
        echo "$datadir is already exist, and it is not empty" | tee -a $logfile
        exit 1    
      fi
    else
      echo "create data directoy $datadir" | tee -a >> $logfile
      sudo mkdir -p $datadir
    fi
  fi
fi
if [ "X$controlfile" == "X" ]
then
  cnt=0
  for f in *.ctl
  do
    if [ "X$f" != "X" ] && [ "$f" != "*.ctl" ]
    then
      cnt=`expr $cnt + 1`
    fi
  done
  if [ $cnt -eq 0 ]
  then
    Usage "ERROR: There is no control file (.ctl) in current directory."
  elif [ $cnt -eq 1 ]
  then
    controlfileDir=`pwd`
    controlfile=$controlfileDir/$f
  else 
    Usage "ERROR: There are more than one control files (.ctl) in current directory."
  fi
else
  if [ -f $controlfile ]
  then
    controlfileDir=`dirname $controlfile`
    controlfile=$controlfileDir/`basename $controlfile`
  fi
fi
echo log file: $logfile
echo FinShare SQL installation starts at `date +%Y-%m-%d.%H:%M:%S` | tee -a $logfile
echo Postgres database name: $dbname | tee -a $logfile
echo Postgres database User: $userid | tee -a $logfile
echo Postgres database port: $port | tee -a $logfile
echo SQL Scripts Root Directory: $scriptdir | tee -a $logfile
echo Control file full path: $controlfile | tee -a $logfile
totalschema=0
totalspace=0
totalfiles=0
errfiles=0
readline=`cat $controlfile | grep -v "^#" | grep -v "^$"`
if [ $initdb = "yes" ]
then
  isTablespace=`echo "$readline" | grep -i "^tablespace:"`
  if [ $? -eq 0 ]
  then
    for AllspaceName in $readline
    do
      line=$AllspaceName
      func_PorcessCtl $initdb
    done
  else
    echo No tablesapce defined in $controlfile | tee -a $logfile
    echo for example: TABLESPACE:tablespaceName1:tablespaceName2:tablespaceName[n]:CDA | tee -a $logfile
    exit 1
  fi
  initdb=no
fi
if [ $initdb = "no" ]
then
  sqlname=`cat $controlfile | grep -v "^#" | grep -v "^$" | grep -v -i "^tablespace:"`
  if [ "X$sqlname" == "X" ]
  then
    echo "Error: No SQL file defined in $controfile" | tee -a $logfile
    exit 1
  fi
  for i in $sqlname
  do
    line=$i
    func_PorcessCtl $initdb
  done
fi
echo "finished at `date +%Y-%m-%d.%H:%M:%S`" | tee -a $logfile
if [ $totalspace -ne 0 ]
then
  echo $totalspace tablespace have been created | tee -a $logfile
fi
if [ $totalschema -ne 0 ]
then
  echo $totalschema database user have been created | tee -a $logfile
fi
echo "$totalfiles files have been executed" | tee -a $logfile
echo "$errfiles files with errors" | tee -a $logfile
echo "Check log file: $logfile"