Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Vacuum and analyze script

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







Comments

Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools