Recently, the company was using the postgresql database and developed many ddldml statements for us. So I spent two days completing this script to Create Table spaces and users, script for automatic data import #! Binbas
Recently, the company was using the postgresql database and developed many ddl/dml statements for us. So I spent two days completing this script to Create Table spaces and users, script for automatic data import #! /Bin/bas
Recently, the company was using the postgresql database and developed many ddl/dml statements for us. So I spent two days completing this script to Create Table spaces and users, script for Automatic Data Import
#! /Bin/bash #2013/07/26, DD. # Usage: fin1__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 login s to login linux to run this script # must add account login s 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 defined s password "echo"-P, -- port. database connection port, default is 5432 "echo"-f, -- ctlfile. control file. default is. ctl in current directory "echo"-l, -- lo Gdir. 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"]; thenecho $1 fi if ["$ help" = "true"] thenecho "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 I T 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}' "] thensqlErrFound = 1 fi} func_PorcessCtl () {line = 'echo $ line | tr-d' \ 136 \ 015 \ 010 ''if [" X $ Line "! = "X"] thenif ["$1" = "yes"] thenparams = '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"] thenfunc_createSchemafor m in $ paramsdotablespaceName = $ mfunc_createTabspadonefifiif ["$1" = "no"] thenfilePath = $ scriptdir/'echo $ line | awk-F: {'print $ 1'} 'schema = 'echo $ line | awk-F: {'print $ 2'} 'if [! -F $ filePath] thenecho Error: $ filePath: no sush file or directory | tee-a $ logfileexit 1 fiif ["X $ schema" = "X"] thenfunc_runSqlfileelsefunc_changeSchema yesfunc_runSqlfilefunc_changeSchemafifi 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 EX ISTS $ 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 # $ psqlC MD-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" $ p SqlCMD-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]; thenecho "---------------------- $ tablespaceName created" | tee-a $ logfile elseecho "created $ tablespaceName create failed" | tee-a $ logfile fi} func_changeSchema () {if ["$1" = "yes"] thenecho "-------------------------------------------" | tee-a $ logfileecho "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 elseecho" success "| tee-a $ logfileecho" 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] thenerrfiles = 'expr $ errfiles + 1 'echo "Error in $ filePath" >>$ logfileTmpecho "Error "in $ filePath. check details in file-$ logfile "fi if [-f $ logfileTmp] thencat $ logfileTmp >>$ logfilerm-f $ logfileTmp fi} func_createDatadir () {while true doread-p "Speciel the data directory:" datadirif [-d $ datadir] thenif ['ls $ datadir | Wc-l '-ne 0] thenecho "$ datadir is already exist, but it is not empty "| tee-a $ logfileecho" please select a another directory "elsedatadir = $ datadirbreakfielseecho" create data directoy $ datadir "| tee-a> $ logfilesudo mkdir- p $ datadirbreakfi done }#================================== ======# echo Parsing command line argumentsnumargs =$ # I = 1 scriptname = '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 1 fiwhile [$ I-le $ numargs] do j = $1 if [$ j =" -- dbname "] | [$ j = "-d"] thendbname = $2 shift 1i = 'expr $ I + 1' fi if [$ j = "-- userid"] | [$ j = "-u "] thenus Erid = $2 shift 1i = 'expr $ I + 1' fi if [$ j = "-- ctlfile"] | [$ j = "-f"] thenuserid = $2 shift 1i = 'expr $ I + 1' fi if [$ j = "-- port"] | [$ j = "-p"] thenport = $2 shift 1i =' expr $ I + 1 'fi if [$ j = "-- passwd"] | [$ j = "-p"] thenport = $2 shift 1i = 'expr $ I + 1 'fi if [$ j = "-- logfile"] | [$ j = "-l"] thenlogdir = $2 shift 1i = 'expr $ I + 1' fi if [$ j = "-- datadir"] | [$ j = "-D"] thendatadir = $2 Shift 1i = 'expr $ I + 1' fi if [$ j = "-- init"] | [$ j = "-I"] theninitdb = yes fi if [$ j = "-- create"] | [$ j = "-c"] thencreatedb = yes fi if [$ j = "-- help"] | [$ j = "-h "] thenhelp = true fi I = 'expr $ I + 1' shift 1 doneif [$ help =" ture "] thenUsagefiif [" X $ dbname "=" X "] then usage "ERROR: dbname is empty. "fiif [" X $ userid "=" X "] then userid = policsfiif [" X $ port "=" X "] then port = 5432 Fiif ["X $ logdir" = "X"] then logdir =/tmpelse if [! -D $ logdir] thenecho create log dirctory $ logdirsudo mkdir-p $ logdir export ilogfile = $ logdir/$ {scriptname }_$ {dbname} _ 'date + % Y-% m- % d _ % H _ % M _ % s '. loglogfileTmp =$ {logfile }. tmpif ["X $ pgpasswd" = "X"] then while true dostty-echoread-p "Enter $ userid's password:" PGPASSWORDstty echoif [! -Z $ PGPASSWORD] | ["X $ PGPASSWORD "! = "X"] thenexport PGPASSWORD = $ PGPASSWORDbreakfi doneelse export PGPASSWORD = $ PGPASSWORD fiif ["$ 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 "] thendbuser = fscs elsedbuser = $ isFSCS fi createuser-s $ dbuser I SCreate = $? If ["$ isCreate"-ne "0"] thenecho "create user $ dbuser faied" exit 1 elseecho User $ dbuser created | tee-a $ logfile fi $ createdbCMD $ dbname-O $ dbuser isCreate = $? If [$ isCreate-eq 0] thenecho The owner of the database $ dbname is $ dbuser | tee-a $ logfileecho Database $ dbname created | tee-a $ logfileecho "created" | tee -a $ logfileecho "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 Initialize the $ dbname [y]: "read initialif [" $ initial "=" y "] | [" $ initial "=" Y "] thenif [" X $ datadir "=" X "] authorization = yeselsedatadir = $ datadirfielseecho "You can use $0-d $ dbname -- init to initialize the database" exit 0 fielseecho create database $ dbname faied | tee-a $ logfileecho check whether $ dbname database is already exist or not? | Tee-a $ logfileexit 1 incluiif [$ initdb = "yes"] then if ["X $ datadir" = "X"] thenfunc_createDatadir elseif [-d $ datadir] thenif ['ls $ datadir | wc-l'-ne 0] thenecho "$ datadir is already exist, and it is not empty "| tee-a $ logfileexit 1 fielseecho" create data directoy $ datadir "| tee-a >>$ logfilesudo mkdir-p $ datadirfi policiif [" X $ controlfile "=" X "] then cnt = 0 for f in *. ctl doif [" X $ f "! = "X"] & ["$ f "! = "*. Ctl "] thencnt = 'expr $ cnt + 1' fi done if [$ cnt-eq 0] thenUsage" ERROR: There is no control file (. ctl) in current directory. "elif [$ cnt-eq 1] thencontrolfileDir = 'pwd' controlfile = $ controlfileDir/$ f elseUsage" ERROR: There are more than one control files (. ctl) in current directory. "fielse if [-f $ controlfile] thencontrolfileDir = 'dirname $ controlfile 'controlfile = $ controlfileDir/'basenam E $ controlfile 'your iecho log file: $ logfileecho FinShare SQL installation starts at 'date + % Y-% m-% d. % H: % M: % s' | tee-a $ logfileecho Premium Technology Inc. | tee-a $ logfileecho s database name: $ dbname | tee-a $ logfileecho s database User: $ userid | tee-a $ logfileecho Postgres database port: $ port | tee-a $ logfileecho SQL Scripts Root Directory: $ scriptdir | tee-a $ logfileecho C Ontrol file full path: $ controlfile | tee-a $ logfiletotalschema = 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] thenfor AllspaceName in $ readlinedoline = $ response $ initdbdone elseecho No tablesapce defined in $ controlfile | tee-a $ logfileecho for example: TABLESPACE: tablespaceName1: tablespaceName2: tablespaceName [n]: CDA | tee-a $ logfileexit 1 fi initdb = nofiif [$ initdb = "no"] then sqlname = 'cat $ controlfile | grep-v "^ #" | grep-v" ^ $ "| grep-v-I" ^ tablespace: "'If [" X $ sqlname "=" X "] thenecho" Error: no SQL file defined in $ controfile "| tee-a $ logfileexit 1 fi for I in $ sqlname doline = $ ifunc_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"
This article is from the "Step by Step" blog and will not be reposted!
