PostgreSQL Alert Rule Configuration

This document provides detailed instructions on configuring alert rules for PostgreSQL data sources in Monitors. Monitors supports using standard SQL syntax to query PostgreSQL and trigger alerts based on query results.

Core concepts

Query language: Uses PostgreSQL SQL syntax.

Field handling: The alert engine automatically converts all field names in query results to lowercase. When configuring value fields and label fields, always use lowercase letters.

Time handling: It's recommended to use functions like NOW(), CURRENT_TIMESTAMP in SQL for time range filtering.

1. Threshold mode

This mode is suitable for scenarios requiring threshold comparisons on aggregated values.

Configuration

Query: Write a SQL aggregation query that returns numeric columns and (optional) label columns.

Example: Count error logs per service in the last 5 minutes.

Field mapping:

Label fields: Fields used to distinguish different alert objects. In the example above, this is service_name. This field can be left empty, and Monitors will automatically treat all fields except value fields as label fields.

Value fields: Numeric fields used for threshold evaluation. In the example above, this is error_cnt.

Threshold conditions:

Use $A.field_name to reference values.

Example: Critical: $A.error_cnt > 50, Warning: $A.error_cnt > 10.

How it works

The engine executes the SQL query and retrieves the result set. It groups data by label fields, then extracts value field values for comparison against threshold expressions.

Recovery logic

Auto recovery: When the latest SQL query result shows that a data group's value no longer meets any alert threshold, a recovery event is automatically generated.

Specific recovery conditions: Configure additional recovery expressions (e.g., $A.error_cnt < 5).

Recovery query:

Supports configuring an independent SQL statement for recovery evaluation.

Supports ${label_name} variable substitution.

Example:

The engine replaces ${network_host} and ${interface} with actual values before executing the query. If data is found, recovery is confirmed.

2. Data exists mode

This mode is suitable for scenarios where filtering logic is written directly in SQL.

Configuration

Query: Use a HAVING clause in SQL to directly filter out anomalous data.

Example: Directly query services with more than 50 errors.

Evaluation rule: An alert is triggered as soon as the SQL query returns data.

Pros and cons

Pros: Leverages PostgreSQL database computing power for filtering, reducing network transmission.

Cons: Cannot distinguish between multiple severity levels.

Recovery logic

Data disappearance means recovery: Recovery is confirmed when the SQL query result is empty.

Recovery query: Supports configuring additional query statements to assist in determining recovery status.

3. No data mode

This mode monitors scenarios where data is expected but actually missing.

Configuration

Query: Write a SQL query that should continuously return data.

Example: Query heartbeat reports from all probes.

Evaluation rule: If a probe_id appeared in previous cycles but cannot be found in the current and N consecutive cycles, a "no data" alert is triggered.

4. Best practices and considerations

Index optimization

Always include time range filtering in the WHERE clause and ensure there's an index on the time field. Otherwise, it may cause full table scans and affect database performance.

Recommended: log_time > NOW() - INTERVAL '5 minutes'.

Field case sensitivity

Monitors engine converts column names returned by PostgreSQL to lowercase.

Configuration: Always use lowercase letters when filling in label fields and value fields.

PostgreSQL

Core concepts#

1. Threshold mode#

Configuration#

How it works#

Recovery logic#

2. Data exists mode#

Configuration#

Pros and cons#

Recovery logic#

3. No data mode#

Configuration#

4. Best practices and considerations#

Index optimization#

Field case sensitivity#

Core concepts

1. Threshold mode

Configuration

How it works

Recovery logic

2. Data exists mode

Configuration

Pros and cons

Recovery logic

3. No data mode

Configuration

4. Best practices and considerations

Index optimization

Field case sensitivity