How to backup AWS RDS Postgres database to a file?

  1. 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.
  2. 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)
  3. 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>


© 2023 Abhinav Saxena   •   Powered by Soopr   •   Theme  Moonwalk