Cannot restore postgres dump on heroku

user_12273958 0
Viewed 4030

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.

django-rest-framework heroku postgresql django python
3 Answers

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

user_122739580

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.

user_6180680
user_107268500
Related