fly pg import questions

Questions / Help
1

I want to transfer a small pg dump file into an existing fly db instance. I looked at wireguard recommendations but was not successful. Then I realized there is fly pg import and tried this:

fly pg import /local/path/file.dump -a myapp-shacker-db

where myapp-shacker-db exists and is running. Two surprising things happened:

  1. It prompted me to select a VM size, implying that it was creating a new db VM even though I had specified one to import into.

  2. It said it “could not parse” the path to the dump file. It wants a DSN. Would it work if I imported to a localhost pg and then use a localhost DSN?

Seems like there’s got to be a straightforward way to import existing data to a fly db, but I’m missing something.

2

@shacker

The fly fly command is designed to import data from a remote PostgreSQL database, not from a local dump file. That’s why it’s expecting a DSN (Data Source Name) instead of a local file path.

To import your local dump file into the Fly database, you can follow these steps:

  1. First, restore the dump file to a local PostgreSQL instance. You can use the pg_restore command for this:

    pg_restore -U your_local_username -d your_local_database /local/path/file.dump

  2. 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:

    fly pg import postgres://your_local_username:your_local_password@localhost:5432/your_local_database --app myapp-shacker-db

More info:

3

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 :slight_smile:

4

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.)

5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.