daily cockroachdb backup script

backup-cockroach.sh

#!/bin/bash
set -euxo pipefail

# this script runs backups, creating full backups when run on the configured
# day of the week and incremental backups when run on other days, tracking the
# backups it has created recently to correctly construct the list of path for
# the INCREMENTAL option.

full_day="Sunday" # Must match (including case) the output of `date +%A`.
what="DATABASE mydb" # what to backup.
base="s3://my-bucket/db-backups" # base dir in which to create backups.
recent="backups.txt" # file in which recent backups are recorded.
options="" # e.g. "WITH revision_history"
cmd="cockroach sql --certs mycerts" # customize as needed with security/network settings. `-e "stmt"` will be appended.

destination="${base}/$(date +"%Y%m%d-%H%M")"

if [[ "$(date +%A)" == "${full_day}" ]] ; then
    $cmd -e "BACKUP ${what} TO '${destination}' AS OF SYSTEM TIME '-1m'${options};"
    echo ${destination}  > ${recent}
else
  sep=""
  prev=""
  for i in $(cat ${recent}); do prev="${prev}${sep}'${i}'"; sep=", "; done;
  if [ -z "${prev}" ]; then
    echo "Missing prior backups, a full backup is required."
    $cmd -e "BACKUP ${what} TO '${destination}' AS OF SYSTEM TIME '-1m'${options};"
    echo ${destination}  > ${recent}
  else
    destination="${destination}-inc"
    $cmd -e "BACKUP ${what} TO '${destination}' AS OF SYSTEM TIME '-1m' INCREMENTAL FROM ${prev}${options};"
    echo ${destination} >> ${recent}
  fi
fi