Cant pg_restore on Heroku: "could not read from input file: end of file"

postgresql heroku heroku-postgres
Mikhail Savushkin picture Mikhail Savushkin · Mar 26, 2016 · Viewed 10.9k times · Source

I am trying to copy my local PostgreSQL DB to Heroku app with pg_dump / pg_restore utils. Doing according to Heroku's official guide: https://devcenter.heroku.com/articles/heroku-postgres-import-export

So, i've done the dump:
pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dump

Then i've uploaded it on a reachable across the web server (and its really reachable, i've checked it downloading the file with wget and pg_restoreing it - works fine).

Then i've tried to restore in on Heroku and no luck:

kulver@kvb:~/projects/gop/gop_flask$ heroku pg:backups restore 'MY_URL_HERE' postgresql-corrugated-15763
r010 ---restore---> DATABASE
An error occurred and your backup did not finish.

Please run `heroku pg:backups info r010` for details.

Here are the details:

kulver@kvb:~/projects/gop/gop_flask$ heroku pg:backups info r010
=== Backup info: r010

Database:    BACKUP
Started:     2016-03-26 20:15:32 +0000
Finished:    2016-03-26 20:15:32 +0000
Status:      Failed
Type:        Manual
Backup Size: 23.9MB
=== Backup Logs
... a bunch of logs here ...
2016-03-26 20:15:32 +0000: pg_restore: processing data for table "cards"
2016-03-26 20:15:32 +0000: waiting for restore to complete
2016-03-26 20:15:32 +0000: pg_restore: [custom archiver] could not read from input file: end of file
2016-03-26 20:15:32 +0000: restore done
2016-03-26 20:15:32 +0000: waiting for download to complete
2016-03-26 20:15:32 +0000: download done

I've tried to remake the dump file, reload it - same error. What's wrong? Why can i download it and restore from it on the just created DB, but not on Heroku?

Thank you for any advise.

Answer

Mikhail Savushkin picture Mikhail Savushkin · Mar 27, 2016

I failed to load the dump in the exact described above way, BUT i've come to another solution which worked fine for me:

Make the dump in simple SQL format:
pg_dump --no-owner mydb > mydb.dump

You may need to switch to the user who has rights to access your DB, postgres for example. So, sudo su postgres and then make the dump.

And then load it with psql tool:
user@pc:~/path/to/your/dump$ heroku pg:psql < mydb.dump

Related questions

Getting "Unknown primary key for table" while the ID is there

I've been debugging this strange problem of Rails giving me "Unknown primary key for table...", even when the table's ID is there. I've copied the database from one heroku app to another, on the original databse there is no problem …

Read More
How can I download db from heroku?

I'm using heroku and I want to download the database from my app(heroku) so I can make some changes in it, I've installed pgbackups, but using heroku pgbackups:url downloads a .dump file How can I download a postgresql …

Read More
How to change DATABASE_URL for a heroku application

I wanted to use an external Database with my heroku application. But I'm unable to edit the configuration cariables. I tried using GUI, Which says, Cannot overwrite attachment values DATABASE_URL. While I tried using CLI as well. I used …

Read More