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

1. 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'.

2. Define RPO and RTO targets based on business requirements: