#!/usr/bin/env bash

_logging=false

## - postgres user source system
## -
if cat /etc/passwd 2> /dev/null | grep postgres > /dev/null ; then
   PSQL_USER_SRC=postgres
else
   PSQL_USER_SRC=pgsql
fi

## - postgres user destination system
PSQL_USER_DST=postgres

BACKUP_BASE_DIR=$1
BACKUP_BASE_DIR=${BACKUP_BASE_DIR:="/data/backup"}

backup_dir=$BACKUP_BASE_DIR/pgsql

mkdir -p $backup_dir


_local=$2
_local=${_local:=true}

PSQL_BIN_DIR_SRC=/usr/local/pgsql/bin

if [ ! -d $PSQL_BIN_DIR_SRC ]; then
   if [ -x /usr/bin/psql ]; then
      PSQL_BIN_DIR_SRC=/usr/bin
   elif [ -x /usr/local/bin/psql ]; then
      PSQL_BIN_DIR_SRC=/usr/local/bin
   else
      echo
      echo "   pgsql bindir not found"
      echo
      exit 1
   fi
fi

PSQL_BIN_DIR_DST=$PSQL_BIN_DIR_SRC

days=5

PATH=/usr/local/sbin:/usr/sbin:/sbin:/usr/local/bin:/usr/bin:/bin:/root/bin

## - muss als user root ausgefuehrt werden
## -
if [ "`id -u`" -ne 0 ]; then
   echo -e "\n\tgeht nur als user \"root\"\n"
   exit 1
fi

YEAR=`date +"%Y"`
MONTH=`date +"%m"`
DAY=`date +"%d"`
HOUR=`date +"%H"`
MINUTE=`date +"%M"`
DATE=${YEAR}${MONTH}${DAY}-${HOUR}${MINUTE}
## DATE=`date +"%Y%m%d-%H%M"`


# -- Erstelle liste der Datenbanken
# --
listfile=${backup_dir}/dblist-${DATE}.txt
echo -e "Stand: ${DATE}\n" > $listfile
su - $PSQL_USER_SRC -c "${PSQL_BIN_DIR_SRC}/psql -l" >> $listfile


# - Erstelle Liste der Datenbanken (zur Weiterverarbeitung)
# -
databases=`su - $PSQL_USER_SRC -c "${PSQL_BIN_DIR_SRC}/psql -l -t"  | grep -v -e"^$" | grep -v -e"^\s*[:|]" | awk '{print$1}'`

# - Erstelle Liste der User (Roles) (zur Weiterverarbeitung)
# -
role_list=`su - $PSQL_USER_SRC -c "${PSQL_BIN_DIR_SRC}/psql -t -F ' ' -c \"\\du\" postgres" |grep -v -e"^$" | grep -v -e"^\s*[:|]" | awk '{print$1}'`


## --------------------------
## - Sichern der User (Roles)
## -
if $_logging ; then
   echo ""
fi
for role in $role_list ; do
   if $_logging ; then
      echo -e "\tdumping role $role.."
   fi
   su - $PSQL_USER_SRC -c "${PSQL_BIN_DIR_SRC}/pg_dumpall -g -c " | grep ${role} > ${backup_dir}/createrole_${role}-${DATE}.sql
   cat <<EOF >${backup_dir}/createrole_${role}-${DATE}.sh
#!/usr/bin/env bash

PATH=/usr/local/sbin:/usr/sbin:/sbin:/usr/local/bin:/usr/bin:/bin:/root/bin

## - muss als user root ausgefuehrt werden
## -
if [ "\`id -u\`" -ne 0 ]; then
   echo -e "\n\tgeht nur als user \"root\"\n"
   exit 1
fi

backup_dir="$backup_dir"

if [ ! -d \$backup_dir ] ; then
   if [ -n "\$1" -a -d "\$1" ];then
      backup_dir="\$1"
   else
      echo
      echo "   Backup directory not found"
      echo "   try: \`basename \$0\` <path-to-backup-basedir>"
      echo
      exit 1
   fi
fi

role=$role

restore_file=\${backup_dir}/createrole_${role}-${DATE}.sql
logfile=\${backup_dir}/createrole_${role}-${DATE}.log

if cat /etc/passwd 2> /dev/null | grep postgres > /dev/null ; then
   PSQL_USER=postgres
