| name | managing-database-recovery |
| description | Process use when you need to work with database operations.
This skill provides database management and optimization with comprehensive guidance and automation.
Trigger with phrases like "manage database", "optimize database",
or "configure database".
|
| allowed-tools | Read, Write, Edit, Grep, Glob, Bash(tar:*), Bash(rsync:*), Bash(aws:s3:*) |
| version | 1.0.0 |
| author | Jeremy Longshore <jeremy@intentsolutions.io> |
| license | MIT |
| compatible-with | claude-code, codex, openclaw |
| tags | ["database","database-recovery"] |
Database Recovery Manager
Overview
Plan and execute database backup and recovery procedures for PostgreSQL and MySQL, including point-in-time recovery (PITR), logical and physical backups, WAL archiving, and disaster recovery testing. This skill covers the full backup lifecycle from configuration through automated verification, ensuring Recovery Point Objective (RPO) and Recovery Time Objective (RTO) targets are met.
Prerequisites
- Database superuser or replication-role credentials
- Backup storage destination (local disk, NFS mount, S3, GCS, or Azure Blob)
pg_basebackup, pg_dump, pg_restore (PostgreSQL) or mysqldump, xtrabackup (MySQL)tar, rsync, or aws s3 CLI for backup transfer and storage- WAL archiving configured for PITR (PostgreSQL:
archive_mode = on, archive_command)
- Sufficient storage for backup retention (estimate 2-3x database size for full + incremental)
Instructions
-
Assess the current backup situation by checking existing backup configurations. For PostgreSQL: verify archive_mode, archive_command, and wal_level in postgresql.conf. For MySQL: check if binary logging is enabled with SHOW VARIABLES LIKE 'log_bin'.
-
Define RPO and RTO targets based on business requirements:
- RPO (acceptable data loss): determines backup frequency and WAL archiving interval
- RTO (acceptable downtime): determines backup type and recovery procedure complexity
- Typical targets: RPO < 1 hour (WAL archiving), RTO < 30 minutes (physical backup restore)
-
Configure WAL archiving for PostgreSQL PITR:
- Set
wal_level = replica and archive_mode = on
- Configure
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f' (or use pgBackRest/WAL-G for S3)
- Verify archiving works:
SELECT * FROM pg_stat_archiver
- For MySQL, enable binary logging:
log_bin = mysql-bin, binlog_format = ROW
-
Create a full physical backup using pg_basebackup -D /backups/base -Ft -z -P (PostgreSQL) or (MySQL). Physical backups are faster to restore than logical backups for databases larger than 10GB.
