How to Create Postgres Database in AWS and connect

In this article, We are going to perform, How to Create Postgres Database in AWS and Connect to RDS from EC2, Backup AWS Postgres DB using pg_dump, Restore the Postgres RDS backup using pg_restore.

Create Postgres Database in AWS

Step #1: Go to the AWS Management console , Enter “RDS” in the find services search area and click on it, Click on Create Database.

click on create database

Step #2: Select Database creation method.

select database creation method 2

Step #3: Choose PostgreSQL Database Engine.

select postgres sql 3

Step #4: Select Database Templates

select templates 4

Step #5: Enter DB Instance identifier, Username and Password

input master username password 5

Step #6: Select DB Instance Size

select db instance size 6

Step #7: Select storage type , size, storage autoscaling and Maximum storage threshold.

select storage 7

Step #8: Select Availability and Durability.

select az 8

Step #9: Select VPC, Subnet group, public access type.

select connectivity 9

Step #10: Select VPC Security Group, availability zone and DB port.

select vpv and port 10

Step #11: Select Database authentication options.

select database authentication 11

Step #12: Click on Create Database.

click on create database 12

once you clicked on Create database, you will redirected to Amazon RDS dashboard and status is “Creating”.

creating postgres RDS database

You can click on DB identifier, you can see summary of postgres RDS and status is “creating”.

click on DB identifier and status is creating

Once your Postgres RDS is ready, Use Endpoint and port to connect Postgres RDS.

click on endpoint and port

Connect to RDS Postgres from command line:

Once your PostgreSQL RDS is ready, below are possible ways to connect to RDS postgres from command line.

If you have created PostgreSQL RDS using public access , open the DB port in security group then you can connect it from any EC2 instance using command line using PostgreSQL RDS Endpoint and port.

If you created PostgreSQL RDS with no public access then you create an EC2 instance in same VPC , open DB port, connect using Endpoint and port.

Connect to RDS from EC2 :

Install the postgres SQL Client on EC2 Instance as per your RDS version, below are steps to install postgres sql client 11 on Ubuntu OS.

sudo apt-get install curl ca-certificates gnupg
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
sudo apt-get update
sudo apt install postgresql-client-11

Now connect to Postgres RDS using below command

psql --host=mypostgresql.c6c8mwvfdgv0.ap-south-1.rds.amazonaws.com --port=5432 --username=pgdev --password --dbname=demo

How to connect to AWS Postgres Database using pgadmin:

Backup AWS Postgres DB using pg_dump

To take backup of DB from AWS Postgres RDS using pg_dump, first connect to posstgres RDS using above command, use below command to take backup

Syntax:

pg_dump -h <public dns> -U <my username> -f dump.sql <name of my database>

Example:

pg_dump -h mypostgresql.c6c8mwvfdgv0.ap-south-1.rds.amazonaws.com -U pgdev -f demo.sql demo

you will be asked for Postgres RDS password and demo.sql file will be created.

Restore the Postgres RDS backup using pg_restore

Use below command to restore database to different RDS

Syntax:

pg_restore -h <host> -U <username> -c -d <database name> <filename to be restored> 

Example:

pg_restore -h remotepostgresql.b7c8mwvfdgv0.ap-south-1.rds.amazonaws.com -U pgdemo -c -d dev demo.sql

Conclusion:

In this article, We have covered, How to Create Postgres Database in AWS and Connect to RDS from EC2, Backup AWS Postgres DB using pg_dump, Restore the Postgres RDS backup using pg_restore.

Related Articles:

How to Create Ubuntu Instance in AWS [7 Steps]

How to connect to AWS EC2 Instance using MobaXTerm

How to Enable Password Authentication for AWS EC2

How to Connect EC2 Instance using Putty

How to Transfer files to AWS Instance using WinSCP [2 Steps]

How to Create AWS DocumentDB and Connect [3 steps]

How to Install MySQL 5.7 on Amazon Linux 2

AWS Create Instance from snapshot

AWS Create EC2 Instance from Snapshot

Monitoring EC2 Instances using CloudWatch

About DevOps Hint

DevOps Hint Founded in 2020 Community Site where you can find about How to Guides, Articles and Troubleshooting Tips for DevOps, SRE, Sysadmins and Developers.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share via
Copy link