pg_restore not working corrupt tar header found
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.Railway Blog
How to Backup and Restore Your Postgres Database
In this post, we will go over the process of backing up and restoring your Postgres database hosted on Railway.
28 Replies
Unknown Userβ’3y ago
Message Not Public
Sign In & Join Server To View
got it from heroku with
heroku pg:backups:downloadUnknown Userβ’3y ago
Message Not Public
Sign In & Join Server To View
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?Unknown Userβ’3y ago
Message Not Public
Sign In & Join Server To View
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.Great success! Important to note I had specify
-F t. pg_dump successfully connected and got the dbUnknown Userβ’3y ago
Message Not Public
Sign In & Join Server To View
Try following the intructions here https://blog.railway.app/p/postgre-backup and connecting pg_dump directly to heroku
Railway Blog
How to Backup and Restore Your Postgres Database
In this post, we will go over the process of backing up and restoring your Postgres database hosted on Railway.
Unknown Userβ’3y ago
Message Not Public
Sign In & Join Server To View
Probably using the connection URL.
Unknown Userβ’3y ago
Message Not Public
Sign In & Join Server To View
Not in this case as you're manually pasting the values.
railway run is used when you need to inject the environment variable values.Unknown Userβ’3y ago
Message Not Public
Sign In & Join Server To View
Yes.
Unknown Userβ’3y ago
Message Not Public
Sign In & Join Server To View
using arguments directly. I didn't try with the url
Unknown Userβ’3y ago
Message Not Public
Sign In & Join Server To View
It's a 2 step process. Seems heroku uses their own format. First pg_dump then pg_restore
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 credentialsUnknown Userβ’3y ago
Message Not Public
Sign In & Join Server To View
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 latelyAgreed.
cc @Angelo if you want to take this or I can do it when I wake up! π
I can put it in my to-dos for today!
Unknown Userβ’3y ago
Message Not Public
Sign In & Join Server To View
Would you mind making a new thread in #βο½help - bumping an old thread makes it hard for us to track
Unknown Userβ’3y ago
Message Not Public
Sign In & Join Server To View
Making an exception for you π
Unknown Userβ’3y ago
Message Not Public
Sign In & Join Server To View