PgBackupAll

This script make a full backup of all PostgreSQL databases and sent email with the status. Optionally, it can send the backup by the email itself or by FTP . It is possible to restore this backup following the info at PostgreSQL.

Requirements

The dest path (default /srv/backup/data/postgresql/alldatabases/) must exists and be owned by postgres user, and the required commands (pg_dumpall, gzip, mail, mutt, ftp) must be installed (apt-get install mailutils mutt gzip ftp).

Script

#!/bin/bash
#
# NAME      postgres_backup_all.sh
# VERSION   1.2
# REQUIRED  pg_dumpall, gzip, mail, mutt , ftp
#
# Note:
# you can use it with cron add something like the follows in the crontab
#
# 00 4   *     *     *     root   cd /tmp && run-parts --report --regex='^[a-zA-Z0-9_-]+\.sh$' /etc/cron/cron.daily_4h
#
# and add this file in the /etc/cron/cron.daily_4h path
#   - you should be able to send email from your system, for instance, configuring exim4
#   dpkg-reconfigure exim4-config # choose "internet site;"



# Var

BACKUP_DIR="/srv/backup/data/postgresql/alldatabases/";
BACKUP_FILE="bkp-postgresql_alldatabases-`date +%Y.%m.%d_%H.%M.%S`-dump.sql.gz";
BACKUP_COMMAND="pg_dumpall | gzip > ${BACKUP_DIR}${BACKUP_FILE}";
BACKUP_USER="postgres";
ROTATE_USER="postgres";
ROTATE_DAYS="8";
EMAIL="[WHO@MAILSERVER.WHR]";
SUBJECT="[YOURDOMAIN.WHR] Postgresql backup";

SEND_BY_FTP="false";
FTP_HOST="[DESTDOMSIN.WHR]";
FTP_DIR="[REMOTEPATH]";
FTP_USER="[USER]";
FTP_PASS="[PASS]";

SEND_BY_EMAIL="false";



# Test

if test ! -e "`which mail`"
then
   echo "Command mail not found, exit." >&2;
   exit 1;
fi;

if test ! -e "`which pg_dumpall`"
then
   echo "Command pg_dumpall not found, exit." | mail -s "${SUBJECT} error." "${EMAIL}";
   exit 1;
fi;

if test ! -e "`which gzip`"
then
   echo "Command gzip not found, exit." | mail -s "${SUBJECT} error." "${EMAIL}";
   exit 1;
fi;

if test "${SEND_BY_FTP}" = "true" -a ! -e "`which ftp`"
then
   echo "Command ftp not found, exit." | mail -s "${SUBJECT} error." "${EMAIL}";
   exit 1;
fi;

if test "${SEND_BY_EMAIL}" = "true" -a ! -e "`which mutt`"
then
   echo "Command mutt not found, exit." | mail -s "${SUBJECT} error." "${EMAIL}";
   exit 1;
fi;

if ! test -d "${BACKUP_DIR}";
then
   echo "Directory ${BACKUP_DIR} don't exist, exit." | mail -s "${SUBJECT} error." "${EMAIL}";
   exit 1;
fi;

if test -f "${BACKUP_DIR}${BACKUP_FILE}";
then
    echo "File ${BACKUP_DIR}${BACKUP_FILE} alredy exist, exit." | mail -s "${SUBJECT} error." "${EMAIL}";
    exit 1;
fi;

if ! su - "${BACKUP_USER}" -c "touch ${BACKUP_DIR}${BACKUP_FILE}";
then
    echo "User ${BACKUP_USER} cannot write in ${BACKUP_DIR}${BACKUP_FILE}, exit." | mail -s "${SUBJECT} error." "${EMAIL}";
    exit 1;
fi;

su - "${BACKUP_USER}" -c "rm -f ${BACKUP_DIR}${BACKUP_FILE}";



# Backup

if ! su - "${BACKUP_USER}" -s /bin/sh -c "${BACKUP_COMMAND}";
then
    echo "Backup ${BACKUP_COMMAND} failure, exit." | mail -s "${SUBJECT} error." "${EMAIL}";
    exit 1;
fi;

chown "${ROTATE_USER}":"${ROTATE_USER}" "${BACKUP_DIR}${BACKUP_FILE}";



# Rotate

if ! su - "${ROTATE_USER}" -s /bin/sh -c "ls -t ${BACKUP_DIR}*-dump.sql.gz | tail -n +${ROTATE_DAYS} | xargs -d '\n' rm -f";
then
    echo "Rotate ${BACKUP_DIR} failure, exit." | mail -s "${SUBJECT} error." "${EMAIL}";
    exit 1;
fi;



# Upload to FTP if required

if test "${SEND_BY_FTP}" = "true";
then
    ftp -i -n ${FTP_HOST} << EOF
user ${FTP_USER} ${FTP_PASS}
binary
put ${BACKUP_DIR}${BACKUP_FILE} ${FTP_DIR}${BACKUP_FILE}
quit
EOF

    if test $? -ne 0;
    then
        echo "Upload to ${FTP_HOST} failure, exit." | mail -s "${SUBJECT} error." ${EMAIL};
        exit 1;
    fi;
fi;



# Send by mail if required, or send succes email

if test "${SEND_BY_EMAIL}" = "true";
then
    ls -l "${BACKUP_DIR}"  | mutt -n -e 'set copy=no' -s "${SUBJECT} success." -a "${BACKUP_DIR}${BACKUP_FILE}" -- "${EMAIL}";
    exit 0;
else
    ls -l "${BACKUP_DIR}"  | mail -s "${SUBJECT} success." "${EMAIL}";
fi;



# End