Postgresql HealthCheck Shell Script

• This script will help DBA's to save their time. Being a DBA the first thing you want to check is the Health check of databases. It's always great to schedule it and let the script do your job. ?? I am sure many more things can be added to the scripts depending on your requirements, I hope this script will help you out to some extent.

#---------------------------------------------------------------------------------------

# SCRIPT FOR OS AND POSTGRESQL DB HEALTH CHECK?

# THIS SCRIPT CAN BE USED FOR SINGLE INSTANCE HEALTH CHECK

# SCRIPT WILL BE SENT ON THE EMAIL IN MAILTO VARIABLE (INSTALL PACKAGE MUTT)

# SCRIPT HAS BEEN TESTED ON UBUNTU OS?

# IT'S RECOMMENDED TO TEST THE SCRIPT ON TEST MACHINE FIRST BEFORE DEPLOYING IT ON LIVE

# REACH ME OUT ON [email protected] IF YOU HAVE ANY QUESTIONS REGARDING THIS SCRIPT?

# REMEMBER ME IN YOUR PRAYERS

#---------------------------------------------------------------------------------------

DT=`date +"%B-%d-%G"`; export DT

Day=`date +"%a"`; export Day

MAILTO='[email protected]'; export MAILTO

echo?"\n----------------------------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"-------------------------- POSTGRESQL HEALTHCHECK --------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"----------------------------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n\t\t------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\t\t---------------- Database Health Check ---------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\t\t------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n\n--------------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"%%%%%%%%%%%%%%%%%% DATABASE IP AND HOSTNAME %%%%%%%%%%%%%%%%%%" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"--------------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

hostname -I | awk '{print $1}' >> /backups/Healthcheck/HealthCheck_$DT.txt;

hostname >> /backups/Healthcheck/HealthCheck_$DT.txt;

echo?"\n\n------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"%%%%%%%%%%%%%%%%%% DATABASE UPTIME %%%%%%%%%%%%%%%%%%%" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

/usr/lib/postgresql/12/bin/psql -U hc?-d postgres -c "SELECT pg_postmaster_start_time() as uptime;" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n\n----------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"%%%%%%%%%%%%%%%%%% REPLICATION_SYNCED_UNTIL %%%%%%%%%%%%%%" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"----------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

/usr/lib/postgresql/12/bin/psql -U hc?-d postgres -c "SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE EXTRACT (hour FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay;" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n\n----------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"%%%%%%%%%%%%%%%%%%%%% WAL RECIEVER %%%%%%%%%%%%%%%%%%%%%%%" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"----------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

/usr/lib/postgresql/12/bin/psql -U hc?-d postgres -c "\x" -c "SELECT * FROM pg_stat_wal_receiver" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n\n----------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"%%%%%%%%%%%%%%%%%%%%% WAL SENDER %%%%%%%%%%%%%%%%%%%%%%%%%" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"----------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

/usr/lib/postgresql/12/bin/psql -U hc?-d postgres -c "\x" -c "select usename, client_addr , state , replay_lag , sync_state, reply_time?from pg_catalog.pg_stat_replication" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n\n----------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"%%%%%%%%%%%%%%% Queries Running Since 5 Mints %%%%%%%%%%%%" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"----------------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

/usr/lib/postgresql/12/bin/psql -U hc?-d postgres -c "SELECT pid, now() - query_start as "runtime", usename, datname, state, query FROM?pg_stat_activity WHERE now() - query_start > '5 minutes'::interval and state = 'active' ORDER BY runtime DESC" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n\t\t-----------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\t\t------------------ OS HEALTH CHECK ------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\t\t-----------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n\n--------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"%%%%%%%%%%%%%%%%%% MOUNT POINTS %%%%%%%%%%%%%%%%%%" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"--------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

df -h?>> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n\n--------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"%%%%%%%%%%%%%%%%%% UPTIME %%%%%%%%%%%%%%%%%%" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"--------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

uptime >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n\n------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"%%%%%%%%%%%%%%%%%% ERROR LOGS %%%%%%%%%%%%%%%%%%" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

egrep -i -E 'error|warn|criti|pg_hba' /var/lib/postgresql/12/main/log/postgresql-$Day.log >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n\n-------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"%%%%%%%%%%%%%%%%%% TOP COMMAND %%%%%%%%%%%%%%%%%%" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"-------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

top -b -n 1| head -n 30 >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"\n\n-------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"%%%%%%%%%%% POSTGRESQL SERVICE STATUS %%%%%%%%%%%" >> /backups/Healthcheck/HealthCheck_$DT.txt

echo?"-------------------------------------------------" >> /backups/Healthcheck/HealthCheck_$DT.txt

/usr/bin/systemctl status postgresql >> /backups/Healthcheck/HealthCheck_$DT.txt

echo "" | mutt -s "HealthCheck" $MAILTO -i /backups/Healthcheck/body.txt?-a /backups/Healthcheck/HealthCheck_$DT.txt

#Postgresql #Healthcheck #DBHealthcheck #Percona #Postgres #shellscripting #Sctipt #DBA #Database #databaseadministration #