公司最近在使用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"
相关推荐
nacos-2.0.1 postgresql初始化脚本
PostgreSQL是一种功能强大的开源关系型数据库管理系统，广泛用于企业级数据存储和管理。要实现PostgreSQL数据库的远程连接，你需要进行一系列的配置步骤，确保数据库服务器允许来自不同网络的连接请求。下面将详细...
2. **创建与管理数据库**：通过`createdb`命令创建数据库，使用`pg_dump`和`pg_restore`进行数据导入导出。 3. **用户与角色管理**：创建和管理数据库用户，设置用户权限，理解GRANT和REVOKE语句的用法。 4. **SQL...
* 定期备份数据库：Postgre SQL 数据库管理员应该定期备份数据库，以防止数据丢失。 Postgre SQL 数据库安全防护需要管理员采取多种措施，包括配置认证机制、加密机制和网络配置，避免数据泄露风险和认证风险。
QGIS软件连接Postgre数据库截图操作
postgre数据库自动备份操作教程.pdf
在VC++环境中，访问数据库是一项常见的任务，而“VC访问PostgreSQL数据库”涉及的技术主要集中在如何利用Microsoft Visual C++（VC）与PostgreSQL数据库进行交互。在这个程序中，两种主要的访问方式被提及：一是使用...
### Postgre数据库学习知识点 #### 一、PostgreSQL简介与安装 **PostgreSQL**是一款功能强大的开源对象关系型数据库系统，以其稳定性、可扩展性以及遵循SQL标准而著称。根据给定的学习文档，我们可以了解到关于...
文档中提到的pg_dump是PostgreSQL提供的一个命令行工具，它专门用于导出数据库到一个SQL脚本文件中，或者归档文件（使用`-Fc`参数）。这个命令支持数据库的完全备份和部分备份。完全备份涉及整个数据库的数据和结构...
除了数据库对象名，PostgreSQL对数据本身也是大小写敏感的。这意味着在查询时，必须确保字符串比较符合预期。例如，如果表`TUser`中有一个字段`Name`，其中包含值“TonyTang”，直接使用`LIKE`操作符进行大小写不...
Linux中一键重启postgresql的脚本命令
### PostgreSQL在CentOS 7上的Shell脚本安装详解 #### 一、安装准备与环境配置 ##### **1. 下载PostgreSQL安装包** 对于没有网络连接的服务器，我们需要提前下载好PostgreSQL的二进制安装文件。根据所需版本，...
2. 使用`psql`命令连接到PostgreSQL数据库。 3. 使用`ALTER USER postgres WITH PASSWORD ；`命令修改PostgreSQL密码， `<new_password>`为新密码。 4. 使用`\q`命令退出PostgreSQL命令行。 5. 使用`exit`命令退出...
PostgreSql+PostGis 创建空间数据库...使用 PostgreSql 和 PostGis 可以创建一个功能强大的空间数据库，支持空间数据的存储、查询和分析。本文提供了详细的安装和配置步骤，帮助您快速上手使用 PostgreSql 和 PostGis。
易语言源码 Postgre SQL 连接池。基于 http://www.sanye.cx/?id=12020 的源码 二次开发...封装了一些高级方法..包括,连接池+查询缓存+不知道真假的储存过程+慢查询记录。使用源码使用了E2EE支持库(模块部分思路借鉴...
针对国产数据库kingbase中数据迁移到mysql和DB2的一个小工具~~ 开发的时候写的方便自己用的一个小工具，想看源码直接winrar进去看就成~
例如，用户可以通过A5进行SQL查询，执行复杂的数据库脚本，进行数据导入导出，甚至进行数据库结构的可视化设计。这些高级功能使得A5在数据处理方面具备了专业级的性能。 在数据安全方面，A5也做得相当到位。它支持...
最近学习Postgre SQL数据库，找了很久也没有找到使用libpq.dll的模块，这里自己按照资料写了一个，写得不好，权当抛砖引玉了哈。顺便说下：Postgre SQL号称是最先进的开源数据库，确实不是盖的，在我使用来看确实比...
《a5m2数据库工具详解——多数据库管理与SQL操作》 在信息技术日新月异的今天，数据库管理系统已经成为...在日常工作中，熟练掌握a5m2的使用技巧，无疑会大大提高你的工作效率，使你在数据库管理的道路上更进一步。
1. **创建数据库脚本**：使用VUGen，我们可以选择合适的协议（如SQL/Oracle，DB2等）来录制或手动编写SQL查询，模拟用户对数据库的操作。 2. **负载设置**：在Controller中，我们可以定义测试场景，包括虚拟用户的...