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

Tagged with:  

1 Response » to “How to Import/restore data into Heroku postgres database from a dump file”

  1. Mike Hoitomt says:

    Nice post, thanks for writing up a clear concise way to do this.

Leave a Reply

*

Search