#!/bin/sh

#BACKUP_BASE_DIR="/data/pgsql/backup"
BACKUP_BASE_DIR="/data/backup/pgsql"
mkdir -p $BACKUP_BASE_DIR

PSQL_BIN_DIR=/usr/local/pgsql/bin

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

days=30

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

## - muss als user root ausgeführt 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_BASE_DIR}/dblist-${DATE}.txt
echo -e "Stand: ${DATE}\n" > $listfile
su postgres -c"${PSQL_BIN_DIR}/psql -l" >> $listfile


# - Erstelle Liste der Datenbanken (zur Weiterverarbeitung)
# -
# database_list=`su postgres -c"${PSQL_BIN_DIR}/psql -Alt -F ' '" | awk '{print$1}'`

# - Erstelle Liste der User (Roles) (zur Weiterverarbeitung)
# -
role_list=`su postgres -c"${PSQL_BIN_DIR}/psql -At -F ' ' -c \"\\du\" postgres" | awk '{print$1}'`


for role in $role_list ; do
   su postgres -c"${PSQL_BIN_DIR}/pg_dumpall -g -c " | grep ${role} > ${BACKUP_BASE_DIR}/createrole_${role}-${DATE}.sql
   cat <<EOF >${BACKUP_BASE_DIR}/createrole_${role}-${DATE}.sh
#!/bin/sh

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

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

BACKUP_BASE_DIR="$BACKUP_BASE_DIR"

if [ ! -d \$BACKUP_BASE_DIR ] ; then
   if [ -n "\$1" -a -d "\$1" ];then
      BACKUP_BASE_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_BASE_DIR}/createrole_${role}-${DATE}.sql
logfile=\${BACKUP_BASE_DIR}/createrole_${role}-${DATE}.log

PSQL_USER=postgres
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/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_BASE_DIR}/createrole_${role}-${DATE}.sh
done



dbusers_databases_file=${BACKUP_BASE_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 -c"${PSQL_BIN_DIR}/psql -f $dbusers_databases_file  template1"
--

--
-- Drop dumped databases
--

\\connect postgres

EOF


# -- Erstelle dump Dateien sowie Wiederherstellungsscripte
# -- fuer die einzelnen Datenbanken
# --
databases=`su postgres -c"${PSQL_BIN_DIR}/psql -l -t"  | grep -v -e"^$" | awk '{print$1}'`

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

   [ "$db" == "template0" ] && continue
   
   # -- Erstelle dump Datei
   # --
   su postgres -c"${PSQL_BIN_DIR}/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
#!/bin/sh

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

BACKUP_BASE_DIR="$BACKUP_BASE_DIR"

if [ ! -d \$BACKUP_BASE_DIR ] ; then
   if [ -n "\$1" -a -d "\$1" ];then
      BACKUP_BASE_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_BASE_DIR}/$db

PSQL_USER=postgres
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/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 ausgeführt 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 postgres -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 postgres -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 postgres -c"${PSQL_BIN_DIR}/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 postgres -c"${PSQL_BIN_DIR}/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_BASE_DIR}/restore_dump-${DATE}.sh
#!/bin/bash

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

BACKUP_BASE_DIR="$BACKUP_BASE_DIR"

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

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

PSQL_USER=postgres
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/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_BASE_DIR}/restore_dump-${DATE}.log

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

## restore_file=$dbusers_databases_file
restore_file=\${BACKUP_BASE_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 Datenbanksätze 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_BASE_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_BASE_DIR}/\$db/*${DATE}.sql\`
   ## file=\`echo \$_list | awk '{print\$1}'\`
   file=\${BACKUP_BASE_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_BASE_DIR}/restore_dump-${DATE}.sh

for db in $dbs_dumped ; do
   cat $dbusers_databases_file | grep "DATABASE $db " > ${BACKUP_BASE_DIR}/${db}/createdb_${db}-${DATE}.sql

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

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

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


BACKUP_BASE_DIR="$BACKUP_BASE_DIR"

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

PSQL_USER=postgres
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/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_BASE_DIR}/${db}/createdb_${db}-${DATE}.sql
logfile=\${BACKUP_BASE_DIR}/${db}/createdb_${db}-${DATE}.log

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

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

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


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

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

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

BACKUP_BASE_DIR="$BACKUP_BASE_DIR"

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

PSQL_USER=postgres
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/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_BASE_DIR}/${db}/createuser_${owner}-${DATE}.sql
logfile=\${BACKUP_BASE_DIR}/${db}/createuser_${owner}-${DATE}.log

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

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

   fi


   

done

# -- Create archive from the whole backup
# --
_curdir=`pwd`
cd ${BACKUP_BASE_DIR}
cd .. > /dev/null 2>&1
tar -czf db_dump-${DATE}.tar.gz `find . -name "*${DATE}*"`
cd $_curdir


# delete all older than $days days
#
/usr/bin/find  $BACKUP_BASE_DIR/.. -type f -mtime +${days} -exec /bin/rm {} \;

exit 0