PostgreSQL: script to connect to DB, and get tables, their records number and size.

#postgres #database #devops #bash

This post demonstrates an example of a Bash script that connects to a DB and prints a list of tables, their records number, and size.

The result set is sorted in descending order by size and then by number of records.

In addition, the output of the script is written into a report trace file, placed in the /tmp directory.

#!/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 " "