DBMS uptime

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and record the uptime statistics of PostgreSQL database instances that are newer than version 9.4 and have the database engine enabled. It aims to help in effective monitoring and management of database uptime by keeping detailed logs of the start times and active periods of the database service.
• Why: This job is important as it helps in analyzing the stability and availability of PostgreSQL databases by tracking their uptime. Regular monitoring of uptime can identify unexpected downtimes, assist in minimizing disruption, and support optimal database performance. It enables proactive maintenance and can help to avoid potential data loss or service interruptions.
• Manual checking: You can manually check database uptime statistics with SQL commands like the ones used in the implementation to create and manage the necessary tables and views:

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:

• Tables and Views:
  • dbw_dbms_uptime_histr – Historical data of uptime events.
  • dbw_dbms_uptime_data – Latest data entries for uptime events.
  • dbv_dbms_uptime_histr to dbv_dbms_uptime_stat_h – Various views that handle uptime statistics, monitoring duration, database availability, and detailed period breakdowns.

• Cleanup on Fail:
  • All dependencies are set to cleanup on failure, ensuring that the database remains clean without unnecessary or corrupted data after an error occurs.

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:

• Uptime data is represented in SQL views which calculate various statistics such as total uptime, periods of availability, downtime, and this is sanitized and prepared for reporting through dbwatch report templates.
• The reports provide comprehensive visibility into uptime:
  • Time spans in days, hours, and minutes spent in uptime and downtime.
  • A percentage calculation reflecting the overall uptime.
  • Detailed records of each period, including start and end times, which can be used to analyze patterns in database activity.

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:

• Ensure databases meet SLAs for uptime.
• Quickly respond to and investigate the causes of downtimes.
• Assess the stability of the database over time.

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