#!/bin/bash
################################################################
#
# calculate_a_pure_size_of_the_postgres_db.sh
#
# This script calculates a pure size of the Postgres DB
#
# DB_Pure_Size = DB_Size - DB_Tables_Bloat - DB_Indexes_Bloat
#
# Author: Dmitry
#
################################################################
helpFunction()
{
echo ""
echo "Usage: $0 -h db_hostname -p db_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 db_user -d 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
db_total_size_mb=`psql -h $inpHost -p $inpPort -U $inpDBUser -d $inpDBName -At -c "SELECT round(pg_database_size('$inpDBName')/1024/1024);"`
db_tables_bloat_size_mb=$(psql -h $inpHost -p $inpPort -U $inpDBUser -d $inpDBName -At << EOF
select round(sum(M.wastedbytes)/1024/1024) tables_bloated_mb
from
(SELECT
distinct
current_database(), schemaname, tablename,
ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM (
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
where schemaname = 'public'
and tablename not in ('table_you_may_want_to_exclude')
ORDER BY wastedbytes DESC) M;
EOF
)
db_indexes_bloat_size_mb=$(psql -h $inpHost -p $inpPort -U $inpDBUser -d $inpDBName -At << EOF
select round(sum(wastedibytes)/1024/1024) indexes_bloated_mb
from
(SELECT
current_database(), schemaname, tablename,
iname,
ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::float/iotta END)::numeric,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
SELECT
schemaname, tablename, cc.reltuples, cc.relpages, bs,
CEIL((cc.reltuples*((datahdr+ma-
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
FROM (
SELECT
ma,bs,schemaname,tablename,
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
FROM (
SELECT
schemaname, tablename, hdr, ma, bs,
SUM((1-null_frac)*avg_width) AS datawidth,
MAX(null_frac) AS maxfracsum,
hdr+(
SELECT 1+count(*)/8
FROM pg_stats s2
WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
) AS nullhdr
FROM pg_stats s, (
SELECT
(SELECT current_setting('block_size')::numeric) AS bs,
CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
FROM (SELECT version() AS v) AS foo
) AS constants
GROUP BY 1,2,3,4,5
) AS foo
) AS rs
JOIN pg_class cc ON cc.relname = rs.tablename
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
LEFT JOIN pg_index i ON indrelid = cc.oid
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
where schemaname = 'public'
and tablename not in ('table_you_may_want_to_exclude')
ORDER BY wastedibytes DESC) M;
EOF
)
db_pure_size_mb=$(( db_total_size_mb - db_tables_bloat_size_mb - db_indexes_bloat_size_mb ))
echo " "
echo "------------------------------------------"
echo "DB Host: $inpHost"
echo "DB Port: $inpPort"
echo "DB Username: $inpDBUser"
echo "DB Name: $inpDBName"
echo "------------------------------------------"
echo "db_total_size_mb: $db_total_size_mb MB"
echo "db_tables_bloat_size_mb: $db_tables_bloat_size_mb MB"
echo "db_indexes_bloat_size_mb: $db_indexes_bloat_size_mb MB"
echo "------------------------------------------"
echo "db_pure_size_mb: $db_pure_size_mb MB"
echo "------------------------------------------"
echo " "
##############################################################
#
# End
#
##############################################################
