High availability of database service: cross AZ resilience and off-site backup

rija commented 2 years ago

User Story

As a developer I want a standard operating procedure to restore database on AWS infrastructure in the Upstream Gitlab group So that I can perform database restoration in case of database failure for GigaDB website

Acceptance criteria

Given the RDS hosting the database has failed When I perform the SOP Then the database is deployed in RDS in another AWS availability zone

Given the RDS backups of GigaDB database are not available When I perform the SOP Then the database is restored from backups stored in another location

Addtional information

this is a continuation of #1008
Check out https://pgbackrest.org/
What could happen - https://www.infoq.com/news/2021/12/aws-outage-postmortem/

If a region or availability zone goes down, it's not just the database, it's the whole stack deployed in that AZ.

0) transfer gigadb.org from Alicloud to Route53

1) TODO in case of region failure:

in advance, determine which is alternative region: Osaka (ap-northeast-3)
In advance, create ssh key pairs in an alternate region for the Upstream deployment and store as Gitlab variables
Describe in SOP:
- Create elastic IP in the new region
- Re-run terraform and ansible in that region
- Update DNS record for gigadb.org to point to the new elastic IP

2) TODO in case of AZ failure (within a region):

Re-run terraform and ansible in the new AZ

We keep database backups also on S3 which is independent from any region/AZ

rija commented 1 year ago

Hi @pli888, @kencho51,

It is now easy to do that through the console: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZSingleStandby.html

We should still look into how to do this through Terraform

rija commented 1 year ago

Hi @pli888, @kencho51,

We should also consider deploying read replicas: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.Replication.ReadReplicas.html

We still need to look into how to do that in Terraform (automation)

but that should be another user story, not here.

gigascience / gigadb-website