| Problem/Solution | Snapshot-based backup | Multi-AZ | Read Replica in the same region | Read Replica in multi region |
| Wrong/deleted data casued by wrong code | Yes Will lose at leas one day or a few hours of data | No The wrong data has been copied to the standby instance | No The wrong data has been copied to the standby instance | No The wrong data has been copied to the standby instance |
| Hardware failure | Yes Will lose at least one day or a few hours of data | Yes It happens automatically | Yes You can manually promote the replica to a master | Yes You can manually promote the replica to a master |
| Instance deleted by mistake | Yes Will lose at least one day or a few hours of data | No The standby instance will be deleted along | Yes You can manually promote the replica to a master Will lose few data | Yes You can manually promote the replica to a master Will lose few data |
| AWS region failure | No Have to wait until the region recovers | No Have to wait until the region recovers | No Have to wait until the region recovers | Yes Note there is extra cost compared to its same-region counterpart |
| Account compromised | No The hacker can also delete the snapshots | No | No The hacker can also delete the snapshots | No The hacker can also delete the snapshots |
Downtime and Influence to application:
| Snapshot-based | Multi-AZ | Read Replica | |
| DB Downtime | Must manually recover saved data (30 minutes – n hours) | No downtime | Must manually promote the replica instance (30 minutes – n hours) |
| DB Endpoint changed? | Yes | No | Yes |
| Application downtime | DB Downtime + App Change/Rebounce time (Must change db endpoint in app) | No downtime | DB Downtime + App Change/Rebounce time (Must change db endpoint in app) |
References: https://blogs.tensult.com/2018/05/26/aws-rds-disaster-recovery/
P.S.
- You may just enable “delete protection” instead of setting up “read replica”
- The snapshot-based recovery may not necessarily cause app-side change. You can,
- Load the snapshot to a new db instance
- Copy data from the new instance to the existing instance using sql scripts