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
pg_dump -h <public dns> -U <my username> -f dump.sql <name of my database>
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
pg_restore -h <host> -U <username> -c -d <database name> <filename to be restored>
pg_restore -h remotepostgresql.b7c8mwvfdgv0.ap-south-1.rds.amazonaws.com -U pgdemo -c -d dev demo.sql
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.