用shell写的postgre数据库初始数据脚本

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

 

#!/bin/bash

# Usage: finshare_install_db.sh --dbname [ --userid ] [ --passwd ] [ --port ] [ --ctlfile ] [ --datadir ] [ -l ] [ --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                      (to run DDL/DML script)"

  echo "   $0 -d --create            (to create a database)"

  echo "   $0 -d --init -D (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 Premium Technology Inc. | 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"