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)
• pgbasebackup, pgdump, pg_restore (PostgreSQL) or mysqldump, xtrabackup (MySQL)
• tar, rsync, or aws s3 CLI for backup transfer and storage
• WAL archiving configured for PITR (PostgreSQL: archivemode = on, archivecommand)
• 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 archivemode, archivecommand, and wallevel in postgresql.conf. For MySQL: check if binary logging is enabled with SHOW VARIABLES LIKE 'logbin'.

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

1. Configure WAL archiving for PostgreSQL PITR:

• Set wallevel = replica and archivemode = on
• Configure archive_command = 'test ! -f /archive/%f && cp %p /archive/%f' (or use pgBackRest/WAL-G for S3)
• Verify archiving works: SELECT * FROM pgstatarchiver
• For MySQL, enable binary logging: logbin = mysql-bin, binlogformat = ROW

1. Create a full physical backup using pg_basebackup -D /backups/base -Ft -z -P (PostgreSQL) or xtrabackup --backup --target-dir=/backups/full (MySQL). Physical backups are faster to restore than logical backups for databases larger than 10GB.

1. Create logical backups for portability and selective restoration: pg_dump -Fc -f database.dump dbname (PostgreSQL) or mysqldump --single-transaction --routines --triggers dbname > database.sql (MySQL).

1. Upload backups to remote storage for disaster recovery: aws s3 cp /backups/base.tar.gz s3://b