AWS Relational Database Service (RDS) Overview

Outline

Introduction & Deployment
RDS Connection and SQL Query
Backup and Restore
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.

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

MariaDB
Microsoft SQL Server
MySQL
Oracle
PostgreSQL

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

RDS Connection and SQL Query

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

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

Remark:

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())

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.

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.