PostgreSQL Database Growth

Hi ,

Here you can find the code for finding the DB Growth Percentage in PostgreSQL.

Required TABLE:-
-------------------
CREATE TABLE PGBAR_PGG.DBSIZES(TIME_STMP TIMESTAMP DEFAULT NOW(),DATNAME VARCHAR(100) NOT NULL,DATSIZE BIGINT NOT NULL,DBGROWTH VARCHAR(100),DBGROWTHPERCENT REAL);

Required Function For Trigger:-
---------------------------------
CREATE OR REPLACE FUNCTION PGBAR_PGG.FEED_DB_GROWTH()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $FUNCTION$
DECLARE
CHECK_DB_PRESENCE INT;
PREV_DB_SIZE BIGINT;
BEGIN
SELECT COUNT(*) INTO CHECK_DB_PRESENCE FROM PGBAR_PGG.DBSIZES WHERE LOWER(DATNAME)=NEW.DATNAME;
IF(CHECK_DB_PRESENCE=0) THEN
RETURN NEW;
ELSE
SELECT DATSIZE INTO PREV_DB_SIZE FROM PGBAR_PGG.DBSIZES WHERE (CTID,DATNAME) IN (SELECT MAX(CTID),DATNAME FROM PGBAR_PGG.DBSIZES GROUP BY DATNAME HAVING DATNAME=NEW.DATNAME);
IF(PREV_DB_SIZE!=NEW.DATSIZE) THEN
SELECT PG_SIZE_PRETTY(NEW.DATSIZE::BIGINT-PREV_DB_SIZE) INTO NEW.DBGROWTH;
SELECT ((NEW.DATSIZE::REAL-PREV_DB_SIZE::REAL)/PREV_DB_SIZE::REAL*100) INTO NEW.DBGROWTHPERCENT;
RETURN NEW;
END IF;
END IF;
RETURN NULL;
END;
$FUNCTION$;

Required Trigger:-
-------------------
CREATE TRIGGER DB_SIZE_FEED_TRIG BEFORE INSERT ON pgbar_pgg.DBSIZES FOR EACH ROW EXECUTE PROCEDURE pgbar_pgg.FEED_DB_GROWTH();

Required Stmt in Cronjob:-
---------------------------
insert into dbsizes(datname,datsize) select datname,pg_database_size(datname) from pg_databases;

Required View for getting required Result:-
-------------------------------------------
create view dbsizes_report
as
select time_stmp::date as "Date",pg_size_pretty(datsize),dbgrowth,dbgrowthpercent
from
dbsizes
where current_date=time_stmp::date;

The table dbsizes provides you the database growth size and percentage growth as well depends on the time interval.