How to backup AWS RDS Postgres database to a file?
- 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.
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>