数据库备份脚本

!/bin/bash
cd $(dirname $0)
curr_dir=$(pwd)

v_config_file=${curr_dir}/backup_recover.conf
chmod 777 ${v_config_file}
. ${v_config_file}

help info
v_version=”v1.0 built time 2020.07.31”
if [ “$1” = “-h” ] || [ “$1” = “—help” ]
then
echo “$(basename $0) [database_name [database_name] [database_name] …]’ #backup specified database, if database not be specified, backup all database”
echo “$(basename $0) -h # print help info ,same as —help “
echo “$(basename $0) —help # print help info ,same as -h”
echo “$(basename $0) -v # print version info ,same as —version”
echo “$(basename $0) —version # print version info ,same as -v”
exit 0
elif [ “$1” = “-v” ] || [ “$1” = “—version” ]
then
echo ${v_version}
exit 0
fi

judge parameter
flag=0
if [ -z ${pg_db_ip} ]
then
echo -e “\e[31;1m\n$(date +”%F %T”) ==> ERROR: parameter pg_db_ip does not config in file ${v_config_file} …\e[0m”
flag=1
fi

if [ -z ${pg_db_port} ]
then
echo -e “\e[31;1m\n$(date +”%F %T”) ==> ERROR: parameter pg_db_port does not config in file d${v_config_file} …\e[0m”
flag=1
fi

if [ -z ${pg_db_user} ]
then
echo -e “\e[31;1m\n$(date +”%F %T”) ==> ERROR: parameter pg_db_user does not config in file ${v_config_file} …\e[0m”
flag=1
fi

if [ -z ${pg_db_passwd} ]
then
echo -e “\e[31;1m\n$(date +”%F %T”) ==> ERROR: parameter pg_db_passwd does not config in file d${v_config_file} …\e[0m”
flag=1
fi

if [ ${flag} -eq 1 ]
then
exit 1
fi

judge database connection
chmod 755 /${curr_dir}/ -R
export LD_LIBRARY_PATH=${curr_dir}/lib:${LD_LIBRARY_PATH}
export PGPASSWORD=${pg_db_passwd}
pg_db_list=$( ${curr_dir}/bin/psql -U ${pg_db_user} -h ${pg_db_ip} -p ${pg_db_port} -t -c “ select datname from pg_database where datname not in (‘postgres’,’template0’,’template1’);” -d postgres |xargs)
if [ $? -ne 0 ]
then
echo -e “\e[34;1m$(date +”%F %T”) ==> ERROR: connect databse failed failed ,exit \e[0m”
exit 1
fi

update database
# user choice
if [ -n “$“ ]
then
pg_db_list=$
fi

mkdir -p ${curr_dir}/bak
for v_dbname in ${pg_db_list}
do
v_db_file=${curr_dir}/bak/${v_dbname}.sql
echo -e “\e[34;1m\n\n$(date +”%F %T”) ==> info: backup database ${v_dbname} begin…\e[0m”
${curr_dir}/bin/pg_dump -U ${pg_db_user} -h ${pg_db_ip} -p ${pg_db_port} -c -C —if-exists -f ${v_db_file} -d ${v_dbname}
if [ $? -ne 0 ]
then
echo -e “\e[31;1m$(date +”%F %T”) ==> ERROR: backup database ${v_dbname} failed ,exit \e[0m”
exit 1
fi

echo -e “$(date +”%F %T”) ==> info: backup file is : ${v_db_file}”
echo -e “\e[34;1m$(date +”%F %T”) ==> info: backup database ${v_dbname} end \e[0m”
done

数据库恢复脚本

!/bin/bash
cd $(dirname $0)
curr_dir=$(pwd)

v_config_file=${curr_dir}/backup_recover.conf
chmod 777 ${v_config_file}
. ${v_config_file}

help info
v_version=”v1.0 built time 2020.07.31”
if [ “$1” = “-h” ] || [ “$1” = “—help” ]
then
echo “$(basename $0) [database_backup_file_name [database_backup_file_name] [database_backup_file_name] …]’ #recovery specified database backup file, if database backup file not be specified, recovery all database backup file from diretory bak”
echo “$(basename $0) -h # print help info ,same as —help “
echo “$(basename $0) —help # print help info ,same as -h”
echo “$(basename $0) -v # print version info ,same as —version”
echo “$(basename $0) —version # print version info ,same as -v”
exit 0
elif [ “$1” = “-v” ] || [ “$1” = “—version” ]
then
echo ${v_version}
exit 0
fi

judge parameter
flag=0
if [ -z ${pg_db_ip} ]
then
echo -e “\e[31;1m\n$(date +”%F %T”) ==> ERROR: parameter pg_db_ip does not config in file ${v_config_file} …\e[0m”
flag=1
fi

if [ -z ${pg_db_port} ]
then
echo -e “\e[31;1m\n$(date +”%F %T”) ==> ERROR: parameter pg_db_port does not config in file d${v_config_file} …\e[0m”
flag=1
fi

if [ -z ${pg_db_user} ]
then
echo -e “\e[31;1m\n$(date +”%F %T”) ==> ERROR: parameter pg_db_user does not config in file ${v_config_file} …\e[0m”
flag=1
fi

if [ -z ${pg_db_passwd} ]
then
echo -e “\e[31;1m\n$(date +”%F %T”) ==> ERROR: parameter pg_db_passwd does not config in file d${v_config_file} …\e[0m”
flag=1
fi

if [ ${flag} -eq 1 ]
then
exit 1
fi

user choice
if [ -n “$“ ]
then
pg_file_list=$
else
pg_file_list=$(find ${curr_dir}/bak -type f -name *.sql |xargs )
fi

for i in $(seq 5)
do
echo -e -n “\e[34;1m\nAre you sure you want to recover file [ ${pg_file_list} ] (yes/no): \e[0m”
read v_answer
if [ “${v_answer}” = “yes” ] || [ “${v_answer}” = “y” ] || [ “${v_answer}” = “YES” ] || [ “${v_answer}” = “Y” ]
then
break
elif [ “${v_answer}” = “no” ] || [ “${v_answer}” = “n” ] || [ “${v_answer}” = “NO” ] || [ “${v_answer}” = “N” ]
then
exit
else
if [ $i -eq 5 ]
then
echo $(date +”%F %T”) WARING: maximum number of attempts reached ,exit.
exit
else
echo “choice is invalid ,please give your choice (yes/no)”
fi
fi
done

judge database connection
chmod 755 /${curr_dir}/ -R
export LD_LIBRARY_PATH=${curr_dir}/lib:${LD_LIBRARY_PATH}
export PGPASSWORD=${pg_db_passwd}
${curr_dir}/bin/psql -U ${pg_db_user} -h ${pg_db_ip} -p ${pg_db_port} -c “select 0;” -d postgres >/dev/null 2>&1
if [ $? -ne 0 ]
then
echo -e “\e[34;1m$(date +”%F %T”) ==> ERROR: connect databse failed failed ,exit \e[0m”
exit 1
fi

recover database
for v_dbbk_file in ${pg_file_list}
do
if [ -f ${v_dbbk_file} ]
then
v_dbname=$(basename ${v_dbbk_file})
v_dbname=${v_dbname%.sql}
echo -e “\e[34;1m\n\n$(date +”%F %T”) ==> info: recover database backup file ${v_dbbk_file} begin…\e[0m”
${curr_dir}/bin/psql -U ${pg_db_user} -h ${pg_db_ip} -p ${pg_db_port} -c “select pg_terminate_backend(pid) FROM pg_stat_activity where pid <> pg_backend_pid() and datname = ‘${v_dbname}’” -d postgres >/dev/null
${curr_dir}/bin/psql -U ${pg_db_user} -h ${pg_db_ip} -p ${pg_db_port} -f ${v_dbbk_file} -d postgres
if [ $? -ne 0 ]
then
echo -e “\e[31;1m$(date +”%F %T”) ==> ERROR: recover database backup file ${v_dbbk_file} failed ,exit \e[0m”
exit 1
fi
echo -e “\e[34;1m$(date +”%F %T”) ==> info: recover database backup file ${v_dbbk_file} end \e[0m”
else
echo -e “\e[31;1m$(date +”%F %T”) ==> WARING: database backup file ${v_dbbk_file} does not exists , skip \e[0m”
fi
done