heroku_db_sync_prod_to_staging.sh

#!/bin/bash

# This was used for a Rails app with PostgreSQL database on Heroku.
# It uses the DATABASE_URL as the source and destination.
#
# Usage:
#
# 1) Specify the SRC_APP, DEST_APP and THREADS variables in this script.
# 2) Run: `sh heroku_db_sync_prod_to_staging.sh`
#
# Steps:
#
# Pull the production database to your local database.
# Then push the local database to your staging database.
# Runs Rails migrations on staging.

SRC_APP="heroku-prod-app-name"
DEST_APP="heroku-staging-app-name"
# DB POOL SIZE
THREADS="40"

main() {
  color @b@red
  box-header "Sync: PROD -> LOCAL -> STG"
  color @reset

  cline @b@red "What local database name should be overwritten with prod data?"

  color @b
  read -p "  > " -r
  color @reset

  LOCAL_DB=$REPLY

  if [[ "$LOCAL_DB" != "" ]]
  then

    box-header "Resetting: LOCAL ($LOCAL_DB)"
    
    dropdb "$LOCAL_DB"

    box-header "Backing up: PROD (app: $SRC_APP)"

    heroku pg:backups:capture DATABASE_URL -a $SRC_APP

    box-header "Pulling: PROD (app: $SRC_APP) -> LOCAL"

    heroku pg:pull DATABASE_URL "$LOCAL_DB" -a $SRC_APP

    box-header "Backing up: STG (app: $DEST_APP)"

    heroku pg:backups:capture DATABASE_URL -a $DEST_APP

    box-header "Resetting: STG (app: $DEST_APP)"

    heroku pg:reset DATABASE_URL -a $DEST_APP

    box-header "Pushing: LOCAL -> STG (app: $DEST_APP)"
    
    heroku pg:push "$LOCAL_DB" DATABASE_URL -a $DEST_APP

    # Run Rails migrations
    
    box-header "Migrating (detached): STG (app: $DEST_APP)"

    heroku run:detached DB_POOL="$THREADS" bundle exec rake db:migrate --trace -s performance -a $DEST_APP

    # Trail Heroku logs while migrations run
    
    box-header "Trailing logs: STG (app: $DEST_APP)"

    heroku logs -t -a $DEST_APP
  else
    echo ""
    echo "... hmm."
  fi

  echo ""
}

colorize() {
  sed \
    -e "s/\(\(@\(red\|orange|green\|yellow\|blue\|magenta\|cyan\|white\|reset\|b\|u\)\)\+\)[[]\{2\}\(.*\)[]]\{2\}/\1\4@reset/g" \
    -e "s/@red/$(tput setaf 1)/g" \
    -e "s/@green/$(tput setaf 2)/g" \
    -e "s/@yellow/$(tput setaf 3)/g" \
    -e "s/@blue/$(tput setaf 4)/g" \
    -e "s/@magenta/$(tput setaf 5)/g" \
    -e "s/@cyan/$(tput setaf 6)/g" \
    -e "s/@white/$(tput setaf 7)/g" \
    -e "s/@reset/$(tput sgr0)/g" \
    -e "s/@b/$(tput bold)/g" \
    -e "s/@u/$(tput sgr 0 1)/g"
}

color() {
  echo "$@" | colorize
}

cline() {
  echo "$@" | colorize
  color @reset
}

box-header() {
  # Put all arguments into single string and all-caps
  name="$*"
  # name=$(echo "$*" | tr 'a-z' 'A-Z')
  edge=$(echo "$name" | sed 's/./-/g')

  color @b
  echo "+----$edge----+"
  echo "|    $name    |"
  echo "+----$edge----+"
  color @reset
}

main