I was working on a rails app hosted on Heroku and i had a huge amount of data in my local Postgres database. I wanted to import this data onto the production database on Heroku.
This is what i did to import data into the database at heroku from a dump file of my local database.
Install the Heroku PGBackups add-on
The Heroku PGBackups add-on lets you easily capture and manage backups for your Heroku Postgres databases.
Install it using the following command.
heroku addons:add pgbackups
Take a backup of your heroku database.
Please take a backup of your current database on heroku, in case you mess up something. Just run the command given below.
It it throws an error, then specify the database.
If you are using the database at DATABASE_URL then the command is:
heroku pgbackups:capture DATABASE
My database was at SHARED_DATABASE_URL.
dipin@localhost:~$ heroku pgbackups:capture SHARED_DATABASE SHARED_DATABASE ----backup---> b002 Capturing... done Storing... done dipin@localhost:~$
Note down the backup id. If you mess up, then you can restore your database using this backup. Below is the command to restore this backup.
heroku pgbackups:restore DATABASE b002
Take the dump of your local database.
We need to get a custom dump file of the local database.
pg_dump -h localhost -U username -Fc dbname > db.dump
Upload the dump to the internet.
We need to upload the dump file to the internet so that the heroku servers can access it.
What they recommend is: using Amazon S3 and S3Fox(Create the file with private access and create a temporary authorized URL for the Heroku import.)
I uploaded the dump to my site/server.
Import the dump into the database on heroku.
It was pretty simple to do that. Just run the command given below.
To import into the heroku database at DATABASE_URL use
heroku pgbackups:restore DATABASE http://www.ex.com/db.dump
To import into the heroku database at SHARED_DATABASE_URL use
heroku pgbackups:restore SHARED_DATABASE http://www.ex.com/db.dump
dipin@localhost:~$ heroku pgbackups:restore SHARED_DATABASE https://dipinkrishna.com/db.dump SHARED_DATABASE <---restore--- db.dump ! WARNING: Destructive Action ! This command will affect the app: dipin ! To proceed, type "example" or re-run this command with --confirm dipin > dipin Retrieving... done Restoring... done dipin@localhost:~$
Hope, it helps! 🙂