AWS Relational Database Service (RDS) Overview
Published: 2023-07-09 12:01:49


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kttmkl7crp4w5mnfb6lg.jpg)
## Outline
1. Introduction & Deployment
2. RDS Connection and SQL Query
3. Backup and Restore
4. High Availablity & Disaster Recovery

## Introduction & Deployment
Amazon Relational Database Service (Amazon RDS) is a managed database service While RDS can be deployed on **EC2** or **Outposts** (the service that allow you to run AWS services on Premises Private Cloud).

When deploying RDS, we need to specify the EC2 instance specification such as vCPUs and RAM.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zd1lxem6s9bbkw80pvf3.png)

In addition, RDS supports different **database engine types** and **verison**, which includes:

1. MariaDB
2. Microsoft SQL Server
3. MySQL
4. Oracle
5. PostgreSQL

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3jv3fc252f11ehu6zgcm.png)

After creating the RDS, you can check the connection details such as **username** and **password** as well as the **Endpoint** and **port**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nb9ag3e5e3jjov1hkw93.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nihffe43pras9d8en7ww.png)

## RDS Connection and SQL Query

We can connect RDS for PostgreSQL via **pgAdmin, psql, or programming language**.

1. Connecting RDS for PostgreSQL via Python
2. Create and List the table within the database

Remark:
1. when you connect the RDS via **public internet**, you need to **allow public access** in RDS and configure the **security group for allowed port and source IP**.
```
import psycopg2
engine = psycopg2.connect(
    database="postgres",
    user="postgres",
    password="HvNnP1UnYVtNZS15ofUf",
    host="database-1.cjylreq9cdvs.ap-east-1.rds.amazonaws.com",
    port='5432'
)
cursor = engine.cursor()
cursor.execute('''
CREATE TABLE IF NOT EXISTS Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);
               ''')
cursor.execute('''
select relname from pg_class where relkind='r' and relname !~ '^(pg_|sql_)';
''')
print("The RDS postgres database contains the following table:")
print(cursor.fetchall())
```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xsktp5r1431ukh2grsxo.png)

## Backup and Restore
By default, RDS is enabled with **automated backups** and **backup retention period** (e.g. 7 days). If you have some significant change on RDS Database, you can manually **take the snapshot** for restoration purpose.

When you select the snapshot, you can restore the database back to that specific time of status.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/foxegwrcnm1gofrnxmeu.png)

## High Availablity & Disaster Recovery

You can create the **read replicas** in another availablity zone for better availablity while the read replicas only allow read database data. Once the primary RDS instance is down, you can promote the read replicas to be the primary RDS.

| Option            | Recovery time objective (RTO) | Recovery point objective (RPO) | Cost   | Scope         |
| ----------------- | ----------------------------- | ------------------------------ | ------ | ------------- |
| Automated backups | Good                          | Better                         | Low    | Single Region |
| Manual snapshots  | Better                        | Good                           | Medium | Cross-Region  |
| Read replicas     | Best                          | Best                           | High   | Cross-Region  |

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hatr87yr9u33ai7vaypc.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tskhjqcjwzct2whgl9r4.png)











Reference:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Welcome.html
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Welcome.html#Welcome.Concepts.on-prem
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToPostgreSQLInstance.html
https://aws.amazon.com/tw/rds/features/backup/
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithAutomatedBackups.html
https://aws.amazon.com/tw/blogs/database/implementing-a-disaster-recovery-strategy-with-amazon-rds/