Site icon Dipin Krishna

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

Dipin Krishna

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

Exit mobile version