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

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

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

#!/bin/bash # 2013/07/26, DD. # 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> $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"

本文出自 “一步一步” 博客，谢绝转载！