In this Shell script will displays schema name and table name in which table having more than dead tubles in a postgresql server
How to create & run shell script file ?
create a shell script file here created tables_dead_tubles.sh using vim editor and change the environment variable as well as db name as per your environment, i – for inserting mode then paste the following script then pressEsc button then enter :wq for saving script then run the script using sh tables_dead_tubles.sh
cat tables_dead_tubles.sh echo " " echo " " HOSTNAME=`hostname` PSQL="/opt/PostgreSQL/9.3/bin/psql" PORT=5432 HOST="localhost" DB="template1" USER="postgres" echo "Enter the dead tuble count for example if you give 5000 means the script will dispays the schema name as well as table name How many table having more than 5000 dead tubles in database "; read count echo "------***WHAT ARE TABLES HAVING MORE THAN $count DEAD TUBLES PARTICULAR DATABASE***------" echo " " $PSQL -d $DB -U $USER -p $PORT <<EOF \c Your_database_name select schemaname,relname from pg_stat_all_tables where n_dead_tup > $count; EOF echo " " # skip following 4 lines (untill EOF) if your postgresql server having single database $PSQL -d $DB -U $USER -p $PORT <<EOF \c your_2nd_db_name select schemaname,relname from pg_stat_all_tables where n_dead_tup > $count; EOF echo " "
After running this script for example If you get 100 of table name in which table having more than 5000 dead tubles
then schedule vacuum for set of tables table using crontjob or schedule autovacuum for that particular tables then only you will get good performance of your postgresql server,This is one of the most important concept in postgresql tuning