Backup or restore postgres db

dbutil.sh

#!/usr/bin/env bash
#
# Backup, or Restore your postgres database
#

set -e

usage()
{
cat << EOF

usage: $0  options

OPTIONS:
  -c command      Where command is one of:

                  - backup : Backup the database.
                             Use options -d, -f

                  - restore: Restore the database.
                             Use options -d, -f

                  - count:   Produce a tablecount of all tables in the public schema.
                             Use options -d

  -d database     Database eg: 'mrr_development'.
  [-f file]       For backup command, specifies the backup file.
                  For restore command, specifies the restore file
                  Default value 'database.backup~'  eg 'mrr_development.backup~'

  [-H host]       database server host or socket directory (default: "local socket")

  [-U user]       database user name (default: $USER)


EOF
}

COMMAND=
DATABASE=
BACKUP=
while getopts “c:hd:[f:][H:][U:]” OPTION
do
     case $OPTION in
         h)
             usage
             exit 1
             ;;
         c)
             COMMAND=$OPTARG
             ;;
         d)
             DATABASE=$OPTARG
             ;;
         f)
             BACKUP=$OPTARG
             ;;
         H)
             echo $OPTARG
             HOST_ARG=" -h $OPTARG"
             ;;
         U)
             USER_ARG=" -U $OPTARG"
             ;;
         ?)
             usage
             exit
             ;;
     esac
done
if [[ -z $DATABASE ]] || [[ -z $COMMAND ]]
then
     echo "ERROR: Aborting, check usage"
     usage
     exit 1
fi
if [[ -z $BACKUP ]]
then
     BACKUP=${DATABASE}.backup~
fi

case "$COMMAND" in
    "backup")
        if [[ -e $BACKUP ]]
        then
             echo "ERROR: Aborting, output file '${BACKUP}' already exists."
             exit 1
        fi
        pg_dump ${DATABASE} > ${BACKUP}
        ;;
    "restore")
        echo "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '${DATABASE}' AND pid <> pg_backend_pid();" | psql ${HOST_ARG} ${USER_ARG} -d postgres

        # check that the database is there before dropping it
        if psql ${HOST_ARG} ${USER_ARG} -lqt | cut -d \| -f 1 | grep -w ${DATABASE}; then
            dropdb ${HOST_ARG} ${USER_ARG} ${DATABASE}
        fi

        createdb ${HOST_ARG} ${USER_ARG}  ${DATABASE}

        psql ${HOST_ARG} ${USER_ARG}  --set ON_ERROR_STOP=off -d ${DATABASE} -f ${BACKUP}
        ;;
    "count")
        PGCOMMAND=" psql -d ${DATABASE} -At -c \"
                    SELECT   table_name
                    FROM     information_schema.tables
                    WHERE    table_type='BASE TABLE'
                    AND      table_schema='public'
                    ORDER BY table_name
                    \""
        TABLENAMES=$(eval "$PGCOMMAND")

        for TABLENAME in $TABLENAMES; do
            PGCOMMAND=" psql -d ${DATABASE} -At -c \"
                        SELECT   '$TABLENAME',
                                 count(*)
                        FROM     $TABLENAME
                        \""
            eval "$PGCOMMAND"
        done
        ;;
    *)
         echo "ERROR: Aborting, invalid command"
         usage
         exit 1
         ;;
esac