Script to copy part or all of PostgreSQL databases between different connections

pg_copy.sh

#!/usr/bin/env bash

# MIT License
# 
# Copyright (c) 2016 Michael K. Werle
# 
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
# 
# The above copyright notice and this permission notice shall be included in all
# copies or substantial portions of the Software.
# 
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
# SOFTWARE.# 

# Make failed commands exit the script
set -e

# Determine script directory
if [ "$(uname -s | cut -c 1-9)" = "MINGW.._NT" ]; then
  SCRIPT=$(basename ${BASH_SOURCE[0]})
  DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
else
  SOURCE="${BASH_SOURCE[0]}"
  SCRIPT=$(basename ${SOURCE})
  while [ -h "$SOURCE" ]; do
    DIR="$( cd -P "$( dirname"$SOURCE" )" && pwd )"
    SOURCE="$(readlink"$SOURCE")"
    [[ ${SOURCE} != /* ]] && SOURCE="$DIR/$SOURCE"
  done
  DIR="$( cd -P "$(dirname "$SOURCE")" && pwd )"
fi

# Some constants
ENTIRE_DATABASE='__ENTIRE_DATABASE__'
ENTIRE_SCHEMA='__ENTIRE_SCHEMA__'
PG_SERVICE_CONF=~/.pg_service.conf

LF=$'\n'
QUOT='"'

NORM=`tput sgr0`
BOLD=`tput bold`
REV=`tput smso`

CC_RED='\033[0;31m'
CC_YELLOW='\033[0;33m'
CC_GREEN='\033[32m'
CC_END='\033[0m'

echo_red() { echo -e "${CC_RED}$@${CC_END}"; }
echo_yellow() { echo -e "${CC_YELLOW}$@${CC_END}"; }
echo_green() { echo -e "${CC_GREEN}$@${CC_END}"; }

show_help() {
  echo -e "Help documentation for ${BOLD}${SCRIPT}.${NORM}"
  echo
  echo -e "Basic usage: ${BOLD}$SCRIPT${NORM}"
  echo
  echo -e "All command line switches are optional. The following switches are recognized."
  echo
  echo -e "\t${BOLD}-b${NORM}                    Save backups after a successful copy."
  echo -e "\t${BOLD}-B [backup location]${NORM}  Backup location (resolved relative to the current working dir)."
  echo -e "\t${BOLD}-d [destination]${NORM}      Destination database service name."
  echo -e "\t${BOLD}-D${NORM}                    Copy entire database."
  echo -e "\t${BOLD}-f [source]${NORM}           Source database service name."
  echo -e "\t${BOLD}-B${NORM}                    No backup (no way to undo failed copies)."
  echo -e "\t${BOLD}-s [schema name]${NORM}      Schema name."
  echo -e "\t${BOLD}-S${NORM}                    Copy entire schema."
  echo -e "\t${BOLD}-t [table_name]${NORM}       Table to copy (multiple accepted)."
  echo -e "\t${BOLD}-T${NORM}                    Restore in transaction (slower, but atomic success/failure)."
  echo -e "\t${BOLD}-u${NORM}                    Unattended operation (skip confirmations)."
  echo
  exit 0
}

# Checks for script requirements
init() {
  if [[ ! -f ${PG_SERVICE_CONF} ]]; then
    echo_red '************************'
    echo_red 'Connection file missing!'
    echo_red '************************'
    echo
    echo_yellow Please create a ${PG_SERVICE_CONF} file with host, port, dbname, user and password.
    echo_yellow '  See https://www.postgresql.org/docs/9.5/static/libpq-pgservice.html'
    echo_yellow '  and https://www.postgresql.org/docs/9.5/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS'
    echo
    echo Example configuration section for ${PG_SERVICE_CONF}:
    echo
    echo -e '\t[my-connection-name]'
    echo -e '\thost=my.host.name.com'
    echo -e '\tport=1234'
    echo -e '\tdbname=my_db_name'
    echo -e '\tuser=my_user'
    echo -e '\tpassword=my_password'
    echo
    exit 1
  fi

  for cmd in psql pg_dump pg_restore; do
    if [[ ! -x "$(command -v ${cmd})" ]]; then
      echo_red '*************************'
      echo_red 'Required command missing!'
      echo_red '*************************'
      echo_red "${cmd} command missing; please install it"
      exit 2
    fi
  done
}


ini_parser () {
  # From; http://theoldschooldevops.com/2008/02/09/bash-ini-parser/
  local oifs="${IFS}"
  local ini="$(<$1)"                         # read the file
  ini="${ini//[/\\[}"                        # escape [
  ini="${ini//]/\\]}"                        # escape ]
  local IFS=$'\n' && ini=( ${ini} )          # convert to line-array
  ini=( ${ini[*]//\;*/} )                    # remove comments with ;
  ini=( ${ini[*]/\    =/=} )                 # remove tabs before =
  ini=( ${ini[*]/=\   /=} )                  # remove tabs be =
  ini=( ${ini[*]/\ =\ /=} )                  # remove anything with a space around =
  ini=( ${ini[*]/#\\[/\}$'\n'ini.section.} ) # set section prefix
  ini=( ${ini[*]/%\\]/ \(} )                 # convert text2function (1)
  ini=( ${ini[*]/=/=\( } )                   # convert item to array
  ini=( ${ini[*]/%/ \)} )                    # close array parenthesis
  ini=( ${ini[*]/%\\ \)/ \\} )               # the multiline trick
  ini=( ${ini[*]/%\( \)/\(\) \{} )           # convert text2function (2)
  ini=( ${ini[*]/%\} \)/\}} )                # remove extra parenthesis
  ini[0]=""                                  # remove first element
  ini[${#ini[*]} + 1]='}'                    # add the last brace
  eval "$(echo "${ini[*]}")"                 # eval the result
  IFS="${oifs}"
}

parse_ini_info() {
  local service="${1}"
  local _return_host="${2}"
  local _return_port="${3}"
  local _return_dbname="${4}"
  local _return_user="${5}"
  local _return_password="${6}"

  ini_parser "${PG_SERVICE_CONF}"

  if [[ "$(type -t ini.section.${service})" != "function" ]]; then
    echo_red '******************'
    echo_red 'Service not found!'
    echo_red '******************'
    echo_red "Missing service ${service} in ${PG_SERVICE_CONF}"
    exit 3
  fi

  ini.section.${service}
  printf -v "${_return_host}" '%s' "${host}"
  printf -v "${_return_port}" '%s' "${port}"
  printf -v "${_return_dbname}" '%s' "${dbname}"
  printf -v "${_return_user}" '%s' "${user}"
  printf -v "${_return_password}" '%s' "${password}"
}

select_db() {
  local db_alias=$1
  local _return_service=$2

  local db_service

  PS3="Choose a ${BOLD}${db_alias}${NORM} database: "
  select db_service in $(cat "${PG_SERVICE_CONF}" | egrep '^\[.*\]' | sed -E 's/\[|\]//g'); do
    printf -v "${_return_service}" '%s' "${db_service}"
    break;
  done
}

echo_db_info() {
  local db_alias="${1}"
  local db_service="${2}"
  local db_host="${3}"
  local db_port="${4}"
  local db_dbname="${5}"
  local db_user="${6}"
  local db_password="${7}"

  echo
  echo ${BOLD}${db_alias}${NORM} database connection information:
  echo
  echo -e "\t${BOLD}${db_alias}${NORM} Host: ${!db_host}"
  echo -e "\t${BOLD}${db_alias}${NORM} Port: ${!db_port}"
  echo -e "\t${BOLD}${db_alias}${NORM} DB Name: ${!db_dbname}"
  echo -e "\t${BOLD}${db_alias}${NORM} Username: ${!db_user}"
  echo -e "\t${BOLD}${db_alias}${NORM} Password: $([[ -n "${!db_password}" ]] && echo '***' || echo_yellow "Password NOT set in ${PG_SERVICE_CONF}" )"
  echo
}

psql_cmd() {
  local service_name=$1
  shift
  psql -d "service=${service_name}" "$@"
}

array_contains() {
  local el="${1}"
  shift
  local arr=("${@}")
  local e
  for e in "${arr[@]}"; do
    if [[ "${e}" == "${el}" ]]; then
      echo -n y
      return
    fi
  done
  echo -n n
}

join_by() {
  local _result=${1}
  local sep=${2}
  local arr=${3}
  shift 3 || shift $(($#))
  printf -v "${_result}" "%s" "${arr}${@/#/${sep}}"
}

list_tables() {
  local service="${1}"
  local schema="${2}"
  psql_cmd "${service}" -tAc "select table_name from information_schema.tables where table_schema='${schema}'"
}

select_tables() {
  local service="${1}"
  local schema="${2}"
  local tifs="${3}"
  local _result="${4}"

  local all_tables=($(list_tables ${service} ${schema}))

  local entire_schema="${BOLD}Entire Schema${NORM}"
  local done_tables="${BOLD}Done selecting tables${NORM}"

  local tables=()

  PS3="Choose tables: "
  select table in "${all_tables[@]}" "${entire_schema}" "${done_tables}"; do
    case ${table} in
      ${entire_schema})
        echo
        echo "${BOLD}All tables selected${NORM}; copying entire schema"
        echo
        tables=(${ENTIRE_SCHEMA})
        break;
        ;;
      ${done_tables})
        # TODO: Check to make sure that at least one table is selected
        break;
        ;;
      *)
        if [[ "$(array_contains ${table} "${tables[@]}")" == "y" ]]; then
          for i in "${!tables[@]}"; do
            [[ "${tables[$i]}" == "${table}" ]] && unset "tables[$i]"
          done
          local removed_tables
          join_by removed_tables "," "${tables[@]}"
          echo_green "Removed ${table} (${removed_tables})"
        else
          tables+=("${table}")
          local added_tables
          join_by added_tables "," "${tables[@]}"
          echo_green "Added ${table} (${added_tables})"
        fi
        ;;
    esac
  done
  join_by ${_result} "${tifs}" "${tables[@]}"
}

