公司最近在使用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"
阅读(1891) | 评论(0) | 转发(1) |
