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.
Table of Contents
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