pg_dump for Dum(p)ies

Two months ago, I followed a tutorial to deploy a Phoenix app, say my-app, using flyctl launch; which, asked me if I wanted to create a Postgres database, and I said Yes, and it created my-app-db.

Now, I’m relearning Fly, and I want to issue a pg_dump command and retrieve the backup file. I’ve searched the forum and docs but am confused.

This post mentions doing pg_dump somhow via fly proxy but no details are given; I read the docs on fly proxy but have no idea how to proceed.

Am I supposed to use fly proxy to proxy through an instance of my-app? How to use fly proxy and fly ssh together?


1 Like

Just figured this out, use directions from here to proxy your local connection.

I used this command to proxy since I had postico connected:

flyctl proxy 15432:5432 -a <postgres-app-name>

Then run:

pg_dump -h localhost -p 15432 -U postgres DATABASE_NAME > BACKUP_FILE_NAME

You’ll be prompted to enter your DB password and voila!, backup is saved to the current directory


Is it possible to do the inverse operation, - restore a DB from a local dump file? (see How to execute a DB dump created from Heroku PG database). Thank you.