#!/bin/bash

##########################################
#
# check_size_db_objects_and_rowsnum.sh
#
# This script connects to the DB and
# retrives the info 
# about tables number of records and size.
#
# Date: 11-Nov-2022
#
# Author: Dmitry
#
##########################################

helpFunction()
{
   echo ""
   echo "Usage: $0 -h db_hostname -p port -U db_username -d db_name"
   echo -e "\t-h Postgres db hostname"
   echo -e "\t-p Postgers db port"
   echo -e "\t-U Postgres db username"
   echo -e "\t-d Postgres db name"
   echo -e " "
   echo -e "Example how to run: $0 -h localhost -p 5432 -U my_db_user -d my_db_name "
   echo -e " "
   exit 1 # Exit script after printing help
}

while getopts "h:p:U:d:" opt
do
   case "$opt" in
      h ) inpHost="$OPTARG" ;;
      p ) inpPort="$OPTARG" ;;
      U ) inpDBUser="$OPTARG" ;;
      d ) inpDBName="$OPTARG" ;;
      ? ) helpFunction ;; # Print helpFunction in case parameter is non-existent
   esac
done

# Print helpFunction in case parameters are empty
if [ -z "$inpHost" ] || [ -z "$inpPort" ] || [ -z "$inpDBUser" ] || [ -z "$inpDBName" ] 
then
   echo "Some or all of the parameters are empty";
   helpFunction
fi

echo " " 
echo "Input parameters:"
echo "---------------- " 
echo "inpHost=$inpHost"
echo "inpPort=$inpPort" 
echo "inpDBUser=$inpDBUser"
echo "inpDBName=$inpDBName"
echo "---------------- "

export the_yyyymmdd=$(date '+%Y%m%d')
export hh24miss=$(date '+%H%M%S')

psql -h $inpHost -p $inpPort -U $inpDBUser -d $inpDBName -qtX << EOF 
SELECT  '$the_yyyymmdd', '$hh24miss', '$inpPort', '$inpDBUser', '$inpDBName',
  pgClass.relname, 
  to_char(pgClass.reltuples, '999999999999999999') row_nums, 
  to_char(pg_relation_size(pgClass.oid)/1024/1024, '999999999999999999') AS tablesize_mega_bytes
FROM pg_class pgClass
INNER JOIN pg_namespace pgNamespace ON (pgNamespace.oid = pgClass.relnamespace)
WHERE pgNamespace.nspname NOT IN ('pg_catalog', 'information_schema') AND pgClass.relkind='r'
ORDER BY cast(to_char(pgClass.reltuples, '999999999999999999') as double precision) DESC,
         cast(to_char(pg_relation_size(pgClass.oid)/1024/1024, '999999999999999999') as double precision) DESC;
EOF

output_name="/tmp/report_check_size_db_objects_and_rowsnum_${the_yyyymmdd}_${hh24miss}.trc"

psql -h $inpHost -p $inpPort -U $inpDBUser -d $inpDBName -qtX << EOF >> ${output_name}
SELECT  '$the_yyyymmdd', '$hh24miss', '$inpPort', '$inpDBUser', '$inpDBName',
  pgClass.relname, 
  to_char(pgClass.reltuples, '999999999999999999') row_nums, 
  to_char(pg_relation_size(pgClass.oid)/1024/1024, '999999999999999999') AS tablesize_mega_bytes
FROM pg_class pgClass
INNER JOIN pg_namespace pgNamespace ON (pgNamespace.oid = pgClass.relnamespace)
WHERE pgNamespace.nspname NOT IN ('pg_catalog', 'information_schema') AND pgClass.relkind='r'
ORDER BY cast(to_char(pgClass.reltuples, '999999999999999999') as double precision) DESC,
         cast(to_char(pg_relation_size(pgClass.oid)/1024/1024, '999999999999999999') as double precision) DESC;
EOF

echo " "
echo "End"
echo " "