從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 <<EOF# 向iredadmin資料庫中插入備份記錄
psql -d iredadmin <<EOF2
${sql_log_msg}2
EOF
    fi
done      

清理舊備份檔案

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" <<EOF
INSERT INTO log (event, loglevel, msg, admin, ip, timestamp) VALUES
    ('backup', 'info', 'Remove old backup: ${REMOVED_BACKUP_DIR}.', 'cron_backup_sql', '127.0.0.1', NOW());
EOF
fi      

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'
fi

export 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}"