Prepare local dump

PGPASSWORD=mypassword pg_dump -Fc --no-acl --no-owner -h localhost -U myuser mydb > mydb.dump

Upload the dump to public domain

I just find Dropbox so easy to use, so…

The default url for the file in Dropbox is

https://www.dropbox.com/XXXXXX/<filename>?dl=0

However, it would not work out of the box, because it’s one HTML, and Heroku is expecting one dump file.

The trick we could play is to change above url to:

https://dl.dropboxusercontent.com/XXXXXX/<filename>

Import online dump to heroku

heroku pgbackups:restore DATABASE 'https://dl.dropboxusercontent.com/XXXX/<filename>' --confirm <appname>

Reference

Importing and Exporting Heroku Postgres Databases with PG Backups

The link inspires me on coming up with the above change