postgres-size-report

#!/bin/bash

 
TABLEQUERY=$(cat <<-END
 SELECT table_name, pg_size_pretty(total_bytes) AS total
      , pg_size_pretty(index_bytes) AS INDEX
      , pg_size_pretty(toast_bytes) AS toast
      , pg_size_pretty(table_bytes) AS TABLE
    FROM (
    SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
        SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
                , c.reltuples AS row_estimate
                , pg_total_relation_size(c.oid) AS total_bytes
                , pg_indexes_size(c.oid) AS index_bytes
                , pg_total_relation_size(reltoastrelid) AS toast_bytes
            FROM pg_class c
            LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE relkind = 'r'
    ) a
  ) a order by total_bytes DESC LIMIT 30;
END
)

if [ -f /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf ]; then
  PGSQL_DIR="/var/opt/rh/rh-postgresql12/lib/pgsql"
else
  PGSQL_DIR="/var/lib/pgsql"
fi

# echo $TABLEQUERY
cd ~postgres

echo ""
echo "************* Candlepin Tablesizes *************"
echo ""
echo $TABLEQUERY | su - postgres -c "psql candlepin"

echo ""
echo "************** Foreman Tablesizes **************"
echo ""
echo $TABLEQUERY | su - postgres -c "psql foreman"

if PULPCORE_TABLESIZES=$(echo $TABLEQUERY | su - postgres -c "psql pulpcore" 2>/dev/null)
then
  echo ""
  echo "************** Pulpcore Tablesizes *************"
  echo ""
  echo "$PULPCORE_TABLESIZES"
fi

echo ""
echo "*************** FileSystem Usage ***************"
echo ""

echo "du -hs $PGSQL_DIR/*"
du -hs $PGSQL_DIR/*
echo
echo "du -hs $PGSQL_DIR/data/*"
du -hs $PGSQL_DIR/data/*