If you would like to view the MYSQL backups section click here

Automatic postgres backups on heroku deployment

Set up external postgres backups to an S3 bucket to take place automatically every time you deploy to Heroku.

Steps

1. Create an S3 bucket
2. Using your Amazon console, add an IAM user with the following security policy:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Stmt1210229835000",
      "Effect": "Allow",
      "Action": [
        "s3:PutObject"
      ],
      "Resource": [
        "arn:aws:s3:::YOUR-S3-BUCKET-NAME/*"
      ]
    }
  ]
}

(Replace the 'YOUR-S3-BUCKET-NAME' in the security policy with your s3 bucket name)

This will give the user write access to the bucket. Copy over the user's AWS access key and secret key to the environment variables.

3. Add the following environment variables

APP (your heroku app name)

HEROKU_API_KEY (this can be generated BY)

DATABASE (your heroku db url - usually = DATABASE_URL)

HEROKU_TOOLBELT_API_EMAIL (your heroku email)

HEROKU_TOOLBELT_API_PASSWORD (you can generate this by typing heroku auth:token into your terminal)

S3_BUCKET_PATH (the name of your s3 backups e.g. app-backups)

AWS_ACCESS_KEY_ID (for your newly created IAM user)

AWS_DEFAULT_REGION (e.g. eu-west-1)

AWS_SECRET_ACCESS_KEY (for your newly created IAM user)

4. Add a buildpack to your Heroku app with the following command in your terminal

heroku buildpacks:add --index 1 https://github.com/heroku/heroku-buildpack-multi.git --app YOURHEROKUAPPNAME

5. Copy over our bin folder and .buildpacks file (or clone this repo)

6. Add the following scripts to your package.json

"backup":"bash ./bin/backup.sh",
"postinstall": "npm run backup"

The automatic backups on deploy should now be complete! The postinstall script should automatically run after your node modules have installed on Heroku and should save a backup of your database to the specified S3 bucket.

Restoring

1. Find your backup in the S3 bucket and download and extract it so you have a .dump file

2. Find the 'public' schema of your database in PGAdmin and delete

3. Create a new 'public' schema for the database

4. Right click the name of the database in the panel on the left, click restore and select your backup

Your database should now have been restored

Reading

Want to set up scheduled backups? Check out the following resources:

http://stefan.haflidason.com/automatic-off-site-backups-for-your-heroku-postgres-databases/

https://github.com/kbaum/heroku-database-backups