Chinaunix首页 | 论坛 | 博客
  • 博客访问: 358578
  • 博文数量: 76
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 2363
  • 用 户 组: 普通用户
  • 注册时间: 2013-07-21 22:30
文章分类
文章存档

2014年(38)

2013年(38)

分类: 网络与安全

2013-09-10 11:02:54

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


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
    
#!/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<=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"
阅读(1838) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~