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

PostgreSQL pgBadger

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

Migrating From Oracle to PostgreSQL using ora2pg open source tools

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

How to configure Replication Manager (repmgr) ?