Backup check – pg_dump
Job details
| Name: | Backup check – pg_dump |
| Platform: | Postgres |
| Category: | Availability |
| Description: | Checks that there exists an up to date backup file. |
| Long description: | Checks that there exists an up to date backup file. |
| Version: | 1.6 |
| Default schedule: | 10 06 * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘postgres’]/.[hasengine=‘YES’ & is_awsrds = ‘0′ & newer_than_ninefour = 1] |
Parameters
| Name | Default value | Description |
|---|---|---|
| backup catalog | CHANGE_THIS | The directory containing the backup files (relative path). |
| alarm period | 96 | Will give an alarm if the number of hours since the last backup exceeds this limit. |
| warning period | 48 | Will give a warning if the number of hours since the last backup exceeds this limit. |
Job Summary
- Purpose: The purpose of this job is to monitor and verify the presence and integrity of PostgreSQL database backups.
- Why: This job is important because it helps ensure that there exists an up-to-date and valid backup for PostgreSQL databases. Reliable backups are crucial for disaster recovery and data integrity. If the backups are not up-to-date or are corrupted, it can lead to significant data loss and operational downtime.
- Manual checking: You can manually check this in the database by issuing this SQL command:
select bck_filename as "Backup file", backup_date as "Modified date", status as "Status" from dbw_backup_check_data order by backup_date desc;Monitoring Details
This monitoring job is designed specifically for PostgreSQL databases that are instances running with a version newer than 9.4, excluding AWS RDS instances. The job uses PostgreSQL features like ‘pg_ls_dir’ and ‘pg_stat_file’ for inspecting the backup directory.
Implementation Details
- The job uses a function ‘dbw_backup_check’ that performs the following operations:
- Updates the backup check directory based on a specified parameter.
- Lists all files in the specified backup directory.
- Checks each file identified to have the “.sql” backup file extension.
- Captures file details like modification time and file size.
- Validates the integrity of each backup file by checking the file’s ending content against expected strings.
- Captures the modification time of the most recent backup file and checks if it’s older than specified alarm and warning thresholds.
- The function will alter the status of the check to either warning or alarm based on the age of the latest backup and the integrity check results.
Dependency Details
- The job has dependencies on internal procedures and tables used for this monitoring:
- ‘dbw_backup_check(integer)’
- ‘dbw_backup_check_data’ table
- ‘dbw_backup_check_dir’ table
- If the job fails, the cleanup includes dropping the dependent procedures and tables.
Database Tables and Procedures
| Table/Procedure Name | Description |
|---|---|
| dbw_backup_check_data | Holds details of each backup file like filename, backup timestamp, and file size. |
| dbw_backup_check_dir | Stores the directory path where backup files are located. |
| dbw_backup_check | Main procedure that reads file details, validates backup integrity, and updates the system based on findings. |
Reporting Details
The reporting generated by this job provides:
- A list of all backup files along with their modification dates and integrity status.
- The specific backup location being monitored.
This report is essential for database administrators to ensure backups are occurring as scheduled and are stored correctly.