pgBackRest INCR backup status

Job details

Name:	pgBackRest INCR backup status
Platform:	Postgres
Category:	Availability
Description:	Monitors the status and recency of PostgreSQL Incremental backups performed with pgBackRest.
Long description:	Checks the status of pgBackRest backups for PostgreSQL instances by analyzing available backup metadata. The job ensures that recent Incremental backups exist and meet expectations regarding freshness and retention, helping to detect potential backup issues early.
Version:	1.0
Default schedule:	15 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘postgres’]/.[hasengine=‘YES’ & is_awsrds = ‘0′ & maj_version > ‘11′ ]

Parameters

Name	Default value	Description
alarm threshold	8	Specifies the maximum number of hours allowed since the last backup before an alarm is triggered. This acts as a critical threshold indicating that backups are significantly overdue and may require immediate attention.
warning threshold	2	Specifies the maximum number of hours allowed since the last backup before a warning is triggered. If the time since the last backup exceeds this threshold, the job will return a warning status.
history threshold	120	Defines the number of days that backup statistics should be retained in the history table. This ensures long-term tracking of backup size and timing trends for auditing or analysis purposes.
pgbackrest output table	dbwatch_cc.pgbackrest_backup_log	Specifies the name of the table (in schema_name.table_name format) where the output from the pgbackrest info command will be stored. This allows the job to persist raw backup metadata for further processing. This table does not need to reside in the dbwatch schema but must be accessible within the dbWatch monitored database.

Job Summary

Purpose: The purpose of this job is to monitor the status and recency of PostgreSQL incremental backups performed with pgBackRest. It ensures backup operations are completed successfully and are up-to-date.
Why: This job is crucial for maintaining the integrity and availability of database backups. It prevents potential data loss by alerting database administrators to issues with the incremental backup process promptly, ensuring that backups are neither outdated nor missing.
Manual checking: You can manually check the status of incremental backups by querying the database with the following SQL commands:
bc.. SELECT backup_label “Label”,
timestamp_start “Backup Start”, timestamp_stop “Backup End”, db_size “Database Size”, insert_time “Insert Time” FROM dbwatch_cc.pgbackrest_backup_log WHERE backup_type = ‘incr’ ORDER BY timestamp_start DESC;

Description

Handles the monitoring of PostgreSQL incremental backup status by analyzing the metadata provided by pgBackRest to verify that recent backups exist and adhere to defined freshness and retention policies.

Implementation Details

The job involves creating and managing several database entities, including:
- A function that performs the backup status check (dbw_pgbackrest_incr_backup_status).
- Data tables (dbw_pgbackrest_incr_backup_status_data, dbw_pgbackrest_incr_backup_status_histr) storing incremental backup details and historical data.
Alerts are set based on two thresholds – warning and alarm – which are triggered based on the number of hours since the last backup.
The history of backup details is maintained for a specified number of days for long-term analysis and audit purposes.

Alert Logic

The function calculates the time elapsed since the last incremental backup.
If the time exceeds the warning threshold, a warning is triggered.
If the time exceeds the alarm threshold, an alarm is triggered, indicating that the backup is significantly overdue.

Dependencies

The job relies on previously established dependencies, which include the main function and status tables. These are crucial for storing and processing incremental backup data.
Cleaning up procedures and tables if execution fails is managed to ensure no residual data impacts future checks.

Reporting

Reports generated include details of incremental backup statistics, presenting key metrics such as backup label, start and end times, and the size of the backups.
Historical data visualizations and tables are used to track backup health over time, providing insights into trends and changes in backup sizes.

Presentation Title	Data Presented
Incremental Backup Statistics	Details of recent incremental backups including times and sizes
Backup Size History	Historical chart showing sizes of past backups, allowing trend analysis

Scheduled Execution

This job is configured to run periodically (every 15 minutes by default) to ensure up-to-date backup status and prompt detection of potential issues.

This monitoring job thus plays a critical role in ensuring the operational continuity and data safety for PostgreSQL databases using pgBackRest for the incremental backups.