Can't make `fly pg import` to work 😓

[Humberti]

The fly pg import command looks amazing, but I must have something going on with my setup that is preventing the import to work.

fly pg import postgres://postgres:postgres@localhost:5432/arpm_development -a agilebooking-db

outputs

[info] Running pre-checks...
[error] failed to connect to source: failed to connect to `host=localhost user=postgres database=arpm_development`: server error (FATAL: database "arpm_development" does not exist (SQLSTATE 3D000))
Waiting for ephemeral machine e2867426b35368 to be destroyed ... done.
Error: failed to run ssh: ssh shell: Process exited with status 1

I’m checking the database exists, I have the same pg version (13), postgresql.conf is listening to all (*) addresses, pg_hba.conf is allowing all users to all databases from all addresses, with trust method.
I followed, I think, all the tips I found online.
I’m clueless on how to trace this down. Any tips?

[mayailurus]

@Humberti

I must have something going on with my setup that is preventing the import to work.

Hi… It’s not you; fly pg import doesn’t work with localhost, unfortunately.

@Humberti

fly pg import postgres://postgres:postgres@localhost:5432/arpm_development -a agilebooking-db
                                           ~~~~~~~~~

(I made that same mistake myself, initially.)

The reason is that the main import job is done by a temporary machine that runs within the Fly.io internal network:

@Humberti

Waiting for ephemeral machine e2867426b35368 to be destroyed ... done.

Most people end up instead using pg_dump locally, SFTPing the resulting file up to the server, and then SSHing in to restore from there.

Alternatively, you can try making a fly proxy tunnel on port 15432 and avoid the SFTP step, at the expense of potentially some network glitchiness (depending on how good your local ISP is).

(Fly Postgres generally expects you to be familiar with these kinds of steps; that’s part of what they mean by “not managed”, .)

(Hopefully, the upcoming Fly-managed Definitely Managed Postgres will come with very extensive importing instructions, since a lot of people are going to want to do that suddenly—I expect.)

[Humberti]

That’s awesome. Thanks for such a documented answer.

I was investing too much time in the wrong approach with the fly pg import.

As you suggested, I used fly sftp shell to put my file in the server with put pg_dump_backup_file.db /tmp/local.db and then fly ssh console and pg_restore -d $DATABASE_URL /tmp/local.db and

I’ll try to contribute to the fly pg import documentation

[Humberti]

One more thing. Since I’m migrating from heroku and I have a PostgreSQL connection string to there I was able to try fly pg import --region mad --clean <heroku_postgres_conn_string> and it worked like a charm.