How to Set up a PostgreSQL Database on Amazon RDS

If you new to AWS RDS or planning to create your first AWS RDS database instance then you are at the right place to learn one of the most popular and widely used Database engine PostgreSQL.

In this tutorial you will learn how to set up a PostgreSQL Database on Amazon RDS in the Amazon cloud from scratch and step by step.

Still interested? Lets get into it.

Join 28 other followers

Table of Content

What is Database?

If you want to store all the information of your employees securely and efficiently such as Name, Employee ID, Employee Address, Employee Joining date, Employee benefits, etc then you need a database.

Basic Database diagram
Basic Database diagram

What is AWS RDS?

Amazon Relational Database (AWS RDS) is an Amazon web service that helps in setting up and configuring the relational database in AWS. With AWS RDS you can scale up or down the capacity i.e you can configure different instance sizes, load-balanced, apply fault-tolerant.

AWS RDS also removes tedious management tasks than setting up manually and saving a lot of our time. AWS RDS supports six database engines: Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, and SQL Server.

With AWS you don’t need to rely on buying hardware, backups, scalability, availability, and it’s more secure than hosting your own database. In the below snap AWS RDS service contains RDS instances and Instances further contain RDS databases and database users & finally you connect them using database clients such as pgadmin4.

Connecting AWS RDS PostgreSQL database from pgadmin client
Connecting AWS RDS PostgreSQL database from pgadmin client

What is PostgreSQL?

PostgreSQL is an open-source relational database system that has the capability to handle heavy workloads, scale systems easily, runs mostly on all Operating systems, and is highly extensible like you can define your own data types, functions. PostgreSQL is one of the most widely used AWS RDS DB engines.

A DB engine is the specific relational database software that runs on your DB instance.

Some of the features of PostgreSQL are listed below:

  • Security
  • Extensibility
  • Text Search
  • Reliable
  • Data Integrity
  • Good Performance

Prerequisites

This tutorial will be step by step and if you would like to follow along, then you must have.

  • Amazon AWS account. If you dont have AWS account create from here.
  • pgAdmin utility to connect to PostgreSQL database instance. To install pgadmin click here.

Creating a PostgreSQL DB instance in AWS RDS

Now that you have a basic idea of what is Postgres database and the benefits of hosting your database on AWS RDS with a database engine like PostgreSQL. Let’s quickly learn how to create a PostgreSQL DB instance in AWS RDS.

  • Sign into your AWS account and and search for AWS RDS in the search box and click on RDS.
Searching for AWS RDS service in AWS Cloud
Searching for AWS RDS service in AWS Cloud
  • Now, in the AWS RDS page click on Create database.
Creating database in AWS RDS service
Creating database in AWS RDS service
  • Further on Create database page choose database creation method as Standard create , Engine as PostgreSQL and Version as : PostgreSQL 12.5-R1 and select FREE tier from Templates.

The Latest vesion of PostgreSQL is PostgreSQL 14.1-R1

Defining all the parameters to create a AWS RDS database engine
Defining all the parameters to create a AWS RDS database engine
  • Next, provide the database name, master username, master password and keeping all the storage values as default .
Specifing the Configuration of database instance
Specifing the Configuration of database instance
Defining storage for database instance
Defining storage for database instance
  • Further in Connectivity section select the Virtual Private Cloud, Subnet group in which you would like to create the AWS RDS instance, Public access as Yes, and select security group as default.

Make sure to allow 0.0.0.0/0 in the Inbound and Outbound traffic in the default security group and subnet group have route to internet so that you can connect to RDS instance from the database client from your browser or local machine.

Defining network connectivity options in AWS RDS
Defining network connectivity options in AWS RDS
  • Now in the “Database authentication” choose Password authentication and finally click on Create database. It usually takes few mins for RDS instance to be launched in AWS Cloud.
Specifying the database authentication method
Specifying the database authentication method

Verifying AWS RDS Postgres database instance in AWS Cloud

Now that you have created the AWS RDS Postgres database instance in AWS Cloud, which is great but unless you verify in Amazon Management console you cannot be sure enough. Lets navigate to AWS console and verify the Postgres instance in AWS RDS service.

As you can see the specified mydb instance has been created successfully in AWS RDS.

Verifying AWS RDS Postgres database instance in AWS Cloud
Verifying AWS RDS Postgres database instance in AWS Cloud

Connecting to a DB instance running the PostgreSQL database engine

Now that you have verified the DB instance running the PostgresSQL in AWS cloud, its time to connect using pgAdmin client from your machine. To connect

  • Open pgAdmin on your machine and click on Create and further Server.
Connecting to PostgreSQL database instance from pgadmin
Connecting to PostgreSQL database instance from pgadmin
  • In the Create-Server Pageunder General tab select name as “myrds”. Next, navigate to Connection tab and provide the all the details such as Host i.e endpoint URL of your database instance, Port, , username and passsword as shown below.
Defining Name of database to connect
Defining Name of database to connect
Defining connection details of the PostgreSQL database instance
Defining connection details of the PostgreSQL database instance
  • After you provide all the details and click on save button, the newly created database will be visible under the severs as shown below.
checking the database instance
checking the database instance
  • Finally under myrds database instance create a database by right clicking on Databases and select Create ➔ Database and provide the name of the database you wish to create.
Creating database instance AWS RDS database instance
Creating database instance AWS RDS database instance
  • As you can see below the testing database is created successfully. 
Viewing the newly launched database in AWS RDS database instance
Viewing the newly launched database in AWS RDS database instance

Conclusion

In this tutorial you learned about one of the most widely used AWS RDS database Postrgres and how to create it in Amazon management console.

So what do you plan to store in this newly created database instance.