Backup check – WAL

Job details

Parameters

Job Summary

• Purpose:
• The purpose of this job is to monitor the status of WAL (Write-Ahead Logging) files in PostgreSQL databases, specifically checking that they have been adequately backed up to prevent data loss and ensure database recovery capabilities.
• Why:

SELECT setting FROM pg_settings WHERE name = 'archive_mode';

SELECT setting FROM pg_settings WHERE name = 'archive_command';

SELECT pg_ls_dir('pg_wal/');

Job Configuration Overview

• “object”: dbw_backup_wal_check
• “description”: Checks that the WAL files have been backed up.
• “long-description”: Provides a detailed examination if WAL files are adequately backed up according to the preset conditions (parameters).
• “company”: dbwatch.no
• “group”: com.dbwatch.job
• “artifactid”: postgres_backup_wal_check
• “default-schedule”: Job is scheduled to run periodically at a frequency defined by the expression ‘19 * * *’.
• “version”: 1.5

Job Dependencies

• “dbw_backup_wal_check(integer)”: Ensures that the function itself can operate correctly, with a self-dependency for consistent execution.
• “dbw_backup_wal_check_data”: A dependency table created to hold operational data which is manipulated by the function during runtime.

Cleanup on Failure

• If the job fails, the following cleanup operations are enacted to remove created artifacts that could interfere with subsequent runs:

drop procedure dbw_backup_wal_check(integer);

drop table dbw_backup_wal_check_data;

Implementation Details

• The WAL Backup Check job functionality is implemented through a PostgreSQL stored procedure declared in PL/pgSQL. It involves:
• Checking if archive mode is enabled and if the archive command is set.

Reporting Template

• “PostgreSQL backup check”:
• This part of the job configuration specifies how the database report should be structured, including the presentation of data such as:

select bck_mode as "Archive mode", bck_command as "Command", num_wal_files as "Num WAL files" from dbw_backup_wal_check_data;

Significance of Job Installation and Maintenance

• “express-install”: The job supports express installation, indicating it can be quickly deployed with minimal configuration, reflecting its critical nature for immediate database monitoring and health checks.