Log size statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and record the size statistics of the log files for Postgres database instances that aren’t hosted on AWS RDS.
• Why: Logging is a crucial component in database management to track and audit events. Monitoring the log file sizes helps manage the disk space effectively and ensures the logging system does not silently fail due to disk space exhaustion. If not monitored, large log files could fill up the disk space, causing database outages or degraded performance.
• Manual checking: To manually check the log file size and related data, you can execute SQL commands outlined in the job’s function. Here’s an example of such a command:

select pg_ls_dir('log_directory');

Job Description and Dependencies

This job is named “Log size statistics”. It primarily focuses on collecting detailed statistics about the log files’ sizes, including the total size of all logs, the size of each log file, and the count of log files. This data is stored in a designated table and updated periodically through a PostgreSQL function.

Dependencies

• The job has a self-dependency where it relies on its function for iterative execution.
• It also depends on a specific table ‘dbw_log_size_statistics_data’ for storing the results of each execution.
• There are cleanup procedures defined to drop the function and table in case the job setup fails.

Implementation Details

The job operates by:

• Checking the configuration settings to see if the logging collector is enabled.
• Navigating the log directory and extracting size data for each file found.
• Summing up the total size of all log files and tracking the most recently modified file.
• Storing the collected data into ‘dbw_log_size_statistics_data’.

The result is periodically updated, including details like the total size and number of files for snapshot reporting purposes via the PostgreSQL function `dbw_log_size_statistics`.

Reporting and Visualization

The job offers various reporting and visualization formats which can be accessed in the “dbWatch Control Center”.

Presentations Included:

• “Log file”: Displays the current log file and its size.
• “Log size statistics”: A history graph showing the size of log files over time.
• “Log file catalog”: Provides a visual representation (dual-axis chart) of the overall log file count and their aggregate sizes across different timestamps.

Table Structure

Graph Overviews

• A line chart correlating log file size with historical data points.
• A dual-axis chart showing the trend in the total size of logs against the number of log files present.

This structured reporting assists in immediate visual assessments and long-term log management strategies.