This script can be used to copy db, with SSH Tunneling support. Use it with caution since it cleans up the destination database.

pg-db-transfer.sh

#!/bin/bash
# GREEN="\e[42m"
function set_foreground_color() {
    if [ -z "$1" ]
    then
        echo -e "$(tput setaf 1)Must pass color code from 0 to 9."
        exit 1
    else
        echo $(tput setaf "$1")
    fi
}
BLACK=$(set_foreground_color 0)
RED=$(set_foreground_color 1)
GREEN=$(set_foreground_color 2)
YELLOW=$(set_foreground_color 3)
BLUE=$(set_foreground_color 4)
MAGENTA=$(set_foreground_color 5)
CYAN=$(set_foreground_color 6)
WHITE=$(set_foreground_color 7)
NOT_SET=$(set_foreground_color 8)
RESET_COLOR=$(set_foreground_color 9)

: ${SOURCE_DB_PORT:=5432}
: ${SOURCE_TUNNELED_PORT:=$(shuf -i 2000-65000 -n 1)}
: ${DESTINATION_DB_PORT:=5432}
: ${DESTINATION_TUNNELED_PORT:=$(shuf -i 2000-65000 -n 1)}

POSITIONAL_ARGS=()

while [[ $# -gt 0 ]]; do
  case $1 in
    -spk|--source-private-key)
      SOURCE_PRIVATE_KEY="$2"
      shift # past argument
      shift # past value
      ;;
    -su|--source-user)
      SOURCE_USER="$2"
      shift
      shift
      ;;
    -sh|--source-host)
      SOURCE_HOST="$2"
      shift
      shift
      ;;
    -sdbu|--source-db-user)
      SOURCE_DB_USER="$2"
      shift
      shift
      ;;
    -sdbh|--source-db-host)
      SOURCE_DB_HOST="$2"
      shift
      shift
      ;;
    -sdbp|--source-db-port)
      SOURCE_DB_PORT="$2"
      shift
      shift
      ;;
    -sdbn|--source-db-name)
      SOURCE_DB_NAME="$2"
      shift
      shift
      ;;
    ## Destination
    -dpk|--destination-private-key)
      DESTINATION_PRIVATE_KEY="$2"
      shift # past argument
      shift # past value
      ;;
    -du|--destination-user)
      DESTINATION_USER="$2"
      shift
      shift
      ;;
    -dh|--destination-host)
      DESTINATION_HOST="$2"
      shift
      shift
      ;;
    -ddbu|--destination-db-user)
      DESTINATION_DB_USER="$2"
      shift
      shift
      ;;
    -ddbh|--destination-db-host)
      DESTINATION_DB_HOST="$2"
      shift
      shift
      ;;
    -ddbp|--destination-db-PORT)
      DESTINATION_DB_PORT="$2"
      shift
      shift
      ;;
    -ddbn|--destination-db-name)
      DESTINATION_DB_NAME="$2"
      shift
      shift
      ;;
    *)
      POSITIONAL_ARGS+=("$1") # save positional arg
      shift # past argument
      ;;
  esac
done

set -- "${POSITIONAL_ARGS[@]}" # restore positional parameters

if [[ -z $SOURCE_DB_USER || -z $DESTINATION_DB_USER ]]; then
    echo "${RED}Source and destination DB usernames are missing."
    echo "${CYAN}Usage: $0 [-spk SOURCE_SSH_PRIVATE_KEY] [-su SOURCE_SSH_USER] [-sh SOURCE_SSH_HOST] -sdbu SOURCE_DB_USER [-sdbp SOURCE_DB_PORT] [-sdbn SOURCE_DB_NAME] [-dpk DESTINATION_SSH_PRIVATE_KEY] [-du DESTINATION_SSH_USER] [-dh DESTINATION_SSH_HOST] -ddbu DESTINATION_DB_USER [-ddbp DESTINATION_DB_PORT] [-ddbn DESTINATION_DB_NAME]"
    exit 1
fi

if ! command -v pg_dump &> /dev/null
then
    echo $RED
    echo "pg_dump not found in PATH."
    exit 1
fi

if ! command -v psql &> /dev/null
then
  echo $RED
  echo "psql not found in PATH."
  exit 1
fi

