Skip to content

Instantly share code, notes, and snippets.

@joshRpowell
Forked from stevenyap/Heroku Database.md
Created October 18, 2016 19:43
Show Gist options
  • Save joshRpowell/c0d0259e5d4b8af17ecc73148d07a6c9 to your computer and use it in GitHub Desktop.
Save joshRpowell/c0d0259e5d4b8af17ecc73148d07a6c9 to your computer and use it in GitHub Desktop.
Heroku for backup, exporting and importing database

Note: You need to have to Heroku PGBackup addon first heroku addons:add pgbackups..

Importing Heroku PG DB to local

Creates the backup on heroku first:

heroku pgbackups:capture

Download it to local via curl:

curl -o latest.dump `heroku pgbackups:url`

Restore it:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -d mydbname latest.dump

Remove the dump:

rm latest.dump

Exporting local to Heroku

Create your DB dump locally:

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

**Add PATH="/Applications/Postgres.app/Contents/MacOS/bin:$PATH" in .bash_profile if you encountered version does not match error when doing pg_dump **

Upload mydb.dump to your S3 or a remote FTP.
Note: remember to set permission for file uploaded to S3

Next, call Heroku to download the dump and then restore it:

heroku pgbackups:restore DATABASE 'https://s3.amazonaws.com/me/items/3H0q/mydb.dump'

Delete a Postgres Backup

heroku pgbackups # To see a list of all backups
heroku pgbackups:destroy <BACKUP_ID>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment