#!/usr/bin/env bash

# - Is this script running on terminal ?
# -
if [[ -t 1 ]] ; then
      terminal=true
      LOGGING=true
else
   terminal=false
   LOGGING=false
fi

curdir=`pwd`
cd /tmp

psql=`which psql`
grep=`which grep`
awk=`which awk`

DATABASES=`/bin/su postgres -c "$psql -lt" | $grep -v -e"^$" | $grep -v -e "^\s*[:|]" | $awk '{print$1}'`

if $terminal ; then
   echo ""
   echo "PostgeSQL: analyze and reindex databases at host $(hostname -f)."
   echo ""
fi

for db in $DATABASES ; do
   if [ "$db" == "template0" ]; then
      continue
   fi

   if $LOGGING ;then
      echo "Database...: $db"
   fi

   #TABLES=`/bin/su postgres -c "$psql -lt -q -c \"\dt \" $db" | awk '{print$3}'`
   TABLES=`/bin/su postgres -c "$psql -t -q -c \"\dt \" $db" | awk '{print$3}'`

   if $LOGGING ;then
      echo
   fi
   for tbl in $TABLES ; do

      if $LOGGING ;then
         echo -e "\tvacuum analyze table $tbl .."
      fi
      /bin/su postgres -c "$psql -q -c \"VACUUM ANALYZE $tbl\" $db"
      [[ $? -gt 0 ]] && echo "[ERROR]: vacuun/analyzing table \"${tbl}\" of database \"$db\" failed !!"

      if $LOGGING ;then
         echo -e "\treindex table $tbl ..\n"
      fi
      /bin/su postgres -c "$psql -q -c \"REINDEX TABLE $tbl\" $db"  > /dev/null 2>&1
      [[ $? -gt 0 ]] && echo "[ERROR]: reindexing table \"${tbl}\" of database \"$db\" failed !!"
   done
   if $LOGGING ;then
      echo
   fi
done

cd $curdir

if $terminal ; then
   echo "Finished analyzing and reindexing databases at host $(hostname -f)."
   echo ""
fi

exit