dataiotech Labs
dbaguide/academy/reliability-and-recovery
MDBA-RELIModül #3Advanced → Expert

3. Reliability & Disaster Recovery (HA/DR)

Patroni HA, InnoDB Cluster, Always On, replication lag, and verifiable PITR

Covers zero data loss backup and recovery strategies, high-availability clustering topologies (Patroni, InnoDB Cluster, Always On), split-brain mitigation, replication lag triage, and deterministic Docker disaster recovery drills.

Publish Progress100%
8
Published
0
Planned
Engineered Competencies Mastered in this Module:
✓PostgreSQL Patroni + etcd + HAProxy zero-downtime automatic failover
✓MySQL InnoDB Cluster + Group Replication + MySQL Router topologies
✓SQL Server Always On AG quorum loss and split-brain resolution
✓10-stage verified isolated Docker restore drill and multi-table checksum validation
Share this engineering module on LinkedIn and X:

Module Syllabus & Lessons8 Lessons

Click published lessons to start learning immediately. Upcoming lessons detail planned objectives below.

MDBA
Educational & Engineering Resource Mapping

The Modern DBA Academy curriculum maps directly to dbaguide's battle-tested deep-dive articles, incident runbooks, diagnostic scripts, and architecture blueprints. Published items connect directly to their verified engineering resources.

MDBA-RELI-001LessonFoundationCore ConceptPublished

Zero Data Loss Backup & Recovery Strategy

RPO/RTO objectives, physical vs. logical backups, continuous WAL archiving, and verified recovery drills.

Key Objectives:
  • •Define business-aligned RPO and RTO thresholds for mission-critical relational workloads
  • •Distinguish between logical exports and physical continuous Point-in-Time Recovery (PITR)
  • •Establish automated, deterministic recovery testing as a mandatory compliance requirement
Prerequisite:MDBA-FUND-001
Next in Syllabus:MDBA-RELI-002: PostgreSQL Disaster Recovery Restore Drill Lab
MDBA-RELI-002Lab DrillAdvancedRunbookPublished

PostgreSQL Disaster Recovery Restore Drill Lab

10-stage isolated Docker disaster recovery lab validating schema parity, global roles, and checksum consistency.

Key Objectives:
  • •Spin up isolated source and target instances via single test command (`npm run test:drill`)
  • •Resolve missing role errors during restore using pg_dumpall --globals-only
  • •Verify database consistency using 7 deterministic checks (row counts, financial sum, MD5 checksum)
Prerequisite:MDBA-RELI-001
Next in Syllabus:MDBA-RELI-003: PostgreSQL Patroni + etcd + HAProxy HA Architecture
MDBA-RELI-003ReferenceExpertBlueprintPublished

PostgreSQL Patroni + etcd + HAProxy HA Architecture

Distributed consensus (etcd), Patroni daemon mechanics, and HAProxy routing for automatic failover.

Key Objectives:
  • •Configure leader election and split-brain fencing using a 3-node etcd cluster
  • •Implement HAProxy health checks to route primary writes and replica read-only traffic
  • •Execute controlled switchover and chaotic failover drills without application downtime
Prerequisite:MDBA-RELI-001
Next in Syllabus:MDBA-RELI-004: MySQL InnoDB Cluster + Group Replication Architecture
MDBA-RELI-004ReferenceExpertBlueprintPublished

MySQL InnoDB Cluster + Group Replication Architecture

Group Replication Paxos consensus, MySQL Shell administration, and MySQL Router transparent routing.

Key Objectives:
  • •Evaluate Single-Primary vs Multi-Primary Group Replication modes under write load
  • •Deploy MySQL Router to transparently bridge application connection pools to cluster primary
Prerequisite:MDBA-RELI-001
Next in Syllabus:MDBA-RELI-005: SQL Server Always On Availability Groups Design
MDBA-RELI-005ReferenceExpertBlueprintPublished

SQL Server Always On Availability Groups Design

WSFC quorum configuration, synchronous/asynchronous commit modes, and AG listener routing.

Key Objectives:
  • •Mitigate split-brain risk using WSFC quorum and cloud/file share witnesses
  • •Configure Always On Availability Group listeners with transparent read-only routing
Prerequisite:MDBA-RELI-001
Next in Syllabus:MDBA-RELI-006: 18-Point Disaster Recovery Drill Checklist
MDBA-RELI-006ReferenceAdvancedChecklistPublished

18-Point Disaster Recovery Drill Checklist

Pre-drill verification, incident simulation, restoration, and data integrity checklist.

Key Objectives:
  • •Execute a compliant 18-step disaster recovery drill protocol for enterprise audit readiness
  • •Benchmark and report Recovery Time Objective (RTO) with minute-level precision
Prerequisite:MDBA-RELI-001
Next in Syllabus:MDBA-RELI-007: PostgreSQL WAL Accumulation & Disk Filling Emergency Triage
MDBA-RELI-007Lab DrillAdvancedRunbookPublished

PostgreSQL WAL Accumulation & Disk Filling Emergency Triage

Resolving runaway pg_wal disk growth caused by archiving failures or inactive replication slots.

Key Objectives:
  • •Prevent database crash when pg_wal volume approaches 100% capacity
  • •Drop stuck inactive replication slots safely
Prerequisite:MDBA-RELI-001
Next in Syllabus:MDBA-RELI-008: PostgreSQL Replication Lag Triage and Resolution
MDBA-RELI-008Lab DrillAdvancedRunbookPublished

PostgreSQL Replication Lag Triage and Resolution

Measuring standby byte and second lag, diagnosing network and I/O write bottlenecks.

Key Objectives:
  • •Diagnose write lag via pg_stat_replication metrics
  • •Resolve long-running query conflicts on read replica
Prerequisite:MDBA-RELI-001
CURRICULUM UPDATES

Get notified about new lessons and labs in 3. Reliability & Disaster Recovery (HA/DR).

Receive instant updates whenever new engineering modules, emergency incident runbooks, diagnostic scripts, and one-command Docker drills are published.