Simple & Best Postgres Free Monitoring Script

Following steps will help you to improve the Postgresql database server performance:
First  you have to issue the top command then see the process which one process is taking more CPU utilization then note that PID, if that process is postgres process means use this script you can find out query and their status and timing else you can use following query to finding query

[root@tutorialdba.com]# cat monitor.sh
echo " "
echo " "
HOSTNAME="101.0.1.123"
PSQL="/opt/edb/as9.6/bin/psql"
PORT=5444
DB="edb"
USER="enterprisedb"
export PGPASSWORD="tcs@12345" 

echo "ENTER THE TIME IN MINUTES ?"
echo "For example if you give 10 means it will display "
echo "queries which is running more than 10 minutes"
echo "0 means will display all queries:"
echo ""
read time
echo " "
echo " "


echo "WHAT ARE THE QUERY IS RUNING MORE THAN $time MINUTES"
$PSQL -d $DB -U $USER -p $PORT -h $HOSTNAME <<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 "*********************COMPLETED 1****************************** "
echo " "
echo " "
echo " "

echo "CHECKING dISK SPACE:"
        df -h

echo "*********************COMPLETED 2****************************** "
echo " "
echo " "
echo " "



echo "CHECKING RAM USAGE:"
       free -h

echo "*********************COMPLETED 3****************************** "
echo " "
echo " "
echo " "



echo "ENTER THE BLOATED COUNT ? "
echo "For example if you give 1000  means it will display the table count"
echo "how many table having more than 1000 dead tubles that database:"
echo ""
read count
echo " "
echo " "



echo "HOW MANY TABLES HAVING MORE THAN $count BLOATED TABLE :"

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

echo " "

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

echo "*********************COMPLETED 4****************************** "
echo " "
echo " "
echo " "


echo "ENTER THE BLOATED COUNT AGAING ? "
echo "it will give schema,table name and dead tuble count including "
echo "with vacuum & analyze script you can copy and run it to the   "
echo "SQL prompt what are the table having more Bloated table "
echo ""
read count1
echo ""
echo ""

echo "WHAT ARE THE TABLE HAVING MORE THAN $count1 BLOATED TABLE : "

$PSQL -d $DB -U $USER -p $PORT -h $HOSTNAME  <<EOF
\c LC
select 'vacuum ANALYZE '||schemaname||'.'||relname||';' from pg_stat_all_tables where n_dead_tup >$count1;
select schemaname,relname,n_dead_tup from pg_stat_all_tables where n_dead_tup >$count1;
EOF

echo " "

$PSQL -d $DB -U $USER -p $PORT -h $HOSTNAME  <<EOF
\c DC
select 'vacuum ANALYZE '||schemaname||'.'||relname||';' from pg_stat_all_tables where n_dead_tup >$count1;
select schemaname,relname,n_dead_tup from pg_stat_all_tables where n_dead_tup >$count1;
EOF

echo "*********************COMPLETED 5****************************** "
echo " "
echo " "
echo " "
echo " "

SAMPLE OUTPUT:

[root@ip-10-0-1-109 ec2-user]# sh monitor.sh


ENTER THE TIME IN MINUTES ?
For example if you give 10 means it will display
queries which is running more than 10 minutes
0 means will display all queries:

4


WHAT ARE THE QUERY IS RUNING MORE THAN 4 MINUTES
Output format is wrapped.
  pid  |     runtime     | usename  |    datname     | state |   query
-------+-----------------+----------+----------------+-------+------------
 15832 | 00:05:57.796746 | hr       | LC             | idle  | unlisten *
 15465 | 00:05:35.23556  | ehis     | LC 	     | idle  | unlisten *
 15113 | 00:05:34.880537 | billing  | LC	     | idle  | unlisten *
 15918 | 00:04:50.816381 | security | LC	     | idle  | unlisten *
 15930 | 00:04:48.148488 | billing  | DC 	     | idle  | unlisten *
...
..
(10 rows)

*********************COMPLETED 1******************************



CHECKING dISK SPACE:
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda2      250G  2.0G  249G   1% /
devtmpfs         16G     0   16G   0% /dev
tmpfs            16G  8.0K   16G   1% /dev/shm
tmpfs            16G  177M   16G   2% /run
tmpfs            16G     0   16G   0% /sys/fs/cgroup
/dev/xvdb       200G   33G  168G  17% /opt
tmpfs           3.2G     0  3.2G   0% /run/user/1000
*********************COMPLETED 2******************************



CHECKING RAM USAGE:
              total        used        free      shared  buff/cache   available
Mem:            31G        717M         27G        1.3G        2.7G         28G
Swap:            0B          0B          0B
*********************COMPLETED 3******************************



ENTER THE BLOATED COUNT ?
For example if you give 1000  means it will display the table count
how many table having more than 1000 dead tubles that database:

1500


HOW MANY TABLES HAVING MORE THAN 1500 BLOATED TABLE :
You are now connected to database "LC" as user "enterprisedb".
 count
-------
     3
(1 row)


You are now connected to database "DC" as user "enterprisedb".
 count
-------
     1
(1 row)

*********************COMPLETED 4******************************



ENTER THE BLOATED COUNT AGAING ?
it will give schema,table name and dead tuble count including
with vacuum & analyze script you can copy and run it to the
SQL prompt what are the table having more Bloated table

2000


WHAT ARE THE TABLE HAVING MORE THAN 2000 BLOATED TABLE :
You are now connected to database "LC" as user "enterprisedb".
                ?column?
-----------------------------------------
 vacuum ANALYZE pg_catalog.pg_attribute;
 vacuum ANALYZE pg_catalog.pg_depend;
 vacuum ANALYZE pg_catalog.pg_shdepend;
(3 rows)

 schemaname |   relname    | n_dead_tup
------------+--------------+------------
 pg_catalog | pg_attribute |      21218
 pg_catalog | pg_depend    |       9869
 pg_catalog | pg_shdepend  |       2730
(3 rows)


You are now connected to database "DC" as user "enterprisedb".
                ?column?
----------------------------------------
 vacuum ANALYZE pg_catalog.pg_shdepend;
(1 row)

 schemaname |   relname   | n_dead_tup
------------+-------------+------------
 pg_catalog | pg_shdepend |       2730
(1 row)

*********************COMPLETED 5******************************





vacuum and analyze the catalog table which is having more dead tuble, Here pg_attribute havig more dead tuble that is what am chose it and vacuum and analyze that.

LC=# vacuum ANALYZE pg_catalog.pg_attribute;
VACUUM