Reading time: 2 mins
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
This should download the latest data in your Heroku PostgreSQL database.
Install PostgreSQL locally for the tools
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
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
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.