How to execute a DB dump created from Heroku PG database

Questions / Help
1

Before deleting the Heroku app, I created a DB dump ( a file with the extension dump). How is it possible to execute this dump at fly.io Postgres instance to recover all the data? Thank you.

2

I found this command in the flyu.io docs: pg_restore -v -d [FLY_POSTGRES_URI] < [path-to-dump-file].
When setting a Postgres DB, I received its url as follows:
DATABASE_URL=postgres://my_db_name:XXXXXXX@top2.nearest.of.my-db-name-db.internal:5432/my_db_name?sslmode=disable.

I tried to run it as follows from my terminal:

Downloads flyctl auth login
Opening https://fly.io/app/auth/cli/76c5f517473de51dbff9273179985853 ...

Waiting for session... Done
successfully logged in as xxxx@gmail.com
➜  Downloads flyctl postgres connect -a my_app-db
WARN The running flyctl agent (v0.0.441) is older than the current flyctl (v0.0.447).
WARN The out-of-date agent will be shut down along with existing wireguard connections. The new agent will start automatically as needed.
Connecting to fdaa:1:208d:a7b:8a:3c8b:a278:2... complete
psql (14.6 (Debian 14.6-1.pgdg110+1))
Type "help" for help.

postgres=#

The quitted with \q.
Then tried in the same Terminal:

pg_restore -v -d postgres://my_db_name:XXXXXXX@top2.nearest.of.my-db-name-db.internal:5432/my_db_name?sslmode=disable < my_db_dump_file.dump

but it failed with:

no matches found: postgres://.....
3

I’m sorry that I don’t have time to link to the docs right now, but I believe the solution is to use fly proxy to set up a wireguard proxy to your Postgres on Fly. This will do something like expose your Fly Postgres on localhost:15432, and and you can then use that to do the restore.

4

Thank you for the feedback, @zachallaun. Here is what I tried (with no success for the moment):

  • run in a Terminal tab fly proxy 15432:5432 -a my-db-app-name
  • run in another Terminal tab: psql -U postgres -h localhost -p 15432
  • #list my db names to get the exact name of my DB with: postgres=> \l
  • run pg_restore -U postgres -h localhost -p 15432 -d <my-app_db_name> < my_dump_file.dump

There were no errors, no results, nothing. When I checked the database, it was still with no migrated data, just empty.

Any clues?

5

Finally here is what worked for me:

  • run flyctl apps list in your Terminal
  • note the name of the DB app
  • run fly proxy 15432:5432 -a <your-db-app-name> (attention to put the DB app name, not your Rails app)
  • open another tab of the terminal and run psql -U postgres -h localhost -p 15432 (where postgres is the name of the Postgres app user you will use to connect)
  • you will be asked for the password of the database, so enter it to connect
  • once authenticated, you will get a Postgres prompt looking like: postgres=#
  • list of databases with \l command
  • note the database you want to run the pg_restore command for.
  • quit the Posgtres session with \q command to return to your classic terminal session
  • run pg_restore -U postgres -d <you_db_name> -h localhost -p 15432 <your_dump_file_name.dump> (put the path to your dump file)
  • you will be asked for a password for your DB
  • once authenticated, the restore procedure should start.
1 Like
6

Thanks Belgoros, that works for me as well. Really appreciate your contribution.

However in my case the roles of the local DB were different of those in the fresh DB created in fly. That throw a few errors while migrating.
So I deleted the fly DB and created a new one, but now I changed owner and password for those of my local DB. Then when did the pg_restore got no errors and migrated the DB perfectly.

pg_restore -U mylocaldbuser -d flydbname -h localhost -p 15432 mydatabase.dump