Easy way to Import and Export Heroku Postgres Databases

This article will show how to export (download) the Heroku Postgres database from one Heroku app and import it (from a local machine) to another app.

Export

To export the data from your Heroku Postgres database, create a backup and download it:

Make sure you have
Heroku Cli
globally installed.

You can export your database by specifying your Heroku app name, here Heroku app name Is example-app

 heroku pg:backups:capture --app example-app 
 heroku pg:backups:download --app example-app

It will first capture and download the database in a dump file called latest.dump.

Now let us see how we can import latest.dump into another app Heroku app called example-app2

Import

To import the latest.dump, we need to upload the latest.dump somewhere, so it is accessible via HTTP.

we cannot directly specify the path from our local machine to Heroku.

Heroku website suggests using Amazon S3 to generate a signed URL, but we will use dropbox here since Amazon S3 is not freely available (it requires you to put your card details)

heroku pg:backups:restore <HTTP_DUMP_FILE_URL> DATABASE_URL --app example2-app

This command imports the dump file which is found in HTTP_DUMP_FILE_URL into example2-app.

Generate Http URL for Dump file

• Step 1) Upload the dump file to dropbox
• Step 2) Now Generate the URL for dump file
• Step 3) Clean up the URL, remove the URL param so the file ends with latest.dump
  • If generated file is https://dl.dropboxusercontent.com/s/asdasdasd/latest.dump?dl=0 then remove ?dl=0 so it becomes https://dl.dropboxusercontent.com/s/asdasdasd/latest.dump

The import command will look like this:

heroku pg:backups:restore 'https://dl.dropboxusercontent.com/s/asdasdasd/latest.dump' DATABASE_URL --app example2-app

This imports a latest.dump found in the link into example2-app; make sure to replace my URL with your URL and put the URL between single quotes.

You will see the following warnings, type your app name to continue.

 ›   Warning: heroku update available from 7.60.1 to 7.67.1.
 ▸    WARNING: Destructive Action
 ▸    This command will affect the app `example2-app`
 ▸    To proceed, type `example2-app` or re-run this command with --confirm example2-app

As it finishes, it should give a success message.

Restoring... done

Now you can delete the dump file from dropbox. Well, thats it!

Thanks for reading. I hope you found this article help.

References

https://stackoverflow.com/questions/46178874/heroku-pgbackupsrestore-from-local-database

https://devcenter.heroku.com/articles/heroku-postgres-import-export#capture-and-download-backup-with-pgbackups

https://zapier.com/blog/generate-direct-dropbox-link/

https://www.youtube.com/watch?v=xo0z4LQEhyI