Cannot restore postgres dump on heroku

Question

I am using this command to create dump locally PGPASSWORD=admin pg_dump -h 127.0.0.1 -p 5432 -U postgres --no-owner --no-acl -f database.dump and my is created successfully.

then uploaded this dump to dropbox and make it public with this link http://www.dropbox.com/s/rsqeiuqzusejz5x/database.dump?dl=1 notice I have changed https to http and dl=0 to dl=1 (dl=1 to make it downloadable)

then on my terminal I am running this command heroku pg:backups:restore "http://www.dropbox.com/s/38prh2y7pdwhqqj/database.dump?dl=1" --confirm tranquil-anchorage-39635

but I am getting this error

 !    An error occurred and the backup did not finish.
 !
 !    pg_restore: error: did not find magic string in file header
 !    waiting for restore to complete
 !    pg_restore finished with errors
 !    waiting for download to complete
 !    download finished successfully
 !
 !    Run heroku pg:backups:info r010 for more details.

I have tried all the official documentation and various answers but nothing seems to work.

aman goyal · Accepted Answer · 2020-04-13 15:51:41Z

6

On doing further research I found out that pg_restore command while restoring dump file expects a certain format that must be mentioned while creating the dump file. That is why there was an error pg_restore: error: did not find magic string in file header.

pg_dump -h <localhost> -p <port> -U <username> --format=c <database_name> > daman.dump after running this command you will be prompt to enter the password for the user.

Notice --format=c in the above command. This will create a dump file in a format that can be restored by pg_restore, also I should mention that the dump file created in this manner is not readable to text-editor like notepad or vscode unlike the in the case when --format=c is not used.

for further details see the documentation here

answered Apr 13, 2020 at 15:51

aman goyal

3113 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pifor Over a year ago

Note that -Fc is exactly the same as --format=c. See postgresql.org/docs/9.6/app-pgdump.html

aman goyal Over a year ago

Yes, I agree, but for some weird reason -Fc doesn't seem to work. I have tried this in many ways, but this doesn't work

aardvarkk · Accepted Answer · 2021-08-18 04:39:30Z

1

I found that even with the correct file format, only certain file hosts would work. I tried file.io and filetransfer.io, but neither one seemed to work with Heroku. Only Amazon S3 worked correctly of those three.

Even if you have the correct file format, you'll get this error if you try to host the file in certain places.

answered Aug 18, 2021 at 4:39

aardvarkk

16.2k9 gold badges73 silver badges103 bronze badges

Comments

pifor · Accepted Answer · 2020-04-11 05:57:14Z

0

Try to run pg_dump with -Fc option (customized format ) as documented in https://devcenter.heroku.com/articles/heroku-postgres-import-export:

 pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dump

answered Apr 11, 2020 at 5:57

pifor

7,9722 gold badges12 silver badges18 bronze badges

1 Comment

aman goyal Over a year ago

No, that didn't help either. But I have found the root cause and solution for this problem posting answer

Collectives™ on Stack Overflow

Cannot restore postgres dump on heroku

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related