【PG】查看PG对象大小增长情况

#!/bin/bash

##################################################
#
# check_postgresql_db_table_rowsnum_and_sizing.sh
#
# This scripts iterates arti databases and 
# populates some target db table with
# info about tables rownum and sizing
#
# Date: 10-Aug-2022
#
# Author: Dmitry
#
##################################################

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

output_name="/tmp/temp_check_tables_rowsnum_and_sizing.lst"

run_id=1

export PGCONNECT_TIMEOUT=15

export the_yyyymmdd=$(date '+%Y%m%d')
export hh24miss=$(date '+%H%M%S')

export PGPASSWORD='password1'
psql -h <db_host> -p <db_port> -U <db_user> -d <db_name> -qtX << EOF > ${output_name}
SELECT '$the_yyyymmdd', '$hh24miss', '<my_db_instance>', '<db_port>', '<db_name>', pgClass.relname, to_char(pgClass.reltuples, '999999999999999999') row_nums, to_char(pg_relation_size(pgClass.oid)/1024/1024, '999999999999999999') AS tablesize_mega_bytes, $run_id
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'
EOF

run_file_name="/tmp/runme_temp_check_tables_rowsnum_and_sizing.tmp.sql"

echo "insert into my_stats_db_tables_and_their_rownums_n_size values " > ${run_file_name}
cat ${output_name} | awk -F "|" ' { if (length($1)>0) printf("(trim(\x27%s\x27), trim(\x27%s\x27), trim(\x27%s\x27), trim(\x27%s\x27), trim(\x27%s\x27), trim(\x27%s\x27), %s, %s, %s),\n", $1, $2, $3, $4, $5, $6, $7, $8, $9); } ' >> ${run_file_name}
cat ${output_name} | awk -F "|" ' NR==1 { if (length($1)>0) printf("(trim(\x27%s\x27), trim(\x27%s\x27), trim(\x27%s\x27), trim(\x27%s\x27), trim(\x27%s\x27), trim(\x27%s\x27), %s, %s, %s)\n", $1, $2, $3, $4, $5, $6, $7, $8, $9); } ' >> ${run_file_name}
echo " on conflict do nothing;" >> ${run_file_name}

n_of_recs_in_the_sql_file=`cat ${run_file_name} | wc -l`

if [ "$n_of_recs_in_the_sql_file" -gt "2" ]; then
export PGPASSWORD='password2'
psql -U <report_db_user> -h <report_db_host> -p <report_db_port> -d <report_db_name> -f ${run_file_name}
fi

echo " "
echo "End"
echo " "