Creating an AWS RDS PostgreSQL Database with pgAdmin

In application development there is little doubt that one of the most useful combinations is combining an object oriented language with a relational database. In this article, we will demonstrate this with the following steps:

1. Install a Postgres pgAdmin database client
2. Create an RDS Postgres instance in your AWS environment
3. Use the pgAdmin console to create a table and query it

With Amazon RDS Free Tier, AWS makes it possible for you to create relational database without much expense. This is an option who need to fulfill the use case of a relational database.

PostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a stron reputation for reliability, feature robustness, and performance.

pgAdmin

Installing pgAdmin

pgAdmin is a popular postgres database client that we will use in this article.

Download pgAmin for your computer https://www.pgadmin.org/download/

You should be able to launch the application and then set the master password for pgAdmin

Now you have setup the pgAdmin, we can now set up an RDS instance.

Postgres RDS Creation

For this demo, you will be required to have an AWS Account setup. You can follow the instructions located at Getting started with AWS, Java 11 (Amazon Corretto), Eclipse and AWS Toolkit or verify your own setup(disregard the Eclipse and Java if you are not using Java.

Once you have logged into your AWS Console you can go to the RDS Dashboard and press “Create Database”

Select Standard create, PostgreSQL and Free Tier to start

Configure the Master username and master password as you wish, for this demonstration I will use a master username of “myusername” and a master password as “mypassword”

Set the Public access value as Yes(This so that we can access the database from our pgAdmin client and Lambdas for the subsequent article at the end). Further security can be done by limiting access to certain IPs and configuring specific access for Lambdas to the RDS VPC but not in scope for this article.

Press Create Database (It takes about 10 minutes to create)

Navigate to the dashboard of your RDS instance and click on the default security group.

1. Click the Security group id

2. Click into the default security group and press “Edit inbound rules” and ensure that all traffic external traffic for IPv4, IPv6 and IPv4 (Your IP)

Tip: If you do not allow have the 3 security group rules above, you can encounter the following error when trying to access your Postgres instance from pgAdmin.

pgAdmin error observed if security group is not configured properly

Testing the RDS instance with the pgAdmin database client

1. Get the RDS endpoint information from the AWS Console and note the username and password created above.

2. Open pgAdmin and Register Server with the RDS endpoint and it’s credentials.

Right click Servers and Register/Server

Enter the Name of RDSPostgresInstance for your server in the General tab and then navigate to the Connection tab. Fill in the RDS endpoint in the Host name field with the username and password.(myusername/mypassword)

You should then be successfully logged in and see the following screen in pgAdmin

Let’s now create a database called santasworkshop by right clicking databases and selecting Create -> Database

We should then be able to open the Query Tool for the database santasworkshop by right clicking over santasworkshop

Now that we have the Query Tool open we can create our first table by executing the following script in the Query Tool

CREATE TABLE LETTERHISTORY
(
ID SERIAL PRIMARY KEY,
LETTERID VARCHAR(50),
LETTERHISTORYJSON VARCHAR(600),
CREATED TIMESTAMP
);

We should be able to insert records by executing the following command in the Query Tool

INSERT INTO LETTERHISTORY (LETTERID, LETTERHISTORYJSON, CREATED) VALUES ('LETTER#4854', '{}', NOW());

We should be able to query records by executing the following command in the Query Tool

SELECT * FROM LETTERHISTORY

Resource cleanup:

Please delete your RDS instance to stop any additional costs to your AWS Account if you don’t require it any further.

If you would like to create an AWS Java Lambda that connects to this database to make queries and updates, please proceed to AWS Java Lambda accessing an AWS RDS PostgreSQL Instance with CDK