else
   PSQL_USER=pgsql
fi

PSQL_BIN_DIR=$PSQL_BIN_DIR_DST

if [ ! -x \$PSQL_BIN_DIR/psql ]; then
   if [ -x /usr/bin/psql ]; then
      PSQL_BIN_DIR=/usr/bin
   elif [ -x /usr/local/bin/psql ];then
      PSQL_BIN_DIR=/usr/local/bin
   elif [ -x /usr/local/pgsql/bin/psql ];then
      PSQL_BIN_DIR=/usr/local/pgsql/bin
   else
      echo
      echo "   pgsql bindir not found"
      echo
      exit 1
   fi
fi

echo -e "\n\trestoring role \$role..\n"
su - \$PSQL_USER -c "\${PSQL_BIN_DIR}/psql -f \$restore_file template1" > \$logfile 2>&1
EOF
   chmod 755 ${backup_dir}/createrole_${role}-${DATE}.sh
done



dbusers_databases_file=${backup_dir}/db_users_databases-${DATE}.sql
# -- Erstelle dump (Teil 1) zum Wiederherstellen der
# -- User und Datenbanken:
# --    Header
# --
cat <<EOF > $dbusers_databases_file
--
-- restore user and databasedefinitions (no data) for db-cluster
--    su - <postgres-user> -c "<postgres-bin-dir>/psql -f $dbusers_databases_file  template1"
--

--
-- Drop dumped databases
--

\\connect postgres

EOF


# -- Erstelle dump Dateien sowie Wiederherstellungsscripte
# -- fuer die einzelnen Datenbanken
# --

if $_logging ; then
   echo ""
fi

dbs_dumped=""
for db in $databases ; do
   BACKUP_DIR=${backup_dir}/$db
   mkdir -p $BACKUP_DIR

   [ "$db" == "template0" ] && continue
   
   # -- Erstelle dump Datei
   # --
   if $_logging ; then
      echo -e "\tdumping database $db.."
   fi
   su - $PSQL_USER_SRC -c " ${PSQL_BIN_DIR_SRC}/pg_dump  -c  $db" > ${BACKUP_DIR}/dump_${db}-${DATE}.sql 2> /dev/null
   if [ $? -eq 0 ]; then
      #echo -e "\n\t$db: successfully dumped\n"
      dbs_dumped="$dbs_dumped $db"

      # -- Erstelle Wiederherstellungsscript
      # --
      cat<<EOF > ${BACKUP_DIR}/restore_${db}-${DATE}.sh
#!/usr/bin/env bash

PATH=/usr/local/sbin:/usr/sbin:/sbin:/usr/local/bin:/usr/bin:/bin:/root/bin

backup_dir="$backup_dir"

if [ ! -d \$backup_dir ] ; then
   if [ -n "\$1" -a -d "\$1" ];then
      backup_dir="\$1"
   else
      echo
      echo "   Backup directory not found"
      echo "   try: \`basename \$0\` <path-to-backup-basedir>"
      echo
      exit 1
   fi
fi

BACKUP_DIR=\${backup_dir}/$db


if cat /etc/passwd 2> /dev/null | grep postgres > /dev/null ; then
   PSQL_USER=postgres
else
   PSQL_USER=pgsql
fi

PSQL_BIN_DIR=$PSQL_BIN_DIR_DST

if [ ! -x \$PSQL_BIN_DIR/psql ]; then
   if [ -x /usr/bin/psql ]; then
      PSQL_BIN_DIR=/usr/bin
   elif [ -x /usr/local/bin/psql ];then
      PSQL_BIN_DIR=/usr/local/bin
   elif [ -x /usr/local/pgsql/bin/psql ];then
      PSQL_BIN_DIR=/usr/local/pgsql/bin
   else
      echo
      echo "   pgsql bindir not found"
      echo
      exit 1
   fi
fi

## - muss als user root ausgefuehrt werden
## -
if [ "\`id -u\`" -ne 0 ]; then
   echo -e "\n\tgeht nur als user \"root\"\n"
   exit 1
fi

## --
## -- restore data for databas $db:
## --    su - \$PSQL_USER -c"\${PSQL_BIN_DIR}/psql -f \${BACKUP_DIR}/dump_${db}-${DATE}.sql  ${db}"
## --

