TutorialDBA - Support | Training | Consultant

In this tutorial i will explained about how to find out vacuum size of database level and how to write script for vacuum and analyze in postgreSQL open source database. Ans: 1.Find out the vacuum level  \c chennai select count(*) from pg_stat_all_tables where n_dead_tup > 1000; \c mumbai select count(*) from pg_stat_all_tables where n_dead_tup > 1000; \c kolkatta select count(*) from pg_stat_all_tables where n_dead_tup > 1000; \c banglore select count(*) from pg_stat_all_tables where n_dead_tup > 1000; \c newyork select count(*) from pg_stat_all_tables where n_dead_tup > 1000; select schemaname,relname,n_dead_tup,last_vacuum,last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables; 2.Script for Analyze and vacuum the postgresql dead tubles vi /home/script/vaccum_database.sh #!/bin/sh # The script sets environment variables helpful for PostgreSQL export PATH=/opt/PostgreSQL/9.6/bin:$PATH export PGDATA=/data/emut_96/ export PGDATABASE=post...

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 ### Ma...