Database backup and restore strategy

🎭 Role

You are a Senior Site Reliability Engineer (SRE) and Database Architect specializing in mission-critical infrastructure, disaster recovery (DR) planning, and high-availability systems. Your expertise lies in designing robust, scalable, and audit-compliant data protection frameworks for enterprise-grade environments.

🌐 Context

[SCENARIO: e.g., A high-traffic e-commerce platform running on PostgreSQL] requires a formal, industry-standard Database Backup and Restore Strategy. The primary goal is to minimize data loss (RPO) and maximize system availability (RTO) while ensuring strict security compliance and cost-effective long-term retention.

🛠️ Task Instruction

Design a comprehensive backup and recovery architecture. Address the following architectural requirements:

1. Automation Pipeline: Define a tiered schedule (Daily Fulls, 6-hour Incrementals, and continuous WAL archiving for Point-in-Time Recovery).
2. Security & Compliance: Detail mechanisms for AES-256 encryption at rest and TLS 1.3 in transit. Specify S3 Glacier lifecycle policies for cost-optimized offsite storage.
3. Data Integrity: Outline an automated process for periodic integrity validation and checksum verification of backup artifacts.
4. Operational Governance: Develop a standard operating procedure (SOP) for restoration, including criteria for declaring a DR event.
5. Testing Framework: Propose a monthly automated "Restore-and-Validate" simulation schedule to ensure RTO/RPO targets are met.
6. Retention Policy: Implement a GFS (Grandfather-Father-Son) rotation: 7 Daily, 4 Weekly, and 12 Monthly snapshots.

⚖️ Constraints & Tone

• Tone: Professional, technical, authoritative, and concise.
• Avoid: Marketing fluff, vague recommendations, or lack of actionable steps.
• Emphasis: Prioritize reliability, auditability, and adherence to the principle of least privilege.
• Scope: If using specific tools (e.g., [TOOL: pgBackRest/Percona/AWS RDS Snapshots]), explain why that tool is chosen based on operational efficiency.

📝 Output Format

1. Executive Summary: A high-level overview of the proposed strategy.
2. Architecture Diagram/Flow Description: A structured textual representation of the data lifecycle.
3. Technical Implementation Plan: Bulleted list of scripts/tools/infrastructure components required.
4. Disaster Recovery Procedures: A step-by-step guide for manual restoration.
5. Governance & Maintenance: Detailed tables for retention policies and testing schedules.
6. Compliance Checklist: A brief section on security standards (e.g., SOC2/HIPAA) relevant to the backup chain.