DEV Community

Cover image for Create A (Postgresql) Database with Amazon RDS
Ukeme David Eseme
Ukeme David Eseme

Posted on • Updated on

Create A (Postgresql) Database with Amazon RDS

You might ask yourself, what is Amazon RDS? Types of Amazon RDS you can choose from ? Why use Postgresql?

On this article we would not just answer these questions, but also run a lab walkthrough on how to set this up on the cloud and on your local computer.

Is everyone ready?

What is Amazon RDS?

Amazon RDS stands for Amazon Relational Database Service, it is a collection of managed services that makes it simple to set up, operate, and scale databases in the cloud.

Types of Amazon RDS you can choose from

AWS give you seven popular engines to choose from, in no particular order:

  • PostgreSQL
  • MariaDB
  • MySQL
  • SQL Server
  • Amazon Aurora with MySQL compatibility
  • Amazon Aurora with PostgreSQL compatibility
  • and Oracle

Why use Postgresql?

PostgreSQL comes with many features aimed to help developers build applications, administrators to protect data integrity and build fault-tolerant environments, and help you manage your data no matter how big or small the dataset.

With all that said, let get to the lab.
Kelvin Hart

Step 1

Login to your AWS Console, click on services, on the top right corner. Click on Database > RDS

AWS Navigation

Step 2

On the RDS landing page, click on Create database.
This would direct you to the RDS Database dashboard.
On the dashboard, click on Create database

Amazon RDS create database

Amazon RDS create database

Step 3

Database Method/Engine options
For database creation method, select standard create.
For Engine option, select PostgreSQL.
For version, select your most preferred option depending on your use case, or just leave the default.

In this case, I would be using PostgreSQL 13.4-R1

Data base engine option

Step 4

Templates
In the template section, select Free tier

Template

Step 5 Availability and durability

leave as default

Step 6

Settings
DB instance identifier, input postgres
Credentials Settings > Master username, use your preferred username, in my case, I would be using skywalker , insert your password, and confirm password.

DB settings

Step 7

Instance configuration
DB instance class, Burstable classes (includes t classes) is selected by default. select db.t3.micro

Instance Configuration

Step 8

Storage
Storage type > General Purpose SSD (gp2)
Allocated storage > 5GB.
Leave everything else on the storage section as default

DB Storage

Step 9

Connectivity
Virtual private cloud (VPC) > select the default vpc or create a new vpc.
Subnet group > select default subnet or use created subnet.
Public access > Yes
VPC security group > choose existing or create new, if you haven't created one before.
Select Existing VPC security groups.
Leave Availability Zone as No preference.

Amazon RDS Connectivity

Step 10

Database authentication
Database authentication options > select Password authentication.
Click Create Database

You have successfully created a Postgres Database, using the Amazon RDS service on the AWS console.

Step 11

Edit Inbound Security Rules
After creating the database you would be redirected to the database dashboard.

Scroll down to the connectivity & security section.
Under security, click on the VPC security groups.

VPC security groups

When you click on the security groups, it would redirect you to EC2 > Security groups.

Security group

click on the security group > click on inbound rules and edit it to the below configuration.

edit inbound rules
Type should be PostgreSQL, on port 5432, IP 0.0.0.0/0 and ::/0

Now lets access our database on using Postbird.

If you don't already have Postbird installed, you can download it here

Now, launch Postbird, input your credentials, use your newly created database endpoint as host. set Port to 5432.
Insert your username and password. leave Database and Start Query empty.
Click on Test Connection, if it returns Successfully connected, then click on save & connect.

Postbird connection

You should be redirected to an interface that looks like this.
Postbird GUI

Thats not all, we need to be able to access the database using the AWS CLI on the terminal.

Firstly, we need to install psql, if you don't have it installed already, you can get it here

Open your terminal, use the following command template.

psql -h yourRdsEndpointUrl -U skywalker postgres

Example:

psql -h postgres.cwunlr23syoo.us-east-1.rds.amazonaws.com -U skywalker postgres
Enter fullscreen mode Exit fullscreen mode

Note: Change yourRdsEndpointUrl, with your original endpoint url.
change skywalker to your username.

Lets explain the above command line.
psql calls the postgresql command, -h means Host, then you insert your RDS endpoint. -U means user, the postgres, is the database name.

After you have entered the above command, if all your credentials are correct, you should have access to the postgres cli.

Enter this command to view a list of databases

\list
Enter fullscreen mode Exit fullscreen mode

psql cli

We have successfully created a postgresql, resource on the cloud, using the Amazon RDS service.
We also installed, Postbird, inputted the appropriate credentials to access the cloud hosted database using Postbird GUI.

Went a step further to install the psql CLI on our local machine, inputted the correct credentials to access our newly created database, in our terminal.

If this article has helped you gain insight on Amazon RDS (postgresql), please like, share and subscribe.

Thank you.

Top comments (0)