heroku pgbackups:restore: Invalid dump format

Question

I have a local psql database dump that needs to be uploaded to heroku. I followed the steps in the Update section from this link. Everything worked fine until the last part, the actual uploading step:

heroku pgbackups:restore --app myAppName DATABASE 'https://www.dropbox.com/myAppPSQLDumpLink/myAppName_local.dump' --confirm myAppName

This was what was showing up in the console:

HEROKU_POSTGRESQL_SILVER_URL (DATABASE_URL)  <---restore---  myAppName_local.dump

Retrieving... done

 !    An error occurred and your restore did not finish.

And this was the error from the logs (courtesy of Toby Hede's question):

2013-01-09T15:39:09+00:00 app[pgbackups]: Invalid dump format: /tmp/GgUz5yU4bF/project_mgr_development_local.dump: HTML document text

I tried searching for this error, but could not find an answer. Does anyone know what needs to be done to solve the problem? The actual dump for my local psql database was performed this way:

pg_dump -Fc --no-acl --no-owner -U myUserName  > myAppName_local.dump

Thank you!

Globeflower

The way I ended up uploading the database dump to the heroku application was by using the postgres.heroku.com tool. You need to: 1) choose the heroku application you want to update the database for 2) choose "PG Restore" from the "Connection Settings" dropdown 3) copy and paste the text that is provided, using the right link to your database dump. – Globeflower

the PG Restore option does not seem to be available any more on Heroku. – Necking

Answer 1

Looks like the link to the dump on Dropbox is redirecting or pointing to an HTML page (HTML document text in the error). Visit the link and make sure you are directly getting the dump. Or download the dump in your browser, right-click on it and Copy Download Link. That link should work with pgbackups:restore.

Sears

Downloading the dump in the browser, right-clicking on it and copying the download link worked. Thank you! – Globeflower

I have a dropbox link which goes directly to file... still get the error! :( – Shipboard

Yes, the link just takes you to a page with a download link on it. You need to right click the download button and copy that url. – Hinojosa

Aaaaaaah! You just saved me from a 2 hour hair-pulling session. It was the dropbox redirect that was messing me up, not some weird dump escaping problem during mysql to psql conversion. You, sir, have my sincere gratitude!!! – Digraph

I am doing this right now myself and have tried using dl.dropbox.com/whatever/my.dump and its still giving me the html error. Ideas? – Madelainemadeleine

I had to do this again recently and the right format for using Dropbox is now dl.dropboxusercontent.com/s/whatever/my.dump – Hinojosa

Answer 2

Dropbox provides an explanation for directly downloading files (https://www.dropbox.com/en/help/201) This can be helpful for using the dropbox links in pg_backups.

In short it says to have the download link with option "dl=1" rather than "dl=0". But this didn't work for me. Even copying the the address of downloaded file didn't work for me.

If you face the above problems, try moving the file into the public folder and copy the link from there. This worked for me.

Dehumanize

@Stonz2 Hi I am new. What do you think of the answer now? – Dehumanize

First off, welcome to Stack Overflow! As formatted, your answer is much better now. Refer to Answering in the Help Center if you have any additional questions about what makes a good answer. – Homerhomere

Answer 3

According to Importing and Exporting Heroku Postgres Databases with PG Backups, you can restore dump at terminal with:

$ curl -o latest.dump `heroku pgbackups:url --app heroku_appname`
$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump

Tso

This would work if you're restoring the dump locally. The original question asked to restore the dump on a Heroku instance – Rope

Answer 4

Got the same error, but different reason, so different solution. Maybe it helps someone.

If you've stored the dump file in a server with HTTPS, and you mistakenly used HTTP for the database URL, the forwarding will be interpreted as an HTML document.

So change

heroku pgbackups:restore --app myAppName DATABASE 'http://www.example.com/my.dump' --confirm myAppName

to

heroku pgbackups:restore --app myAppName DATABASE 'https://www.example.com/my.dump' --confirm myAppName

Brusque

Thanks for your effort to help others. In this question however, the question states that he uses https already. So this is not an answer to the question but an answer to another possibly similar question that may not have been asked yet. If you want, you can post that modified question yourself and also answer it yourself. – Breeder

Thanks! She doesn't explicitly state that she's aware she is using https (just hacking around with a dump file hosted on Dropbox, which happens to use https) so she and others may be unaware that https has anything to do with "Invalid dump format". Same error message, but different cause. You're right though, creating and answering a new question discussing this is a good idea. – Brusque