How to copy a production AWS RDS to your local PostgreSQL database.

  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 postgres -c "DROP DATABASE <database name>;"
    • psql -U "<my username>" -d postgres -c "CREATE DATABASE <database name>;"
    • postgres in this case can be any local DB 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