Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Script To Listing Postgresql dead tuble tables

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 press Esc 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

Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

What's is the difference between streaming replication Vs hot standby vs warm standby ?

PostgreSQL Sequence