create_backup_location() {
  local base_name="${1}"
  local backup="${2}"
  local location="${3}"
  local unattended="${4}"
  local _result="${5}"

  # If we are saving the backup, default to the working directory
  if [[ "${backup}" == "save" || -n "${location}" ]]; then
    local working_dir="$(pwd)"
    local default_location="${working_dir}/${base_name}.dmp"
    if [[ -z "${location}" && "${unattended}" != "true" ]]; then
      echo "Relative backup paths will be resolved relative to '${working_dir}'"
      local input
      read -r -p "Backup location (${default_location}): " input
      location=${input:-${default_location}}
    else
      location="${location:-${default_location}}"
    fi

    # Crudely resolve relative paths (does not simplify)
    [[ "${location:0:1}" == "/" ]] || location="${working_dir}/${location}"

    if [[ -e "${location}" ]]; then
      echo
      echo_yellow "*******************************"
      echo_yellow "Cannot overwrite existing file."
      echo_yellow "*******************************"
      echo_yellow "Found existing file at: '${location}'"
      exit 4;
    fi

    # Make an empty backup file
    touch "${location}"
  else
    # This results in the file existing
    location="$(mktemp)"
  fi
  printf -v "${_result}" '%s' "${location}"
}

create_backup() {
  local backup_location="${1}"
  local backup_cmd="${2}"
  # Append to the location because it should already have been created
  local start_msg="Creating backup at ${backup_location}"
  if [[ -x "$(command -v pv)" ]]; then
    echo_green "${start_msg} (progress total will be less than DB size)..."
    ${backup_cmd} | pv >> "${backup_location}"
  else
    echo_green "${start_msg} (may be slow; install 'pv' for progress)..."
    ${backup_cmd} >> "${backup_location}"
  fi
  echo_green "Backup completed successfully."
}

