Using heroku pg:backups:restore to Import to Heroku Postgres

Question

I am trying to copy a local PostgreSQL database to Heroku per this article.

Here is what I have done:

1. Make a dump file

pg_dump -Fc --no-acl --no-owner -h localhost -U postgres mydb > mydb.dump

2.Upload dump file to aws my-bucket-name/db-backup folder.

aws s3 cp mydb.dump s3://my-bucket-name/db-backup/mydb.dump

3. Generate a signed URL:

aws s3 presign s3://my-bucket-name/db-backup/mydb.dump --region us-east-2

4. Verify that the signed URL is accessible.

Navigate to the presigned URL in an incognito tab of a browser. It works.

5. Back up to Heroku using the generated signed URL

I am using double quotes around GENERATED_URL because I'm on Windows:

heroku pg:backups:restore --app my-app-name --confirm my-app-name "GENERATED_URL"

For example:

heroku pg:backups:restore --app my-app-name --confirm my-app-name "https://s3.us-east-2.amazonaws.com/s3.console.aws.amazon.com/s3/buckets/my-bucket-name/db-backup/mydb.dump?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIABCDVKE2GXCY3YXL7V%2F20200934%2Fus-east-2%2Fs3%2Faws4_request&X-Amz-Date=20200924T164718Z&X-Amz-Expires=3600&X-Amz-SignedHeaders=host&X-Amz-Signature=fb2f51c0d7fbe1234e3740cf23c37f003575d968a1e4961684a47ac627fbae2e"

THE RESULT

I get the following errors:

Restoring... !
 !    An error occurred and the backup did not finish.
 !
 !    Could not initialize transfer
 !
 !    Run heroku pg:backups:info r021 for more details.
'X-Amz-Credential' is not recognized as an internal or external command,
operable program or batch file.
'X-Amz-Date' is not recognized as an internal or external command,
operable program or batch file.
'X-Amz-Expires' is not recognized as an internal or external command,
operable program or batch file.
'X-Amz-SignedHeaders' is not recognized as an internal or external command,
operable program or batch file.
'X-Amz-Signature' is not recognized as an internal or external command,
operable program or batch file.

I've found others with similar problems, but no solutions. Thanks in advance to anyone who can help.

Parasang

Answer 1

This is resolved. There were two issues.

1. PowerShell wasn't properly escaping characters. So, I switched to CMD.
2. The dump file was invalid.

This line of code produced an invalid dump file:

pg_dump -Fc --no-acl --no-owner -h localhost -U postgres mydb > mydb.dump

Instead, I needed to use the following syntax:

pg_dump -Fc --no-acl --no-owner -h localhost -U postgres -d mydb -f mydb.dump

After making that change, all worked smoothly.

Parasang

I assume that > results in a non-compressed format? – Rub

does it work with Heroku dashboard-generated backups? – Look

Answer 2

For what it's worth, I had the same issue and my solution was to copy the S3 URL which is formatted as https://s3.amazonaws.com/<bucket_name>/<dump_file>.dump. For some reason the pre-signed URL approach did not work but the public URL did.

Holmberg

Defeats the point of a pre-signed url... – Rub

I just used the S3 URL too (after making it Public) and it worked fine. I couldn't get the pre-signed URL to work. – Anfractuosity