Easy way to Import and Export Heroku Postgres Databases
Tags: heroku, postgres, database, webdev
Published: 2023-02-03 18:12:21

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](https://devcenter.heroku.com/articles/heroku-cli)
globally installed.

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

```curl
 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](https://devcenter.heroku.com/articles/heroku-postgres-import-export#import) 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)


```curl 
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](https://www.youtube.com/watch?v=xo0z4LQEhyI) the dump file to [dropbox](https://www.dropbox.com/)
- Step 2) Now [Generate](https://zapier.com/blog/generate-direct-dropbox-link/) 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:

```curl 
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.

```curl
 ›   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