In my previous tutorial Database Backup I have discussed Backup and Restore a PostgreSQL Database. In this tutorial I will discuss How to Backup and Restore a AWS RDS PostgreSQL Database.

Amazon Relational Database Service Amazon RDS makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching and backups. It frees you to focus on your applications so you can give them the fast performance, high availability, security and compatibility they need. To backup AWS RDS postgresql database we follow the following steps:

Configure RDS Security Group

Change your database RDS instance security group to allow your machine to access it. Add your ip to the security group to access the instance via Postgres. Follow the steps:

  • Go to your RDS Dashboard, select Instances and open the instance you want to connect to. AWS RDS Security Group
  • Look for a line like this : Security Groups rds-launch-wizard rds-launch-wizard (sg-0ef8c1f5c707d7e05)( active )(see the upper image). You should be able to click on rds-launch-wizard (sg-3e9axxx) which leads you to the EC2 Dashboard in the Security Groups panel. AWS RDS Security Group Inboud Tab
  • Select your security group and open the Inbound Tab in the lower panel(see the upper image).
  • Click on Edit and Add Rule. Don’t forget to set the Port Range to 5432 and select My IP in the source.
  • Click on Save.
  • You should be able to connect to your RDS Instance from your local IP.

We will use pg_dump tool to backup database. pg_dump dumps a database as a text file or to other formats. It will dump all the contents of a selected database into a single file. We need to run pg_dump in the command line on the computer where the database is stored. So if the database is stored on a remote server, you will need to SSH to that server. Here I will discuss two of of backup database:

Database Backup

We backup database as a plain-text file format containing SQL script using pg_dump. Run the following command in the remote server terminal:

sudo pg_dump -h <public_dns> -U <db_user> -f </path/to/your/file/name_of_dump_file.sql> <db_name>

Here,

  • -h to specify AWS RDS public dns Endpoint.
  • -U to specify which user will connect to the PostgreSQL database server.
  • db_user means database username
  • -f is used to specify the output format of file.
  • db_name means database name
  • name_of_dump_file means backup database file name
  • .sql means backup database file as plain-text file containing SQL script.

If your AWS RDS public_dns is test_db.cnxjhgdlehkxv.ap-southeast-1.rds.amazonaws.com, db_user is khan, db_name is test_db and want to save the database in Downloads folder then

sudo pg_dump -h test_db.cnxjhgdlehkxv.ap-southeast-1.rds.amazonaws.com -U khan -W -F t test_db > /home/khan/Downloads/bk_test_db_18_05_2018.sql

bk_test_db_18_05_2018.sql contains all the SQL queries that are required to restore your database. You will find your dump file in the path you provide. Database backup part is done, lets move on next restoring part.

Copy Backup Database to Local

To restore database we need to copy the remote server backup dump file locally. For this run the following command locally:

scp -i </path/to/your/file/test_db.pem> username@remote:/file/to/send /where/to/put

If your username is user and remote server is 171.16.220.227 then

scp -i /home/bs086/Downloads/test_db.pem user@171.16.220.227:/home/khan/Downloads/bk_test_db_18_05_2018.sql /home/khan/Downloads

Now go to your Downloads folder you will see this bk_test_db_18_05_2018.sql file.

Database Restore

We will use psql for restoring from a plain SQL script file created with pg_dum. Run the following command in the local terminal to restore your plain-text file containing SQL script:

sudo psql -U <db_user> -d <new_created_db_name> -f </path/to/your/file/dump file that you want to restore>

Here,

  • -U to specify which user will connect to the PostgreSQL database server.
  • db_user means database username
  • -d to specify which new created database you will restore your backup database.
  • -f is used to specify the output format of file.

If your db_user is khan, newly created db is new_test_db and backup file is bk_test_db_02_05_2018.sql then run following command

sudo psql -U khan -d new_test_db -f /home/khan/Downloads/bk_test_db_02_05_2018.sql

After entering the command you will see some tamil/telegu programming UI. Now go to your management tool for PostgreSQL like pgadmin, [datagrip](https://www.jetbrains.com/datagrip/ “”) view and you will see the restore database.

See my the previous tutorial How to Backup and Restore a PostgreSQL Database

Some useful link that I have followed:

  • https://www.axiomq.com/blog/backup-and-restore-a-postresql-database/
  • http://stackoverflow.com/questions/31881786/how-to-pg-dump-an-rds-postgres-database
  • http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/

Note: If you have any query or any upgradation of my writing or any mistakes please comment and suggest me. You are warmly welcomed always.