fly.io - Dump your PostgreSQL DB locally

Step 1: Proxy the PostgreSQL port

First, you need to proxy the PostgreSQL port from your fly.io app to your local machine. Open a terminal and run:

fly proxy 15432:5432 -a your-app-db

This command forwards port 5432 from your fly.io PostgreSQL instance to port 15432 on your local machine. Keep this terminal open while you perform the dump.

Step 2: Dump the database

Open a new terminal window and run pg_dump to create a backup of your database:

pg_dump -h localhost -p 15432 -U postgres your_database_name > dump.sql

This will create a file called dump.sql with the complete database dump. You may be prompted for the PostgreSQL password.

Step 3: Close the proxy

Once the dump is complete, go back to the first terminal and close the proxy by pressing Ctrl+C.

Step 4: Restore locally

To restore the dump into your local PostgreSQL database, run:

psql -h localhost -U postgres -d your_local_database < dump.sql

Make sure your local PostgreSQL server is running and the target database exists before running this command. You can create a new database with:

createdb -h localhost -U postgres your_local_database