How to reset pg_stat_database statistics for all DBs on a PostgreSQL Server?

This script connects to each DB on a DB instance and resets pg_stat_database statistics.

#!/bin/bash

##################################################################################################################################
#
# Name: reset_pg_stat_database_statistics.sh
#
# Description: This script connects to each DB on a DB instance and resets pg_stat_database statistics
#
# Author: Dmitry
#
# Date: 11-Jan-2023
#
# Usage Example:
#
#     ./reset_pg_stat_database_statistics.sh -h localhost -p port -x db_pattern -a start_position -b end_position
#
####################################################################################################################################


helpFunction()
{
   echo ""
   echo "Usage: $0 -h hostname -p port -x db_pattern -a offset -b limit"
   echo -e "\t-h Postgres hostname"
   echo -e "\t-p Postgers port"
   echo -e "\t-x Postgres db pattern"
   echo -e "\t-a Offset starting from 0"
   echo -e "\t-b Limit"
   echo -e " "
   echo -e "Example how to run: $0 -h localhost -p 5432 -x % -a 1 -b 10 "
   echo -e " "
   exit 1 # Exit script after printing help
}

while getopts "h:p:x:a:b:" opt
do
   case "$opt" in
      h ) inpHost="$OPTARG" ;;
      p ) inpPort="$OPTARG" ;;
      x ) inpDBPattern="$OPTARG" ;;
      a ) inpOffset="$OPTARG" ;;
      b ) inpLimit="$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 "$inpDBPattern" ] || [ -z "$inpOffset" ] || [ -z "$inpLimit" ] 
then
   echo "Some or all of the parameters are empty";
   helpFunction
fi

echo " " 
echo "Input parameters:" 
echo "---------------- " 
echo "inpHost=$inpHost" 
echo "inpPort=$inpPort" 
echo "inpDBPattern=$inpDBPattern"
echo "inpOffset=$inpOffset"
echo "inpLimit=$inpLimit"
echo "---------------- "

num_of_dbs_to_process=$(psql -h $inpHost -p $inpPort -t -c "select count(M.*) from (select datname from pg_database where datname like '${inpDBPattern}' order by 1 offset ${inpOffset} limit ${inpLimit}) M " postgres)

echo " "
echo "------------------------------------------------------------------"
echo " Number of databases: ${num_of_dbs_to_process}"
echo "------------------------------------------------------------------"

idx=${inpOffset}

idx=$(($idx + 1))

for DBs_to_process in $(psql -h $inpHost -p $inpPort -t -c "select M.* from (select datname from pg_database where datname like '${inpDBPattern}' order by 1 offset ${inpOffset} limit ${inpLimit}) M " postgres); do
# echo " " 
# echo "#${idx}:"
# echo "${DBs_to_process}"

psql -h $inpHost -p $inpPort -U postgres -d $DBs_to_process -qtX << EOF
select pg_stat_reset(); 
select '#${idx}: ${DBs_to_process}', stats_reset from pg_stat_database where datname='$DBs_to_process';
EOF

idx=$(($idx + 1))

done

echo " "
echo "                      --- The End ---                            " 
echo " "

[e-mail]