Bash script to copy a Postgres database from one host/instance to another

pgcopy.sh

#!/bin/bash
#
# NOTE: Although this script attempts to be safe, it has the potential
#       to be highly destructive; please run with caution.
#
# This copies the specified database from pgsql.rc.pdx.edu to
# postgresql.rc.pdx.edu.
#
# If the database is PostGIS-enabled, use the --postgis flag. This also
# requires that postgis_restore.pl is on your $PATH.
#
# Run with -h to see all options.

set -e -f -u -o pipefail

# TODO: Add script options for hosts.
OLD_HOST="pgsql.rc.pdx.edu"
NEW_HOST="postgresql.rc.pdx.edu"
PGPASSWORD="${PGPASSWORD:-""}"

# Options
DB_USER=""
DB_NAME=""
OWNER=""
DROP_FIRST="no"
POSTGIS_ENABLED="no"

while [[ $# -gt 0 ]]; do
    option="$1"
    case $option in
        -o|--owner)
            OWNER="$2"
            shift
            ;;
        -d|--drop-first)
            DROP_FIRST="yes"
            ;;
        -p|--postgis)
            POSTGIS_ENABLED="yes"
            ;;
        -h|--help)
            echo "Copy database from $OLD_HOST to $NEW_HOST"
            echo "Usage: pg_upgrade.sh [-o] <owner> [-d] [-p] [user] <database>"
            echo "    -o|--owner => Specify database owner [<database>_l]"
            echo "    -d|--drop-first => Drop <database> on $NEW_HOST first"
            echo "    -p|--postgis => <database> is PostGIS-enabled"
            exit
            ;;
        -*)
            echo "Unknown option: $option" 1>&2
            exit 1
            ;;
        *)
            DB_NAME="$option"
            ;;
    esac
    shift
done

if [ "$DB_NAME" = "" ]; then
    echo "No database specified" 1>&2
    exit 2
fi

if [ "$DB_USER" = "" ]; then
    DB_USER="${USER}_a"
fi

if [ "$OWNER" = "" ]; then
    OWNER="${DB_NAME}_l"
fi

echo "Copying $DB_NAME from $OLD_HOST to $NEW_HOST"
echo "Connecting as $DB_USER"
echo "Database owner is $OWNER"
if [ "$DROP_FIRST" = "yes" ]; then
    echo "$DB_NAME will be dropped on $NEW_HOST first"
fi
if [ "$POSTGIS_ENABLED" = "yes" ]; then
    echo "$DB_NAME is PostGIS-enabled"
fi

read -p "Continue? [yes/N] " answer
if [ "$answer" != "yes" ]; then
    echo "Aborted"
    exit
fi

while [ -z "$PGPASSWORD" ]; do
    read -s -p "Password for ${DB_USER}@${NEW_HOST}: " PGPASSWORD
    export PGPASSWORD
    echo
done

if [ "$DROP_FIRST" = "yes" ]; then
    read -p "Drop $DB_NAME on $NEW_HOST? [yes/N] " answer
    if [ "$answer" = "yes" ]; then
        echo -n "Dropping $DB_NAME on $NEW_HOST..."
        dropdb -h $NEW_HOST -U $DB_USER $DB_NAME
        echo "Done"
    fi
fi

echo -n "Creating $DB_NAME on $NEW_HOST..."
createdb -h $NEW_HOST -U $DB_USER --owner $OWNER $DB_NAME
echo "Done"

if [ "$POSTGIS_ENABLED" = "yes" ]; then
    echo "Copying PostGIS-enabled database $DB_NAME from $OLD_HOST to $NEW_HOST..."
    psql -h $NEW_HOST -U $DB_USER -d $DB_NAME -c "CREATE EXTENSION postgis"
    psql -h $NEW_HOST -U $DB_USER -d $DB_NAME -c  "ALTER TABLE spatial_ref_sys OWNER TO $OWNER"
    rm -f $DB_NAME.prod.dump
    pg_dump -Fc -h $OLD_HOST -U $DB_USER -d $DB_NAME -f $DB_NAME.prod.dump
    postgis_restore.pl $DB_NAME.prod.dump | psql -h $NEW_HOST -U $DB_USER -d $DB_NAME
else
    echo "Copying database $DB_NAME from $OLD_HOST to $NEW_HOST..."
    pg_dump -C -h $OLD_HOST -U $DB_USER $DB_NAME | psql -h $NEW_HOST -U $DB_USER -d $DB_NAME
fi
echo "Done copying $DB_NAME from $OLD_HOST to $NEW_HOST"