What is automating-database-backups?

This Claude skill automates the end-to-end database backup lifecycle, providing robust script generation for PostgreSQL, MySQL, MongoDB, and SQLite. It focuses on data integrity and security by integrating GPG/OpenSSL encryption, zstd/gzip compression, and automated cron scheduling. Designed for developers and SREs, it streamlines disaster recovery planning by generating not only backup scripts but also retention policies and step-by-step restoration procedures to ensure business continuity.

When should I use automating-database-backups?

automating-database-backups is useful in the following scenarios: • Automate daily encrypted backups for production PostgreSQL or MySQL databases to secure sensitive data against loss or corruption. • Implement a multi-tier retention policy (e.g., daily, weekly, monthly) to automatically manage disk space and comply with data archiving regulations. • Configure scheduled MongoDB dumps with high-ratio compression to optimize storage costs and reduce backup transfer windows. • Generate comprehensive disaster recovery runbooks that include automated restore scripts and point-in-time recovery procedures for SQLite or SQL databases. • Set up monitoring and error handling for database maintenance tasks to receive alerts on backup failures or insufficient disk space.

automating-database-backups – AI Agent Skills

name	automating-database-backups
description	\|
- Bash(pg_dump	, mysqldump:, mongodump:, cron:)
version	1.0.0
license	MIT

Prerequisites

Before using this skill, ensure:

Database credentials with backup permissions (SELECT on all tables)
Sufficient disk space for backup files (estimate 2-3x database size with compression)
Cron or task scheduler access for automated scheduling
Backup destination storage (local disk, NFS, S3, GCS, Azure Blob)
Encryption tools installed (gpg, openssl) for secure backups
Test database available for restore validation

Instructions

Step 1: Assess Backup Requirements

Identify database type (PostgreSQL, MySQL, MongoDB, SQLite)
Determine backup frequency (hourly, daily, weekly, monthly)
Define retention policy (how long to keep backups)
Calculate expected backup size and storage needs
Document RTO (Recovery Time Objective) and RPO (Recovery Point Objective)

Step 2: Design Backup Strategy

Choose backup type: full, incremental, or differential
Select backup destination (local, network storage, cloud)
Plan backup scheduling to avoid peak usage times
Define backup naming convention with timestamps
Determine compression and encryption requirements

Step 3: Generate Backup Scripts

Create database-specific backup command (pg_dump, mysqldump, mongodump)
Add compression using gzip or zstd for storage efficiency
Implement encryption using gpg or openssl for security
Add error handling and logging to backup script
Include backup verification (checksum, test restore)

Step 4: Configure Backup Schedule

Create cron job entry for automated execution
Set appropriate schedule based on backup frequency
Configure environment variables for credentials
Set up log rotation for backup logs
Test manual execution before enabling automation

Step 5: Implement Retention Policy

Create cleanup script to remove old backups
Implement tiered retention (daily 7 days, weekly 4 weeks, monthly 12 months)
Schedule retention cleanup after backup completion
Add safeguards to prevent accidental deletion of recent backups
Log all backup deletions for audit trail

Step 6: Create Restore Procedures

Document step-by-step restore process
Create restore scripts for each database type
Include procedures for point-in-time recovery
Test restore process on non-production environment
Document restore time estimates and validation steps

Output

This skill produces:

Backup Scripts: Shell scripts for database dumps with compression and encryption

Cron Configurations: Crontab entries for automated backup scheduling

Retention Scripts: Automated cleanup scripts implementing retention policies

Restore Procedures: Step-by-step documentation and scripts for database restoration

Monitoring Configuration: Log file locations and success/failure notification setup

Error Handling

Backup Failures:

Check database connectivity and credentials
Verify sufficient disk space for backup files
Review database logs for lock or permission issues
Implement retry logic with exponential backoff
Send alerts on backup failures

Insufficient Disk Space:

Monitor disk usage before backup execution
Implement pre-backup cleanup of old backups
Use incremental backups to reduce space requirements
Compress backups more aggressively
Move backups to remote storage immediately after creation

Encryption Errors:

Verify encryption tools (gpg, openssl) are installed
Check encryption key availability and permissions
Test encryption/decryption process manually
Document key management procedures
Store encryption keys securely separate from backups

Schedule Conflicts:

Ensure only one backup runs at a time (use lock files)
Adjust backup schedule to avoid peak database usage
Implement backup queuing for multiple databases
Monitor backup duration and adjust schedule if needed
Alert if backup duration exceeds acceptable window

Resources

Backup Script Templates:

PostgreSQL: {baseDir}/templates/backup-scripts/postgresql-backup.sh
MySQL: {baseDir}/templates/backup-scripts/mysql-backup.sh
MongoDB: {baseDir}/templates/backup-scripts/mongodb-backup.sh
SQLite: {baseDir}/templates/backup-scripts/sqlite-backup.sh

Restore Procedures: {baseDir}/docs/restore-procedures/

Point-in-time recovery
Full database restore
Selective table restore
Cross-server migration

Retention Policy Templates: {baseDir}/templates/retention-policies.yaml Cron Job Examples: {baseDir}/examples/crontab-entries.txt Monitoring Scripts: {baseDir}/scripts/backup-monitoring.sh

automating-database-backups

When & Why to Use This Skill

Use Cases