check_database() {
  local service="${1}"
  set +e
  psql_cmd "${service}" --command="SELECT version();" >/dev/null 2>&1
  local retval=$?
  set -e
  [[ ${retval} -eq 0 ]] && echo -n 'y' || echo -n 'n'
}

copy_database() {
  local db_source_service="${1}"
  local db_source_host="${2}"
  local db_source_port="${3}"
  local db_source_dbname="${4}"
  local db_source_user="${5}"
  local db_source_password="${6}"
  local db_destination_service="${7}"
  local db_destination_host="${8}"
  local db_destination_port="${9}"
  local db_destination_dbname="${10}"
  local db_destination_user="${11}"
  local db_destination_password="${12}"
  local backup="${13}"
  local backup_location="${14}"
  local transaction="${15}"
  local unattended="${16}"

  local db_source_size=$(psql_cmd "${db_source_service}" -tAc "select pg_size_pretty(pg_database_size('${db_source_dbname}'));")

  if [[ "${unattended}" != "true" ]]; then
    echo
    echo "About to copy entire database from:"
    echo
    echo_green "\t${db_source_user}/***@${db_source_host}:${db_source_port}/${db_source_dbname}"
    echo
    echo to:
    echo
    echo_red "\t${db_destination_user}/***@${db_destination_host}:${db_destination_port}/${db_destination_dbname}"
    echo
    echo_yellow "Please check for available disk space; ${BOLD}${db_source_size}${NORM} required."
    echo
    read -r -p "Are you sure? (yes to confirm) " confirm
    case ${confirm} in
      [yY][eE][sS])
        # Let fall through to do the copy
        ;;
      *)
        echo
        echo_green "Aborted"
        exit 0
        ;;
    esac
    echo
  fi

  local backup_location
  local dump_cmd="pg_dump -d service=${db_source_service} -Fc"
  local restore_cmd="pg_restore -d service=${db_destination_service} -c --if-exists"

  echo_green "Checking for existing DB..."
  local database_status="$(check_database ${db_destination_service})"

  if [[ "${database_status}" != "y" ]]; then
    echo_red "*****************"
    echo_red "Database missing!"
    echo_red "*****************"
    echo_red "Database ${db_destination_dbname} does not exist on ${db_destination_host}"
    return -1
  fi

  # Only backup if we are not doing the restore in a transaction
  if [[ "${transaction}" == "true" ]]; then
    restore_cmd="${restore_cmd} --single-transaction"
  elif [[ "${backup}" != "skip" ]]; then
    create_backup_location "${db_destination_dbname}" "${backup}" "${backup_location}" "${unattended}" backup_location
    create_backup "${backup_location}" "${dump_cmd}"
  fi

  local start_msg="Executing copy of entire DB from ${db_source_host} to ${db_destination_host}"
  set +e
  if [[ -x "$(command -v pv)" ]]; then
    echo_green "${start_msg} (progress total will be less than DB size)..."
    ${dump_cmd} | pv | ${restore_cmd}
  else
    echo_green "${start_msg} (may be slow; install 'pv' for progress)..."
    ${dump_cmd} | ${restore_cmd}
  fi
  local retval=$?
  set -e

  if [[ ${retval} -eq 0 ]]; then
    echo_green "Copy completed."
    [[ "${backup}" != "save" ]] && rm -f ${backup_location}
    return 0;
  fi

  echo_red "************"
  echo_red "Copy failed!"
  echo_red "************"
  echo_red "Note that full database restores must be done as a super-user"

  # No need to restore if we have a failed transaction
  if [[ -r "${backup_location}" && "${transaction}" != "true" ]]; then
    # Attempt to restore the backup
    echo_yellow "Attempting to restore target DB from backup..."
    ${restore_cmd} "${backup_location}"
    echo_yellow "Restore of backup completed successfully."
    [[ "${backup}" != "save" ]] && rm -f ${backup_location}
  fi
  return ${retval}
}

