It’s the fly proxy command with psql that I listed above.
flyctl proxy 15432:5432 -a my-postgres-app
just hangs for me. No error.
That sets up a proxy from the app port 5432 to local port 15432. Then you can use psql against localhost with port 15432.
Understood. When I set up the proxy on the app port, it hangs. I can’t even get to step two.
It opens up a foreground process that should remain open. You’ll need to use psql in a second terminal.
$ fly proxy 15432:5432 -a my-app
Proxying local port 15432 to remote [my-app.internal]:5432
Ahhh, did not know that. Will try when I’m back.
I’m having the same issues as @wiznaibus here.
I have flyctl proxy 15432:5432 -a my-app
in a terminal and it’s proxying
But when I run
psql -U hora -h localhost -p 15432 my-postgres-db < data.sql
I get
zsh: no such file or directory: data.sql
I have tried doing the same with latest.dump
downloaded straight from heroku and that was saved at the root of my project:
psql -U hora -h localhost -p 15432 my-postgres-db < latest.dump
but this one just hangs Do you know how to get that .sql
file to dump onto my db?
Trying to be clearer this time.
-
Obtain a sql dump file from heroku and have it locally.
-
Proxy the postgres from fly to localhost.
$ fly proxy 15432:5432 -a my-app
Proxying local port 15432 to remote [my-app.internal]:5432
- Use psql like you would normally, but on port 15432. For instance, you should be able to connect, etc.
$ psql -U your_user -h localhost -p 15432
psql #
$ psql -U your_user -h localhost -p 15432 < your_dump_file
...this will load the database dump and give some output...
@dornby - see step 3. If updated the user/db info, make sure you can connect directly first before trying to load the dump file.
@tj1 thank you for your approach
but i’m having trouble
hope you can assist
(this is google translate)
You’ve connected correctly, but you need to use the correct database name.
psql -U postgres -h localhost -p 15432 -d your_database < latest.dump
You can find out the name of your databases by connecting locally and listing them with \l
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+------------+----------+------------+------------+-------------------------
--
main | flypgadmin | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | flypgadmin | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | flypgadmin | UTF8 | en_US.utf8 | en_US.utf8 | =c/flypgadmin
+
| | | | | flypgadmin=CTc/flypgadmi
n
template1 | flypgadmin | UTF8 | en_US.utf8 | en_US.utf8 | =c/flypgadmin
+
| | | | | flypgadmin=CTc/flypgadmi
n
(4 rows)
Wuhu it finally worked for me.
Thank you so much @tj1 for leading me on the way. Here is a precise breakdown of what I did for it to work, with a lot of repetition from @tj1 's advice:
- Get your latest dump by running
heroku login
heroku pg:backups:capture
heroku pg:backups:download
- In one terminal, launch:
fly proxy 15432:5432 -a my-db-app-name
# make sure it's the name of your db-app, not your main app
- In another terminal, list your db names to get the exact name of your DB with
\l
:
psql -U postgres -h localhost -p 15432
postgres=> \l
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+------------+----------+------------+------------+---------------------------
foobar | flypgadmin | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | flypgadmin | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | flypgadmin | UTF8 | en_US.utf8 | en_US.utf8 | =c/flypgadmin +
| | | | | flypgadmin=CTc/flypgadmin
template1 | flypgadmin | UTF8 | en_US.utf8 | en_US.utf8 | =c/flypgadmin +
| | | | | flypgadmin=CTc/flypgadmin
(4 rows)
- Once you have your name, here
foobar
, import your dump withpg_restore
:
pg_restore -U postgres -h localhost -p 15432 -d foobar < latest.dump
And voilà, or at least it worked for me.
@tj1 @dornby
Thanks for the guidance of the two masters
I also succeeded
But I have a new problem.
I use “pgadmin” or “tableplus” software to connect postgresql db.
An error occurs:
could not translate host name “dbname-db.internal” to address: nodename nor servname provided, or not known.
“host name” I have tried the following but failed:
top2.nearest.of.dbname-db.internal
dbname-db.internal
I have closed the fly proxy and connected again, but the same error occurs.
If I turn on fly proxy and use localhost:15432 to connect,
Can connect to db, but not production db.
I’m a beginner and would appreciate some guidance.
(this is google translate)
@jedi I too use TablePlus and all worked fine:
- Keep your fly proxy open
- Use the following in TablePlus:
- host:
localhost
- user:
postgres
(well, your db user name) - password: your db password
- port: 15432
- database: the same name as we used in our last step in my previous message:
foobar
in the example
@dornby thanks for your reply
I have enabled “fly proxy”.
Follow your instructions to connect,
It can be successfully connected.
I tried to change the data in the db in TablePlus and save it,
Then “puts db data” on the Internet,
But it shows the old data (maintain the data of the original heroku db)
Obviously the data I changed in TablePlus was not updated synchronously,
Or what action is required to upload the database?
please help thanks
(this is google translate)
OMG
my DATABASE_URL=$(heroku config:get DATABASE_URL)
I’m such an idiot…
It is normal to change DATABASE_URL to fly’s db…
Thank u
Also for anyone else following along here, you’ll need to add your heroku app name to that command DATABASE_URL=$(heroku config:get DATABASE_URL -a your-heroku-app-name)
.
Cross-posting this for people following this thread. There is a new solution posted here in the Help migrating postgres from heroku rails app thread that uses the postgres --data-only
flag.
Thanks, I’m going to tear everything down and start over from scratch this afternoon and see if I can get through the steps and avoid the version mismatch issue.
i would like to post the command for importing a local db dump that worked for me
first i proxied with
fly proxy 15432:5432 -a app-db
and then
pg_restore -U postgres -h localhost -p 15432 -W -d db_name -c < db.sql
-W so it asks for the password
-c so it deletes the existing database so you don’t get for relation "db_table" already exists