echo -e "\n\trestore data for databas $db from backup at ${DAY}.${MONTH}.${YEAR} ${HOUR}:${MINUTE}h\n"
su - \$PSQL_USER -c "\${PSQL_BIN_DIR}/psql -f \${BACKUP_DIR}/dump_${db}-${DATE}.sql  ${db}"

EOF

      chmod 755 ${BACKUP_DIR}/restore_${db}-${DATE}.sh 

      # -- Erstelle dump (Teil 2) zum Wiederherstellen der
      # -- User und Datenbanken:
      # --    DROP DATABASE statements
      # --
      echo "DROP DATABASE \"$db\";" >> $dbusers_databases_file

   else
      echo -e "\n\t$db: [WARNING] cannot dump database $db\n"
      rm -f ${BACKUP_DIR}/dump_${db}-${DATE}.sql
   fi

done


# -- Erstelle dump (Teil 3) zum Wiederherstellen der
# -- User und Datenbanken:
# --    DROP ROLE ..
# --    CREATE ROLE..
# --
su - $PSQL_USER_SRC -c "${PSQL_BIN_DIR_SRC}/pg_dumpall -g -c " >> $dbusers_databases_file


# -- Erstelle dump (Teil 3) zum Wiederherstellen der
# -- User und Datenbanken:
# --    CREATE DATABASE
# --
cat<<EOF >> $dbusers_databases_file


--
-- Database creation
--


EOF

su - $PSQL_USER_SRC -c "${PSQL_BIN_DIR_SRC}/pg_dumpall -s"  | grep -e "^CREATE DATABASE" >> $dbusers_databases_file

# -- Bemerkung:
# -- Wiederherstellung der user und datenbanken (nur definitionen - keine daten )
# --    su - postgres -c "psql -f $dbusers_databases_file  template1"


# -- Erstelle script zum Wiederherstellen der Geasmaten Datenbank,
# -- User, Datenbanken ( Definitionen und Datensätze
# --
cat<<EOF > ${backup_dir}/restore_dump-${DATE}.sh
#!/usr/bin/env bash

PATH=/usr/local/sbin:/usr/sbin:/sbin:/usr/local/bin:/usr/bin:/bin:/root/bin

backup_dir="$backup_dir"

## - muss als user root ausgefuehrt werden
## -
if [ "\`id -u\`" -ne 0 ]; then
   echo -e "\n\tgeht nur als user \"root\"\n"
   exit 1
fi

if [ ! -d \$backup_dir ] ; then
   if [ -n "\$1" -a -d "\$1" ];then
      backup_dir="\$1"
   else
      echo
      echo "   Backup directory not found"
      echo "   try: \`basename \$0\` <path-to-backup-basedir>"
      echo
      exit 1
   fi
fi

if cat /etc/passwd 2> /dev/null | grep postgres > /dev/null ; then
   PSQL_USER=postgres
else
   PSQL_USER=pgsql
fi

PSQL_BIN_DIR="$PSQL_BIN_DIR"

if [ ! -x \$PSQL_BIN_DIR/psql ]; then
   if [ -x /usr/bin/psql ]; then
      PSQL_BIN_DIR=/usr/bin
   elif [ -x /usr/local/bin/psql ];then
      PSQL_BIN_DIR=/usr/local/bin
   elif [ -x /usr/local/pgsql/bin/psql ];then
      PSQL_BIN_DIR=/usr/local/pgsql/bin
   else
      echo
      echo "   pgsql bindir not found"
      echo
      exit 1
   fi
fi

logfile=\${backup_dir}/restore_dump-${DATE}.log

# -- Wiedrherstellung der Datanbank User und Datenbank Definitionen 
# --
## restore_file=\`find \$backup_dir -maxdepth 1  -mindepth 1  -type f -printf "%f\n" | grep db_users_databases\`
## _list=\`ls -t  \${backup_dir}/db_users_databases*\`
## restore_file=\`echo \$_list  | awk '{print\$1}'\`

## restore_file=$dbusers_databases_file
restore_file=\${backup_dir}/db_users_databases-${DATE}.sql

echo -e "\n\tWiedrherstellung der Datanbank User und Datenbank Definitionen"
echo -e "\t==============================================================\n"
## echo -e "\tsu - \$PSQL_USER -c \"\${PSQL_BIN_DIR}/psql -f \$restore_file template1\"\n"

