PSQL archiving script

do_archive.sh

#!/bin/bash

STARTSCRIPT=$(date +%s)
STOPSCRIPT=''
ListOfBases='dblist'
PORT='5432'
USERNAME='root'
SERVERNAME='localhost'
ARCHIVEDIR='/archive_dir'
MAXDBCOUNT='1000' #count of db names to select from cluster
TEMPDIR='/cache'
DAILYTIMEOUT='120' #timeout before start new series of rdiff deltas in minutes
RAMDISKSIZE='6144M'
ISIZE='1048576' #  -I, --input-size=BYTES    Input buffer size
OSIZE='1048576' #  -O, --output-size=BYTES   Output buffer size
BSIZE='1048576' # --block-size 1048576
SUFFIX=''
NETDIR='//192.168.0.110/PostgreSQL'
ERRLOG='/var/log/archive.log'
STARTFILE=''
SERIALNUM=''
SERVERLABEL=$(hostname)
CHATID='-325999999'
BOTID='bot1444444475:AAH1y14666tZtYMaP_N888Ew3DzzzzzzSJM'

if [[ 'daily' = $1 ]]
then
    SUFFIX='daily_'$(date +%Y-%m-%d-%H)
fi

if [[ 'monthly' = $1 ]]
then
    SUFFIX='monthly_'$(date +%Y-%m-%d)
fi

if [[ 'hot' = $1 ]]
then
    SUFFIX='hot_'$(date +%Y-%m-%d-%H%M)
fi

if [[ 'weekly' = $1 ]]
then
    SUFFIX='weekly_'$(date +%Y-%m-%d)
fi

if [[ $SUFFIX = '' ]]
then
    echo 'Type monthly, weekly, daily or hot as parametr'
    exit
fi


if mount | grep -qw $NETDIR
then
    echo "Network directory is mounted"
else
    echo "Network direcrory $NETDIR is not mounted" >> $ERRLOG
    MESSAGE="FAIL | pg_dump | ${SERVERLABEL} | ${SUFFIX}"
    /usr/bin/curl -s -X POST -H 'Content-Type: application/json' -d '{"chat_id": "'"$CHATID"'", "text": "'"$MESSAGE"'", "disable_notification": false}' https://api.telegram.org/$BOTID/sendMessage
    exit
fi

DBLIST=$(psql -U $USERNAME -p $PORT -l | q -d'|' "select c1 from - where c1 <> '' and c2 <> '' and c1 not like 'template%' and c1 not like '%_bak%' limit 1,$MAXDBCOUNT")

echo "$DBLIST" > ${ARCHIVEDIR}'/'$ListOfBases

mkdir -p "$TEMPDIR/temp"

