Database status alert
Job details
| Name: | Database status alert |
| Platform: | Postgres |
| Category: | Availability |
| Description: | Collects database server uptime statistics. |
| 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.2 |
| Default schedule: | 10m |
| Requires engine install: | No |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘postgres’]/.[newer_than_ninetwo = ‘1′] |
Parameters
| Name | Default value | Description |
|---|---|---|
| ignore_databases | 2 | The list of databases to be ignored (separated by comma). |
| 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 PostgreSQL databases within an instance, particularly determining if they can allow connections or not.
- Why: This job is vital to ensure database availability and access. If certain databases cannot allow connections, it might indicate downtime or configuration issues that could affect business operations and data accessibility.
- Manual checking: You can manually check this in the PostgreSQL database by issuing SQL commands to query the datallowconn status of databases:
bc. SELECT datname FROM pg_database WHERE datistemplate = false AND datallowconn != true;
Core Functionality and Logic
- The job fetches the database names from the “pg_database” system catalog that are not templates and do not allow connections.
- It compares the fetched database list with a user-defined list of databases to be ignored from monitoring.
- Sets the job status to ALARM if any non-ignored database does not allow connections and lists these databases in the report. The message and status change based on whether any databases are found to be offline and whether they are ignored.
Tables and Outputs
| Column Name | Data Type |
|---|---|
| Details | string |
| Database | string |
Alert Logic
- If no databases are offline, status is set to OK with a message “No databases are offline”.
- If one database is offline, an ALARM is raised mentioning which specific database is affected.
- If multiple databases are offline, an ALARM is raised indicating how many databases are offline.
- If databases are flagged but are listed in the ignore list, these databases are noted, but do not influence the alarm status.
Scheduling
- Job execution is scheduled to occur every 10 minutes as indicated by the “default-schedule” value.
- The monitoring logic allows for quick detection of issues, aiming to minimize downtime and disruption.
Integration and Compatibility
- This job is specifically designed for PostgreSQL instances (verifying version newer than 9.2 due to the use of certain PostgreSQL-specific system catalogs and features).
- Installable on PostgreSQL instances that have no additional engine and are specified as type ‘postgres’ within the compatible environments.
Error Handling
- Encoded JavaScript logic includes error catching mechanisms. If an error occurs during the monitoring process, it catches it and sets the job status to WARNING, also providing the error message encountered.
Deployment and Usage
- This job comes with an express-install option, indicating that it can be quickly and easily deployed to a PostgreSQL environment via dbWatch Control Center.
- Given the transactional nature of database operations and the criticality of availability, having this job actively monitoring allows database administrators (DBAs) to swiftly address connectivity issues before they impact end-users or critical operations.