Heroku PostgreSQL database backup and restore

If you are new to Heroku and PostgreSQL and need to backup/restore data to and from your PostgreSQL database in Heroku, it might be overwhelming to get it right. This post has everything you will need to do just that.

If you have not yet added your current project to a Heroku app. Add Heroku remote to the current project root.

heroku git:remote -a your-heroku-app

After that, you should be able to run Heroku commands directly to your current Heroku app. For example, run bash on Heroku app by running

heroku run bash

Similarly to run PostgreSQL commands like capture and download on Heroku PostgreSQL database you need to run following

heroku pg:backups:capture

heroku pg:backups:download

This should download the latest data in your Heroku PostgreSQL database.

Install PostgreSQL locally for the tools pg_dump and pg_restore. pg_dump allows you to dump your local PostgreSQL database and pg_restore allows you to restore

brew install postgresql

Export your local PostgreSQL db using pg_dump

PGPASSWORD=your_db_password pg_dump -Fc --no-acl --no-owner -h localhost -U postgres your_dbname > mydb.dump

Copy your dump to S3 or any server which can be accessed with a URL

aws s3 cp ./mydb.dump s3://your-s3-bucket

aws s3 presign s3://your-s3-bucket/mydb.dump

Restore the local dump into the Heroku PostgreSQL database

heroku pg:backups:restore https://your-s3-bucket.s3.REGION.amazonaws.com/mydb.dump\?PARAMS-WITH-CREDENTIALS

It will show following warning

WARNING: Destructive Action
▸ This command will affect the app your-heroku-app
▸ To proceed, type your-heroku-app or re-run this command with –confirm
▸ your-heroku-app

Just enter the name of your Heroku app to confirm, but be aware that your database will be overwritten by the new data you just restored.