How to connect to an AWS RDS instance

Hello Everyone

Welcome to CloudAffaire and this is Debjeet.

In the last blog post, we have discussed how to create an AWS RDS instance.

https://cloudaffaire.com/how-to-create-an-aws-rds-instance/

In this blog post, we will discuss how to connect to an AWS RDS instance.

Prerequisites for this demo:

One AWS RDS (PostgreSQL) instance
One EC2 AWS Linux instance hosted in the same VPC

How to connect to an AWS RDS instance:

Step 1: Prepare AWS RDS connection string.

psql \
--host=<DB instance endpoint> \
--port=<port> \
--username=<master user name> \
--password \
--dbname=<database name>

psql \

--host= \

--port= \

--username= \

--password \

--dbname=

Below RDS details will be required that can be gathered from AWS RDS console.

username: myuser
password: xxxxxxxx
dbname: mydb
host(endpoint): mydbinstance.xxxxxxxxxxxx.ap-south-1.rds.amazonaws.com
port: 5432

Note: If you don’t have an RDS instance created already, created it first.

Step 2: Login to AWS EC2 instance and install the PostgreSQL client.

sudo amazon-linux-extras install postgresql10

1	sudo amazon-linux-extras install postgresql10

Step 3: Execute below ‘psql’ command to connect to the PostgreSQL RDS instance.

psql \
--host=mydbinstance.xxxxxxxxxxxxx.ap-south-1.rds.amazonaws.com \
--port=5432 \
--username=myuser \
--password \
--dbname=mydb

psql \

--host=mydbinstance.xxxxxxxxxxxxx.ap-south-1.rds.amazonaws.com \

--port=5432 \

--username=myuser \

--password \

--dbname=mydb

How To Connect To An AWS RDS Instance

Note: Replace the host with your RDS instance endpoint. If you get a connection error, make sure port 5432 is open in your security group.

How To Connect To An AWS RDS Instance

Step 4: Create and insert some data into a table.

-- Create a table
CREATE TABLE EMPLOYEE(
EMP_ID INT PRIMARY KEY NOT NULL,
EMP_NAME CHAR(50) NOT NULL,
EMP_ADDRESS CHAR(50) NOT NULL
);

-- List tables
\d

-- Insert some data
INSERT INTO EMPLOYEE (EMP_ID,EMP_NAME,EMP_ADDRESS) VALUES (1, 'Debjeet', 'Kolkata');
INSERT INTO EMPLOYEE (EMP_ID,EMP_NAME,EMP_ADDRESS) VALUES (2, 'Bob', 'Newyork');
INSERT INTO EMPLOYEE (EMP_ID,EMP_NAME,EMP_ADDRESS) VALUES (3, 'David', 'Sydney');

-- Select data
SELECT * FROM EMPLOYEE;

-- Quit psql
\q

-- Create a table

CREATE TABLE EMPLOYEE(

EMP_ID INT PRIMARY KEY NOT NULL,

EMP_NAME CHAR(50) NOT NULL,

EMP_ADDRESS CHAR(50) NOT NULL

);

-- List tables

-- Insert some data

INSERT INTO EMPLOYEE (EMP_ID,EMP_NAME,EMP_ADDRESS) VALUES (1, 'Debjeet', 'Kolkata');

INSERT INTO EMPLOYEE (EMP_ID,EMP_NAME,EMP_ADDRESS) VALUES (2, 'Bob', 'Newyork');

INSERT INTO EMPLOYEE (EMP_ID,EMP_NAME,EMP_ADDRESS) VALUES (3, 'David', 'Sydney');

-- Select data

SELECT * FROM EMPLOYEE;

-- Quit psql

Hope you have enjoyed this article, In the next blog post, we will discuss how to take a backup (snapshot) of an AWS RDS instance.

To get more details on AWS RDS, please refer below AWS documentation

https://docs.aws.amazon.com/rds/index.html