# Transferring Postgres from Heroku

**URL:** <https://community.fly.io/t/transferring-postgres-from-heroku/6611>\
**Category:** Questions / Help\
**Created:** [August 27, 2022, 7:50am UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611 "2022-08-27T07:50:41Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![kaspermeyer](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/kaspermeyer/32/4987_2.png) [@kaspermeyer](https://community.fly.io/u/kaspermeyer)\
**Post date:** [August 27, 2022, 7:50am UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/1 "2022-08-27T07:50:41Z")

</div>

I’m attempting to migrate a Rails app from Heroku, and I’ve followed the [instructions](https://fly.io/docs/rails/getting-started/migrate-from-heroku/) without any issues up to the [section about database migration](https://fly.io/docs/rails/getting-started/migrate-from-heroku/#transfer-the-database). I ran the command laid out from the guide:

```bash
$ pg_dump --no-owner -C -d $HEROKU_DATABASE_URL | psql -d $DATABASE_URL

```

However, the following error occurs:

```auto
pg_dump: option requires an argument -- d
Try "pg_dump --help" for more information.
psql: option requires an argument -- d
Try "psql --help" for more information.

```

I have tried replacing the two variables with the actual database URL’s, but the `$DATABASE_URL` is an internal address, which makes me think I’m missing something.

Am I supposed to run the command from within my VM? Hope someone can help 🙏

**Update** : I attempted to run the command from within the app container with `fly ssh console` but got the following error:

```bash
$ pg_dump --no-owner -C -d $HEROKU_DATABASE_URL | psql -d $DATABASE_URL
pg_dump: server version: 14.4 (Ubuntu 14.4-1.pgdg20.04+1); pg_dump version: 11.17 (Debian 11.17-0+deb10u1)
pg_dump: aborting because of server version mismatch

```

---

<div class="post-metadata">

**Author:** ![tj1](https://avatars.discourse-cdn.com/v4/letter/t/22d042/32.png) [@tj1](https://community.fly.io/u/tj1)\
**Post date:** [August 27, 2022, 8:59am UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/2 "2022-08-27T08:59:48Z")

</div>

I think that you have to ssh to your app before running these commands.

`fly ssh console`

Otherwise, you can also load a database dump from your machine by adjusting the following commands:

```auto
flyctl proxy 15432:5432 -a my-postgres-app
psql -U <your-user> -h localhost -p 15432 <your-db> < <your_dump_file.sql>

```

---

<div class="post-metadata">

**Author:** ![flyHigh](https://avatars.discourse-cdn.com/v4/letter/f/48db29/32.png) [@flyHigh](https://community.fly.io/u/flyHigh)\
**Post date:** [August 28, 2022, 1:33am UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/3 "2022-08-28T01:33:39Z")

</div>

This works for me from the command line when at my Rails project  
The first may not be necessary depending on how you have Heroku set up

```auto
   heroku login -I
    heroku pg:backups:capture
    heroku pg:backups:download

```

This downloads the database to your project folder. Uploading to [fly.io](http://fly.io) is another story and in fact that is what I’m trying to figure out.

I hadn’t read those instructions when I first posted, but it makes some sense to download and upload rather than trying to do in one step. When I deploy I’m getting errors about a database table not being seen, but I’ve got the rails app running in localhost also. It seems the fly deploy uploads the database too. I just started with this yesterday, so haven’t gotten very far and will separately post if I can’t figure it out.

---

<div class="post-metadata">

**Author:** ![tj1](https://avatars.discourse-cdn.com/v4/letter/t/22d042/32.png) [@tj1](https://community.fly.io/u/tj1)\
**Post date:** [August 28, 2022, 5:07am UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/4 "2022-08-28T05:07:58Z")

</div>

It’s the fly proxy command with psql that I listed above.

---

<div class="post-metadata">

**Author:** ![wiznaibus](https://avatars.discourse-cdn.com/v4/letter/w/e9c0ed/32.png) [@wiznaibus](https://community.fly.io/u/wiznaibus)\
**Post date:** [August 29, 2022, 10:05am UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/5 "2022-08-29T10:05:37Z")

</div>

`flyctl proxy 15432:5432 -a my-postgres-app` just hangs for me. No error.

---

<div class="post-metadata">

**Author:** ![tj1](https://avatars.discourse-cdn.com/v4/letter/t/22d042/32.png) [@tj1](https://community.fly.io/u/tj1)\
**Post date:** [August 29, 2022, 2:44pm UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/6 "2022-08-29T14:44:29Z")

</div>

That sets up a proxy from the app port 5432 to local port 15432. Then you can use psql against localhost with port 15432.

---

<div class="post-metadata">

**Author:** ![wiznaibus](https://avatars.discourse-cdn.com/v4/letter/w/e9c0ed/32.png) [@wiznaibus](https://community.fly.io/u/wiznaibus)\
**Post date:** [August 29, 2022, 2:49pm UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/7 "2022-08-29T14:49:48Z")

</div>

> [@tj1](#):
>
> ```auto
> flyctl proxy 15432:5432 -a my-postgres-app
> psql -U hora -h localhost -p 15432 my-postgres-db < data.sql
> 
> ```

Understood. When I set up the proxy on the app port, it hangs. I can’t even get to step two.

---

<div class="post-metadata">

**Author:** ![tj1](https://avatars.discourse-cdn.com/v4/letter/t/22d042/32.png) [@tj1](https://community.fly.io/u/tj1)\
**Post date:** [August 29, 2022, 3:07pm UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/8 "2022-08-29T15:07:30Z")

</div>

It opens up a foreground process that should remain open. You’ll need to use psql in a second terminal.

```auto
$ fly proxy 15432:5432 -a my-app
Proxying local port 15432 to remote [my-app.internal]:5432

```

---

<div class="post-metadata">

**Author:** ![wiznaibus](https://avatars.discourse-cdn.com/v4/letter/w/e9c0ed/32.png) [@wiznaibus](https://community.fly.io/u/wiznaibus)\
**Post date:** [August 29, 2022, 3:20pm UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/9 "2022-08-29T15:20:31Z")

</div>

Ahhh, did not know that. Will try when I’m back.

---

<div class="post-metadata">

**Author:** ![dornby](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/dornby/32/40023_2.png) [@dornby](https://community.fly.io/u/dornby)\
**Post date:** [August 29, 2022, 5:19pm UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/10 "2022-08-29T17:19:49Z")

</div>

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

```auto
psql -U hora -h localhost -p 15432 my-postgres-db < data.sql

```

I get

```auto
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:

```auto
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?

---

<div class="post-metadata">

**Author:** ![tj1](https://avatars.discourse-cdn.com/v4/letter/t/22d042/32.png) [@tj1](https://community.fly.io/u/tj1)\
**Post date:** [August 29, 2022, 8:02pm UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/11 "2022-08-29T20:02:43Z")

</div>

Trying to be clearer this time.

1. Obtain a sql dump file from heroku and have it locally.

2. Proxy the postgres from fly to localhost.

```auto
$ fly proxy 15432:5432 -a my-app
Proxying local port 15432 to remote [my-app.internal]:5432

```

1. Use psql like you would normally, but on port 15432. For instance, you should be able to connect, etc.

```auto

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

> [@dornby](#):
>
> `psql -U hora -h localhost -p 15432 my-postgres-db < latest.dump`

---

<div class="post-metadata">

**Author:** ![jedi](https://avatars.discourse-cdn.com/v4/letter/j/839c29/32.png) [@jedi](https://community.fly.io/u/jedi)\
**Post date:** [August 30, 2022, 9:23am UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/12 "2022-08-30T09:23:39Z")

</div>

![image](https://global.discourse-cdn.com/fly1/original/3X/9/3/930205ec4f593c90d49527c9c546c1b51e2b06e3.png)  
@tj1 thank you for your approach  
but i’m having trouble  
hope you can assist  
(this is google translate)

---

<div class="post-metadata">

**Author:** ![tj1](https://avatars.discourse-cdn.com/v4/letter/t/22d042/32.png) [@tj1](https://community.fly.io/u/tj1)\
**Post date:** [August 30, 2022, 2:59pm UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/13 "2022-08-30T14:59:47Z")

</div>

You’ve connected correctly, but you need to use the correct database name.

```auto
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`

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![dornby](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/dornby/32/40023_2.png) [@dornby](https://community.fly.io/u/dornby)\
**Post date:** [August 30, 2022, 3:59pm UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/14 "2022-08-30T15:59:54Z")

</div>

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:

1. Get your latest dump by running

```auto
heroku login
heroku pg:backups:capture
heroku pg:backups:download

```

1. In one terminal, launch:

```auto
fly proxy 15432:5432 -a my-db-app-name
# make sure it's the name of your db-app, not your main app

```

1. In another terminal, list your db names to get the exact name of your DB with `\l`:

```auto
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)

```

1. Once you have your name, here `foobar`, import your dump with `pg_restore`:

```auto
pg_restore -U postgres -h localhost -p 15432 -d foobar < latest.dump

```

And voilà, or at least it worked for me.

---

<div class="post-metadata">

**Author:** ![jedi](https://avatars.discourse-cdn.com/v4/letter/j/839c29/32.png) [@jedi](https://community.fly.io/u/jedi)\
**Post date:** [August 31, 2022, 10:30am UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/15 "2022-08-31T10:30:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dornby](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/dornby/32/40023_2.png) [@dornby](https://community.fly.io/u/dornby)\
**Post date:** [August 31, 2022, 12:27pm UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/16 "2022-08-31T12:27:29Z")

</div>

@jedi I too use TablePlus and all worked fine:

1. Keep your fly proxy open
2. 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

---

<div class="post-metadata">

**Author:** ![jedi](https://avatars.discourse-cdn.com/v4/letter/j/839c29/32.png) [@jedi](https://community.fly.io/u/jedi)\
**Post date:** [August 31, 2022, 12:50pm UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/17 "2022-08-31T12:50:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jedi](https://avatars.discourse-cdn.com/v4/letter/j/839c29/32.png) [@jedi](https://community.fly.io/u/jedi)\
**Post date:** [August 31, 2022, 3:45pm UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/18 "2022-08-31T15:45:16Z")

</div>

@dornby

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 👍

---

<div class="post-metadata">

**Author:** ![dornby](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/dornby/32/40023_2.png) [@dornby](https://community.fly.io/u/dornby)\
**Post date:** [September 1, 2022, 3:36pm UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/19 "2022-09-01T15:36:07Z")

</div>

@jedi you’re not an idiot 🙂  
Well done on solving this one, I wouldn’t have managed to help 😄

---

<div class="post-metadata">

**Author:** ![lortza](https://sea1.discourse-cdn.com/fly1/user_avatar/community.fly.io/lortza/32/5615_2.png) [@lortza](https://community.fly.io/u/lortza)\
**Post date:** [October 15, 2022, 8:32pm UTC](https://community.fly.io/t/transferring-postgres-from-heroku/6611/20 "2022-10-15T20:32:31Z")

</div>

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

[Next page](https://community.fly.io/t/transferring-postgres-from-heroku/6611.md?page=2)