丁久

Posted on May 16 • Originally published at dingjiu1989-hue.github.io

Database Disaster Recovery: RPO, RTO, Cross-Region Replication

#database #backend #data

This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Database Disaster Recovery: RPO, RTO, Cross-Region Replication

Disaster recovery (DR) ensures your database can survive catastrophic events: region outages, data corruption, accidental deletions, or ransomware attacks. Unlike high availability, which handles component failures, DR addresses large-scale disasters.

RPO and RTO

Two metrics define DR requirements:

Recovery Point Objective (RPO) : The maximum acceptable data loss measured in time. An RPO of 1 hour means you can lose at most 1 hour of data.

Recovery Time Objective (RTO) : The maximum acceptable downtime. An RTO of 4 hours means the database must be operational within 4 hours of the disaster.

| Scenario | RPO | RTO | Strategy | |----------|-----|-----|----------| | Internal tool | 24 hours | 24 hours | Daily backups, restore | | E-commerce | 5 minutes | 1 hour | Cross-region replication | | Financial trading | 0 (zero loss) | 5 minutes | Synchronous replication + DR site |

Cross-Region Replication

PostgreSQL Logical Replication Across Regions

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- On primary (us-east-1)

CREATE PUBLICATION dr_pub FOR ALL TABLES;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- On standby (us-west-2)

CREATE SUBSCRIPTION dr_sub

CONNECTION 'host=primary-us-east-1.example.com port=5432 dbname=proddb'

PUBLICATION dr_pub

WITH (copy_data = true, connect = true, create_slot = true);

Logical replication works across regions with asynchronous delivery. Monitor lag carefully:

SELECT pg_size_pretty(

pg_wal_lsn_diff(

pg_current_wal_lsn(),

replay_lsn

)

) AS replication_lag

FROM pg_stat_replication

WHERE application_name = 'dr_sub';

AWS RDS Cross-Region Read Replicas

Create cross-region read replica

aws rds create-db-instance-read-replica \

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\--db-instance-identifier mydb-dr \

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\--source-db-instance-identifier mydb \

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\--region us-west-2 \

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\--db-instance-class db.r6g.large

Promote to standalone for DR

aws rds promote-read-replica \

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\--db-instance-identifier mydb-dr \

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\--region us-west-2

Multi-Region with Patroni

Patroni can manage clusters across regions with careful configuration:

DR site configuration

sco

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.

DEV Community

Database Disaster Recovery: RPO, RTO, Cross-Region Replication

Database Disaster Recovery: RPO, RTO, Cross-Region Replication