DBMS uptime


Job details

Name: DBMS uptime
Platform: Postgres
Category: Availability
Description: Collects database uptime statistics.
Long description: Collects database uptime statistics.
Version: 1.4
Default schedule: * * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘postgres’]/.[hasengine=‘YES’ & newer_than_ninefour = 1]

Parameters

Name Default value Description

Job Summary

SELECT * FROM dbv_dbms_uptime_stat;

Job Dependencies

This section lists various dependencies associated with the job, such as database objects and cleanup behavior in case of failures:

Detailed Uptime Implementation

The job includes a PL/pgSQL function that manages the insertion and update of uptime records based on the database’s postmaster start time. Here is a brief breakdown of the key operations:

  1. Tracking the start time of the database using `pg_postmaster_start_time()`.
  2. Comparing stored start times to detect any restarts.
  3. Inserting new records into `dbw_dbms_uptime_data` for new periods or updating the last checked times if the database continues to run.
  4. Historical records are transferred to `dbw_dbms_uptime_histr` in case of restarts, ensuring that all session data is maintained.
  5. Various views are created to present calculated statistics such as total uptime, monitored periods, and reboots, among others.

Reporting on Uptime Statistics

Data Collection and Presentation:

Importance for Database Administrators

For administrators, this job is crucial in maintaining high availability and performance of PostgreSQL databases. By systematically logging detailed uptime data and changes in database state, administrators can:

Essentially, this monitoring job acts as both a diagnostic tool and a performance indicator for critical database infrastructure.