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.

referencespostgresql_backup_restore.md

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

PostgreSQL Backup and Restore Guide

Backup Methods

pg_dump (Logical Backup)

Single Database Backup:

pg_dump -h localhost -U postgres -d mydb > mydb.sql

With Compression (Custom Format):

pg_dump -h localhost -U postgres -d mydb \
  --format=custom \
  --compress=9 \
  --file=mydb.dump

Plain SQL with Compression:

pg_dump -h localhost -U postgres -d mydb | gzip > mydb.sql.gz

pg_dumpall (All Databases)

pg_dumpall -h localhost -U postgres > all_databases.sql

pg_basebackup (Physical Backup)

For point-in-time recovery and replication:

pg_basebackup -h localhost -U replication -D /var/lib/postgresql/backup \
  --wal-method=stream \
  --checkpoint=fast \
  --progress

Backup Options

Option Description Example
-F c Custom format (compressed) pg_dump -F c -f backup.dump
-F p Plain SQL (default) pg_dump -F p -f backup.sql
-F t Tar format pg_dump -F t -f backup.tar
-F d Directory format pg_dump -F d -f backup_dir/
-j N Parallel jobs (dir format only) pg_dump -F d -j 4
-T table Exclude table pg_dump -T logs
-t table Include only table pg_dump -t users
-n schema Dump specific schema pg_dump -n public
--data-only Data without schema pg_dump --data-only
--schema-only Schema without data pg_dump --schema-only

Restore Methods

psql (Plain SQL)

psql -h localhost -U postgres -d mydb < mydb.sql

# From compressed
gunzip -c mydb.sql.gz | psql -h localhost -U postgres -d mydb

pg_restore (Custom/Tar/Directory)

# Create empty database first
createdb -h localhost -U postgres mydb_restored

# Restore
pg_restore -h localhost -U postgres -d mydb_restored mydb.dump

Parallel Restore:

pg_restore -h localhost -U postgres -d mydb -j 4 mydb.dump

Restore Options:

pg_restore \
  --clean           # Drop objects before recreating
  --if-exists       # Don't error if object doesn't exist
  --no-owner        # Skip ownership commands
  --no-privileges   # Skip grant/revoke commands
  --single-transaction  # All-or-nothing restore
  -d mydb mydb.dump

Point-in-Time Recovery (PITR)

Setup WAL Archiving

In postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Recovery

  1. Stop PostgreSQL

  2. Clear data directory (backup first!)

  3. Restore base backup

  4. Create recovery.signal file

  5. Configure postgresql.conf:

    restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
    recovery_target_time = '2025-01-15 14:30:00'
  6. Start PostgreSQL

Production Backup Script

#!/bin/bash
set -euo pipefail

# Configuration
BACKUP_DIR="/var/backups/postgresql"
RETENTION_DAYS=7
DB_HOST="localhost"
DB_USER="postgres"
DB_NAME="production"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"
LOG_FILE="${BACKUP_DIR}/backup.log"

# Ensure backup directory exists
mkdir -p "$BACKUP_DIR"

# Function to log messages
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# Perform backup
log "Starting backup of $DB_NAME"

if PGPASSWORD="${PGPASSWORD:-}" pg_dump -h "$DB_HOST" -U "$DB_USER" -d "$DB_NAME" \
    --format=custom \
    --compress=9 \
    --verbose \
    --file="$BACKUP_FILE" 2>> "$LOG_FILE"; then

    SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
    log "Backup completed: $BACKUP_FILE ($SIZE)"

    # Verify backup
    if pg_restore --list "$BACKUP_FILE" > /dev/null 2>&1; then
        log "Backup verification passed"
    else
        log "ERROR: Backup verification failed"
        exit 1
    fi
else
    log "ERROR: Backup failed"
    exit 1
fi

# Cleanup old backups
log "Cleaning backups older than $RETENTION_DAYS days"
find "$BACKUP_DIR" -name "*.dump" -mtime "+$RETENTION_DAYS" -delete

log "Backup process completed"

Selective Restore

Single Table:

pg_restore -h localhost -U postgres -d mydb \
  --table=users \
  --data-only \
  mydb.dump

List Contents:

pg_restore --list mydb.dump > toc.txt
# Edit toc.txt to select items
pg_restore -h localhost -U postgres -d mydb \
  --use-list=toc.txt \
  mydb.dump

Troubleshooting

Issue Solution
"permission denied" Grant CONNECT, SELECT on tables to backup user
"out of memory" Use -F d with -j for parallel backup
"lock timeout" Run during low-traffic period
Restore fails on constraints Use --disable-triggers or restore in correct order
Large object issues Add --blobs to include BLOBs

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