Copy the contents of a table or view into a local database.

db_dump_thing.sh

#!/usr/bin/env bash

set -eu

help() {
	cat <<EOF
  Copy the contents of a table or view into a local database.
  The copy is always a table, so view dependencies don't have to be downloaded.
  SELECT is optionally granted to a named user.
  dump --ssh USER@HOST:PORT --from USER@DATABASE:PORT/table --to USER@DATABASE:PORT --grant USER
EOF
}

parse(){
	echo "$1" | sed -nr 's!^([-0-9a-zA-Z_]+)@([-0-9a-zA-Z_]+):([0-9]+)(/([-0-9a-zA-Z_]+))?$!\1 \2 \3 \5!g;ta;b;:a;p'
}

SSH_ARG=""
FROM_ARG=""
TO_ARG=""
GRANT_ARG=""

while (( $# > 0 )) ; do
	case $1 in
	--help|\
	-help|\
	-h)		help ; exit 0 ;;
	--ssh)		SSH_ARG="$2" ; shift 2 ;;
	--from)		FROM_ARG="$2" ; shift 2 ;;
	--to)		TO_ARG="$2" ; shift 2 ;;
	--grant)	GRANT_ARG="$2" ; shift 2 ;;
	*)	{ echo "Unknown arg: $1" >&2 ; help ; exit 1 ; } >&2 ;
	esac
done

test -n "${SSH_ARG:-}"  || { echo "Missing --ssh arg."  >&2 ; help ; exit 1 ; } >&2
test -n "${FROM_ARG:-}" || { echo "Missing --from arg." >&2 ; help ; exit 1 ; } >&2
test -n "${TO_ARG:-}"   || { echo "Missing --to arg."   >&2 ; help ; exit 1 ; } >&2

SSH_USER=""
SSH_HOST=""
SSH_PORT=""
FROM_DB_USER=""
FROM_DB_NAME=""
FROM_DB_PORT=""
FROM_DB_TABLE=""
TO_DB_USER=""
TO_DB_NAME=""
TO_DB_PORT=""
TO_DB_TABLE=""

set -- $(parse "$SSH_ARG")  ; (( $# == 3 )) || { echo "Bad --ssh arg: $SSH_ARG"   ; help ; exit 1 ; } >&2 ; SSH_USER=$1 ; SSH_HOST=$2 ; SSH_PORT=$3
set -- $(parse "$FROM_ARG") ; (( $# == 4 )) || { echo "Bad --from arg: $FROM_ARG" ; help ; exit 1 ; } >&2 ; FROM_DB_USER=$1 ; FROM_DB_NAME=$2 ; FROM_DB_PORT=$3 ; FROM_DB_TABLE=$4
set -- $(parse "$TO_ARG")   ; (( $# == 3 )) || { echo "Bad --to arg: $TO_ARG"     ; help ; exit 1 ; } >&2 ; TO_DB_USER=$1 ; TO_DB_NAME=$2 ; TO_DB_PORT=$3 ; TO_DB_TABLE=$FROM_DB_TABLE

#####################################
#        I N S T A L L E R S        #
# Generic install functions taken from: https://raw.githubusercontent.com/bitdivine/bashops-install/master/install

install(){ # Need functions "get" and (optionally) "check".
	set -eu
	local recycler_install_name="$*"
	local recycler_install_ootfile="$(mktemp ",install.XXXXXXXX" 2>/dev/null || mktemp "${TMPDIR:-/tmp}/,install.XXXXXXXX")"
	   { install_check && install_extant ; } \
	|| { install_proceeding && ( get &>"$recycler_install_ootfile" ; ) && install_check_infdef && install_succeeded ; } \
	|| install_failed
}
install_check()       {	check &>/dev/null ; }
install_check_infdef(){ ! type check &>/dev/null || install_check ; }
install_extant()      { printf ":-) %s\n" "$recycler_install_name already present" ; }
install_proceeding()  { printf ":-O %s\n" "Installing $recycler_install_name..." ; }
install_succeeded()   { printf ":-) %s\n" "Installed $recycler_install_name" ; rm -f "$recycler_install_ootfile" ; }
install_failed()      { printf ":-( %s\n" "Failed to install ${recycler_install_name:-}.  Log in '${recycler_install_ootfile:-/dev/null}'" ; cat "${recycler_install_ootfile:-}" ; declare -f check || true ; declare -f get || true ; printf "X-( %s\n" "Bailing out at ${recycler_install_name:-}" ; exit 1 ; }

#####################################
#        C O P Y   D A T A          #

( # Ceate local table for the contents of the remote materialized view.
  # The long query gets the list of fields in the view and their types and makes a create table query from them.
	get(){
		ssh $SSH_USER@$SSH_HOST sudo -u $FROM_DB_USER psql $FROM_DB_NAME -Xqt --port=$FROM_DB_PORT <<EOF | sudo -u $TO_DB_USER psql $TO_DB_NAME --port=$TO_DB_PORT
copy (
select
    'drop table if exists $TO_DB_TABLE ; create table $TO_DB_TABLE (' || string_agg(attr.attname || ' ' || trim(leading '_' from tp.typname), ', ') || ');'
from pg_catalog.pg_attribute as attr
join pg_catalog.pg_class as cls on cls.oid = attr.attrelid
join pg_catalog.pg_namespace as ns on ns.oid = cls.relnamespace
join pg_catalog.pg_type as tp on tp.typelem = attr.atttypid
where
    ns.nspname = 'public' and
    cls.relname = '$TO_DB_TABLE' and
    not attr.attisdropped and
    cast(tp.typanalyze as text) = 'array_typanalyze' and
    attr.attnum > 0
) to stdout;
EOF
	}
	install table
)

( # Dump table:
	get(){
		echo "COPY (select * from $FROM_DB_TABLE) TO STDOUT DELIMITER ',' CSV HEADER;" |\
		ssh $SSH_USER@$SSH_HOST sudo -u $FROM_DB_USER psql $FROM_DB_NAME -Xqt --port=$FROM_DB_PORT --quiet |\
		tee ,db-dump-$FROM_DB_TABLE |\
		sudo -u $TO_DB_USER psql $TO_DB_NAME -t --pset="footer=off" --port=$TO_DB_PORT -c "DELETE FROM $TO_DB_TABLE; COPY $TO_DB_TABLE FROM STDIN DELIMITER ',' CSV HEADER;"
	}
	install local dump
)

[[ "${GRANT_ARG:-}" == "" ]] ||
( # User may access?
	user=$GRANT_ARG
	table=$TO_DB_TABLE
	check(){ sudo -u $TO_DB_USER psql --port=$TO_DB_PORT $TO_DB_NAME -t -c "copy (SELECT * FROM has_table_privilege('$GRANT_ARG', '$TO_DB_TABLE', 'select')) to STDOUT;" | grep -qw t ; }
	get(){ sudo -u $TO_DB_USER psql --port=$TO_DB_PORT $TO_DB_NAME -t -c "GRANT SELECT ON $TO_DB_TABLE TO $GRANT_ARG;" ; }
	install user database permissions
)