Index of every page on this site: llms.txt
All skills
jeremylongshore avatar

/automating-database-backups

@820dfc8
Run it yourself

Automate database backup processes with scheduling, compression, and encryption. Supports PostgreSQL (pg_dump), MySQL (mysqldump), MongoDB (mongodump), and SQLite. Generates production-ready backup scripts with retention policies and restore procedures. Trigger: "automate database backups", "schedule backups", "create backup script", "disaster recovery". Use when working with automating database backups. Trigger with 'automating', 'database', 'backups'.

Use this Skill: https://skilld.dev/gh/jeremylongshore/tons-of-skills-marketplace/automating-database-backups

Nothing lands on disk. Nothing to clean up.

Fork this Skill

Edit a local copy. It keeps the author and licence.

referencesbackup_best_practices.md

≈1.8k tokens on demand. Your agent reads this file only when SKILL.md points to it.

Database Backup Best Practices

The 3-2-1 Backup Rule

  • 3 copies of data
  • 2 different storage media
  • 1 offsite location
Primary Database
       │
       ├──► Local Backup (NAS/SAN)
       │
       ├──► Remote Backup (S3/GCS)
       │
       └──► Archive (Tape/Glacier)

Backup Types

Full Backup

Complete copy of entire database.

Pros Cons
Simple to restore Large storage
Self-contained Long backup time
No dependencies Network bandwidth

Incremental Backup

Only changes since last backup (any type).

Pros Cons
Fast backup Complex restore (need all incrementals)
Small size Single corrupt file breaks chain
Less I/O More restore time

Differential Backup

Changes since last full backup.

Pros Cons
Simpler than incremental Grows larger over time
Only need full + latest diff Still requires full backup
Faster restore than incremental More storage than incremental

Recommended Strategy

Week 1:
  Sun: Full backup (retain 4 weeks)
  Mon-Sat: Daily incremental

Monthly:
  1st: Monthly full (retain 12 months)

Yearly:
  Jan 1: Annual archive (retain 7 years)

Security Best Practices

Encryption

At Rest:

# GPG encryption
pg_dump mydb | gzip | gpg --symmetric --cipher-algo AES256 -o backup.sql.gz.gpg

# OpenSSL encryption
pg_dump mydb | gzip | openssl enc -aes-256-cbc -salt -pbkdf2 -out backup.sql.gz.enc

In Transit:

# Use SSL/TLS for remote connections
pg_dump "host=remote.server.com sslmode=require" ...

Key Management

  1. Never store encryption keys with backups
  2. Use separate key storage (HashiCorp Vault, AWS KMS, GCP KMS)
  3. Rotate keys periodically
  4. Document key recovery procedure
# Example: Using AWS KMS
aws kms encrypt --key-id alias/backup-key \
  --plaintext fileb://backup.key \
  --output text --query CiphertextBlob > backup.key.encrypted

Access Control

-- PostgreSQL: Dedicated backup user
CREATE USER backup_user WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
GRANT USAGE ON SCHEMA public TO backup_user;

-- MySQL: Backup-only privileges
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'secure_password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO 'backup'@'localhost';

Testing and Validation

Regular Restore Testing

#!/bin/bash
# Monthly restore test
TEST_DB="restore_test_$(date +%Y%m)"

# Create test database
createdb "$TEST_DB"

# Restore latest backup
pg_restore -d "$TEST_DB" /backup/latest.dump

# Run validation queries
psql -d "$TEST_DB" -c "SELECT COUNT(*) FROM users;"

# Compare row counts with production
PROD_COUNT=$(psql -d production -t -c "SELECT COUNT(*) FROM users;")
TEST_COUNT=$(psql -d "$TEST_DB" -t -c "SELECT COUNT(*) FROM users;")

if [ "$PROD_COUNT" -eq "$TEST_COUNT" ]; then
    echo "Restore validation PASSED"
else
    echo "Restore validation FAILED: $PROD_COUNT vs $TEST_COUNT"
fi

# Cleanup
dropdb "$TEST_DB"

Checksum Verification

# Create checksum during backup
pg_dump mydb | tee backup.sql | sha256sum > backup.sql.sha256

# Verify before restore
sha256sum -c backup.sql.sha256

Monitoring and Alerting

Key Metrics

Metric Alert Threshold
Backup duration > 150% of average
Backup size < 50% or > 150% of expected
Storage usage > 80% capacity
Time since last backup > configured interval
Restore test age > 30 days

Backup Monitoring Script

#!/bin/bash
# backup_monitor.sh

BACKUP_DIR="/var/backups/postgresql"
MAX_AGE_HOURS=25  # Alert if backup older than this
MIN_SIZE_MB=100   # Alert if backup smaller than this

# Check latest backup age
LATEST=$(find "$BACKUP_DIR" -name "*.dump" -type f -printf '%T@\n' | sort -n | tail -1)
AGE_HOURS=$(( ($(date +%s) - ${LATEST%.*}) / 3600 ))

