pgsql.sh

#!/bin/bash
# ================================================================================
#
# LOCALDUMP :: POSTGRES
# create gzipped plain text backups from each scheme
#
# --------------------------------------------------------------------------------
# ah - Axel Hahn <axel.hahn@iml.unibe.ch>
# ds - Daniel Schueler <daniel.schueler@iml.unibe.ch>
#
# 2016-11-10  ah,ds  v1.0
# 2017-03-29  .....  v1.1  added restore
# 2022-01-20         v1.2  fixes with shellcheck
# ================================================================================

if [ -z "$BACKUP_TARGETDIR" ]; then
  echo "ERROR: you cannot start $(basename $0) directly"
  rc=$rc+1
  exit 1
fi

# --------------------------------------------------------------------------------
# CONFIG
# --------------------------------------------------------------------------------

  # unix user of postgres database
  PGUSER=postgres


# --------------------------------------------------------------------------------
# FUNCTION
# --------------------------------------------------------------------------------

function doPgsqlBackup(){

  create_targetdir

  # ----- change owner of directory because of su command
  chown ${PGUSER}. "${BACKUP_TARGETDIR}"

  # ----- GO

  sSqlGetDblist="select datname from pg_database where not datistemplate and datallowconn order by datname;"
  for DATABASE in $(su ${PGUSER} -c "psql -At -c '$sSqlGetDblist' postgres" 2>/dev/null)
  do
    echo "----- $DATABASE"
    echo -n "backup ... "
    OUTFILE="${BACKUP_TARGETDIR}/$(get_outfile ${DATABASE}).sql"
    su ${PGUSER} -c "pg_dump -Fp ${DATABASE} >$OUTFILE"
    fetchrc

    if [ $myrc -eq 0 ]; then
      echo -n "compress ... "
      compress_file "$OUTFILE"
    else
      color error
      echo "ERROR occured - no gzip"
      color reset
      # mv $OUTFILE $OUTFILE
    fi
    ls -l "$OUTFILE"*
  echo
  done
}


# restore database dump file into database
# param  string  database dump file (gzipped)
# param  string  optional: database to import; default: database is parsed from file
function restoreByFile(){
  sMyfile=$1
  sMyDb=$2

  if [ -z "$sMyDb" ]; then
    h2 "analyze dump $sMyfile"
    sMyDb="$(guessDB "$sMyfile" )"
    echo "detected db schema from file: [${sMyDb}]"
  else
    echo "db schema from param 2: [${sMyDb}]"
  fi

  echo

  echo import to "$sMyDb"...

  h2 "deleting database [$sMyDb] ..."
  color cmd
  su ${PGUSER} -c "dropdb ${sMyDb}"
  color reset

  h2 "ensure that database exists ..."
  color cmd
  su ${PGUSER} -c "psql -c \"CREATE DATABASE ${sMyDb};\""
  fetchrc
  color reset

  h2 "import ..."
  ls -l "${sMyfile}"
  echo "import to database [${sMyDb}]"
  color cmd
  zcat "${sMyfile}" | su ${PGUSER} -c "psql -d ${sMyDb}"
  fetchrc
  color reset

}


# --------------------------------------------------------------------------------
# MAIN
# --------------------------------------------------------------------------------

  # ----- requirements
  j_requireBinary "pg_dump"   1
  j_requireProcess "postgres" 1

  if [ $rc -ne 0 ]; then
    rc=0
    echo "SKIP: postgres seems not to be here"
  else
    if [ "$1" = "restore" ]; then
      shift 1
      restoreByFile $*

    else
      doPgsqlBackup
    fi

  fi

  echo "$0 $* [postgres] final returncode rc=$rc"

# --------------------------------------------------------------------------------