su - \$PSQL_USER -c "\${PSQL_BIN_DIR}/psql -f \$restore_file template1" > \$logfile 2>&1


echo
echo -n "Sollen die Datenbanksaetze reorganisiert werden? [ja/nein]: "
read OK

while [ "X\$OK" != "Xyes" -a "X\$OK" != "XYes" -a "X\$OK" != "Xja"  -a "X\$OK" != "XJa" \
        -a "X\$OK" != "XNo" -a "X\$OK" != "Xno" -a "X\$OK" != "Xn" -a "X\$OK" != "Xnein" -a "X\$OK" != "XNein" ]
do
   echo -n "falsche Angabe! [ja/nein]: "
   read OK
done

[ \$OK = "Yes" -o \$OK = "yes" -o "\$OK" = "ja"  -o "\$OK" = "Ja" ] || exit 0

# -- Wiedrherstellung der Datanbankdaten
# --
## databases=\`find \$backup_dir -maxdepth 1  -mindepth 1  -type d -printf "%f "\`
databases="$dbs_dumped"
echo -e "\n\tWiedrherstellung der Datanbankdaten"
echo -e "\t===================================\n"
for db in \$databases ; do

   ## _list=\`ls -d -t \${backup_dir}/\$db/*${DATE}.sql\`
   ## file=\`echo \$_list | awk '{print\$1}'\`
   file=\${backup_dir}/\$db/dump_\${db}-${DATE}.sql
   ## echo -e "\\t\$file"
   if [ -f \$file ]; then
      ## echo -e "\\t\${db}: su - \$PSQL_USER -c\"\${PSQL_BIN_DIR}/psql -f \$file \$db\" restoring..\n"
      echo -e "\\t\${db}: restoring..\n"
      su - \$PSQL_USER -c" \${PSQL_BIN_DIR}/psql -f \$file \$db" >> \$logfile 2>&1
   else
      echo -e "\\t\${db}: [WARNING] konnte keine Sicherung vom ${DAY}.${MONTH}.${YEAR} (${HOUR}:${MINUTE}h) finden\n"
   fi
done
echo

exit 0
EOF

chmod 755 ${backup_dir}/restore_dump-${DATE}.sh

for db in $dbs_dumped ; do
   cat $dbusers_databases_file | grep -e "CREATE DATABASE\ *$db" > ${backup_dir}/${db}/createdb_${db}-${DATE}.sql

   cat <<EOF >${backup_dir}/${db}/createdb_${db}-${DATE}.sh
#!/usr/bin/env bash

PATH=/usr/local/sbin:/usr/sbin:/sbin:/usr/local/bin:/usr/bin:/bin:/root/bin

## - muss als user root ausgefuehrt werden
## -
if [ "\`id -u\`" -ne 0 ]; then
   echo -e "\n\tgeht nur als user \"root\"\n"
   exit 1
fi


backup_dir="$backup_dir"

if [ ! -d \$backup_dir ] ; then
   if [ -n "\$1" -a -d "\$1" ];then
      backup_dir="\$1"
   else
      echo
      echo "   Backup directory not found"
      echo "   try: \`basename \$0\` <path-to-backup-basedir>"
      echo
      exit 1
   fi
fi

if cat /etc/passwd 2> /dev/null | grep postgres > /dev/null ; then
   PSQL_USER=postgres
else
   PSQL_USER=pgsql
fi

PSQL_BIN_DIR="$PSQL_BIN_DIR"

if [ ! -x \$PSQL_BIN_DIR/psql ]; then
   if [ -x /usr/bin/psql ]; then
      PSQL_BIN_DIR=/usr/bin
   elif [ -x /usr/local/bin/psql ];then
      PSQL_BIN_DIR=/usr/local/bin
   elif [ -x /usr/local/pgsql/bin/psql ];then
      PSQL_BIN_DIR=/usr/local/pgsql/bin
   else
      echo
      echo "   pgsql bindir not found"
      echo
      exit 1
   fi
fi

file=\${backup_dir}/${db}/createdb_${db}-${DATE}.sql
logfile=\${backup_dir}/${db}/createdb_${db}-${DATE}.log

