Database status
Job details
| Name: | Database status |
| Platform: | Postgres |
| Category: | Availability |
| Description: | This job monitors the availability status of databases in a PostgreSQL instance by checking the datallowconn column in the pg_database system catalog. |
| Long description: | This job monitors the availability status of databases in a PostgreSQL instance by checking the datallowconn column in the pg_database system catalog. |
| Version: | 1.0 |
| Default schedule: | 7,17,27,37,47,57 * * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘postgres’]/.[hasengine=‘YES’] |
Parameters
| Name | Default value | Description |
|---|---|---|
| ignore databases | List of databases (separated by comma) which will be ignored. | |
| ignore OLD status | NO | Ignore databases if status (datallowconn = false) have been registered for a period of time controlled by parameter “ignore status time threshold”. |
| ignore status time threshold | 96 | If the parameter “ignore OLD status” is set to “YES“ and the database has been not accessible for a period of time that exceeds the value of this parameter (in hours), then the database will be ignored. |
| return status | 2 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when a database is not accessible. |
Job Summary
- Purpose: The purpose of this job is to monitor the availability status of databases in a PostgreSQL instance, specifically by checking the “datallowconn” column in the pg_database system catalog.
- Why: This job is important because it helps detect when databases become unexpectedly unavailable (not allowing connections). Detecting these issues early can prompt timely interventions, thus reducing the potential impact on users and dependent applications.
- Manual checking: To manually check the database status, use the SQL command:
bc. SELECT datname, datallowconn FROM pg_database;
Detailed Description
This monitoring job examines if databases in a PostgreSQL instance allow connections by inspecting the “datallowconn” column. If a database has “datallowconn” set to ‘false’, it implies that the database is not currently accepting connections. This status triggers a warning or an alarm based on the job’s configured thresholds.
Job Configuration
The job runs at a default schedule: every 7, 17, 27, 37, 47, and 57 minutes of each hour.
- Acknowledgements: This attribute is enabled, indicating that the monitoring system acknowledges the job statuses.
Job Dependencies
Several objects and procedures are crucial for this job:
- “dbw_database_status(integer)”: A procedural dependency used for executing the task.
- “dbw_database_status_histr”: A table used to log historical statuses.
- “dbw_database_status_ignore_list”: A list of databases that are ignored during status checks. This list is maintained in a table format.
- Cleanup is enabled on failure, ensuring that any partial modifications made during job execution do not persist in the event of an error.
Database Status Presentation
The system creates a report template that includes comprehensive details:
- A query that fetches database name, template status, connection allowance, and default collation from the pg_database catalog:
- “SELECT datname, datistemplate, datallowconn, datcollate FROM pg_database ORDER BY 1 ASC“
- Another query specific to tracking offline periods:
- “SELECT db_id, db_name, period, first_offline_detection, last_offline_detection, times_checked, db_status FROM dbw_database_status_histr ORDER BY last_offline_detection DESC LIMIT 10″
- This provides tracking over time for databases that transition in and out of offline status, detailing the first and last detections and how many times the offline status was checked.
Additional Implementation Considerations
The job implementation involves setting up necessary tables and indices:
- Creating “dbw_database_status_ignore_list” and “dbw_database_status_histr” tables.
- Setting up relevant indices to improve query performance on these tables.
This structured configuration and detailed monitoring logic are designed to help maintain high availability and prompt status awareness of databases within a PostgreSQL environment.