greenplum 自动导数工具
公司測試環境同步數據需求量巨大，每天都要導出大量生產數據到測試環境， 寫了一個自動導數的工具，支持并行導出并自動導入到 測試環境，使用時需自己指定放有 導出過濾語句的文件，需放開生產到測試的網絡環境。
?
#/bin/bash
source /usr/local/greenplum-db/greenplum_path.sh
export local_dir=/backup/csh
USAGE ()
{
echo ""
echo "Usage: -ds -dt -Ds -Dt -hs -ht -p -pt -us -ut -Us -Ut -f -w";
echo "Copydump Parameter Infomation: ";
echo "? Parameter -ds: Source Database Name??????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[源數據庫名]";
echo "? Parameter -dt: Target Database Name??????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[目標數據庫名]";
echo "? Parameter -Ds: Source Outputfile Directory???????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[源庫數據輸出路徑]";
echo "? Parameter -Dt: Target Copyfile Directory?????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[目標庫數據存放路徑]";
echo "? Parameter -hs: Source Database Host??????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[源庫主機IP地址]";
echo "? Parameter -ht: Target Database Host??????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[目標庫主機IP地址]";
echo "? Parameter -p:? Source Database Port??????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[源數據庫端口號]";
echo "? Parameter -pt: Target Database Port??????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[目標數據庫端口號]";
echo "? Parameter -us: Source Database Username??????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[源數據庫用戶名]";
echo "? Parameter -ut: Target Database Username??????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[目標數據庫用戶名]";
echo "? Parameter -Us: Source Host Username??????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[源庫主機的用戶名]";
echo "? Parameter -Ut: Target Host Username??????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[目標庫主機的用戶名]";
echo "? Parameter -w: Workflow Number????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[工作流號]";
echo "? Parameter -f: Copy SQLFile Directory,Default Value: /backup/csh/copysql.sql????????????????????????????????????????????????????????????????????????????????????????????????[源庫copysql文件路徑]";
echo "? Parameter -pa: Parallel Processes????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????[并行度]";
echo ""????????????????????????????????????????????????????????????????????????????????????????????????
echo "? EasyMode:????????????????????????????????????????????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????[簡易模式：]";
echo "? run (copydump 87) can copy data to 192.168.218.87????????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????['copydump 87'可拷貝數據至開發庫]";
echo "? run (copydump 163) can copy data to 192.168.218.163??????????????????????? ????????????????????????????????????????????????????????????????????????????????????????????????['copydump 163'可拷貝數據至開發庫]";
echo "";????????????????????????????????????????????????????????????????????????????????????????????????
exit 1;
}
if [ $# -lt 2 ]
then
if [ x"$1" == x"87" ]
then
export source_database=dp_bidb
export target_database=dp_dev
export source_ip=10.249.15.22
export target_ip=192.168.218.87
export source_hostuser=gpadmin
export target_hostuser=gpadmin
export source_dbuser=gpadmin
export target_dbuser=gpadmin
export source_port=9432
export target_port=5432
export source_dir=/data2/copy
export target_dir=/u01/csh
echo ""
read -p "Now Input the Workflow Number: " num
if [ x"$num" == x"" ]
then
echo "You Must Input the Workflow Number! Run 'copydump --help' to Get Usage"
echo ""
exit
else
export jira_num="$num"
fi
echo ""
read -p "Now Input Your SQLFile Directory( Default SQLFile is $local_dir/copysql.sql ): " dir
if [ x"$dir" == x"" ]
then
export sql_text=$local_dir/copysql.sql
else
export sql_text="$dir"
fi
echo ""
read -p "Now Input Copy Parallel Processes( Default Parallel is 4 ): " par
if [ x"$par" == x"" ]
then
export parallel=4
else
export parallel="$par"
fi
elif [ x"$1" == x"163" ]
then
export source_database=dp_bidb
export target_database=dp_dw163
export source_ip=10.249.15.22
export target_ip=192.168.218.163
export source_hostuser=gpadmin
export target_hostuser=sgpadmin
export source_dbuser=gpadmin
export target_dbuser=sgpadmin
export source_port=9432
export target_port=6432
export source_dir=/data2/copy
export target_dir=/data1/csh
echo ""
read -p "Now Input the Workflow Number: " num
if [ x"$num" == x"" ]
then
echo "You Must Input the Workflow Number! Run 'copydump --help' to Get Usage"
echo ""
exit
else
export jira_num="$num"
fi
echo ""
read -p "Now Input Your SQLFile Directory( Default SQLFile is $local_dir/copysql.sql ): " dir
if [ x"$dir" == x"" ]
then
export sql_text=$local_dir/copysql.sql
else
export sql_text="$dir"
fi
echo ""
read -p "Now Input Copy Parallel Processes( Default Parallel is 4 ): " par
if [ x"$par" == x"" ]
then
export parallel=4
else
export parallel="$par"
fi
else
USAGE
fi
else
export sql_text=""
export source_database=""
export target_database=""
export source_ip=""
export target_ip=""
export source_hostuser=""
export target_hostuser=""
export source_dbuser=""
export target_dbuser=""
export source_port=""
export target_port=""
export source_dir=""
export target_dir=""
export jira_num=""
while getopts ds:dt:Ds:Dt:hs:ht:p:pa:pt:us:ut:Us:Ut:f:w OPTION ;
do
case "$OPTION" in
ds)???? source_database="$OPTARG" ;;
dt)???? target_database="$OPTARG" ;;
Ds)???? source_dir="$OPTARG" ;;
Dt)???? target_dir="$OPTARG" ;;
hs)???? source_ip="$OPTARG" ;;
ht)???? target_ip="$OPTARG" ;;
p)????? source_port="$OPTARG" ;;
pa)????????????????parallel="$OPTARG" ;;
pt)???? target_port="$OPTARG" ;;
us)???? source_dbuser="$OPTARG" ;;
ut)???? target_dbuser="$OPTARG" ;;
Us)???? source_hostuser="$OPTARG" ;;
Ut)???? target_hostuser="$OPTARG" ;;
f)????? sql_text="$OPTARG" ;;
w)????? jira_num="$OPTARG" ;;
\?) #usage statement
USAGE;
;;
esac
done
if [ x"$source_database" == x"" ]
then
echo "-ds Source Database Name Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$target_database" == x"" ]
then
echo "-dt Target Database Name Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$source_dir" == x"" ]
then
echo "-Ds Source Outputfile Directory Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$target_dir" == x"" ]
then
echo "-Dt Target Outputfile Directory Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$source_ip" == x"" ]
then
echo "-hs Source Database Host Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$target_ip" == x"" ]
then
echo "-ht Target Database Host Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$parallel" == x"" ]
then
echo "-pa Target Database Parallel Default Value is 4 "
export parallel=4
else
export parallel="$parallel"
fi
if [ x"$source_port" == x"" ]
then
echo "-p Source Database Port Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$target_port" == x"" ]
then
echo "-pt Target Database Port Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$source_dbuser" == x"" ]
then
echo "-us Source Database Username Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$target_dbuser" == x"" ]
then
echo "-ut Target Database Username Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$source_hostuser" == x"" ]
then
echo "-Us Source Host Username Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$target_hostuser" == x"" ]
then
echo "-Ut Target Host Username Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$sql_text" == x"" ]
then
echo "-f Copy SQLFile Directory Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
if [ x"$jira_num" == x"" ]
then
echo "-w Workflow Number Must be Specified , Run 'copydump --help' to Get Usage"
echo ""
exit
fi
fi
#開始復制程序
echo '--------------------------------------------------------------------'
echo ""
echo 'Use ('$sql_text') Copy Data From ('$source_ip'):('$source_database') To ('$target_ip'):('$target_database')'
read -p "Do You Want To Continue ? Please Input (yes\no): " char
if [ "$char" == "yes" ]
then
echo ""
mkdir -p $local_dir/$jira_num
#copy源庫數據到指定文件夾下
while read line
do
#獲取表名
export table_nm=`echo? "$line" | awk '{print $4}'`
echo `date` > $local_dir/copy_error.log 2>&1
#判斷查詢腳本是否可執行
psql -d "$source_database" -p "$source_port" -h "$source_ip" -U "$source_dbuser" -c " copy ( ${line} limit 1 ) to stdout " >> $local_dir/copy_error.log 2>&1
if [ $? -eq 0 ]
then
echo $jira_num': Test Copy Table '$table_nm' Successfully ' >> $local_dir/copy.log
else
#不可執行拋出異常
echo `date` >> $local_dir/copy.log
echo $jira_num':Test Copy Table '$table_nm' With Error,Check /data1/csh/copy_error.log For More Information' >> $local_dir/copy.log
echo ""
echo '########################################################################################'
echo 'Copy Table '$table_nm' With Error !!! Cat '$local_dir'/copy_error.log For More Information'
echo '########################################################################################'
echo ""
exit
fi
done < $sql_text
if [ "$?" != 0 ]
then
exit
fi
#開并行
i=1
n=`cat "$sql_text" | wc -l`
if [ $n -lt $parallel ]
then
export parallel="$n"
fi
while [ $i -le $n ]
do
scopy()
{
line=`head -n $i $sql_text | tail -n 1`
typeset -l tab
tab="$line"
table_name=`echo? "$tab" | awk '{print $4}'`
echo `date` >> $local_dir/copy.log
echo $jira_num': Copy Table '$table_name' Start ' >> $local_dir/copy.log
psql -d $source_database -p $source_port -h $source_ip -U $source_dbuser -c " copy (${tab}) to stdout " | gzip > $local_dir/$jira_num/$table_name.gz
echo `date` >> $local_dir/copy.log
echo 'Copy Table '$table_name
echo $jira_num': Copy Table '$table_name' Successfully ' >> $local_dir/copy.log
}
m=1
while [ $m -le $parallel ]
do
scopy &
i=`expr $i + 1`
m=`expr $m + 1`
done
while [ 1 == 1 ]
do
copy_num=`ps -ef | grep -i copy | grep -v grep | wc -l`
if [ $copy_num -gt 2 ]
then
sleep 1
else
break
fi
done
done
wait
#while read name
#do
#typeset -l tab
#tab="$name"
#export table_name=`echo? "$tab" | awk '{print $4}'`
#echo `date` >> $local_dir/copy.log
#echo $jira_num': copy table '$table_name' start ' >> $local_dir/copy.log
#psql -d $source_database -p $source_port -h $source_ip -U $source_dbuser -c " copy (${tab}) to stdout " | gzip > $local_dir/$jira_num/$table_name.gz
#echo `date` >> $local_dir/copy.log
#echo 'copy table '$table_name
#echo $jira_num': copy table '$table_name' successfully ' >> $local_dir/copy.log
#done < $sql_text
#把導出的數據庫文件傳至58中間庫
#echo ""
#echo 'Now Scp the Copy File to 192.168.5.58 !'
#echo '--------------------------------------------------------------------'
#echo ""
#scp -r $source_hostuser@$source_ip:$source_dir/$jira_num $local_dir
#if [ "$?" -eq 0 ]
#then
#????????echo `date` >> $local_dir/copy.log
#????????echo $jira_num': Copy File to 192.168.5.58 Start ' >> $local_dir/copy.log
#else
#????????echo ""
#????????echo 'scp file to 192.168.5.58 faild,check your password! and run this commond to continue: sh /tmp/scp_error/continue.sh' | tee -a $local_dir/copy.log
#????????echo ""
#????????exit
#fi
#echo `date` >> $local_dir/copy.log
#echo $jira_num': copy file to 192.168.5.58 end ' >> $local_dir/copy.log
#把導出的數據文件復制到目標庫
echo ""
echo 'Now Scp The Copy File To '$target_ip' !'
echo '--------------------------------------------------------------------'
echo ""
smakdir(){
echo "#!/usr/bin/expect" > $local_dir/spawn_makdir.sh
echo "set password sgpadmin" >> $local_dir/spawn_makdir.sh
echo "spawn ssh $target_hostuser@$target_ip" >> $local_dir/spawn_makdir.sh
echo 'expect {' >> $local_dir/spawn_makdir.sh
echo '"*yes/no" { send "yes\r"; exp_continue}' >> $local_dir/spawn_makdir.sh
echo '"*password:" { send "$password\r" }' >> $local_dir/spawn_makdir.sh
echo '}' >> $local_dir/spawn_makdir.sh
echo 'expect "]$ "' >> $local_dir/spawn_makdir.sh
echo "send \"mkdir -p $target_dir/$jira_num\r\"" >> $local_dir/spawn_makdir.sh
echo 'send "exit\r"' >> $local_dir/spawn_makdir.sh
echo "expect eof" >> $local_dir/spawn_makdir.sh
chmod a+x $local_dir/spawn_makdir.sh
cd $local_dir
./spawn_makdir.sh
}
smakdir
i=1
n=`cat "$sql_text" | wc -l`
if [ $n -lt $parallel ]
then
export parallel="$n"
fi
while [ $i -le $n ]
do
sscp(){
echo "#!/usr/bin/expect" > $local_dir/spawn_scp$i.sh
echo "set password sgpadmin" >> $local_dir/spawn_scp$i.sh
echo "set timeout 100000000" >> $local_dir/spawn_scp$i.sh
echo "spawn scp $local_dir/$jira_num/$table_name.gz $target_hostuser@$target_ip:$target_dir/$jira_num" >> $local_dir/spawn_scp$i.sh
echo 'expect {' >> $local_dir/spawn_scp$i.sh
echo '"*yes/no" { send "yes\r"; exp_continue}' >> $local_dir/spawn_scp$i.sh
echo '"*password:" { send "$password\r" }' >> $local_dir/spawn_scp$i.sh
echo '}' >> $local_dir/spawn_scp$i.sh
echo "expect eof" >> $local_dir/spawn_scp$i.sh
chmod a+x $local_dir/spawn_scp$i.sh
cd $local_dir
./spawn_scp$i.sh
rm $local_dir/spawn_scp$i.sh
}
m=1
while [ $m -le $parallel ]
do
line=`head -n $i $sql_text | tail -n 1`
typeset -l tab
tab="$line"
table_name=`echo? "$tab" | awk '{print $4}'`
sscp &
i=`expr $i + 1`
m=`expr $m + 1`
done
while [ 1 == 1 ]
do
scp_num=`ps -ef | grep -i scp | grep -v grep | wc -l`
if [ $scp_num -gt 2 ]
then
sleep 1
else
break
fi
done????????????????
done
wait
if [ "$?" -eq 0 ]
then
echo `date` >> $local_dir/copy.log
echo $jira_num': Copy File To '$target_ip' Start ' >> $local_dir/copy.log
else
echo $jira_num': Copy File To '$target_ip' Error ' >> $local_dir/copy.log
mkdir -p /tmp/scp_error/
echo ""
echo 'Scp File To '$target_ip' Faild,Check Your Password! And Run This Commond To Continue: sh /tmp/scp_error/continue.sh' | tee -a $local_dir/copy.log
echo ""
exit
fi
echo `date` >> $local_dir/copy.log
echo $jira_num': Copy File To '$target_ip' End ' >> $local_dir/copy.log
#解壓導出的數據文件
echo ""
echo 'Now Gunzip The Copy File !'
echo '--------------------------------------------------------------------'
echo ""
i=1
n=`cat "$sql_text" | wc -l`
if [ $n -lt $parallel ]
then
export parallel="$n"
fi
while [ $i -le $n ]
do
sgunzip(){
echo "#!/usr/bin/expect" > $local_dir/spawn_gunzip$i.sh
echo "set password sgpadmin" >> $local_dir/spawn_gunzip$i.sh
echo "spawn ssh $target_hostuser@$target_ip" >> $local_dir/spawn_gunzip$i.sh
echo 'expect {' >> $local_dir/spawn_gunzip$i.sh
echo '"*yes/no" { send "yes\r"; exp_continue}' >> $local_dir/spawn_gunzip$i.sh
echo '"*password:" { send "$password\r" }' >> $local_dir/spawn_gunzip$i.sh
echo '}' >> $local_dir/spawn_gunzip$i.sh
echo 'expect "]$ "' >> $local_dir/spawn_gunzip$i.sh
echo "send \"gunzip -f $target_dir/$jira_num/$table_name.gz\r\"" >> $local_dir/spawn_gunzip$i.sh
echo 'send "exit\r"' >> $local_dir/spawn_gunzip$i.sh
echo 'expect eof' >> $local_dir/spawn_gunzip$i.sh
chmod a+x $local_dir/spawn_gunzip$i.sh
cd $local_dir
./spawn_gunzip$i.sh
rm $local_dir/spawn_gunzip$i.sh
}
m=1
while [ $m -le $parallel ]
do
line=`head -n $i $sql_text | tail -n 1`
typeset -l tab
tab="$line"
table_name=`echo? "$tab" | awk '{print $4}'`
sgunzip &
i=`expr $i + 1`
m=`expr $m + 1`
done
while [ 1 == 1 ]
do
gunzip_num=`ps -ef | grep -i gunzip | grep -v grep | wc -l`
if [ $gunzip_num -gt 2 ]
then
sleep 1
else
break
fi
done????????????????
done
wait
if [ "$?" -eq 0 ]
then
echo `date` >> $local_dir/copy.log
echo $jira_num': '$target_ip' Gunzip File Start ' >> $local_dir/copy.log
else
echo $jira_num': Copy File To '$target_ip' Error ' >> $local_dir/copy.log
mkdir -p /tmp/gunzip_error/
echo ""
echo 'Gunzip File On '$target_ip' Faild,Check Your Password! And Run This Commond To Continue: sh /tmp/gunzip_error/continue.sh' | tee -a $local_dir/copy.log
echo ""
exit
fi
echo `date` >> $local_dir/copy.log
echo $jira_num': '$target_ip' Gunzip File End ' >> $local_dir/copy.log
#操作目標庫導入數據
echo ""
echo 'Now Copy Data To '$target_ip':'$target_database
echo '--------------------------------------------------------------------'
echo ""
echo `date` >> $local_dir/copy.log
echo $jira_num': Copy Data To '$target_database' Start' >> $local_dir/copy.log
i=1
n=`cat "$sql_text" | wc -l`
if [ $n -lt $parallel ]
then
export parallel="$n"
fi
while [ $i -le $n ]
do
tcopy(){
LINE=`head -n $i $sql_text | tail -n 1`
typeset -l tab
tab="$LINE"
table=`echo? "$tab" | awk '{print $4}'`
where=`echo? "$tab" | awk '{print $5}'`
#判斷是否全表導出
if [ x"$where" ==? x"where" ]
then
#不是全表，先拼接刪除語句
export delete="${LINE/select \* from/delete from}"
echo `date` >> $local_dir/copy.log
echo $jira_num': '"$delete" >> $local_dir/copy.log
#刪除數據
echo "$delete"
psql -d $target_database -h $target_ip -U $target_dbuser -p $target_port -c "$delete" 2>&1 | tee -a $local_dir/copy.log
#導入數據
echo `date` >> $local_dir/copy.log
export copy="copy $table from '$target_dir/$jira_num/$table'"
echo "$copy"
echo ""
echo $jira_num': '"$copy" >> $local_dir/copy.log
psql -d $target_database -h $target_ip -U $target_dbuser -p $target_port -c "$copy" 2>&1 | tee -a $local_dir/copy.log
#確認數據
#echo `date` >> $local_dir/copy.log
#export select="${LINE/select \* from/select count(1) from}"
#echo $jira_num': '"$select" >> $local_dir/copy.log
#psql -d $target_database -h $target_ip -U $target_dbuser -p $target_port -c "$select" 2>&1 >>? $local_dir/copy.log
#全表導出先truncate表
else
export truncate="truncate table $table"
echo `date` >> $local_dir/copy.log
echo "$truncate"
echo $jira_num': '"$truncate" >> $local_dir/copy.log
psql -d $target_database -h $target_ip -U $target_dbuser -p $target_port -c "$truncate" 2>&1 | tee -a $local_dir/copy.log
#導入數據
echo `date` >> $local_dir/copy.log
export copy="copy $table from '$target_dir/$jira_num/$table'"
echo "$copy"
echo ""
echo $jira_num': '"$copy" >> $local_dir/copy.log
psql -d $target_database -h $target_ip -U $target_dbuser -p $target_port -c "$copy" 2>&1 | tee -a $local_dir/copy.log
#確認數據
#echo `date` >> $local_dir/copy.log
#export select="${LINE/select \* from/select count(1) from}"
#echo $jira_num': '"$select" >> $local_dir/copy.log
#psql -d $target_database -h $target_ip -U $target_dbuser -p $target_port -c "$select" 2>&1 >> $local_dir/copy.log
fi
}
m=1
while [ $m -le $parallel ]
do
tcopy &
i=`expr $i + 1`
m=`expr $m + 1`
done
while [ 1 == 1 ]
do
copy_num=`ps -ef | grep -i copy | grep -v grep | wc -l`
if [ $copy_num -gt 2 ]
then
sleep 1
else
break
fi
done
done
wait
echo ""
echo '##########################################################'
echo 'Copy Data To '$target_ip $target_database' End'
echo '##########################################################'
echo ""
echo `date` >> $local_dir/copy.log
echo $jira_num': Copy Data To '$target_ip $target_database' End' >> $local_dir/copy.log
echo '----------------------------------------------------------' >> $local_dir/copy.log
echo "" >> $local_dir/copy.log
else
echo ""
echo '##########################################################'
echo 'Copydump End With Do Nothing , Check Your Parameter !'
echo '##########################################################'
echo ""
fi
總結
以上是生活随笔為你收集整理的greenplum 自动导数工具的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 大学毕业设计答辩PPT模板
- 下一篇： CreatarGlobe实现多机立体显示