pg_pipe.sh

#!/bin/bash

set -e

DUMP_ARGS=""
SOURCE_PORT=5432
TARGET_PORT=5432

# Prepare argv
argv=( "$@" )

prepare() {
	export PGPASSWORD=$TARGET_PASSWORD

	psql -h $TARGET_HOST -p $TARGET_PORT -U $TARGET_USER $TARGET_DATABASE <<EOF
	DROP SCHEMA IF EXISTS ${TARGET_SCHEMA}_tmp;
	CREATE SCHEMA ${TARGET_SCHEMA}_tmp;
EOF
}

dump() {
	export PGPASSWORD=$SOURCE_PASSWORD

	pg_dump -Fp --no-owner -n $SOURCE_SCHEMA $DUMP_ARGS -x -h $SOURCE_HOST -p $SOURCE_PORT -U $SOURCE_USER -d $SOURCE_DATABASE
}

rename() {
	sed \
		-e "s/WITH SCHEMA $SOURCE_SCHEMA/WITH OWNER ${TARGET_SCHEMA}_tmp/g" \
		-e "s/SET search_path = .*/SET search_path = ${TARGET_SCHEMA}_tmp;/g"
}

pipe() {
	export PGPASSWORD=$TARGET_PASSWORD

	psql -h $TARGET_HOST -p $TARGET_PORT -U $TARGET_USER $TARGET_DATABASE
}

rotate() {
	export PGPASSWORD=$TARGET_PASSWORD

	psql -h $TARGET_HOST -p $TARGET_PORT -U $TARGET_USER $TARGET_DATABASE <<EOF
	START TRANSACTION;
	DROP SCHEMA IF EXISTS $TARGET_SCHEMA CASCADE;
	ALTER SCHEMA ${TARGET_SCHEMA}_tmp RENAME TO $TARGET_SCHEMA;
	COMMIT;
EOF
}

usage() {
	echo "usage: pg_pipe [options]"
	echo ""
	echo "Options:"
	echo "    -x <pattern>"
	echo ""
	echo "Source Options:"
	echo "    -Sh <hostname>"
	echo "    -Sp <port>"
	echo "    -Su <username>"
	echo "    -Sd <database>"
	echo "    -Ss <schema>"
	echo ""
	echo "Target Options:"
	echo "    -Th <hostname>"
	echo "    -Tp <port>"
	echo "    -Tu <username>"
	echo "    -Td <database>"
	echo "    -Ts <schema>"
}

run() {
	echo "Preparing..."
	prepare

	echo "Dumping..."
	dump | rename | pipe

	echo "Rotating..."
	rotate

	echo "Done!"
	exit 0
}

parse_source() {
	# Parse
	getopts ":h:p:u:s:d:" opt

	case $opt in
		h) SOURCE_HOST=$OPTARG ;;
		p) SOURCE_PORT=$OPTARG ;;
		u) SOURCE_USER=$OPTARG ;;
		s) SOURCE_SCHEMA=$OPTARG ;;
		d) SOURCE_DATABASE=$OPTARG ;;
	esac
}

parse_target() {
	# Parse
	getopts ":h:p:u:s:d:" opt

	case $opt in
		h) TARGET_HOST=$OPTARG ;;
		p) TARGET_PORT=$OPTARG ;;
		u) TARGET_USER=$OPTARG ;;
		s) TARGET_SCHEMA=$OPTARG ;;
		d) TARGET_DATABASE=$OPTARG ;;
	esac
}

parse_host() {
	HOST=$1
	ARG=$2

	# Patch arguments
	argv[OPTIND-2]="-$ARG"
	OPTIND=$((OPTIND-1))

	# Jump
	case $HOST in
		SOURCE) parse_source "${argv[@]}" ;;
		TARGET) parse_target "${argv[@]}" ;;
	esac
}

parse_args() {
	while getopts ":x:S:T:" opt; do
		case $opt in
			S) parse_host SOURCE $OPTARG ;;
			T) parse_host TARGET $OPTARG ;;
			x) DUMP_ARGS="$DUMP_ARGS -T $OPTARG" ;;
		esac
	done
}

validate_args() {
	if [ -z "$SOURCE_HOST" ] || [ -z "$SOURCE_DATABASE" ] || [ -z "$SOURCE_PORT" ] || [ -z "$SOURCE_USER" ] || [ -z "$SOURCE_SCHEMA" ]; then
		echo "Missing source options"
		exit 1
	fi

	if [ -z "$TARGET_HOST" ] || [ -z "$TARGET_DATABASE" ] || [ -z "$TARGET_PORT" ] || [ -z "$TARGET_USER" ] || [ -z "$TARGET_SCHEMA" ]; then
		echo "Missing target options"
		exit 1
	fi
}

read_passwords() {
	if [ -z "$SOURCE_PASSWORD" ]; then
		echo -n 'Source Password: '
		read -s SOURCE_PASSWORD
		echo
	fi

	if [ -z "$TARGET_PASSWORD" ]; then
		echo -n 'Target Password: '
		read -s TARGET_PASSWORD
		echo
	fi
}

parse_args $*
validate_args
read_passwords
run