Import/exporting database info from Heroku

heroku_export.md

Importing/Exporting a Postgres DB from Heroku

In the following scenario, we have the following apps on the Heroku server:

• Staging Server: staging-stackoverfluff
• Production Server: stackoverfluff

In addition, each developer has a local development environment.

The goal is to move production data over to the staging server, and also provide production data to each of the developers. Here's what we will do.

Step 1.

Install backup tools for each database:

heroku addons:add pgbackups --app stackoverfluff
heroku addons:add pgbackups --app staging-stackoverfluff

Step 2.

Backup the current production database:

heroku pgbackups:capture --app stackoverfluff

Note: You can get a list of your existing backups thru:

heroku pgbackups --app stackoverfluff

Step 3.

Generate a download URL for the backup:

heroku pgbackups:url --app stackoverfluff

This will give you a long S3 amazon url, copy and paste this into your browser (or curl for ninja points) to download the file. File should be in the format, b###.dump

Step 4.

Migrate data to staging database:

heroku pgbackups:restore DATABASE 'https://s3.amazonaws.com/hkpg...{ really long S3 url}' --app staging-stackoverfluff

Step 5.

Send dump file to team members to import into local development environments:

Via email, hipchat, etc...attach the .dump file and ask team members to execute the following command:

pg_restore -c -d stackoverflow_development ~/Desktop/b001.dump

Breakdown:

• -c : 'create' creates the database before restoring it
• -d : specifies the database name (as specified in the application)
• ~/Desktop/b001.dump : location of the dump file, will vary by user