su - \$PSQL_USER -c "\${PSQL_BIN_DIR}/psql -f \$file" | tee  \${backup_dir}/${db}/createdb_${db}-${DATE}.log
EOF

   chmod 755 ${backup_dir}/${db}/createdb_${db}-${DATE}.sh

   ## - get user 
   ## - 
   _owner=`cat ${backup_dir}/${db}/createdb_${db}-${DATE}.sql  | grep OWNER | sed -e "s/.*OWNER[\ ]\{1,\}=[\ ]\{1,\}\([^\ ]*\).*/\1/"`
   owner=`echo $_owner | cut -d' ' -f1`
   owner=`echo $owner | cut -d';' -f1`
   ## echo -e "\tOWNER = X${owner}X"
   if [ -n "$owner" ]; then
      cp ${backup_dir}/createrole_${owner}-${DATE}.sql ${backup_dir}/${db}/createuser_${owner}-${DATE}.sql


      cat <<EOF >${backup_dir}/${db}/createuser_${owner}-${DATE}.sh
#!/usr/bin/env bash

PATH=/usr/local/sbin:/usr/sbin:/sbin:/usr/local/bin:/usr/bin:/bin:/root/bin

## - muss als user root ausgefuehrt werden
## -
if [ "\`id -u\`" -ne 0 ]; then
   echo -e "\n\tgeht nur als user \"root\"\n"
   exit 1
fi

backup_dir="$backup_dir"

if [ ! -d \$backup_dir ] ; then
   if [ -n "\$1" -a -d "\$1" ];then
      backup_dir="\$1"
   else
      echo
      echo "   Backup directory not found"
      echo "   try: \`basename \$0\` <path-to-backup-basedir>"
      echo
      exit 1
   fi
fi

if cat /etc/passwd 2> /dev/null | grep postgres > /dev/null ; then
   PSQL_USER=postgres
else
   PSQL_USER=pgsql
fi

PSQL_BIN_DIR="$PSQL_BIN_DIR"

if [ ! -x \$PSQL_BIN_DIR/psql ]; then
   if [ -x /usr/bin/psql ]; then
      PSQL_BIN_DIR=/usr/bin
   elif [ -x /usr/local/bin/psql ];then
      PSQL_BIN_DIR=/usr/local/bin
   elif [ -x /usr/local/pgsql/bin/psql ];then
      PSQL_BIN_DIR=/usr/local/pgsql/bin
   else
      echo
      echo "   pgsql bindir not found"
      echo
      exit 1
   fi
fi

file=\${backup_dir}/${db}/createuser_${owner}-${DATE}.sql
logfile=\${backup_dir}/${db}/createuser_${owner}-${DATE}.log

su - \$PSQL_USER -c "\${PSQL_BIN_DIR}/psql -f \$file" | tee  \${backup_dir}/${db}/createdb_${db}-${DATE}.log
EOF

      chmod 755 ${backup_dir}/${db}/createuser_${owner}-${DATE}.sh

   fi


   

done

if $_logging ; then
   echo ""
fi

# -- Create archive from the whole backup
# --
if  $_local ; then
   _curdir=`pwd`
   cd ${backup_dir}
   cd .. > /dev/null 2>&1
   if $_logging ; then
      echo -e "\tCreate archiv pgsql_dump-${DATE}.tar.gz.."
   fi
   tar -czf pgsql_dump-${DATE}.tar.gz `find . -name "*${DATE}*"`
   cd $_curdir
fi

if $_logging ; then
   echo ""
fi


# delete all older than $days days
#

if $_logging ; then
   echo -e "\tDelete all generated archives in $BACKUP_BASE_DIR older than ${days} days.."
fi
/usr/bin/find $BACKUP_BASE_DIR -type f -name "*.tar.gz" -mtime +${days} -exec /bin/rm {} \;

if $_logging ; then
   echo -e "\tDelete generated dumps for all databases older than ${days} days.."
fi
/usr/bin/find $BACKUP_BASE_DIR -type f -name "*.sql" -mtime +${days} -exec /bin/rm {} \;

if $_logging ; then
   echo -e "\tDelete generated scripts for all databases older than ${days} days.."
fi
/usr/bin/find $BACKUP_BASE_DIR -type f -name "*.sh" -mtime +${days} -exec /bin/rm {} \;

if $_logging ; then
   echo ""
fi

exit 0