How to backup AWS RDS Postgres database to a file?

January 2023

Allow your machine to access RDS - modify your database RDS instance security group to do that.
- Add your ip to the relevant security group that has access to RDS.
Make a copy of the database using pg_dump
- $ pg_dump -h <RDS dns> -U <RDS username> -w -f <name of dump file .sql> <name of my database>
- If you don’t specify -w flag, you will be prompted for the password.
- However, pg_dump doesn’t authenticate with the password if your RDS username doesn’t match with your machine username. This is very likely to happen.
- To fix this, create an environment variable PGPASSWORD with the password and specify -w as an option. pg_dump will authenticate accordingly.
- Output will be a dump file(.sql)
To restore that dump file to a database, do the following.
- $ psql -U <postgresql username> -d <database name> -f <dump file that you want to restore>

https://gist.github.com/syafiqfaiz/5273cd41df6f08fdedeb96e12af70e3b
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/