用shell寫的postgre資料庫初始資料指令碼

公司最近在使用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 argumentsnumargs=$#i=1scriptname=`basename "$0"`scriptdir=`pwd "$0"`psqlCMD=psqlcreatedbCMD=createdbhostname="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 1fiwhile [ $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 1doneif [ $help = "ture" ]then   Usagefiif [ "X$dbname" == "X" ]then  Usage "ERROR: dbname is empty."fiif [ "X$userid" == "X" ]then  userid=postgresfiif [ "X$port" == "X" ]then  port=5432fiif [ "X$logdir" == "X" ]then  logdir=/tmpelse  if [ ! -d $logdir ]  then    echo create log dirctory $logdir    sudo mkdir -p $logdir  fifilogfile=$logdir/${scriptname}_${dbname}_`date +%Y-%m-%d_%H_%M_%S`.loglogfileTmp=${logfile}.tmpif [ "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  doneelse  export PGPASSWORD=$PGPASSWORDfiif [ "$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  fifiif [ $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  fifiif [ "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."  fielse  if [ -f $controlfile ]  then    controlfileDir=`dirname $controlfile`    controlfile=$controlfileDir/`basename $controlfile`  fifiecho log file: $logfileecho FinShare SQL installation starts at `date +%Y-%m-%d.%H:%M:%S` | tee -a $logfileecho Postgres database name: $dbname | tee -a $logfileecho Postgres database User: $userid | tee -a $logfileecho Postgres database port: $port | tee -a $logfileecho SQL Scripts Root Directory: $scriptdir | tee -a $logfileecho Control file full path: $controlfile | tee -a $logfiletotalschema=0totalspace=0totalfiles=0errfiles=0readline=`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=nofiif [ $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  donefiecho "finished at `date +%Y-%m-%d.%H:%M:%S`" | tee -a $logfileif [ $totalspace -ne 0 ]then  echo $totalspace tablespace have been created | tee -a $logfilefiif [ $totalschema -ne 0 ]then  echo $totalschema database user have been created | tee -a $logfilefiecho "$totalfiles files have been executed" | tee -a $logfileecho "$errfiles files with errors" | tee -a $logfileecho "Check log file: $logfile"

本文出自 “一步一步” 部落格，謝絕轉載！