postgresql数据表备份 ansible发送备份文件到远程服务器_postgresql数据备份到其他服务器

制作思路：

1.将数据库信息写到配置里

2.通过传递的参数（resourceTables）明确需要备份的表（原因：前台可以选择需要备份什么表）

3.生成备份文件并发送到远程服务器（如果本机出现故障，可以到远程服务器获取备份信息）并记录文件详情信息

4.定时调度每天生成一个备份文件，所以为了节省资源空间。需要定期清理备份文件 dump_clear_day是配置的参数。

#!/bin/sh
# =========================================================================
#
# 输入参数：1.待备份数据库名(db_name)  2.备份文件存储路径(bakDir)  3.需要备份的表(resourceTables) 4.备份文件远程存储服务器IP（非必选项）   5.远程备份文件存储路径（remoteBakDir）
#
# wd 2019.1.23
# 
# =========================================================================
resourceTables=${@:1}
today=`date +%Y-%m-%d`
function sourceConfig(){
        echo $INSPECTORWEB_HOME
    configfile=$INSPECTORWEB_HOME/bin/config.sh
    if [ -f $configfile ]; then
        printTime
        echo source $configfile
        source $configfile
    else
        printTime
        echo [FATAL] cannot find $configfile.
        exit -1
    fi
}
function printTime(){
    echo -n `date "+%Y-%m-%d %H:%M:%S"`" "
}
 
function insertInfo(){
bakDir=$INSPECTORWEB_HOME/${dump_backup_path}
if [ ! -d $bakDir ];then
    mkdir -p $bakDir
fi
statusFile=${bakDir}/${db_name}_bak_${today}.status
/usr/local/pgsql/bin/pg_dump  -U ${db_user}  ${db_name} -a ${resourceTables} --inserts -f ${bakDir}/${db_name}_bak_${today}.sql
step1=$?
if [ ${step1} != '0' ];then
   echo ".........pg_dump failed!"
   echo -e "${today}\t1" >${statusFile}
   exit 1
	else
path=${bakDir}/${db_name}_bak_${today}.sql
fileName=${db_name}_bak_${today}.sql
fileSize=`ls -l ${path} | awk '{print $5}'`
echo "---------------${path}-----------------${fileSize} "
psql -U ${db_user}  ${db_name} <<EOF
 INSERT INTO data.dump_table_history (file_name,file_size,create_time,path) VALUES('$fileName','$fileSize',to_date('$today','YYYY-MM-DD'),'$path')
EOF
fi
}
 
function deleteInfo(){
clear_day=`date -d "-${dump_clear_day} day ${today}" +%Y-%m-%d`
psql -U ${db_user}  ${db_name} <<EOF
 DELETE FROM data.dump_table_history WHERE create_time < to_date('$clear_day','YYYY-MM-DD')
EOF
echo $clear_day
}
function sendToRemote(){
if [ ! -z ${dump_remote_ip} ];then
   remoteIp=${dump_remote_ip}
   if [ -z ${dump_remote_dir} ];then
       remoteBakDir=$bakDir
   else
           remoteBakDir=$dump_remote_dir
   fi
echo "---------------${remoteBakDir}-----------${bakDir}------${remoteIp}"
ansible ${remoteIp} -u ${dump_remote_user} -e ansible_ssh_pass:${dump_remote_password} -m copy -a "src=${path} dest=${remoteBakDir}" 
   step2=`echo $?`
   if [ ${step2} != '0' ];then
           echo ".........ansible bak file failed!"
           echo -e "${today}\t2"        >${statusFile}
           exit 2
   fi
fi
echo -e "${today}\t0" >${statusFile}
}
function clearDocuments(){
 find  ${bakDir}   -mtime +${dump_clear_day}  -type f  -name "${db_name}_bak_*"  -exec rm -f {} \;
if [ ! -z ${dump_remote_ip} ];then
ansible ${remoteIp} -u ${dump_remote_user} -e ansible_ssh_pass:${dump_remote_password} -m shell -a  'find '${remoteBakDir}' -mtime +'${dump_clear_day}' -type f -name '${db_name}'_bak_* -exec rm -f {} \;'
echo $?
fi
deleteInfo
}
 
function main(){
# source config.sh
sourceConfig
insertInfo
sendToRemote
clearDocuments
}
main