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

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

    My database was at SHARED_DATABASE_URL.

    dipin@localhost:~$ heroku pgbackups:capture SHARED_DATABASE
    SHARED_DATABASE  ----backup--->  b002
    Capturing... done
    Storing... done

    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

    To import into the heroku database at SHARED_DATABASE_URL use

    heroku pgbackups:restore SHARED_DATABASE


    dipin@localhost:~$ heroku pgbackups:restore SHARED_DATABASE
    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

Hope, it helps! 🙂

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.