How to check the number of transactions per second (TPS) for the given PostgreSQL DB instance?

Photo by Lukas Blazek on Unsplash

To find the number of transactions per second for the given PostgreSQL DB instance run the following script.

#!/bin/sh
###############################################
#
# Check transactions per second
# for the given PostgreSQL DB instance
#
# This script connects
# to the given PostgreSQL DB instance
# and retrieves information -
# the number of transactions per second
#
# Date: 10-Aug-2020
#
# Author: Dmitry
#
###############################################

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

trim() {
  local var="$*"
  # remove leading whitespace characters
  var="${var#"${var%%[![:space:]]*}"}"
  # remove trailing whitespace characters
  var="${var%"${var##*[![:space:]]}"}"
  printf '%s' "$var"
}

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

a=$(psql -h $inpHost -p $inpPort -U $inpDBUser -d $inpDBName -t -c "select clock_timestamp() t, sum(xact_commit+xact_rollback) n, round(extract(epoch from now())) tt from pg_stat_database")
a=`trim $a`

tps_a_t=`echo $a | awk ' { printf("%s %s", $1, $2) } '`
tps_a=`echo $a | awk ' { print $4 } '`
tps_a_tt=`echo $a | awk ' { print $6 } '`

sleep 1

b=$(psql -h $inpHost -p $inpPort -U $inpDBUser -d $inpDBName -t -c "select clock_timestamp() t, sum(xact_commit+xact_rollback) n, round(extract(epoch from now())) tt from pg_stat_database")
b=`trim $b`

tps_b_t=`echo $b | awk ' { printf("%s %s", $1, $2) } '`
tps_b=`echo $b | awk ' { print $4 } '`
tps_b_tt=`echo $b | awk ' { print $6 } '`

# echo "($tps_a_t, $tps_a_tt, $tps_a)"
# echo "($tps_b_t, $tps_b_tt, $tps_b)"

x=$((tps_b-tps_a))
y=$((tps_b_tt-tps_a_tt))

# echo "x: $x"
# echo "y: $y"

tps=$((x/y))

echo "tps: $tps"