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.

Step #2: Select Database creation method.

Step #3: Choose PostgreSQL Database Engine.

Step #4: Select Database Templates

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

Step #6: Select DB Instance Size

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

Step #8: Select Availability and Durability.

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

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

Step #11: Select Database authentication options.

Step #12: Click on Create Database.

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

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

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

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