PostgreSQL Backup and Restore Script

pgbackup.sh

#!/usr/bin/env bash
################################################################################
# pgbackup - Run without arguments for help.
#          - Wrapper for PG backup/restore utilities
# (C) 2018 Allen Fair. All rights reserved.
################################################################################

# Use ENV Variables to override these defaults

user=${PGUSER-$USER}
dir=${PGBACKUPDIR-.}
host=${PGHOST-localhost}
pgjobs=${PGJOBS-4}
userhost=${PGUSERHOST--U $user -h $host}
pgdata=${PGDATA-`psql -A -t $userhost -c "show data_directory;"`}
pgopts="$PGOPTS $userhost"

ts=`date "+%Y%m%d-%H%M%S"`
dt=`date "+%Y%m%d"`
cmd=${1-help}

# BACKUP ALL ###################################################################
if [[ "$cmd" = "all" ]]; then

  base=$dir/pgbackup.$dt
  echo "BACKUP PostgreSQL ALL Directory Structure -> $base"
  mkdir $base
  echo UH=$userhost
  pg_dumpall --globals-only $userhost | gzip > $base/globals.sql.gz
  echo UH=$userhost
  pg_dumpall --schema-only  $userhost | gzip > $base/schema.sql.gz
  echo UH=$userhost
  psql -A -t $userhost \
    -c "SELECT datname FROM pg_database WHERE NOT datistemplate order by 1" \
  | while read db; do
      mkdir $base/$db
      opts="$pgopts --format=directory --jobs=$pgjobs --dbname=$db"
      echo $db
      pg_dump $opts --file=$base/$db # --no-synchronized-snapshots
      du -hs $base/$db | sort -h
    done;
  du -hs $base | sort -h
  exit;

