cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Custom Alert - Collect Database Size - Postgres

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

Version history
Revision #:
3 of 3
Last update:
‎08-07-2020 03:40 PM
Updated by: