Shell script to check and record the database size in Redshift Database

Here is the custom script used to check the Redshift DB size and record it every month .This  will  be useful to get the know the DB growth.

1.create the table on DB  to store the DB size

CREATE TABLE admin.rs_db_size_dev (

    db_name    VARCHAR(256) ENCODE zstd,

    db_size_gb NUMERIC(    db_size_gb NUMERIC(37,2) ENCODE az64,

    date       DATE ENCODE az64

)

DISTSTYLE AUTO

2.schedule the job on cron

2 0 12 * *  /redshiftadmin/aws/scripts/dev_db_size_chk.sh DB1_DW1_India >> /redshiftadmin/aws/audit/log/db_size_dev_india.log 2>&1

3.load the script to  the server.

##################################################################################################

## purpose     :script used to collect Redshift dev DB size details                              #

## Author      : Bala P                                                                          #

## Developed   :10-july-2023 V1                                                                  #

##################################################################################################

#

##!/usr/bin/bash

#

export PGHOST=XXXXXXXXXXX.redshift.amazonaws.com

export PGPORT='5439'

export PGDATABASE=$1

export PGUSER=rsdbadmin

export PGPASSWORD='XXXXXXXXXXXX'

export wdir=/redshiftadmin/aws/audit/scripts/

export logdir=/redshiftadmin/aws/audit/log

query_result=$(psql -tA -c  "INSERT INTO admin.rs_db_size_dev (db_name, db_size_GB, date)

SELECT     dbase_name, total_GB AS db_size, CURRENT_DATE   FROM ( SELECT   dbase_name,  SUM(megabytes/1024) AS total_GB

FROM  admin.v_space_used_per_tbl   GROUP BY   dbase_name ) AS aggregated_data;")

#

#

if [[ -n "$query_result" ]]; then

##   if [[ -n "$query_result" && $(echo "$query_result" | grep -c 'exec_time_hours > 0.001') -gt 0 ]]; then

#       

recipient="BALAS@abc.com"

       subject="DB size  @ $PGDATABASE in Dev"

       body="DB size in dev cluster\n$query_result"

       echo -e "$body" | mailx -s "$subject" "$recipient"

      fi

4.output of the script

=# select * from admin.rs_db_size_dev;

 db_name    | db_size_gb |    date

---------------+------------+------------

DB1_DW1_India |    1654.00 | 2024-10-12

DB1_DW1_India |    1671.00 | 2024-11-12

DB1_DW1_India |    1515.00 | 2024-12-12

DB1_DW1_India |    1528.00 | 2025-01-12

DB1_DW1_India |    1573.00 | 2025-02-12

DB1_DW1_India |    1579.00 | 2025-03-12

DB1_DW1_India |    1666.00 | 2025-04-12

DB1_DW1_India |    1787.00 | 2025-05-12

DB1_DW1_India |    1788.00 | 2025-06-12

DB1_DW1_India |    1791.00 | 2025-07-12

DB1_DW1_India |    1788.00 | 2025-08-12

DB1_DW1_India |    1796.00 | 2025-09-12

DB1_DW1_India |    1801.00 | 2025-10-12

DB1_DW1_India |    1801.00 | 2025-11-12

DB1_DW1_India |    1801.00 | 2025-12-12

DB1_DW1_India |     249.00 | 2023-11-10

DB1_DW1_India |     249.00 | 2023-11-10

DB1_DW1_India |     249.00 | 2023-11-12

DB1_DW1_India |     246.00 | 2023-12-12

DB1_DW1_India |     166.00 | 2024-01-12

DB1_DW1_India |     318.00 | 2024-02-12

DB1_DW1_India |     236.00 | 2024-03-12

DB1_DW1_India |     334.00 | 2024-04-12

DB1_DW1_India |     772.00 | 2024-05-12

DB1_DW1_India |     965.00 | 2024-06-12

DB1_DW1_India |    1082.00 | 2024-07-12

DB1_DW1_India |    1528.00 | 2024-08-12

DB1_DW1_India |    1606.00 | 2024-09-12

(28 rows)