list_schemas() {
  local service="${1}"
  psql_cmd "${service}" -tA << EOF
select schema_name
  from information_schema.schemata
 where schema_name in (select replace(state_slug, '-', '_') from public.state)
EOF
}

select_schema() {
  local service="${1}"
  local _result="${2}"

  local entire_database="${BOLD}Entire Database${NORM}"
  all_source_schemas=$(list_schemas ${service})
  PS3="Choose a schema to copy: "
  select schema in ${all_source_schemas} "${entire_database}"; do
    [[ "${schema}" == "${entire_database}" ]] && schema="${ENTIRE_DATABASE}"
    printf -v "${_result}" '%s' "${schema}"
    break;
  done
}

check_schema() {
  local service="${1}"
  local schema="${2}"
  psql_cmd "${service}" -tAc "select schema_name from information_schema.schemata where schema_name='${schema}'"
}

schema_size() {
  local service="${1}"
  local schema_name="${2}"
  psql_cmd "${service}" -tA <<EOF
SELECT pg_size_pretty(SUM(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::BIGINT)
  FROM pg_tables
 WHERE schemaname = '${schema_name}';
EOF
}

copy_schema() {
  local db_source_service="${1}"
  local db_source_host="${2}"
  local db_source_port="${3}"
  local db_source_dbname="${4}"
  local db_source_user="${5}"
  local db_source_password="${6}"
  local db_destination_service="${7}"
  local db_destination_host="${8}"
  local db_destination_port="${9}"
  local db_destination_dbname="${10}"
  local db_destination_user="${11}"
  local db_destination_password="${12}"
  local schema_name="${13}"
  local backup="${14}"
  local backup_location="${15}"
  local transaction="${16}"
  local unattended="${17}"

  local source_schema_size=$(schema_size ${db_source_service} ${schema_name})

  if [[ "${unattended}" != "true" ]]; then
    echo
    echo "About to copy ${BOLD}${schema_name}${NORM} (${BOLD}${source_schema_size}${NORM}) from:"
    echo
    echo_green "\t${db_source_user}/***@${db_source_host}:${db_source_port}/${db_source_dbname} (schema: ${BOLD}${schema_name}${NORM})"
    echo
    echo to:
    echo
    echo_red "\t${db_destination_user}/***@${db_destination_host}:${db_destination_port}/${db_destination_dbname} (schema: ${BOLD}${schema_name}${NORM})"
    echo
    echo_yellow "Please check for available disk space; ${BOLD}${source_schema_size}${NORM} required."
    echo
    read -r -p "Are you sure? (yes to confirm) " confirm
    case ${confirm} in
      [yY][eE][sS])
        # Let fall through to do the copy
        ;;
      *)
        echo
        echo_green "Aborted"
        exit 0
        ;;
    esac
    echo
  fi

  local backup_location
  local dump_cmd="pg_dump -d service=${db_source_service} -n ${schema_name} -Fc"
  local restore_cmd="pg_restore -d service=${db_destination_service} -c --if-exists"

  echo_green "Checking for existing schema..."
  local existing_schema=$(check_schema ${db_destination_service} ${schema_name})

  if [[ -z "${existing_schema}" && "${backup}" == "save" ]]; then
    echo
    echo_yellow "********************************************************"
    echo_yellow "Invalid state: Cannot backup schema that does not exist."
    echo_yellow "********************************************************"
    return 5
  fi

  # Only backup if we are not doing the restore in a transaction, there is an existing schema, and it is not explicitly skipped
  if [[ "${transaction}" == "true" ]]; then
    restore_cmd="${restore_cmd} --single-transaction"
  elif [[ -n "${existing_schema}" && "${backup}" != "skip" ]]; then
    create_backup_location "${db_destination_dbname}__${schema_name}" "${backup}" "${backup_location}" "${unattended}" backup_location
    # Append to the location because we just created an empty file
    create_backup "${backup_location}" "${dump_cmd}"
  fi

  local start_msg="Executing copy of ${schema_name} from ${db_source_host} to ${db_destination_host}"
  set +e
  if [[ -x "$(command -v pv)" ]]; then
    echo_green "${start_msg} (progress total will be less than DB size)..."
    ${dump_cmd} | pv | ${restore_cmd}
  else
    echo_green "${start_msg} (may be slow; install 'pv' for progress)..."
    ${dump_cmd} | ${restore_cmd}
  fi
  local retval=$?
  set -e

  if [[ ${retval} -eq 0 ]]; then
    echo_green "Copy completed."
    [[ "${backup}" != "save" ]] && rm -f ${backup_location}
    return 0;
  fi

  echo_red "************"
  echo_red "Copy failed!"
  echo_red "************"

  # No need to restore if we have a failed transaction
  if [[ -r "${backup_location}" && "${transaction}" != "true" ]]; then
    # Attempt to restore the backup
    echo_yellow "Attempting to restore target schema from backup..."
    ${restore_cmd} "${backup_location}"
    echo_yellow "Restore of backup completed successfully."
    [[ "${backup}" != "save" ]] && rm -f ${backup_location}
  fi
  return ${retval}
}

