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>

Reference

  1. https://gist.github.com/syafiqfaiz/5273cd41df6f08fdedeb96e12af70e3b
  2. http://stackoverflow.com/questions/31881786/how-to-pg-dump-an-rds-postgres-database
  3. http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/
© 2023 Abhinav Saxena   •   Powered by Soopr   •   Theme  Moonwalk