[Django]-How to use pg_restore with AWS RDS correctly to restore postgresql database


Ok, since this is apparently useful to some I will post – to the best of what I remember – an answer to this. I will answer this more broadly and not too AWS-specific because a) I don’t use this instance anymore and b) I also don’t remember perfectly how I did this.

But I gained experience with PostreSQL and since AWS RDS was also just a postgres instance the steps should work quite similar.

Here are my recommended steps when restoring a postgreSQL DB instance:

  1. Pull the backup in a .dump-format and not in .sql-format. Why? The file-size will be smaller and it is easier to restore. Do this with the following command:

pg_dump -h <your_public_dns_ending_with.rds.amazonaws.com> -U <username_for_your_db> -Fc <name_of_your_db> > name_for_your_backup.dump

  1. Now you can restore the backup easily to any postgreSQL instance. In general I’d recommend to set up a fresh DB instance with a new username and new databasename. Let’s say you have a DB that is called testname with superuser testuser. Then you can just do:

pg_restore --no-owner --no-privileges --role=testuser -d testname <your_backup_file.dump>

And that should restore your instance.

When restoring to AWS or to any remote postgreSQL instance you will have to specify the host with the -h-flag. So this might be something like:

pg_restore -h <your_public_dns_ending_with.rds.amazonaws.com> -p 5432 --no-owner --no-privileges --role=testuser -d testname <your_backup_file.dump>

If you have a DB-instance running on a remote linux server, the host will be be your remote IP-address (-h <ip_od_server>) and the rest will be the same.

I hope this helps. Any questions please comment and I’ll try my best to help more.


This command worked for me, given your machine has access to the rds or being given access to the machine through aws security groups

psql -h your_public_dns_ending_with.us-east-2.rds.amazonaws.com -U yourusername -d db_name_to_backup_to < pg_dump_backup_file.sql

Leave a comment