pgBackRest FULL backup status
Job details
| Name: | pgBackRest FULL backup status |
| Platform: | Postgres |
| Category: | Availability |
| Description: | Monitors the status and recency of PostgreSQL FULL 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 FULL backups exist and meet expectations regarding freshness and retention, helping to detect potential backup issues early. |
| Version: | 1.0 |
| Default schedule: | 10 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: The purpose of this job is to monitor the status and recency of PostgreSQL FULL backups performed with pgBackRest, ensuring they are performed regularly and meet the set freshness requirements.
- Why: This job is critical as it helps in early detection of backup issues, ensuring data integrity and availability by checking that recent FULL backups exist and are correctly logged according to the stipulated retention policies. If the backup freshness thresholds are exceeded, this may pose serious risks in data recovery abilities during disaster recovery scenarios.
- Manual checking: You can check the status of the backups manually by querying the backup tables created by pgBackRest:
bc. SELECT * FROM dbwatch_cc.pgbackrest_backup_log ORDER BY timestamp_start DESC;
Implementation Details
This job consists of several SQL statements and PL/pgSQL functions that perform a series of tasks:
- Creating tables for storing backup data and backup history.
- Monitoring the freshness of the backups.
- Monitoring the size details of the backups.
- Deleting old backup entries based on the history threshold setting.
- Reporting back the status of the backup.
Key SQL code snippets include:
bc. CREATE TABLE dbw_pgbackrest_full_backup_status_data (id SERIAL PRIMARY KEY, stanza TEXT NOT NULL, backup_type TEXT NOT NULL, backup_label TEXT NOT NULL, timestamp_start TIMESTAMPTZ NOT NULL, timestamp_stop TIMESTAMPTZ NOT NULL, wal_start TEXT NOT NULL, wal_stop TEXT NOT NULL, db_size TEXT NOT NULL, db_backup_size TEXT NOT NULL, repo_backup_set_size TEXT NOT NULL, repo_backup_size TEXT NOT NULL, insert_time TIMESTAMPTZ DEFAULT now());
CREATE OR REPLACE FUNCTION dbw_pgbackrest_full_backup_status (taskId int) RETURNS void AS -- Function details omitted for brevity.Backup Job Dependencies
This monitoring job depends on a set of objects and tables. The dependencies ensure that data regarding backup operations is managed correctly, including the creation and cleanup of necessary database objects:
|_. Object |_. Object Type |_. Cleanup on Fail |_. Description |
| dbw_pgbackrest_full_backup_status | Function | Yes | Main function to check backup status |
| dbw_pgbackrest_full_backup_status_data | Table | Yes | Stores current backup data |
| dbw_pgbackrest_full_backup_status_histr | Table | Yes | Stores historical backup size data |
Cleanup code on job failure includes:
bc. DROP PROCEDURE dbw_pgbackrest_full_backup_status(integer);
bc. DROP TABLE dbw_pgbackrest_full_backup_status_data;
Reporting
The job provides reports that help in evaluating the backup status:
- A query for showing the details of the full backups:
bc. SELECT backup_label “Label”, timestamp_start “Backup start”, timestamp_stop “Backup end”, db_size “Size (MB)”, insert_time “Insert time” FROM dbw_pgbackrest_full_backup_status_data WHERE backup_type = ‘full’ ORDER BY timestamp_start DESC - A history trend showing backup sizes over the time to monitor growth and changes:
bc. SELECT timestamp_start as “Backup start”, timestamp_stop as “Backup end”, db_size as “Backup size (MB)” FROM dbw_pgbackrest_full_backup_status_histr ORDER BY timestamp_start
The job also has a visual representation chart in the dbWatch report that plots backup sizes against backup start times to easily visualize backup trends.