How to set up an emergency recovery plan and failover for PostgreSQL in AWS?

To set up a disaster recovery plan and failover for PostgreSQL in AWS, the following approaches are usually used:

1. Using Amazon RDS for PostgreSQL:

   • Enable Multi-AZ deployment — AWS will automatically create a synchronous replica in another availability zone and ensure automatic failover.
   • Configure automatic backups and point-in-time recovery.

2. Self-managed PostgreSQL on EC2:

   • Set up replication (streaming replication) between the primary and standby server in different AZs.
   • Use tools for automatic failover, such as Patroni, repmgr, or Pacemaker.
   • Regularly back up using pg_basebackup or pg_dump.

3. Disaster recovery plan:

   • Define RTO (Recovery Time Objective) and RPO (Recovery Point Objective).
   • Set up monitoring and alerts for replica status.
   • Test failover and recovery procedures from backups.

Example of using Multi-AZ in RDS:

aws rds create-db-instance \
    --db-instance-identifier mydb \
    --engine postgres \
    --allocated-storage 20 \
    --db-instance-class db.m5.large \
    --multi-az \
    --master-username admin \
    --master-user-password password

This approach minimizes downtime and provides automatic failover without manual intervention.