Database Backup

This skill enables an AI agent to plan and execute database backup strategies across PostgreSQL, MySQL, and MongoDB. The agent selects the appropriate backup type (full, incremental, differential, or point-in-time), generates backup scripts with compression and encryption, configures automated scheduling via cron or similar tools, defines retention policies, and verifies backup integrity through restore tests.

Workflow

1. Assess backup requirements: Determine the database type, size, acceptable data loss window (Recovery Point Objective), and acceptable downtime (Recovery Time Objective). Identify whether the backup must be consistent (application-level locks or snapshots) and whether the database can tolerate brief locking during the backup.

2. Select backup strategy: Choose the appropriate backup type based on requirements. Full backups capture the entire database and are simplest to restore but slowest to create. Incremental backups capture only changes since the last backup, saving time and storage. Differential backups capture changes since the last full backup, offering a middle ground. Point-in-time recovery (PITR) uses write-ahead logs or oplogs to restore to any moment, providing the lowest RPO.

3. Generate backup scripts: Produce shell scripts that invoke the correct backup tool for the target database. Include compression (gzip, lz4, or zstd), optional encryption (GPG or OpenSSL), timestamped filenames, and error handling with exit codes. Script should log output for monitoring.

4. Configure scheduling and retention: Set up automated execution using cron, systemd timers, or a task scheduler. Define a retention policy (e.g., keep 7 daily, 4 weekly, 12 monthly backups) and implement cleanup of expired backups to manage storage usage.

5. Verify backup integrity: After each backup, verify the file is non-empty and checksums are valid. Periodically perform a test restore to a staging environment to confirm the backup is actually recoverable. Alert on verification failures.

6. Document and monitor: Record the backup schedule, retention policy, storage location, and restore procedure. Set up monitoring alerts for missed backups or backup failures using tools like Prometheus, Datadog, or simple email notifications.

Supported Technologies