OpenSkillIndex← back to search
claude-code

Skillshub automating-database-backups

install
source · Clone the upstream repo
git clone https://github.com/ComeOnOliver/skillshub
Claude Code · Install into ~/.claude/skills/
T=$(mktemp -d) && git clone --depth=1 https://github.com/ComeOnOliver/skillshub "$T" && mkdir -p ~/.claude/skills && cp -r "$T/skills/jeremylongshore/claude-code-plugins-plus-skills/automating-database-backups" ~/.claude/skills/comeonoliver-skillshub-automating-database-backups && rm -rf "$T"
manifest: skills/jeremylongshore/claude-code-plugins-plus-skills/automating-database-backups/SKILL.md
tags
#database#postgresql#mysql#mongodb
source content

Database Backup Automation

Generate production-ready backup scripts for PostgreSQL, MySQL, MongoDB, and SQLite with compression, encryption, scheduling, and retention policies.

Quick Start

PostgreSQL Backup

#!/bin/bash
set -euo pipefail
BACKUP_DIR="/var/backups/postgresql"
DB_NAME="mydb"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"

pg_dump -h localhost -U postgres -d "$DB_NAME" \
  --format=custom \
  --compress=9 \
  --file="$BACKUP_FILE"

# Encrypt with GPG (optional)
gpg --symmetric --cipher-algo AES256 --batch --passphrase-file /etc/backup.key "$BACKUP_FILE"
rm "$BACKUP_FILE"

MySQL Backup

#!/bin/bash
BACKUP_DIR="/var/backups/mysql"
DB_NAME="mydb"
DATE=$(date +%Y%m%d_%H%M%S)

mysqldump -h localhost -u root -p"${MYSQL_PASSWORD}" \
  --single-transaction \
  --routines \
  --triggers \
  "$DB_NAME" | gzip > "${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"

MongoDB Backup

#!/bin/bash
mongodump --uri="mongodb://localhost:27017" \  # 27017: MongoDB port
  --db=mydb \
  --out=/var/backups/mongodb/$(date +%Y%m%d_%H%M%S) \
  --gzip

Instructions

Step 1: Gather Requirements

Ask the user for:

  • Database type (PostgreSQL, MySQL, MongoDB, SQLite)
  • Database connection details (host, port, database name)
  • Backup schedule (cron expression or frequency)
  • Retention policy (days to keep)
  • Encryption requirement (yes/no)
  • Backup destination (local path, S3, GCS)

Step 2: Generate Backup Script

Use 

scripts/backup_script_generator.py
to create a customized backup script:

python3 ${CLAUDE_SKILL_DIR}/scripts/backup_script_generator.py \
  --db-type postgresql \
  --database mydb \
  --output /opt/backup-scripts/mydb-backup.sh \
  --compression gzip \
  --encryption gpg

Step 3: Schedule with Cron

Use 

scripts/backup_scheduler.py
to create cron entries:

python3 ${CLAUDE_SKILL_DIR}/scripts/backup_scheduler.py \
  --script /opt/backup-scripts/mydb-backup.sh \
  --schedule "0 2 * * *" \
  --user postgres

Step 4: Validate Backup

After backup completes, validate integrity:

python3 ${CLAUDE_SKILL_DIR}/scripts/backup_validator.py \
  --backup-file /var/backups/postgresql/mydb_20250115.sql.gz \
  --db-type postgresql

Step 5: Generate Restore Procedure

Create matching restore script:

python3 ${CLAUDE_SKILL_DIR}/scripts/restore_script_generator.py \
  --db-type postgresql \
  --database mydb \
  --output /opt/backup-scripts/mydb-restore.sh

Cron Schedule Reference

ScheduleCron ExpressionDescription
Daily 2 AM
0 2 * * *
Low-traffic window
Every 6 hours
0 */6 * * *
Frequent backups
Weekly Sunday
0 2 * * 0
Weekly full backup
Monthly 1st
0 2 1 * *
Monthly archive

Retention Policy Example

# Keep daily backups for 7 days
# Keep weekly backups for 4 weeks
# Keep monthly backups for 12 months

find /var/backups -name "*.gz" -mtime +7 -delete  # Daily cleanup
find /var/backups/weekly -mtime +28 -delete       # Weekly cleanup
find /var/backups/monthly -mtime +365 -delete     # 365: Monthly cleanup

Output

  • Backup Scripts: Database-specific shell scripts with compression and encryption
  • Cron Entries: Ready-to-install crontab configurations
  • Restore Scripts: Matching restore procedures for each backup type
  • Validation Reports: Integrity check results for backup files

Error Handling

ErrorCauseSolution
Connection refusedDB not runningCheck service status: 
systemctl status postgresql
Permission deniedWrong credentialsVerify user has backup privileges
Disk fullNo spaceCheck space: 
df -h
, clean old backups
Lock timeoutActive transactionsUse 
--single-transaction
for MySQL

Resources

  • ${CLAUDE_SKILL_DIR}/references/postgresql_backup_restore.md
    - PostgreSQL backup guide
  • ${CLAUDE_SKILL_DIR}/references/mysql_backup_restore.md
    - MySQL backup guide
  • ${CLAUDE_SKILL_DIR}/references/mongodb_backup_restore.md
    - MongoDB backup guide
  • ${CLAUDE_SKILL_DIR}/references/sqlite_backup_restore.md
    - SQLite backup guide
  • ${CLAUDE_SKILL_DIR}/references/backup_best_practices.md
    - Security and storage best practices
  • ${CLAUDE_SKILL_DIR}/references/cron_syntax.md
    - Cron scheduling reference

Overview

Automate database backup processes with scheduling, compression, and encryption.

Prerequisites

  • Access to the PostgreSQL environment or API
  • Required CLI tools installed and authenticated
  • Familiarity with PostgreSQL concepts and terminology

Examples

Basic usage: Apply automating database backups to a standard project setup with default configuration options.

Advanced scenario: Customize automating database backups for production environments with multiple constraints and team-specific requirements.