check_tables() {
  local service="${1}"
  local schema="${2}"
  shift 2
  local tables=("${@}")

  local tables_param
  join_by tables_param "', '" "${tables[@]}"
  tables_param="'${tables_param}'"

  psql_cmd "${service}"  -tAc "select tablename from pg_tables where schemaname='${schema}' and tablename in (${tables_param})"
}

table_type() {
  local service="${1}"
  local schema="${2}"
  local table="${3}"
  local table_type="$(psql_cmd "${service}" -tAc "select table_type from information_schema.tables where table_schema='${schema}' and table_name='${table}'")"
  [[ "${table_type}" == "VIEW" ]] && echo -n "VIEW" || echo -n "TABLE"
}

tables_size() {
  local service="${1}"
  local schema_name="${2}"
  shift 2
  local tables=("${@}")

  local tables_param
  join_by tables_param "', '" "${tables[@]}"
  tables_param="'${tables_param}'"

  psql_cmd "${service}" -tA <<EOF
SELECT pg_size_pretty(SUM(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)))::BIGINT)
  FROM pg_tables
 WHERE schemaname = '${schema_name}'
   AND tablename in (${tables_param});
EOF
}

copy_tables() {
  local db_source_service="${1}"
  local db_source_host="${2}"
  local db_source_port="${3}"
  local db_source_dbname="${4}"
  local db_source_user="${5}"
  local db_source_password="${6}"
  local db_destination_service="${7}"
  local db_destination_host="${8}"
  local db_destination_port="${9}"
  local db_destination_dbname="${10}"
  local db_destination_user="${11}"
  local db_destination_password="${12}"
  local schema_name="${13}"
  local backup="${14}"
  local backup_location="${15}"
  local transaction="${16}"
  local unattended="${17}"
  shift 17
  local tables=("${@}")

  local source_tables_size=$(tables_size "${db_source_service}" "${schema_name}" "${tables[@]}")

  if [[ "${unattended}" != "true" ]]; then
    echo
    echo "About to copy (${BOLD}${source_tables_size}${NORM})"
    echo
    for table in "${tables[@]}"; do
      echo -e "\t${table}"
    done
    echo
    echo "from:"
    echo
    echo_green "\t${db_source_user}/***@${db_source_host}:${db_source_port}/${db_source_dbname} (schema: ${BOLD}${schema_name}${NORM})"
    echo
    echo "to:"
    echo
    echo_red "\t${db_destination_user}/***@${db_destination_host}:${db_destination_port}/${db_destination_dbname} (schema: ${BOLD}${schema_name}${NORM})"
    echo
    echo_yellow "Please check for available disk space; ${BOLD}${source_tables_size}${NORM} required."
    echo
    read -r -p "Are you sure? (yes to confirm) " confirm
    case ${confirm} in
      [yY][eE][sS])
        # Let fall through to do the copy
        ;;
      *)
        echo
        echo_green "Aborted"
        exit 0
        ;;
    esac
    echo
  fi

  echo_green "Checking for existing schema..."
  local existing_schema=$(check_schema ${db_destination_service} ${schema_name})

  local existing_tables=()
  if [[ -n "$existing_schema" ]]; then
    echo_green "Checking for existing tables..."
    existing_tables=($(check_tables "${db_destination_service}" "${schema_name}" "${tables[@]}"))
  fi

  local backup_location
  if [[ "${backup}" == "save" && (-z "$existing_schema" || ${#existing_tables[@]} -eq 0) ]]; then
    echo
    echo_yellow "******************************************************"
    echo_yellow "Invalid state: Cannot backup tables that do not exist."
    echo_yellow "******************************************************"
    return 5
  elif [[ -z "$existing_schema" ]]; then
    echo
    echo_yellow "Schema does not exist! Creating...."
    echo
    # Copy the DDL from source to destination for the whole schema to get it created
    pg_dump -d "service=${db_source_service}" -s -n "${schema_name}" -Fc | pg_restore -d "service=${db_destination_service}"

    # Drop all the tables in the schema we just crated
    local all_destination_tables=($(list_tables ${db_destination_service} ${schema_name}))
    local drop_empty_sql="BEGIN;${LF}"
    for table in "${all_destination_tables[@]}"; do
      local tt="$(table_type ${db_destination_service} ${schema_name} ${table})"
      drop_empty_sql+="drop ${tt} if exists ${QUOT}${schema_name}${QUOT}.${QUOT}${table}${QUOT} cascade;${LF}"
    done
    drop_empty_sql+="COMMIT;${LF}"
    psql_cmd "${db_destination_service}" -tAc "${drop_empty_sql}"

    echo_green "Created schema ${schema_name}...."
    echo
  elif [[ "${backup_location}" != "skip" && "${transaction}" != "true" && ${#existing_tables[@]} -gt 0 ]]; then
    # Means that we need to do a backup
    create_backup_location "${db_destination_dbname}__${schema_name}__${#existing_tables}_tables" "${backup}" "${backup_location}" "${unattended}" backup_location
    local existing_table_flags=()
    for table in "${existing_tables[@]}"; do
      existing_table_flags+=("-t")
      existing_table_flags+=("${schema_name}.${table}")
    done
    local backup_cmd="pg_dump -d service=${db_source_service} -n ${schema_name} ${existing_table_flags[@]}"
    create_backup "${backup_location}" "${backup_cmd}"
  fi

  local table_flags=()
  for table in "${tables[@]}"; do
    table_flags+=("-t")
    table_flags+=("${schema_name}.${table}")
  done
  local dump_cmd="pg_dump -d service=${db_source_service} ${table_flags[@]} -Fc"
  local restore_cmd="pg_restore -d service=${db_destination_service} -c --if-exists"
  local start_msg="Executing copy from ${db_source_host} to ${db_destination_host}"

  [[ "${transaction}" == "true" ]] && restore_cmd="${restore_cmd} --single-transaction"

  set +e
  if [[ -x "$(command -v pv)" ]]; then
    echo_green "${start_msg} (progress total will be less than DB size)..."
    ${dump_cmd} | pv | ${restore_cmd}
  else
    echo_green "${start_msg} (may be slow; install 'pv' for progress)..."
    ${dump_cmd} | ${restore_cmd}
  fi
  local retval=$?
  set -e

  if [[ ${retval} -eq 0 ]]; then
    echo_green "Copy completed."
    [[ "${backup}" != "save" ]] && rm -f ${backup_location}
    return 0;
  fi

  echo_red "************"
  echo_red "Copy failed!"
  echo_red "************"

  # No need to restore if we have a failed transaction
  if [[ -r "${backup_location}" && "${transaction}" != "true" ]]; then
    # Attempt to restore the backup
    echo_yellow "Attempting to restore target tables from backup..."
    ${restore_cmd} "${backup_location}"
    echo_yellow "Restore of backup completed successfully."
    [[ "${backup}" != "save" ]] && rm -f ${backup_location}
  fi
  return ${retval}
}

main() {
  local db_source_service
  local db_destination_service
  local backup="delete"
  local backup_location
  local schema_name
  local tables=()
  local transaction="false"
  local unattended="false"

  OPTIND=1
  while getopts "h?bB:d:Df:ns:St:Tu" opt; do
    case "${opt}" in
      h|\?)
          show_help
          exit 0
          ;;
      b)  backup="save"
          ;;
      B)  backup_location="${OPTARG}"
          ;;
      d)  db_destination_service="${OPTARG}"
          ;;
      D)  schema_name="${ENTIRE_DATABASE}"
          ;;
      f)  db_source_service="${OPTARG}"
          ;;
      n)  backup="skip"
          ;;
      s)  schema_name="${OPTARG}"
          ;;
      S)  tables=("${ENTIRE_SCHEMA}")
          ;;
      t)  tables+=("${OPTARG}")
          ;;
      T)  transaction="true"
          ;;
      u)  unattended="true"
          ;;
    esac
  done

  local db_source_host
  local db_source_port
  local db_source_dbname
  local db_source_user
  local db_source_password

  local db_destination_host
  local db_destination_port
  local db_destination_dbname
  local db_destination_user
  local db_destination_password

  [[ -n "${db_source_service}" ]] || select_db source db_source_service
  parse_ini_info ${db_source_service} db_source_host db_source_port db_source_dbname db_source_user db_source_password
  echo_db_info Source db_source_service db_source_host db_source_port db_source_dbname db_source_user db_source_password

  [[ -n "${db_destination_service}" ]] || select_db destination db_destination_service
  parse_ini_info ${db_destination_service} db_destination_host db_destination_port db_destination_dbname db_destination_user db_destination_password
  echo_db_info Destination db_destination_service db_destination_host db_destination_port db_destination_dbname db_destination_user db_destination_password

  [[ -n "${schema_name}" ]] || select_schema ${db_source_service} schema_name

  if [[ "${schema_name}" == "${ENTIRE_DATABASE}" ]]; then
    copy_database "${db_source_service}" "${db_source_host}" "${db_source_port}" "${db_source_dbname}" "${db_source_user}" "${db_source_password}" \
                  "${db_destination_service}" "${db_destination_host}" "${db_destination_port}" "${db_destination_dbname}" "${db_destination_user}" "${db_destination_password}" \
                  "${backup}" "${backup_location}" "${transaction}" "${unattended}"
    return 0
  fi

  echo "Selected schema: ${schema_name}"
  echo

  if [[ ${#tables[@]} -eq 0 ]]; then
    local table_list
    select_tables "${db_source_service}" "${schema_name}" "${IFS}" table_list
    local oifs="${IFS}"
    local IFS=$'\t'
    tables=(${table_list})
    IFS="${oifs}";
  fi

  if [[ "${tables[0]}" == "${ENTIRE_SCHEMA}" ]]; then
    copy_schema "${db_source_service}" "${db_source_host}" "${db_source_port}" "${db_source_dbname}" "${db_source_user}" "${db_source_password}" \
                "${db_destination_service}" "${db_destination_host}" "${db_destination_port}" "${db_destination_dbname}" "${db_destination_user}" "${db_destination_password}" \
                "${schema_name}" "${backup}" "${backup_location}" "${transaction}" "${unattended}"
    return 0
  fi

  local selected_tables
  join_by selected_tables "," "${tables[@]}"
  echo_green "Tables selected (${#tables[@]}): ${selected_tables}"
  echo

  if [[ ${#tables[@]} -gt 0 ]]; then
    copy_tables "${db_source_service}" "${db_source_host}" "${db_source_port}" "${db_source_dbname}" "${db_source_user}" "${db_source_password}" \
                "${db_destination_service}" "${db_destination_host}" "${db_destination_port}" "${db_destination_dbname}" "${db_destination_user}" "${db_destination_password}" \
                "${schema_name}" "${backup}" "${backup_location}" "${transaction}" "${unattended}" "${tables[@]}"
    return 0
  fi
}

init
main "${@}"