cat $ARCHIVEDIR'/'$ListOfBases | while read DBNAME
do
    mkdir -p ${ARCHIVEDIR}'/'${DBNAME}

    if [[ 'weekly' = $1 ]]
    then
        > ${ARCHIVEDIR}/${DBNAME}'/start'
        STARTFILE=''
    fi

    if ! [[ -e ${ARCHIVEDIR}/${DBNAME}'/start' ]]
    then
        > ${ARCHIVEDIR}/${DBNAME}'/start'
    fi

    STARTFILE="$(cat ${ARCHIVEDIR}/${DBNAME}'/start')"

    if [[ $STARTFILE == '' ]]
    then
        SERIALNUM=$(/usr/bin/pwgen 15 1)
        STARTFILE="${DBNAME}_${SERIALNUM}_${SUFFIX}.dump"
        echo "$STARTFILE" > ${ARCHIVEDIR}'/'${DBNAME}'/start'
        nice -n 19 ionice -c3 pg_dump -d $DBNAME -h ${SERVERNAME} -p $PORT -U ${USERNAME} -w > ${TEMPDIR}/temp/${STARTFILE}
        if [[ $? -ne 0 ]]
        then
            echo "$DBNAME: pg_dump error code is "$? >> $ERRLOG
        fi
        /usr/bin/rdiff signature ${TEMPDIR}/temp/${STARTFILE} ${ARCHIVEDIR}/${DBNAME}/${STARTFILE}.signature
        if [[ $? -ne 0 ]]
        then
            echo "$DBNAME: signature creation error "$? >> $ERRLOG
        fi
        /usr/bin/pigz --keep -c ${TEMPDIR}/temp/${STARTFILE} > ${ARCHIVEDIR}/${DBNAME}/${STARTFILE}.gz
        if [[ $? -ne 0 ]]
        then
            echo "$DBNAME: start file archiving error "$? >> $ERRLOG
        fi
        rm -f ${TEMPDIR}/temp/${STARTFILE}
    else

        case $1 in
            hot|monthly)
                nice -n 19 ionice -c3 pg_dump -d $DBNAME -h ${SERVERNAME} -p $PORT -U ${USERNAME} -w | pigz > ${ARCHIVEDIR}'/'${DBNAME}'/'${DBNAME}'_'$SUFFIX.dump.gz;
                if [[ $? -ne 0 ]]
                then
                    echo "$DBNAME: pg_dump error code is "$? >> $ERRLOG
                fi
            ;;
            *)
                SERIALNUM=$(echo "$STARTFILE" | sed 's/^.*'"${DBNAME}"'_//;s/_weekly.*//')
                nice -n 19 \
                ionice -c3 \
                pg_dump -d $DBNAME -h ${SERVERNAME} -p $PORT -U ${USERNAME} -w | \
                /usr/bin/rdiff --block-size="$BSIZE" --input-size="$ISIZE" --output-size="$OSIZE" -- delta \
                ${ARCHIVEDIR}/${DBNAME}/$STARTFILE.signature \
                - \
                ${ARCHIVEDIR}/${DBNAME}/${DBNAME}_${SERIALNUM}_${SUFFIX}.dump.delta
                if [[ $? -ne 0 ]]
                then
                    echo "$DBNAME: delta creation error "$? >> $ERRLOG
                fi
                STOPSCRIPT=$(date +%s)
                EXECUTIONTIME=$(( ($STOPSCRIPT - $STARTSCRIPT) / 60 ))
                if [[ $EXECUTIONTIME -gt $DAILYTIMEOUT ]]
                then
                    MESSAGE="TIMEOUT | pg_dump | ${SERVERLABEL} | ${SUFFIX} | $EXECUTIONTIME min"
                    /usr/bin/curl -s -X POST -H 'Content-Type: application/json' -d '{"chat_id": "'"$CHATID"'", "text": "'"$MESSAGE"'", "disable_notification": false}' https://api.telegram.org/$BOTID/sendMessage
                    rm -R "$TEMPDIR"
                    $0 weekly
                    exit 1
                fi
            ;;
        esac
    fi
done


if [ -d "$TEMPDIR" ]; then
    rm -R "$TEMPDIR"
fi


BackupErr=$(stat $ERRLOG -c %s)

STOPSCRIPT=$(date +%s)

EXECUTIONTIME=$(( ($STOPSCRIPT - $STARTSCRIPT) / 60 ))

MESSAGE=''

if [[ $BackupErr = 0 ]]
then
    MESSAGE="OK | pg_dump | ${SERVERLABEL} | ${SUFFIX} | $EXECUTIONTIME min"
else
    MESSAGE="FAIL | pg_dump | ${SERVERLABEL} | ${SUFFIX} | $EXECUTIONTIME min"
    echo date >> $ERRLOG
fi

/usr/bin/curl -s -X POST -H 'Content-Type: application/json' -d '{"chat_id": "'"$CHATID"'", "text": "'"$MESSAGE"'", "disable_notification": false}' https://api.telegram.org/$BOTID/sendMessage

do_restore.sh

#!/bin/bash

PORT=5432
USERNAME='root'
SERVERNAME='localhost'
ARCHIVEDIR='/Backup'
NEWDBNAME=''
OLDDBNAME=''
ARCHIVEDATE=''
FILETORESTORE=''

if [[ $1 != '' ]]
then
    NEWDBNAME=$1
else
    echo "Enter new DB name as first parametr"
    exit
fi

if [[ $2 != '' ]]
then
    OLDDBNAME=$2