# RESTORE-ALL ##################################################################
elif [[ "$cmd" = "restore-all" ]]; then

  base=${2-.}
  echo "RESTORE PostgreSQL ALL Directory Structure <- $base"
  cd $base
  gzcat globals.sql.gz | psql $userhost -d template1
  find . -type d \
    | while read db; do
        db=${db#./}
        if [[ "$db" != "." ]]; then
          createdb $db
          echo pg_restore -h $host --format=directory -d $db $db
          pg_restore -h $host --format=directory -d $db $db
        fi
      done
  exit;

# PLAIN|CUSTOM|all [DBNAME [SCHEMA [TABLE]]] ##################################
elif [ "$cmd" = "plain" -o "$cmd" = "custom" -o "$cmd" = "directory" ]; then

  format=$cmd
  db=${2-$user}
  schema=$3
  table=$4
  dest=$dir/pgbackup

  if [[ "$db" > " " ]]; then
    pgopts=" --dbname=$db"
    dest=$dest.$db
  fi
  if [[ "$schema" > " " ]]; then
    pgopts=" --schema=$schema"
    dest=$dest.$schema
  fi
  if [[ "$table" > " " ]]; then
    pgopts=" --table=$table"
    dest=$dest.$table
  fi

  dest=$dest.$format.$dt
  #echo dest=$dest db=$db opt=$pgopts s=$schema t=$table format=$format
  if [[ "$db" = "" ]]; then
    echo "BACKUP PostgreSQL CLUSTER -> $dest.gz"
    pg_dumpall $pgopts > $dest
    gzip $dest
    ls -lh $dest.gz
  else
    echo "BACKUP PostgreSQL $db $schema $table -> $dest"
    if [[ "$format" = "plain" ]]; then
      dest=$dest.gz
      pg_dump $pgopts --format=$format | gzip > $dest
    else
      echo pg_dump $pgopts --format=$format --file=$dest
      pg_dump $pgopts --format=$format --file=$dest
    fi
    ls -lh $dest
    du -hs $dest
  fi
  exit;

# RESTORE[-PLAIN|-CUSTOM] BACKUP DBNAME [SCHEMA [TABLE]] ################################
elif [ "$cmd" = "restore" -o "$cmd" = "restore-custom" -o "$cmd" = "restore-plain" ]; then

  if [[ "$cmd" = "restore" ]]; then
    format=directory
  elif [[ "$cmd" = "restore-plain" ]]; then
    format=plain
  else
    format=custom
  fi
  backup=$2
  db=$3
  schema=$4
  table=$5
  if [[ "$db" > " " ]]; then
    pgopts=" --dbname=$db"
  fi
  echo "RESTORE $backup -> PostgreSQL $db $schema $table"
  if [[ "$format" = "plain" ]]; then
    db=${schema-$USER}
    schema=${schema-public}
    # Optional: psql --set ON_ERROR_STOP=on dbname < dumpfile
    zcat $backup | psql "host=$host user=$user dbname=$db options=--search_path=$schema,public"
  else
    pgopts="$pgopts --format=$format"
    if [[ "$format" = "directory" ]]; then
      pgopts="$pgopts --jobs $pgjobs"
    fi
    if [[ "$schema" > " " ]]; then
      pgopts=" --schema=$schema"
    fi
    if [[ "$table" > " " ]]; then
      pgopts=" --table=$table"
    fi
    echo pg_restore $pgopts --format=$format $backup
    pg_restore $pgopts --format=$format $backup
  fi
  exit;

# MIGRATE FROMHOST FROMDB TOHOST TODB TOSCHEMA TOUSER ##########################
elif [[ "$cmd" = "migrate" ]]; then

  fromhost=${2-localhost}
  fromdb=${3-$USER}
  tohost=${4-localhost}
  todb=${6-$fromdb}
  as=${7-$user}

  dumpdir=$dir/dump.$fromdb
  echo mkdir $dumpdir
  echo time pg_dump    --format=directory -j $pgjobs -U $as -h $fromhost -d $fromdb -f $dumpdir
  echo time pg_restore --format=directory -j $pgjobs -U $as -h $tohost -d $todb -e $dumpdir
  echo rm -r $dumpdir
  exit;

# MIGRATE-SIMPLE FROMHOST FROMDB TOHOST TODB TOUSER ############################
elif [[ "$cmd" = "migrate-simple" ]]; then

  fromhost=${2-localhost}
  fromdb=${3-$USER}
  tohost=${4-localhost}
  todb=${6-$fromdb}
  as=${7-$user}

  pg_dump -h $fromhost -U $as $fromdb | psql --set ON_ERROR_STOP=on -h $tohost -U $as $todb
  exit;

# BACKUP-RSYNC [FROMHOST:]DIR [TOHOST:]DIR #########################################
elif [[ "$cmd" = "backup-rsync" ]]; then

  fromdir=$2
  todir=${3-$pgdata}

  rsync -az --exclude pg_xlog --exclude postgresql.conf $fromdir/* $todir/
  echo "Stop postgresql on old server, enter when ready to complete, Ctrl+C to abort"
  read go
  rsync -c -az --exclude pg_xlog --exclude postgresql.conf $fromdir/* $todir/
  echo "Start postgresql on the new server"
  exit;

# BASE FROMHOST [DIR] ##########################################################
elif [[ "$cmd" = "base" ]]; then

  fromhost={$2-$host}
  dir={$3-$pgdata}
  pg_basebackup $PGOPTS -h $fromhost -U $user -D $dir
  exit;

# USAGE ########################################################################
else
  echo " Usage: pgbackup [command] # Wrapper for PostgreSQL Backup/Restore Utilities"
  echo "   all - Backs up the cluster to directory format: ./pgbackup.yyyymmdd/dbname/backupfiles"
  echo "   restore-all [backupdir] - Restores all DB by directory backup (via all)"
  echo "   directory|plain|custom [dbname [schema [table]]] - Backup to format (all -> directory)"
  echo "   restore[-custom] backupname [dbname [schema [table]]] - Restores dir or custom Backup"
  echo "   restore-plain backupname [dbname [schema]] - Restores backup (using psql)"
  echo "   migrate fromhost fromdb todb - Migrates DB from one host to another"
  echo "Configure with enviroment variables:"
  echo "   PGUSER=user (default current user)"
  echo "   PGBACKUPDIR=path (default is current directory)"
  echo "   PGHOST=host|ip (default localhost)"
  echo "   PGJOBS=4 (number of backup jobs, default 4)"
  echo "   PGDATA=path (PostgreSQL Data Dir, defaults to 'show data_directory')"
  echo "   PGOPTS=.... (additional options to pass on command line"

fi