Creating an RDS Postgres Database in AWS and Connect with Ec2 Instance

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the AWS Cloud. It provides cost-efficient, resizable capacity for an industry-standard relational database and manages common database administration tasks.

Steps to create RDS Database:

1. Sign in to AWS Console: Log in to your AWS account using your credentials at https://aws.amazon.com/console/.
2. Navigate to RDS: Once logged in, go to the AWS Management Console and search for “RDS” or locate it under the “Database” section.
3. Choose a Database Engine: Click on “Create Database” to start the database creation process. Select the database engine you want to use (MySQL, PostgreSQL, Oracle, SQL Server, etc.).
4. Select Use Case & Deployment Type: Choose the use case that best suits your needs, such as “Dev/Test” or “Production.” Select the deployment type as “Standard create.”
5. Specify DB Details: Provide the following details for your RDS instance:

• DB Instance Identifier: A unique name for your RDS instance.
• Master Username and Password: The username and password for the master user of the database.
• DB Instance Size: Select the compute and memory resources appropriate for your workload.
• Storage: Specify the storage capacity and type (e.g., General Purpose SSD, Provisioned IOPS).
• Availability & Durability: Choose the availability zone(s) and enable Multi-AZ (for high availability and automatic failover).
• Network & Security: Define the Virtual Private Cloud (VPC), subnet, security group, and other network-related configurations.

6. Database Settings: Configure the database-specific settings, such as character set, collation, parameter group, etc.

7. Backup & Maintenance: Configure the automated backup retention period, preferred backup window, maintenance window, etc.

8. Monitoring & Performance Insights: Optionally, you can enable database monitoring and Amazon RDS Performance Insights for better visibility into database performance.

9. Tags: Add tags (key-value pairs) to your RDS instance for easier management and identification.

10. Review: Double-check all the configuration settings you have provided to ensure they are correct.

11. Create Database: Click the “Create Database” button to start the database creation process. It might take a few minutes to set up your RDS instance.

12. Database Created

13 . Once database created, we can find the End point Details and port number keep note these details we need to configure the Postgres database with EC2 Instance

14.login to ec2 :

option 1 : instance connect

option 2: Session manager

After login to session manager follow the commands

sudo dnf update -y
sudo dnf install postgresql15
psql --host=database-1.c5i68qc6qzs0.us-east-1.rds.amazonaws.com --port=5432 --username=postgres

Enter password 

CREATE DATABASE my_database;
\c my_database;
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE DEFAULT CURRENT_DATE
);
INSERT INTO employees (first_name, last_name, department, salary) 
VALUES 
('John', 'Doe', 'Engineering', 70000.00),
('Jane', 'Smith', 'Marketing', 65000.00),
('Sam', 'Williams', 'Finance', 55000.00);
SELECT * FROM employees;

Once Data base connected you can enter the above SQL Query to connect with Database and Table, we can view using the select Query Command

Thank you & Happy learning!!