# Source SSH tunneling
if [[ ! -z $SOURCE_PRIVATE_KEY ]]
then
  echo $CYAN
  if [[ -z $SOURCE_USER ]]
  then
  until read -p "Source SSH User: " SOURCE_USER && test "$SOURCE_USER" != ""; do
    continue
  done
  fi
    if [[ -z $SOURCE_HOST ]]
  then
   until read -p "Source SSH Host: " SOURCE_HOST && test "$SOURCE_HOST" != ""; do
    continue
  done
  fi


  ssh -i $SOURCE_PRIVATE_KEY $SOURCE_USER@$SOURCE_HOST id

  if [[ $? != 0 ]]
  then
    echo $RED
    echo "Source SSH Connection Failed"
    exit 1
  fi
  ssh -i $SOURCE_PRIVATE_KEY -N -L ${SOURCE_TUNNELED_PORT}:127.0.0.1:${SOURCE_DB_PORT} $SOURCE_USER@$SOURCE_HOST &
  SOURCE_SSH_PID=$!
  echo $SOURCE_SSH_PID
  SOURCE_DB_PORT=$SOURCE_TUNNELED_PORT
  sleep 10
fi


# Destination SSH tunneling
if [[ ! -z $DESTINATION_PRIVATE_KEY ]]
then
  echo $CYAN
  if [[ -z $DESTINATION_USER ]]
  then

  until read -p "Destination SSH User: " DESTINATION_USER && test "$DESTINATION_USER" != ""; do
    continue
  done
  fi
  if [[ -z $DESTINATION_HOST ]]
  then
   until read -p "Destination SSH Host: " DESTINATION_HOST && test "$DESTINATION_HOST" != ""; do
    continue
  done
  fi

  ssh -i $DESTINATION_PRIVATE_KEY $DESTINATION_USER@$DESTINATION_HOST id

  if [[ $? != 0 ]]
  then
    echo $RED
    echo "Destination SSH Connection Failed"
    exit 1
  fi
  ssh -i $DESTINATION_PRIVATE_KEY -N -L ${DESTINATION_TUNNELED_PORT}:127.0.0.1:${DESTINATION_DB_PORT} $DESTINATION_USER@$DESTINATION_HOST &
  DESTINATION_SSH_PID=$!
  echo $DESTINATION_SSH_PID
  DESTINATION_DB_PORT=$DESTINATION_TUNNELED_PORT
  sleep 10
fi



if [[ -z $SOURCE_DB_HOST ]]
then
  SOURCE_DB_HOST=127.0.0.1
fi

if [[ -z $DESTINATION_DB_HOST ]]
then
  DESTINATION_DB_HOST=127.0.0.1
fi

echo $MAGENTA
echo "Dumping from source to destination => "

if [[ -z $SOURCE_DB_NAME ]]
then
  echo $CYAN
  until read -p "Source DB Name: " SOURCE_DB_NAME && test "$SOURCE_DB_NAME" != ""; do
    continue
  done
fi

echo $CYAN
until read -s -p "Source DB Password: " SOURCE_DB_PASSWORD && test "$SOURCE_DB_PASSWORD" != ""; do
  continue
done

if [[ -z $DESTINATION_DB_NAME ]]
then
  echo $CYAN
  until read -p "Destination DB Name: " DESTINATION_DB_NAME && test "$DESTINATION_DB_NAME" != ""; do
    continue
  done
fi

echo $CYAN
until read -s -p "Destination DB Password: " DESTINATION_DB_PASSWORD && test "$DESTINATION_DB_PASSWORD" != ""; do
  continue
done

echo $MAGENTA
SOURCE_PG_STRING="postgres://$SOURCE_DB_USER:$SOURCE_DB_PASSWORD@$SOURCE_DB_HOST:$SOURCE_DB_PORT/$SOURCE_DB_NAME --column-inserts --if-exists --clean"
DESTINATION_PG_STRING="postgres://$DESTINATION_DB_USER:$DESTINATION_DB_PASSWORD@$DESTINATION_DB_HOST:$DESTINATION_DB_PORT/$DESTINATION_DB_NAME"
echo "Source PG String: $SOURCE_PG_STRING"
pg_dump $SOURCE_PG_STRING | psql $DESTINATION_PG_STRING

if [[ $? != 0 ]]
then
  echo $RED
  echo 'Failed.'
  if ps -p $SOURCE_SSH_PID &> /dev/null
  then
    kill -INT $SOURCE_SSH_PID
    sleep 2
  fi

  if ps -p $DESTINATION_SSH_PID &> /dev/null
  then
    kill -INT $DESTINATION_SSH_PID
    sleep 2
  fi
  exit 1
fi

if ps -p $SOURCE_SSH_PID &> /dev/null
  then
    kill -INT $SOURCE_SSH_PID
    sleep 2
fi

if ps -p $DESTINATION_SSH_PID &> /dev/null
  then
    kill -INT $DESTINATION_SSH_PID
    sleep 2
fi

echo $GREEN
echo 'Completed.'
exit 0