Once the data is restored to your local PostgreSQL instance, you can use the fly pg import command to import the data from your local instance to the Fly database. You’ll need to provide the DSN for your local PostgreSQL instance:
Thanks for the response @francoab . I actually did try that technique after writing the original message, but import said it could not resolve hostname “localhost” - I think that’s because the command is ultimately pulling from the fly cloud to FQDN - it can’t reach localhost.
So then I figured out another technique - used fly sftp to transfer the dump file to the db server, then shell’d into the db server, and tried to pg_restore from there. But that didn’t quite work either - psql \l did not show that my database actually lived there! So I’m a bit confused about why I was giving a connection string to a db that doesn’t exist in the db server I was issued.
It’s kind of interesting figuring all of this out, but honestly it’s starting to feel like a lot of crazy workarounds for missing tooling that I expect to just “be there.” On the other hand, I’m on the free plan so am not allowed to complain
Hi @shacker! fly pg import works by creating a new, temporary Fly Machine in your database app that runs pg_dump and psql remotely. (We could certainly make this clearer.) So you’re right, it won’t be able to reach your local machine.
As an alternative to copying the dump via SFTP, you could also try the following:
fly proxy -a myapp-shacker-db 5432:5432
This will start a proxy that forwards local port 5432 to your Postgres app. You can then run pg_restore or psql locally against localhost.
This is a totally valid feeling, and I’d be happy to hear more feedback about this! (Just remember that Fly Postgres is not managed Postgres, so depending on what you’re looking for you may be interested in a managed option—see the list on that page.)