database upgrade bash script for CCCD

cccd_db_upgrade.sh

#!/bin/bash

# *********************************************************** #
# Ideal path to minimise downtime is:
# - [recommended] perform `VACUUM (VERBOSE, ANALYZE);` on db
#    * to improve speed of db generally
# - [recommended] create manual snapshot
#    * to improve speed of upgrade process's automated snapshots
# - put site in maintenance mode
# - perform VACUUM; of postgres DB (this can be done before too but may place heavy load on db)
# - upgrade (using this script multiple times to upgrade to desired version)
# - take site out of maintenance mode
# - perform `ANALYZE;` on db (to "warm" it up)
#
# *********************************************************** #
function cccd_db_upgrade() {
  usage="cccd_db_upgrade [db-instance-id] [db-name] -- upgrade rds db instance
  where:
    db-instance-id [from rds] required
    db-name [adp_demo, adp_dev_new, adp_disaster, adp_staging, adp_api_sandbox, or adp_gamma] required for confirmation
  example:
    cccd_db_upgrade artpth14fsdf adp_staging"

  if [[ $# -eq 0 || $# -gt 2 ]]
    then
      echo "$usage"
      return 1
  else
    current_db_instance_id=$1

    case "$2" in @(adp_gamma|adp_staging|adp_api_sandbox|adp_dev_new|adp_disaster|adp_demo))
      current_db_name=$2
      ;;
    *)
      echo "$usage"
      return 1
      ;;
    esac
  fi

  profile=moj-crimebillingonline

  echo "+-----------------------------------------+"
  echo "| RDS DB upgrade - will involve downtime! |"
  echo "+-----------------------------------------+"
  echo ""

  db_instance_name=$( aws rds describe-db-instances --profile $profile --db-instance-identifier $current_db_instance_id --query 'DBInstances[0].[DBName]' --output text )
  if [ -z "$db_instance_name" ]; then return 1; fi

  if [ $current_db_name != $db_instance_name ]
  then
    echo "Error: non-matching db name: instance id ${current_db_instance_id} has DB named ${db_instance_name}, not ${current_db_name}"
    all_instance_details=($(aws rds describe-db-instances --profile $profile | jq -r '.[]? | .[] | "\(.DBInstanceIdentifier):\(.DBName):\(.DBInstanceStatus)"'))
    echo "+------------------------------------------------------------+"
    echo "| Available instances (db-instance-id:db-name:db-status)     |"
    echo "+------------------------------------------------------------+"
    for i in "${all_instance_details[@]}"; do echo $i; done;
    return 1
  fi

  current_db_instance_engine_version=$( aws rds describe-db-instances --profile $profile --db-instance-identifier $current_db_instance_id --query 'DBInstances[0].[EngineVersion]' --output text )
  if [ -z "$current_db_instance_engine_version" ]; then return 1; fi

  target_db_instance_engine_version=$( aws rds describe-db-engine-versions --profile $profile | jq -r ".[] | .[] | select(.EngineVersion==\"${current_db_instance_engine_version}\") | .ValidUpgradeTarget[-1].EngineVersion" )

  status=$( aws rds describe-db-instances --profile $profile --db-instance-identifier $current_db_instance_id --query 'DBInstances[0].[DBInstanceStatus]' --output text )
  if [ $status != 'available' ]; then echo "Instance is $status. Try again later!"; return 1; fi

  function wait_til_available() {
    # wait for modification to start
    sleep 30s

    current_status=$( aws rds describe-db-instances --profile $profile --db-instance-identifier $current_db_instance_id --query 'DBInstances[0].[DBInstanceStatus]' --output text )
    printf "${current_status}";
    while [ $current_status != 'available' ]
    do
      # alternative is to synchronously wait for instance to become available
      #
      # echo "Waiting for ${current_db_instance_id} to enter 'available' state..."
      # aws rds wait db-instance-available --profile $profile --db-instance-identifier $current_db_instance_id
      # exit_status="$?"

      current_status=$( aws rds describe-db-instances --profile $profile --db-instance-identifier $current_db_instance_id --query 'DBInstances[0].[DBInstanceStatus]' --output text )
      printf '.%s'
      sleep 30s
    done
    printf "\n"
  }

  function upgrade() {
    echo "Starting upgrade process at $(date)"

    aws rds modify-db-instance \
        --profile $profile \
        --db-instance-identifier $current_db_instance_id \
        --engine-version $target_db_instance_engine_version \
        --allow-major-version-upgrade \
        --apply-immediately &> /dev/null

    wait_til_available

    echo 'Rebooting instance for param groups to take effect...'
    aws rds reboot-db-instance --profile $profile --db-instance-identifier $current_db_instance_id &> /dev/null

    wait_til_available

    echo "Finished upgrade process at $(date)"
  }

  while true; do
    read -p "Are you sure you wish to upgrade ${current_db_instance_id}:${db_instance_name} from ${current_db_instance_engine_version} to ${target_db_instance_engine_version}? " yn
    case $yn in
        [Yy]es ) upgrade; break;;
        [Nn]o ) return 1;;
        * ) echo "Please answer yes or no.";;
    esac
  done
}