How to copy production database on AWS RDS(postgresql) to local development database.

how-to-copy-remote-postgresql-to-local.md

1. Make a copy of the database using pg_dump
   • $ pg_dump -h <public dns> -U <my username> -f <name of dump file .sql> <name of my database>
   • you will be asked for postgressql password.
   • a dump file(.sql) will be created
2. Clean the existing database by recreating it.
   • psql -U "<my username>" -d <some local database name> -c "DROP DATABASE <database name>;"
   • psql -U "<my username>" -d <some local database name> -c "CREATE DATABASE <database name>;"
3. The dump file might contain references to the original user priveleges. In this case you will have to do one of the two things:
   • Create the same user locally: psql -U "<my username>" -d <database name> -c "CREATE USER <original postgresql username> SUPERUSER;"
   • Replace all mentions of the old username with a new one in your <name of dump file .sql>
4. Restore that dump file to your local database.
   • but you might need to drop the database and create it first
   • $ psql -U <postgresql username> -d <database name> -f <dump file that you want to restore>
   • the database is restored

#####ref

1. http://stackoverflow.com/questions/31881786/how-to-pg-dump-an-rds-postgres-database
2. http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/