Backup job – pg_dump_all (ssh)

Job details

Name:	Backup job – pg_dump_all (ssh)
Platform:	Postgres
Category:	Maintenance
Description:	Runs a backup of all the postgres databases on an instance using pg_dumpall through an ssh connection.
Long description:	Runs a backup of all the postgres databases on an instance using pg_dumpall through an ssh connection.
Version:	0.4
Default schedule:	10 18 * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘postgres’]/.[hasengine=‘YES’ & newer_than_ninetwo = ‘1′]

Parameters

Name	Default value	Description
backup directory	CHANGE_THIS	The directory where we put the backup files (absolute path).
database backup user	postgres	Username used to log onto postgres and run backup.
pg_dumpall executable	/opt/PostgreSQL/10/bin/pg_dumpall	Full filename and options for the pg_dumpall executable (with options if you have them)
backup filename	postgres_full_backup.tar	Backup file output filename

Job Summary

Purpose: The purpose of this job is to manage the backup of all PostgreSQL databases using the pg_dumpall utility over SSH, ensuring that backups are correctly executed and managed.
Why: This job is essential to ensure that all PostgreSQL databases are consistently backed up, helping to safeguard data against corruption, loss, or other issues. Regular backups help in maintaining data integrity and operational continuity in case of system failure.
Manual Checking: You can check the status and results of the backup manually by accessing the database and executing the following SQL command:

select bck_filename as "Backup file", backup_date as "Modified date", status as "Status" from dbw_backup_job_pg_dumpall_data order by backup_date desc;

Key Features and Operations

“Backup job – pg_dump_all (ssh)” is designed specifically for PostgreSQL databases that are version 9.2 and newer.
It ensures that backups are performed through a secure SSH connection, which ensures that data is securely transferred and stored.
The job targets instances where PostgreSQL is installed, verifying that there is a specific engine running.
It employs a pre-defined backup script and maintains a log of backup status which includes details like file names, backup dates, sizes, and status codes.

Dependencies and Error Handling

The task has self-dependency and also relies on a specific data table named “dbw_backup_job_pg_dumpall_data”.
In the event of a failure, it performs cleanup by dropping relevant procedures and tables as prescribed in the task setup.
Error management is proactive, with detailed checks and balances at each step of the execution process to ensure that any configuration issues are flagged early.

Backup Job Specifics

Parameter Name	Description
Backup Directory	Absolute path for where backup files are stored.
Database Backup User	Username used for logging into PostgreSQL and performing backups.
PG Dumpall Executable	Full path and options for the pg_dumpall utility used for backups.
Backup Filename	Name of the file where the backup will be saved.

Default scheduling for the backup is set to initiate daily at 6:10 PM.

Implementation Details

A PostgreSQL function “dbw_backup_job_pgdump_all_ssh” is used to perform the actual backup.
It involves a multi-step verification of parameters to ensure that backups are not executed unless properly configured.
Post-completion or error, updates are logged for review, providing status and error codes for each attempted backup.

This monitoring job is pivotal for maintaining the consistency and reliability of database backups in PostgreSQL environments, ensuring that operational data is protected against unforeseen circumstances through systematic, secure backup procedures.