#!/bin/bash

# POSTGRES SQL backup

#################################

BACKDIR=/backup/pgsql

KEEPDAYS=120

#################################

TIMESTAMP=`date +%y-%m-%d`

[ -d $BACKDIR ] || mkdir $BACKDIR
[ -d $BACKDIR/weekly ] || mkdir $BACKDIR/weekly

cd $BACKDIR

# delete older files than 8 days

NEWDIRS=`find $BACKDIR -maxdepth 1 -mindepth 1 -type d -name "pgsql.*"`

for DIRNAME in $NEWDIRS; do

    FOUND=""
    FOUND=`find $DIRNAME/* -mtime +8`
    if ! [ "$FOUND" == "" ]; then
        rm -r $DIRNAME
    fi

done

# weekly rotation on sunday
if [ "`date +%u`" = "7" ]; then

    for DIRNAME in $NEWDIRS; do
        if [ -d $DIRNAME ] ; then
            [ "`find $DIRNAME/* -mtime +7`" != "" ] && mv $DIRNAME $BACKDIR/weekly/
        fi
    done

    WEEKDIRS=`find $BACKDIR/weekly -maxdepth 1 -mindepth 1 -type d -name "pgsql.*"`

    for DIRNAME in $WEEKDIRS; do
        [ "`ls -1 $DIRNAME`" == "" ] && rmdir $DIRNAME
        [ "`find $DIRNAME/* -mtime +$KEEPDAYS`" != "" ] && rm -r $DIRNAME
    done
fi


BDIR=$BACKDIR/pgsql.$TIMESTAMP

# remove today's backup if there is one
[ -d $BDIR ] && rm -r $BDIR

#making backup folder
mkdir $BDIR

# get database names from postgresql
DBS=`sudo -u postgres psql -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'} | grep -vE '^template[0|1]' |grep -vE '^\|'`

# save databases into separate files
for db in $DBS; do

    FILE=$BDIR/$db.tar.gz

    sudo -u postgres pg_dump -Ft $db | gzip -c > $FILE
    
    #chown for security
    chown 0:0 $FILE
    chmod 0600 $FILE

done