How to Import/restore data into Heroku postgres database from a dump file

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.

1. 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

2. 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.

   heroku pgbackups:capture

   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

   Eg:-
   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

3. 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

4. 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.

5. 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

   Eg:-

   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! 🙂