DEV Community

Cover image for Setting up a PostgreSQL database in AWS RDS and connecting it to pgAdmin.
Samuel Mugane
Samuel Mugane

Posted on

Setting up a PostgreSQL database in AWS RDS and connecting it to pgAdmin.

Introduction

PgAdmin is a client-side platform for adminstration and development of PostgreSQL database. It simplifies the visualization of schemas, logs and other relevant information based on SQL standards. Amazon has a great relational database hosting service known as Amazon Relational Database Service (RDS). The article focuses on creating a PostgreSQL database in RDS and connecting it pgAdmin.


Creating a PostgreSQL database in AWS

  • Setup and login to your AWS account as ROOT user. Go to services, choose database and select RDS.
    screenshot

  • You will be redirected to a dashboard as shown below. Click on create database.

Screenshot

  • Select PostgreSQL under engine options and choose templates as shown below. Use Free Tier if setting up the database for personal projects.

Screenshot

  • Scroll down to settings and set master username and password. Copy these credentials to the clipboard as they will be used to make the pgAdmin connection.

Screenshot

  • Scroll down to connectivity and select "Yes" for public access. This will allow the database to accept the connection from local setup.

Screenshot

  • Scroll down and click on "create database". The process will take a few minutes and you will be directed to the page below. Copy the endpoint to the clipboard for later reference.

Screenshot

  • Under security, click on the VPC security groups and you will be redirected to an EC2 console as shown below.

Screenshot

  • Click on security group ID and you will be redirected to the page below.

Screenshot

  • Under inbound rules, check if there are rules for PostgreSQL that accepts TCP connections. If there isn't click on "edit inbound rules". Click on "Add rule". Under type, select PostgreSQL. Under source, select custom. Under the search input, select 0.0.0.0/0 and click on "Save rules".

Screenshot

The PostgreSQL database has been successfully been created and ready to accept connections from a local host.


Installing pgAdmin and making RDS connection

First we need to install pgAdmin to make the connection to our PostgreSQL database hosted in AWS RDS.
Here is an installation guide for installing pgAdmin on Ubuntu, Mac and Windows.

After installing pgAdmin, launch it and register a new server as shown below.

Screenshot

Under general, enter server name and move to connection tab. Under host name/address, paste the endpoint copied from AWS-RDS console. Enter the port set in RDS. Enter the master username set on RDS under username and corresponding password. Under advanced, set connection timeout (seconds) to 1000 secs to avoid timeout during connection. Leave every other configuration in its default.

After the connection is accepted, the pgAdmin dashboard will look as follows;

Screenshot

Now you can view tables created under schemas/tables.

Conclusion

PgAdmin offers a versatile interface to view database information while AWS RDS offers a great service to host databases to the cloud for better security and ease of access.

Top comments (0)