pgBackRest DIFF backup status

Job details

Name:	pgBackRest DIFF backup status
Platform:	Postgres
Category:	Availability
Description:	Monitors the status and recency of PostgreSQL Differential 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 Differential backups exist and meet expectations regarding freshness and retention, helping to detect potential backup issues early.
Version:	1.0
Default schedule:	20 6 * *
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	48	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	24	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: Monitors and reports on the status and timing of PostgreSQL Differential backups performed using pgBackRest.
Why: Ensures backups are performed within specified thresholds for freshness and retention which are crucial for data recovery and compliance with backup policies.
Manual Checking: Check this manually in the PostgreSQL database by querying the backup metadata stored by pgBackRest using the following SQL commands:

SELECT backup_label, timestamp_start, timestamp_stop, db_size FROM dbw_pgbackrest_diff_backup_status_data WHERE backup_type = 'diff' ORDER BY timestamp_start DESC;

Dependencies

Checks and manages the data integrity and freshness based on three main components:
- Main function
- Status Table
- History Table

Implementation Details

The implementation initiates by creating necessary tables and functions to record backup data and metadata:
- dbw_pgbackrest_diff_backup_status_data: Holds detailed data of each backup.
- dbw_pgbackrest_diff_backup_status_histr: Maintains history of backups for a specified duration.
A PL/pgSQL function is used to perform the actual check and records the backup status, examining details such as backup start and end timestamps, and size of each backup.

Function Action	Description
Data Insertion	Inserts backup metadata into monitoring tables using pgBackRest output.
Backup Verification	Verifies if the most recent backups meet defined thresholds for warning and alarm conditions.
History Maintenance	Cleans up outdated records based on history thresholds to ensure relevant data persistence.

Output Data Examples (Excerpt from Reporting Template)

Data is formatted into reportable content for clear visualization:
- “PostgreSQL Differential Backup Check” – Lists latest Differential backup statistics including label, start and end times, size, and insertion time.
- “Backup Size History” – Visualizes the size trend of backups over time, aiding in understanding backup growth and patterns.

Backup Attribute	Details
Label	Unique identifier of each backup
Backup start	Start timestamp of the backup
Backup end	End timestamp of the backup
Size (MB)	The size of the backup in megabytes
Insert time	When the backup record was inserted into the database for monitoring

This structured monitoring and visual presentation allow for proactive management of PostgreSQL backups using pgBackRest, ensuring robust data protection strategies.