Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Postgresql Server monitoring shell script

In this shell script will be helpful for monitoring RAM usage,DISK size,how many queries is running more than 5 minutes in a postgres cluster and how many dead tuble is occurred particular database in cluster,

How to create & run shell script file ?
create  a shell script file here created monitor.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 monitor.sh
$ cat monitor.sh

echo " "
echo " "
HOSTNAME=`hostname`
PSQL="/opt/PostgreSQL/9.3/bin/psql"
PORT=5432
HOST="localhost"
DB="template1"
USER="postgres"
echo "Enter the Time in minutes ,For example if you give 10 means script will displayed what are the query is running more than 10 minutes";
read time
echo "------***WHAT ARE THE QUERY IS RUNING MORE THAN $time MINUTES***------"
$PSQL -d $DB -U $USER -p $PORT <<EOF
\pset format wrapped
SELECT pid, now() - query_start as "runtime", usename, datname, state, query
  FROM  pg_stat_activity
  WHERE now() - query_start > '$time minutes'::interval
 ORDER BY runtime DESC;

EOF

echo " "
echo " "
echo " "

echo "------***CHECKING dISK SPACE***------"
        df -h

echo " "
echo " "
echo " "

echo "------***CHECKING RAM USAGE***------"
       free -h

echo " "
echo " "
echo " "
echo "Enter the dead tuble count for example if you give 5000 display the table count how many table having more than 5000 dead tubles in database ";
read count
echo "------***HOW MANY TABLES HAVING MORE THAN $count DEAD TUBLES PARTICULAR DATABASE***------"


$PSQL -d $DB -U $USER -p $PORT <<EOF
\c mhrorpar
select count(*) from pg_stat_all_tables where n_dead_tup > $count;
EOF

echo " "

$PSQL -d $DB -U $USER -p $PORT <<EOF
\c mhroraur
select count(*) from pg_stat_all_tables where n_dead_tup >$count;
EOF

echo " "

$PSQL -d $DB -U $USER -p $PORT <<EOF
\c mhroryav
select count(*) from pg_stat_all_tables where n_dead_tup >$count;
EOF

echo " "

$PSQL -d $DB -U $USER -p $PORT <<EOF
\c mhrorlat
select count(*) from pg_stat_all_tables where n_dead_tup >$count;
EOF

echo " "

$PSQL -d $DB -U $USER -p $PORT <<EOF
\c mhrornan
select count(*) from pg_stat_all_tables where n_dead_tup >$count;
EOF

echo " "

$PSQL -d $DB -U $USER -p $PORT <<EOF
\c mhrornad
select count(*) from pg_stat_all_tables where n_dead_tup >$count;
EOF 
 

Comments

Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

spfile and pfile errors

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

2017 Shortern URL - Other Website Like ADFOC.US

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