从iRedMail 定时备份数据库脚本学习PostgreSQL数据库

从iRedMail 定时备份数据库脚本学习PostgreSQL数据库

backup_pgsql.sh脚本将postgresql数据库中数据使用pg_dump从数据库中备份到$BACKUP_ROOTDIR目录下，默认为/var/vmail/backup路径。需要将该脚本设置为crontab条目，达到定时备份的目的。

# * Add crontab job for root user (or whatever user you want):## # crontab -e -u root# 1 4 * * * bash /path/to/backup_pgsql.sh

备份

循环为vmail roundcubemail amavisd iredadmin sogo iredapd sa_bayes这些数据库进行备份，

export DATABASES='vmail roundcubemail amavisd iredadmin sogo iredapd sa_bayes'echo "* Backing up databases: ${DATABASES}." >> ${LOGFILE}for db in ${DATABASES}; do output_sql="${db}-${TIMESTAMP}.sql" # Check database existence 检查数据库是否存在 su - "${SYS_USER_PGSQL}" -c "psql -d ${db} # Dump if [ X"$?" == X'0' ]; then su - "${SYS_USER_PGSQL}" -c "${CMD_PG_DUMP} ${db} > ${PGSQL_USER_HOMEDIR}/${output_sql}" # 调用pg_dump配置到sql文件中 if [ X"$?" == X'0' ]; then # 成功 # Move to backup directory. mv ${PGSQL_USER_HOMEDIR}/${output_sql} ${BACKUP_DIR} cd ${BACKUP_DIR} # Get original SQL file size original_size="$(${CMD_DU} ${output_sql} | awk '{print $1}')" # Compress ${CMD_COMPRESS} ${output_sql} >>${LOGFILE} # 压缩 rm -f ${output_sql} >> ${LOGFILE} echo -e " + ${db} >> ${LOGFILE} # Get compressed file size # 压缩后的dump文件 compressed_file_name="${output_sql}.${COMPRESS_SUFFIX}" compressed_size="$(${CMD_DU} ${compressed_file_name} | awk '{print $1}')" # Log to SQL table `iredadmin.log`, so that global domain admins can check backup status sql_log_msg="INSERT INTO log (event, loglevel, msg, admin, ip, timestamp) VALUES ('backup', 'info', 'Database: ${db}, size: ${compressed_size} (original: ${original_size})', 'cron_backup_sql', '127.0.0.1', NOW());" else export BACKUP_SUCCESS='NO' sql_log_msg="INSERT INTO log (event, loglevel, msg, admin, ip, timestamp) VALUES ('backup', 'info', 'Database backup failed: ${db}, check log file ${LOGFILE} fi su - "${SYS_USER_PGSQL}" >/dev/null <

清理旧备份文件

直接删除

if [ X"${REMOVE_OLD_BACKUP}" == X'YES' -a -d ${REMOVED_BACKUP_DIR} ]; then echo -e "* Old backup found. Deleting: ${REMOVED_BACKUP_DIR}." >>${LOGFILE} rm -rf ${REMOVED_BACKUP_DIR} >> ${LOGFILE} 2>&1 # Try to remove empty directory. rmdir ${REMOVED_BACKUP_MONTH_DIR} 2>/dev/null rmdir ${REMOVED_BACKUP_YEAR_DIR} 2>/dev/null su - ${SYS_USER_PGSQL} -c "psql -d iredadmin" <

旧文件通过文件名上的时间戳来标识

export KERNEL="$(uname -s)"if [[ X"${KERNEL}" == X'Linux' ]]; then shift_year=$(date --date="${KEEP_DAYS} days ago" "+%Y") shift_month=$(date --date="${KEEP_DAYS} days ago" "+%m") shift_day=$(date --date="${KEEP_DAYS} days ago" "+%d")elif [[ X"${KERNEL}" == X'FreeBSD' ]]; then shift_year=$(date -j -v-${KEEP_DAYS}d "+%Y") shift_month=$(date -j -v-${KEEP_DAYS}d "+%m") shift_day=$(date -j -v-${KEEP_DAYS}d "+%d")elif [[ X"${KERNEL}" == X'OpenBSD' ]]; then epoch_seconds_now="$(date +%s)" epoch_shift="$((${KEEP_DAYS} * 86400))" epoch_seconds_old="$((epoch_seconds_now - epoch_shift))" shift_year=$(date -r ${epoch_seconds_old} "+%Y") shift_month=$(date -r ${epoch_seconds_old} "+%m") shift_day=$(date -r ${epoch_seconds_old} "+%d")else export REMOVE_OLD_BACKUP='NO'fiexport REMOVED_BACKUP_DIR="${BACKUP_ROOTDIR}/pgsql/${shift_year}/${shift_month}/${shift_day}"export REMOVED_BACKUP_MONTH_DIR="${BACKUP_ROOTDIR}/pgsql/${shift_year}/${shift_month}"export REMOVED_BACKUP_YEAR_DIR="${BACKUP_ROOTDIR}/pgsql/${shift_year}"

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。