DESCRIPTION
This alert will collect the size of each database in a Postgres Cluster. This example alert is used only for collecting data and would never alert anyone. This data is provided as input for another custom alert named "Postgres Database Percent Growth". Also shown below is a query that will allow you to report on this data as collected in the DPA alert tables.
ALERT DEFINITION
To create this alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return making it look similar to this example.
Alert Name: Collect Database Size - Postgres
Execution Interval: 1 Day
Notification Text: <None required>
Available Instances: <choose any Postgres instances>
SQL Statement:
SELECT pg_database.datname || ' Size='|| pg_size_pretty(pg_database_size(pg_database.datname)), pg_database_size(pg_database.datname) FROM pg_database ORDER by 2 DESC;
Execute Against: Monitored Database
Units: Bytes
High Threshold: Min: -2, Max: -1
REPORTING FROM THIS DATA
The data this alert collects is stored in the DPA alert tables and can be retrieved running a query similar to this.
Note: this query retrieves data collected by this alert for the last 14 days. If the alert name was changed from the default listed above, change the "where a.alertname" part of this query as well.
select ah.dbname instance_name, ah.actiondate timestamp, ahr.parametername database_name, convert(float, replace(ahr.levelvalue,',','')) db_size_bytes
from con_alert a
inner join con_alert_history ah on ah.alertid=a.id
inner join con_alert_history_results ahr on ahr.historyid=ah.historyid
where a.alertname='Collect Database Size - Postgres'
and ah.actiondate >= current_timestamp - 14.0
order by ah.dbname, ah.actiondate, ahr.parametername