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?

user_8108149 · Answer

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.

:(

user_205546 · Answer

I also could only get this working through PgAdmin, here's the command line that the backup job in PgAdmin used. Windows 10 but the pg_dump options should work on any OS.

C:\Program Files\PostgreSQL\13\bin\pg_dump.exe --file "mydump.dump" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs --compress "8" --no-owner --section=pre-data --section=data --section=post-data --no-privileges --no-tablespaces --no-unlogged-table-data --no-comments "mydatabase"

I then uploaded that to a public Azure blob file and restored it like this:

heroku pg:backups:restore "https://redacted.blob.core.windows.net/piis2/mydump.dump" DATABASE_URL

Heroku DB deployment

4 Answers