PostgreSQL dump and restore from RDS Amazon

In this article, we shall see how to take the dump of our database from our RDS server and restore it. RDS takes backup of our database almost every day and sort them by dates.

Choose the date that we want and create a snapshot. This will literally create a clone of our RDS server, but with the data on the day that we chose.

Once the server is created. We can login to the remote server and take a dump of the db by running the following command.

Taking dump of the database

pg_dump -h host -p 5432 -U username test > database.sql

pg_dump – postgreSQL command to take dump.

host – host or IP address

username – DB username

test – Name of the database

database.sql – Output file name

On successful execution of the command the file will be saved to the same directory from where we executed this command.

Note: Sometimes the postgreSQL version on the server and on our local system will mismatch and we may be unable to take a dump. In that case we have to upgrade the postgreSQL version on our system.

We can update the postgreSQL in our system by running the following command.

sudo apt-get install wget ca-certificates

wget --quiet -O - 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-get install postgresql postgresql-contrib

Restoring the database

Run the following command to restore the data from the dump to the database.

psql --host=host --port=5432 --username=user --password --dbname=database

psql – The later versions require psql and not pg_restore

The rest of the command names and definitions are already given above.

Conclusion

Hope this article was helpful. For more articles keep reading our blog. If you have any doubts ask them in the comments section.

Happy coding!

Check out these amazing python books on amazon.