Table of Content
- What is Database?
- What is AWS RDS?
- Database Created Manually vs Database Created using AWS RDS
- What is PostgreSQL?
- Creating Amazon RDS with PostgreSQL Manually
- Important Parameters for setting up Postgres
- Security Required in Setting up Postgres
- Connecting to DB instance using Postgres engine with Master Password from pgadmin4
- Troubleshooting the PostgreSQL connectivity issues.
What is Database?
All of you might be very well aware of what database is used for , right ? But Let me share few examples of it. Suppose you have an company and you want to store all the information of your employees, such as Name, Employee ID , Employee Address , Employee Joining date , Employee benefits , where do you store them ? So , the Answer is database where you keep your all data securely and efficiently.
What is AWS RDS?
“Amazon Relational Database” is a web service which helps in setting up and maintaining the relational database in AWS. What I mean by maintaining here is : we can configure our RDS in such a way that it can scale up or scale down whenever required , it has resizable capacity i.e you can configure different instance size and can be load balanced and fault tolerant in nature. All depends on how we would like to configure. The service helps us in solving by removing lots of tedious management tasks than setting up manually and saving lot of our time. RDS supports 6 database engines: Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database and SQL Server.
|Database Created Manually||Database Created using AWS RDS|
|You need to buy Sever||No need to buy any hardware|
|You need to configure backups manually||AWS RDS takes care of backups automatically|
|To make it highly available you need to configure many things||You just need to choose highly available option|
|You cannot utilize IAM if you database is not build using RDS or on AWS||You can configure access using IAM|
|Less secure , as you get SHELL access||More secure , as you don’t have access to SHELL|
What is PostgreSQL?
PostgreSQL is an open source relational database system which has capability to handle heavy workload and can scale systems very easily. It can run mostly on all Operating systems. It is an open source but highly extensible like you can define your own data types, functions .
Some of the features of PostgreSQL are:
- Text Search
- Data Integrity
- Good Performance
Creating Amazon RDS with PostgreSQL Manually
Prerequisites: Make sure you have two things
a) Amazon AWS account , you can create your account via https://signin.aws.amazon.com if you don’t have it already.
b) pgAdmin utility to connect to PostgreSQL database instance via https://www.pgadmin.org/download which will be use later in this tutorial once we are done with creation part.
So, Lets go and create our AWS RDS Postgres now !!
Step 1) Sign into your AWS account and on the top of the Page you will see “Search for services, features, marketplace products and docs” , here please type AWS RDS and choose RDS.
Step 2) On Left Panel click on Databases and then click on Create database.
Step 3) After you click on Create database choose method as Standard create , Engine as PostgreSQL and Latest Version : PostgreSQL 12.5-R1 and select FREE tier from Templates.
Step 4) Lets provide the database name , master username , master password and keeping all the storage values as default .
*According to your need and setup you can Provide storage.
Step 5) Lets Configure the Connectivity now : “Availability & durability and connectivity “
Important Note : Security group is used to allow your Inbound and Outbound traffic. In order for us to connect to RDS instance we will modify the inbound and outbound rules of default security group which we are using to create the RDS instance.
Path to reach security group : On top of the Page you will see “Search for services, features, marketplace products and docs” , here look for EC2 and enter and then you will see Security groups under Network and security. Choose the default security group which is linked with your VPC.
Step 6) Make sure the default settings for “Database authentication” and “Additional configuration” are selected.
Step 7) We are now ready : Lets click on Create Database
**** It usually takes few mins for your RDS instance to be launched.
Here we GO !! Our Database instance is successfully created now
Step 8) Lets Open pgAdmin and connect to our “myrds” database instance
After you open pgadmin you will see Servers on the left side. Right click and servers and create a new server
Step 9) Lets Open pgAdmin and connect to our “myrds” database instance
After you open pgadmin you will see Servers on the left side. Right click and create a new server
In General Tab select name as “myrds” and in Connection Tab provide the Host i.e endpoint URL of your database instance ( You can find this in Connectivity and security under Databases in AWS RDS
Step 10) Click “SAVE”
Step 11) Now, let’s go ahead and create a database within the server. Right click on “Databases” and select “Create” and then “Database…” Give the database a name and save when done.
Step 12) Alright, now our new database “testing” is also created successfully.
Troubleshooting the PostgreSQL connectivity issues.
|Error while connecting||Solution|
|Database name doesn’t exist while connecting||Try connecting the default database i.e postgres|
|Couldn’t connect to server: connection time out||1.Check if you entered correct host name|
2. Make sure DB is publicly accessible
3. Check if you are giving correct port i.e 5432
4. Check the Inbound and outbound rule of default security group