else
    echo "Enter DB name in archive"
    exit
fi

if [[ $3 != '' ]]
then
    ARCHIVEDATE=$3
else
    echo "Enter date in forman YYYY-mm-dd"
    exit
fi

if psql -U $USERNAME -h $SERVERNAME -p $PORT -lqt | cut -d \| -f 1 | grep -qw $NEWDBNAME
then
    echo "Database with this name exists on this cluster"
    exit
fi

CURR_DATE=$(date +%Y-%m-%d)

#echo $CURRENT_DATE

DAYS_AGO=$(( (($(date -d ${CURR_DATE} +%s) - $(date -d ${ARCHIVEDATE} +%s)) / 86400) - 1 ))

#echo $DAYS_AGO

#DBLIST="$(ls $ARCHIVEDIR/$OLDDBNAME | grep -E "^${OLDDBNAME}.*$ARCHIVEDATE.*(.delta|.dump.gz)\$")"

DBLIST="$(find $ARCHIVEDIR/$OLDDBNAME -mtime ${DAYS_AGO} -regex ".*\.\(delta\|dump.gz\)$" -type f -printf "%f\n")"

if [[ "$DBLIST" = '' ]]
then
    echo "There is no file to restore"
    exit
fi


index=1
for var in $DBLIST
do
    echo "${index} $var"
    index=$(($index+1))
done

echo -n "Enter the number of archive in this list "

read answer

index=1
for var in $DBLIST
do
    if [[ $index = $answer ]]
    then
        FILETORESTORE=$var
        echo "$FILETORESTORE"
        break
    fi
    index=$(($index+1))
done

if [[ "$FILETORESTORE" = '' ]]
then
    echo "$answer -- is incorrect answer"
    exit
fi

filetype=0

if file $ARCHIVEDIR/$OLDDBNAME/$FILETORESTORE | grep -qw "rdiff network-delta data"
then
    filetype=1
fi

if file $ARCHIVEDIR/$OLDDBNAME/$FILETORESTORE | grep -qw "PostgreSQL custom database dump"
then
    filetype=2
fi

if file $ARCHIVEDIR/$OLDDBNAME/$FILETORESTORE | grep -qw "gzip compressed data"
then
    filetype=3
fi


case "$filetype" in
    1)
        echo "rdiff network-delta data"
        SERIALNUM=$(echo "$FILETORESTORE" | sed 's/^.*'"${OLDDBNAME}"'_//;s/_daily.*//')
        echo $SERIALNUM
        ORIGINAL=$(find $ARCHIVEDIR/$OLDDBNAME/*$SERIALNUM*.dump.gz)
        echo $ORIGINAL
        gunzip -c $ORIGINAL > $ORIGINAL.decompressed
        /usr/bin/rdiff patch $ORIGINAL.decompressed $ARCHIVEDIR/$OLDDBNAME/$FILETORESTORE $ORIGINAL.restored
        rm $ORIGINAL.decompressed
        psql -U $USERNAME -h $SERVERNAME -p $PORT -c 'create database '$NEWDBNAME -d postgres
        psql -h $SERVERNAME -U $USERNAME -p $PORT -d $NEWDBNAME < $ORIGINAL.restored
        rm $ORIGINAL.restored
        ;;
    2)
        echo "PostgreSQL custom database dump"
        psql -U $USERNAME -h $SERVERNAME -p $PORT -c 'create database '$NEWDBNAME -d postgres
        pg_restore -h $SERVERNAME -U $USERNAME -p $PORT -d $NEWDBNAME -w -Fc $ARCHIVEDIR/$OLDDBNAME/$FILETORESTORE
        ;;
    3)
        echo "gzip compressed data"
        psql -U $USERNAME -h $SERVERNAME -p $PORT -c 'create database '$NEWDBNAME -d postgres
        gunzip < $ARCHIVEDIR/$OLDDBNAME/$FILETORESTORE | psql -h $SERVERNAME -U $USERNAME -p $PORT -d $NEWDBNAME
        ;;
    *)
        echo "Undefined file type"
        exit 0
        ;;
esac