shell_sequencer/seqs/postgres.sh

#!/bin/bash

toolName=postgres
toolDeps=postgresql
toolUser=postgres

# Needed for different steps
postgresDb=""
postgresUser=""
postgresPass=""

# Get script working directory
# (when called from a different directory)
WDIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" >>/dev/null 2>&1 && pwd )"
CONFIG=0
CONFIG_FILE_NAME="postgres.cfg"
CONFIG_FILE_TEMPLATE="$WDIR/${CONFIG_FILE_NAME}.example"

step_config() {
  initSeqConfig "$CONFIG_FILE_NAME" "$CONFIG_FILE_TEMPLATE"
  if [ $? -eq 0 ] ; then
    CONFIG=1
  fi
}

step_1_info() { echo "Installing $toolName dependencies"; }
step_1_alias() { ALIAS="install"; }
step_1() {
  local aptOption=

  exe apt update
  endReturn -o $? "Updating apt repositories failed"

  if [ $QUIET -ne 0 ] ; then
    aptOption="-y"
  else
    aptOption=""
  fi

  exe apt install $toolDeps $aptOption
}

step_2_info() { echo "Create postgres database"; }
step_2_alias() { ALIAS="createdb"; }
step_2() {
  readDatabaseInfos

  exe cd ~postgres 
  exe su ${toolUser} -c "psql -c \"CREATE USER ${postgresUser} WITH ENCRYPTED password '${postgresPass}';\""
  exe su ${toolUser} -c "psql -c \"CREATE DATABASE ${postgresDb} ENCODING \"UTF8\" LC_COLLATE='C' LC_CTYPE='C' template=template0 OWNER ${postgresUser};\""
  exe su ${toolUser} -c "psql -c \"GRANT ALL PRIVILEGES ON DATABASE \"${postgresDb}\" to ${postgresUser};\""
}

step_4_info() { echoinfoArgs "[DATABASE]"; echo "Drop a postgres database"; }
step_4_alias() { ALIAS="dropdb"; }
step_4() {
  shift
  local dbname=$1
  if [ -z $dbname ]; then
    readDatabaseInfos
    dbname=$postgresDb
  fi
  exe cd ~postgres
  exe su ${toolUser} -c "psql -c \"DROP DATABASE ${dbname};\""
}

step_6_info() { echoinfoArgs "[DATABASE]"; echo "Size of a postgres database"; }
step_6_alias() { ALIAS="sizedb"; }
step_6() {
  shift
  local dbname=$1
  if [ -z $dbname ]; then
    readDatabaseInfos
    dbname=$postgresDb
  fi
  exe cd ~postgres
  exe su ${toolUser} -c "psql -c \"SELECT pg_size_pretty( pg_database_size('$dbname') );\""
}

step_8_info() { echo "List available databases"; }
step_8_alias() { ALIAS="listdb"; }
step_8() {
  exe cd ~postgres
  exe su ${toolUser} -c "psql -c '\l'"
}

step_10_info() { echoinfoArgs "[DATABASE]"; echo "List all tables of a postgres database"; }
step_10_alias() { ALIAS="listtables"; }
step_10() {
  shift
  local dbname=$1
  if [ -z $dbname ]; then
    readDatabaseInfos
    dbname=$postgresDb
  fi
  exe cd ~postgres
  exe su ${toolUser} -c "psql -d $dbname -c \"\\dt\""
}

step_20_info() { echoinfoArgs "[DATABASE]"; echo "Backup ${toolName} database"; }
step_20_alias() { ALIAS="backupdb"; }
step_20() {
  shift
  local dbname=$1
  if [ -z $dbname ]; then
    readDatabaseInfos
    dbname=$postgresDb
  fi
  #if [ ! -s ~/.pgpass ] ; then
  #  echo " [I] For unattended backup please define ~/.pgpass containing credentials"
  #  echo "     e.g. localhost:5432:database:user:pass"
  #fi
  # -Fc  custom format
  #exep "pg_dump -h 127.0.0.1 -U ${postgresUser} -Fc synapse | bzip2 -c > ${toolDbBackupFolder}/`date +%Y-%m-%d\"_\"%H-%M-%S`.backup.bz2"
  exe mkdir -p "$POSTGRES_BACKUP_DIR"
  exe cd ~postgres
  exep "su ${toolUser} -c \"pg_dump -Fc $dbname\" | bzip2 -c > ${POSTGRES_BACKUP_DIR}/`date +%Y-%m-%d\"_\"%H-%M-%S`_${dbname}.backup.bz2"
}

step_22_info() { echo "Postgres database restore"; }
step_22_alias() { ALIAS="restoredb"; }
step_22() {
  echo " [I] Postgres database restore procedure"
  echo "1. Create a empty postgres database first (step 4)"
  echo "2. psql -h <host> -U <database user> -d <database name> -W -f <sql dump file>"
  echo "   e.g.  psql -h 127.0.0.1 -U synapse -d synapse -W -f 2018-06-07_18-10-56.sql"
  echo "or"
  echo "3. Custom postgres format dump restore:"
  echo "   pg_restore -h localhost -p 5432 -U synapse -d new_db -v \"10.70.0.61.backup\""
  echo
  echo "Available postgresql databases:"
  exe cd ~postgres
  exe su ${toolUser} -c "psql -c '\l'"
  echo "Available postgresql user:"
  exe su ${toolUser} -c "psql -c '\du'"
}

step_24_info() { 
  local DELYEAR=$(($(date +%Y)-2))
  echoinfoArgs "[DATABASE]"
  echo "Clean all ${DELYEAR} backups of a database";
}
step_24_alias() { ALIAS="backupclean"; }
step_24() {
  shift
  local DELYEAR=$(($(date +%Y)-2))
  local dbname=$1
  if [ -z $dbname ]; then
    readDatabaseInfos
    dbname=$postgresDb
  fi
  exe rm -f ${POSTGRES_BACKUP_DIR}/${DELYEAR}*${dbname}*
}

# Read postgres database information dbname/user/pass if empty
readDatabaseInfos() {
  if [ "$postgresDb" == "" ] ; then
    read -p "Enter postgres database name: " postgresDb
    endCheckEmpty postgresDb "database"
  fi
  if [ "$postgresUser" == "" ] ; then
    read -p "Enter postgres user name: " postgresUser
    endCheckEmpty postgresUser "user name"
  fi
  if [ "$postgresPass" == "" ] ; then
    read -s -p "Enter postgres password: " postgresPass
    endCheckEmpty postgresPass "password"
  fi
  echo
}

VERSION_SEQREV=14
. /usr/local/bin/sequencer.sh