[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

2. 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.