Heroku DB deployment

Question

I'm attempting my first Heroku deployment and am having trouble

heroku pg:backups:restore "https://s3.us-east-2.amazonaws.com/myusername/POSTGRESQL.dump" 
                          DATABASE_URL --app MyAppName

I receive the error

 !    An error occurred and the backup did not finish.
 !
 !    pg_restore: [archiver] did not find magic string in file header
 !    pg_restore finished with errors
 !    waiting for download to complete
 !    download finished with errors
 !    please check the source URL and ensure it is publicly accessible
 !
 !    Run heroku pg:backups:info r006 for more details.

And sometimes the error is:

Starting restore of https://s3.us-east-2.amazonaws.com/talXXXXXXXX to postgresql-XXXXXXXXX... done

Use Ctrl-C at any time to stop monitoring progress; the ba
Use heroku pg:backups to check progress.
Stop a running restore with heroku pg:backups:cancel.

Restoring... !
 !    An error occurred and the backup did not finish.
 !
 !    waiting for restore to complete
 !    pg_restore finished with errors
 !    waiting for download to complete
 !    download finished with errors
 !    please check the source URL and ensure it is publicl
 !
 !    Run heroku pg:backups:info r015 for more details.

I have confirmed from various browsers that the url is accessible to the public and I can download the file. I'm using double quotes around the URL as recommended for Windows, what am I doing wrong?

Answer 1

I was facing the same problem. Turn out it was a problem with my dump file. I wasn't compressing it properly.

--format=c selects custom as the format output (which is the same as -Fc). It compress the file by default, but it wasn't enough so I also used the --compress flag.

This flag tells the level of the compression; it goes from 0 (lighter) to 9 (heavier).

I used 9, just in case, and my command end up like that

pg_dump --format=c --compress=9 --no-acl --no-owner -h THE_HOST -U YOUR_USER THE_DATABASE > YOUR_FILE.dump

And it worked.

Answer 2

In the end I couldn't work out how to import to Heroku as they advertise it can be done at https://devcenter.heroku.com/articles/heroku-postgres-import-export#import

So I used a DB connection client like DBeaver, converted my DB dump into an SQL script and ran the script manually to import the data.

:(