How to copy a production AWS RDS to your local PostgreSQL database.
- 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
- 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.
- 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>
- 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