script for VACUUM and ANALYZE for postgres database

vacuum_and_analyze.sh script vacuums and analyzes all the database of an instance. Script pick the instance on the basis of details provided in cronjobs_conf_5432.sh. If you have multiple instances, and you want to run vacuum_and_analyze.sh for those instances too, by tweaking the instance details in cronjobs_conf_5432.sh.

bash-4.1$ cat  /post/open_source/scripts/vacuum_and_analyze.sh
#!/bin/bash

usage()
{
   echo -e "\n\tUsage:  `basename $0` -c /home/postgres/edbscripts/cronjobs_conf_5432.sh\n"
   echo -e "\tNote :  Absolute path required for cronjobs configuration file\n"
   exit 1
}

while getopts ":c:" opt; do
    case "${opt}" in
         c)
            CONF=${OPTARG}
            ;;
         *) echo "Unknown option: -$OPTARG" >&2; usage ;break;;
     esac
done
if [ -z "${CONF}" ]; then
   usage
fi
if [ ! -f "${CONF}" ]; then
   echo -e "\t\nNot a valid conf file...$CONF"
   exit 1
fi

### Main script process

source ${CONF}

process()
{
   echo -e "\n::::::::::::  VACUUM ANALYZE Details  :::::::::::::::\n"
   echo    "VACUUM ANALYZE LOG.: $CRONLOG_LOCATION/vacuum_analyze_$FILE_TIMESTAMP.log"
   START=$(date +%s)
   $PGBIN/vacuumdb -a -z -v -h $PGHOST -U $PGUSER -p $PGPORT >>$CRONLOG_LOCATION/vacuum_analyze_$FILE_TIMESTAMP.log 2>>$CRONLOG_LOCATION/vacuum_analyze_$FILE_TIMESTAMP.log
   result=${?}
   if [ ${result} -ne 0 ]
   then
      echo "Please connect to the server ASAP"| mailx -s "::ALERT::`hostname` - VACUUM Failed" $CUSTOMER_EMAIL
      echo "`date`|$CUSTOMER_NAME|`basename $0`|false|vacuum failed" >>$CRONLOG_LOCATION/cronjobs.csv
      exit 1
   else
      echo "vacuum analyze completed successfully">>$CRONLOG_LOCATION/vacuum_analyze_$FILE_TIMESTAMP.log
      END=$(date +%s)
      DIFF=$(($END-$START))
      VDB=`awk '/vacuuming database/ {print $4}' $CRONLOG_LOCATION/vacuum_analyze_$FILE_TIMESTAMP.log|wc -l`
      ISSUES=`grep -i "error:" $CRONLOG_LOCATION/vacuum_analyze_$FILE_TIMESTAMP.log|wc -l`
      echo "VACUUMED INFO.....: vacuumed $PGPORT port of $VDB databases."
      echo "TIME..............: started at $(date -d @"$START" +%d-%m-%y' '%T) with total time $((diff/60))M $((diff%60))s"
      echo "VACUUM ISSUES.....: In logs $(if [ $ISSUES -ge 1 ]; then echo "$ISSUES found. Please check logs"; else echo "nothing found"; fi)"
      echo "`date`|$CUSTOMER_NAME|`basename $0`|true|vacuum completed" >>$CRONLOG_LOCATION/cronjobs.csv
   fi
}
process >>$CRONLOG_LOCATION/crons_summary.log
exit 0