Sync production database to local

Example with the holmen-quiz app

Proceed from in your project folder.

Fly app name: holmen-quiz
Fly database app name: holmen-quiz-db
Local database name: holmen_quiz_backend_2022_development

1. Preparation

Start proxy:

fly proxy 15432:5432 -a holmen-quiz-db

Run in new terminal window:

fly ssh console -a holmen-quiz
# Connecting to xxx... complete
echo $DATABASE_URL
> postgres://username:password@...
exit

Copy username and password into local terminal and run:

pg_dump postgres://<username>:<password>@localhost:15432 --no-owner > dump.sql

2. Restore to local database from dump

Clean existing database:

dropdb --if-exists holmen_quiz_backend_2022_development
createdb holmen_quiz_backend_2022_development -T template0

Restore database dump:

psql -d holmen_quiz_backend_2022_development -f dump.sql

3. Cleanup

Remove database dump and close database proxy started in step 1.

rm dump.sql