if [ "$AGE_HOURS" -gt "$MAX_AGE_HOURS" ]; then
    echo "ALERT: Last backup is $AGE_HOURS hours old"
    # Send alert (email, Slack, PagerDuty)
fi

# Check backup size
SIZE_MB=$(du -m "$BACKUP_DIR"/*.dump 2>/dev/null | tail -1 | cut -f1)
if [ "$SIZE_MB" -lt "$MIN_SIZE_MB" ]; then
    echo "ALERT: Backup size ($SIZE_MB MB) below threshold ($MIN_SIZE_MB MB)"
fi

# Check disk space
USAGE=$(df "$BACKUP_DIR" | tail -1 | awk '{print $5}' | tr -d '%')
if [ "$USAGE" -gt 80 ]; then
    echo "ALERT: Backup disk usage at $USAGE%"
fi

Cloud Storage Integration

AWS S3

# Upload to S3
aws s3 cp /backup/database.dump s3://my-backups/postgresql/

# With lifecycle policy for automatic transitions
# Configure in S3: Move to Glacier after 30 days, delete after 365

# Sync backup directory
aws s3 sync /backup/ s3://my-backups/postgresql/ --delete

Google Cloud Storage

# Upload to GCS
gsutil cp /backup/database.dump gs://my-backups/postgresql/

# With retention policy
gsutil retention set 365d gs://my-backups/postgresql/

Azure Blob Storage

# Upload to Azure
az storage blob upload \
  --container-name backups \
  --name postgresql/database.dump \
  --file /backup/database.dump

Retention Policies

Example Policy

retention:
  hourly:
    keep: 24
    path: /backup/hourly
  daily:
    keep: 7
    path: /backup/daily
  weekly:
    keep: 4
    path: /backup/weekly
  monthly:
    keep: 12
    path: /backup/monthly
  yearly:
    keep: 7
    path: /backup/yearly

Cleanup Script

#!/bin/bash
# cleanup_backups.sh

# Daily: Keep 7 days
find /backup/daily -name "*.dump" -mtime +7 -delete

# Weekly: Keep 4 weeks
find /backup/weekly -name "*.dump" -mtime +28 -delete

# Monthly: Keep 12 months
find /backup/monthly -name "*.dump" -mtime +365 -delete

# Log deleted files
echo "$(date): Cleanup completed" >> /var/log/backup_cleanup.log

Disaster Recovery Checklist

RTO/RPO Definition

Tier RPO RTO Example
Critical < 1 hour < 15 min Payment database
Important < 4 hours < 1 hour User data
Standard < 24 hours < 4 hours Logs, analytics
Archive < 1 week < 24 hours Historical data

Recovery Procedure Document

# Database Recovery Procedure

## Pre-Recovery Checklist
- [ ] Identify failure scope
- [ ] Notify stakeholders
- [ ] Prepare recovery environment
- [ ] Locate latest verified backup

## Recovery Steps
1. Stop dependent applications
2. Assess current database state
3. Select appropriate backup
4. Execute restore procedure
5. Verify data integrity
6. Test application connectivity
7. Resume operations

## Post-Recovery
- [ ] Document incident
- [ ] Update runbooks
- [ ] Review backup strategy

Common Pitfalls

Pitfall Solution
Never tested restores Monthly restore drills
Backups on same disk Use separate storage/offsite
No encryption Always encrypt sensitive data
No monitoring Implement backup alerts
Incomplete backups Include schema, procedures, permissions
No documentation Maintain runbooks
Stale backups Monitor backup freshness

Source: SKILL.md on GitHub

skilld matched fixed text patterns in SKILL.md and file names. Patterns miss obfuscated code.

skilld run checks every file with the same patterns. It asks for approval before it loads a Skill with a behavior marked Needs approval.

2 warnings2mo3 checks · Risk MEDIUM
  • Gen Agent Trust Hub2mo

    The skill automates database backups by generating and scheduling shell scripts. While performing its intended function, it handles sensitive credentials, modifies system persistence via cron, and utilizes sudo for administrative tasks. The script generation logic is vulnerable to command injection if malicious database parameters are provided, and the documentation suggests insecure credential handling practices.

  • Socket2mo

    2 alerts: gptAnomaly

  • Snyk2mo

    Risk: LOW · No issues

Signed by skilld at 820dfc8. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub yesterday.

Activeupdated last month
What it can do
Reads files Edits files Runs commands
version
1.27.0
author
Jeremy Longshore <jeremy@intentsolutions.io>
tags
[
  "database",
  "postgresql",
  "mysql",
  "mongodb"
]
All 10 allowed tools
ReadWriteEditGrepGlobBash(pg_dump:*)Bash(mysqldump:*)Bash(mongodump:*)Bash(cron:*)Bash(gpg:*)
Other metadata
compatibility
Designed for Claude Code

README badge

README badge for jeremylongshore/tons-of-skills-marketplace/automating-database-backups