How to copy Heroku production database into development environment?

Sometimes you want to run a copy of your production database in development.

It can be really useful for debugging, or experimenting with potentially dangerous operations.

Here’s how you can run your Heroku Postgresql database in development:

• 1 Create Heroku backup

heroku pg:backups:capture --app myappname

• 2 Download Heroku backup (source)

heroku pg:backups:download --app myappname

• 3 Reset local database

rails db:drop
rails db:drop DISABLE_DATABASE_ENVIRONMENT_CHECK=1
rails db:create

• 4 Populate local database from heroku download

(place development database name from database.yml)

pg_restore -h localhost -d myappname_development latest.dump

• 5 run migrations

rails db:migrate

• 6 Remove downloaded database from app repository:

rm latest.dump 

That’s it! Now you have a copy of your production database running in development🥳!

---

Troubleshooting

• Update postgresql

• Create a new postgresql user and password (source)
  

createuser --interactive --pwprompt
yaro
pass

• Specify newly created postgresql user for restoring database and restore

pg_restore -h localhost -U yaro -d myappname_development latest.dump
pass

or

PGPASSWORD=pass pg_restore -h localhost -U yaro -d myappname_development latest.dump

or

set "PGPASSWORD=pass"
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U yaro -d myappname_development latest.dump

---

Adding latest.dump to gitignore:

echo 'latest.dump*' >> .gitignore