pg_restore not working corrupt tar header found

[wide parcel]

Trying to migrate from heroku postgres over to railway. The guide (https://blog.railway.app/p/postgre-backup) recommends using pg_dump and pg_restore. Once I had my latest.dump file from heroku I tried importing with pg_restore. It fails with pg_restore: error: corrupt tar header found in PGDMP (expected 0, computed 19329) file position 512

Found this StackOverflow answer to a similar problem. It recommended making sure pg_dump and pg_restore have the same version. Upgraded my local pg installation to 14.5 (the version heroku is running) but the error remains.

Any help appreciated.

[grand stag]

Where did you get your latest.dump file from?

[fiery harness]

having the same issue right now

[grand stag]

yeah, looking

[grand stag]

Where did you get your latest.dump file from?

[wide parcel]

got it from heroku with heroku pg:backups:download

[fiery harness]

I ran these commands in the Heroku CLI: heroku pg:backups:capture --app example-app
heroku pg:backups:download --app example-app

[devout vapor]

Any chance y'all can run pg_dump on the heroku db?

Ah, looks like that's what Heroku runs internally.

Could you share the pg_restore command you ran?

[fiery harness]

I tried these:

pg_restore -U postgres -h containers-us-west-16.railway.app -p 7241 -W -F t -d railway latest.dump

railway run pg_restore -U postgres -h containers-us-west-16.railway.app -p 7241 -W -F t -d railway latest.dump

both giving that error

[devout vapor]

Could you try the command Heroku runs when they restore? pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump

Seems like they run a pg_dump with the custom format option.

[wide parcel]

Great success! Important to note I had specify -F t. pg_dump successfully connected and got the db

[fiery harness]

it worked with no issues, pulled the data into my local development database

[fiery harness]

what command did you use?

[wide parcel]

Try following the intructions here https://blog.railway.app/p/postgre-backup and connecting pg_dump directly to heroku

[fiery harness]

how do you connect to the heroku database?

[devout vapor]

Probably using the connection URL.

[fiery harness]

Thanks. One last question, when running the restore command do I need 'railway run' before the command?

[devout vapor]

Not in this case as you're manually pasting the values.

railway run is used when you need to inject the environment variable values.

[fiery harness]

ok, so just pg_restore -U postgres -h containers-us-west-16.railway.app -p 7241 -W -F t -d railway latest.dump in the terminal?

[devout vapor]

Yes.

[fiery harness]

perfect, thanks for the help

[wide parcel]

using arguments directly. I didn't try with the url

[fiery harness]

would you be able to share the command you used? just heroku run pg_dump etc?

[wide parcel]

It's a 2 step process. Seems heroku uses their own format. First pg_dump then pg_restore

[wide parcel]

pg_dump -h HOST -d $DATABASE -U $USER -p $PORT -W -F t > latest.dump then type the password when prompted. The DATABASE_URL format is postgres://$USER:$PASSWORD@$HOST:$PORT/$DATABASE. Run pg_dump with the heroku credentials

[fiery harness]

legend

[wide parcel]

Then pg_restore -U $USER -h $HOST -p $PORT -W -F t -d $DATABASE latest.dump with the railway credentials.

fp, it'd would be cool to update the heroku migration article with the fact that heroku uses a weird dump format. Lots of people moving over lately

[devout vapor]

Agreed.

cc @shadow cobalt if you want to take this or I can do it when I wake up! 😄

[shadow cobalt]

I can put it in my to-dos for today!

[drowsy crag]

Got same issue here, can help?

[drowsy crag]

Please, anyone help .., I want migrate my Postgres from Heroku to Railway.

I already did same with the method in Railway's blog, I also tried in Ubuntu but still didn't work.

[shadow cobalt]

Would you mind making a new thread in #1006629907067064482 - bumping an old thread makes it hard for us to track

[drowsy crag]

Okay ..
I thought it not allowed to create same topic.

[shadow cobalt]

Making an exception for you 🙂