What does this guide solve?
HA, replication, backup and DR are different controls. HA reduces service outage, backup preserves data history, and DR provides recovery in a separate failure domain.
PostgreSQL
Streaming replication and WAL-based standby designs support HA/DR; point-in-time recovery requires base backups plus WAL archiving.
Failover orchestration should be designed using appropriate tooling or a managed platform.
MySQL / MariaDB
Options include asynchronous/semi-sync replication, Group Replication/InnoDB Cluster and MariaDB Galera.
More synchronous models change latency and operational complexity; choose according to workload.
SQL Server
Always On Availability Groups, Failover Cluster Instances and log shipping solve different availability/DR problems.
The transaction-log backup chain is critical for RPO and point-in-time restore.
Oracle
Data Guard addresses standby/DR while RAC addresses selected local HA/scale scenarios; they are not interchangeable.
RMAN backup and restore testing remain necessary even with Data Guard or RAC.
MongoDB
Replica sets provide redundancy and automatic failover; sharding is for scale-out and is not backup.
Measure oplog window, backup consistency and restore procedures against DR objectives.
RPO / RTO and testing
RPO defines acceptable data loss and RTO acceptable outage duration. Technology choices should map to those targets.
Test planned failover, node failure, storage corruption, accidental deletion and full-site loss as separate scenarios.
Frequently Asked Questions
If we have HA, do we still need backups?
Yes. HA does not preserve historical copies against accidental deletion, logical corruption or ransomware.
Does replication guarantee zero data loss?
No. Data-loss exposure varies by replication mode, network behavior and commit semantics.
How often should DR be tested?
Test periodically according to business criticality and rate of change, including real restore/failover exercises rather than paperwork only.
Official technical sources
- https://www.postgresql.org/docs/current/high-availability.html
- https://dev.mysql.com/doc/refman/8.4/en/replication.html
- https://learn.microsoft.com/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server
- https://docs.oracle.com/en/database/oracle/oracle-database/23/sbydb/
- https://www.mongodb.com/docs/manual/replication/
- https://mariadb.com/docs/server/ha-and-performance/
Evaluate Your Database Infrastructure
We can review workload, security, hardware, HA and backup requirements together.
Architecture and operating model
HA provides continuity, replication transfers changes, backup restores earlier states, and DR recovers business from broader failures. Erroneous deletion can reach replicas. Design synchronous/asynchronous behavior, quorum, fencing and connection targets together. Prevent uncontrolled writes when an old primary returns.
- 1Commit and replication
- 2Quorum/fencing
- 3New writer
- 4Backup/PITR and business tests
Design parameters
- Quorum and fencing
- Ensure one authorized writer after failover; prevent the old primary writing on reconnection.
- Lag and recovery point
- Replica connectivity is not freshness; measure last sent and applied transactions.
- Workload and query mix
- Measure read/write ratio, connection count, transaction duration and expensive queries. Database size alone does not explain hardware needs.
- Memory and storage
- Examine working set, log writes, temporary space and checkpoint behavior separately. Reliable write guarantees matter alongside speed.
- Recovery chain
- Test recovery of the starting backup, required logs, configuration and keys together.
- Access and change
- Separate application, administrator and schema-change permissions. Control production index and parameter changes.
Worked example
After primary loss, compare replica age with the last committed operation. Measure application redirection and retries. In a separate exercise, recover an accidental deletion to a clean target using backup/PITR; failover does not replace deletion recovery.
Troubleshooting
| Observation | Likely cause / distinction | Verification |
|---|---|---|
| Both sides accept writes | Fencing or failover decisions may be incorrect. | In an approved test, isolate the old primary and verify write authority after reconnection. |
| CPU is low but queries are slow | Locks, I/O or client waits may dominate. | Inspect the execution plan and wait type for the same query. |
| Replica is connected but stale | Sending, replay or transaction waits can create lag. | Compare the last applied operation with source commit time. |
| Storage keeps growing | Log retention, open transactions, replication slots or maintenance may be involved. | Inspect supported product statistics; do not delete logs manually. |
Acceptance checks
- Test node failure and data corruption as different recovery scenarios.
- Test representative transactions and queries using realistic data distributions.
- Restore to a separate target and validate application records.
- Reject unauthorized reads and schema changes using a controlled identity.
- Verify one authorized writer and the correct connection target after failover.
- Measure locks, log volume and latency during maintenance.
Related concepts
Availability versus recovery
High availability aims to keep service running through specified failures with a short interruption; backup recovers lost or corrupted data from an earlier point. A cluster can replicate an accidental deletion to another node. HA therefore does not replace backup. Consider DNS, identity, network, storage and power dependencies together. Successful node failover is insufficient by itself: measure user sessions, application writes and external integrations after the transition as well.
Replication scope
Replication transfers data or state changes to another copy. Synchronous transfer can introduce latency and connectivity dependence; asynchronous transfer can lag behind. A current replica is not necessarily protected from incorrect changes: deletion and corruption can propagate too. Compare lag with the application’s last committed operation, not merely connection status. Define which copy receives write authority after the source is lost and how the old node is reconciled when it returns. Report replication lag and the actual recoverable point separately.
Quorum and split brain
Quorum helps determine which group of nodes has authority to continue service. It prevents disconnected groups from independently writing to the same resource. Vote counts, dynamic voting and witness behavior depend on the product; node count alone is insufficient. A witness in the same failure domain may not provide the expected benefit. Test network partition and witness loss separately. After failover, verify that there is one authorized writer and that an old node cannot rejoin service without controlled reconciliation.
Failover and failback
Failover moves service to another component; failback returns it to the preferred location. Their risks and sequencing may differ. DNS updates, routing, sessions, replication lag and application dependencies determine user interruption. Define failover triggers, false-alarm behavior and approval for returning service. Simply shutting down a VM does not test every failure type. Measure network, storage and management-plane failures separately.
RPO and the actual loss window
RPO is the acceptable duration of data loss after an incident. A backup schedule alone does not prove it: failed jobs or delayed replication to another site can enlarge the window. Compare the incident time with the latest usable, consistent recovery point. If an incident occurs at 14:00 and the verified copy is from 13:20, the observed loss window is 40 minutes. Set targets per application; a file archive and a database receiving continuous orders may have different requirements.
RTO and end-to-end recovery time
RTO specifies how soon a service must become usable after an incident. Downloading a backup or booting a VM accounts for only part of that duration. Record detection, approval, infrastructure preparation, data transfer, application startup and business validation separately. Define exactly what starts and stops the test clock. The same technical restore duration can produce different business interruptions when dependencies or access approvals introduce additional waiting.