Backup DB postgresql

.pgpass

DB_HOST:DB_PORT:DB_DATABASE:DB_USER:DB_PASSWORD

backup.sh

#!/bin/bash

#
# FTP Bash Backup.
# Copyright (C) 2015 Johnnywoof
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program. If not, see <http://www.gnu.org/licenses/>.
#

# FTP FTP_SERVER settings
FTP_USERNAME="FTP_USERNAME"
FTP_PASSWORD="FTP_PASSWORD"
FTP_SERVER="FTP_SERVER"
# example: FTP_PORT=21
FTP_PORT="FTP_PORT"
# Remote FTP_SERVER directory to upload backup
# example: FTP_DIR="/home/foo"
FTP_DIR="FTP_DIR" 

#############################
# Acceso a la Base de Datos #
#############################
DB_HOST="DB_HOST"
DB_USER="DB_USER"
# * DB_PASSWORD is taken from ~/.pgpass file
# example: DB_PORT=5432
DB_PORT="DB_PORT"
DB_DATABASE="DB_DATABASE"

# The absolute directory path to store temporary files in.
# This must be granted write access for this script.
# Please do not end the path with a forward slash.
timestamp=$(date --iso)

# example: /home/foo/backup/
BACKUP_PATH="BACKUP_PATH"
BACKUP_FILENAME="$timestamp.gz"


# Backups older than ndays will be removed
ndays=7


# Please note that if you want to supFTP_PORT encryption for backups, openssl must be installed.
# Should backups be encrypted before uploading?
ENCRYPT_BACKUP=false

# The absolute file path to the AES FTP_PASSWORD.
# You can generate a random FTP_PASSWORD using the following command:
# openssl rand -base64 256 > aes.key
# The number 256 is the amount of bytes to generate.
AES_FTP_PASSWORD_FILE=""

# END CONFIGURATION
# Script below, no need to modify it


# work out our cutoff date
MM=`date --date="$ndays days ago" +%b`
DD=`date --date="$ndays days ago" +%d`

echo "Removing files older than $MM $DD"

# get directory listing from remote source
listing=`ftp -i -n $FTP_SERVER $FTP_PORT <<EOMYF 
user $FTP_USERNAME $FTP_PASSWORD
binary
passive
cd $FTP_DIR
ls
quit
EOMYF
`
lista=( $listing )

# loop over our files
for ((FNO=0; FNO<${#lista[@]}; FNO+=9));do
  # month (element 5), day (element 6) and filename (element 8)
  #echo Date ${lista[`expr $FNO+5`]} ${lista[`expr $FNO+6`]}          File: ${lista[`expr $FNO+8`]}
  echo Date ${lista[`expr $FNO+8`]} ${lista[`expr $FNO+9`]}
  FILE_TO_DELETE=${lista[`expr $FNO+11`]}
  echo ${FILE_TO_DELETE}

  # check the date stamp
  if [ ${lista[`expr $FNO+8`]}=$MM ];
  then
    if [[ ${lista[`expr $FNO+9`]} -lt $DD ]];
    then
      # Remove this file
      echo "Removing ${FILE_TO_DELETE}"
      ftp -i -n $FTP_SERVER $FTP_PORT <<EOMYF2 
      user $FTP_USERNAME $FTP_PASSWORD
      binary
      passive
      cd $FTP_DIR
      delete ${FILE_TO_DELETE}
      quit
EOMYF2
    fi
  fi
done

echo "Creating backup..."

# Backup de la base de datos
PGPASSFILE=~/.pgpass pg_dump --no-owner -h $DB_HOST -p $DB_PORT -U $DB_USER $DB_DATABASE | gzip -9 > $BACKUP_PATH/$BACKUP_FILENAME


if [ "$ENCRYPT_BACKUP" == "true" ]
then
      echo "Encrypting backup using OpenSSL..."
      OUTPUT_ENCRYPTED_FILE="$BACKUP_FILENAME.enc"
      openssl enc -aes-256-cbc -salt -in $BACKUP_PATH/$BACKUP_FILENAME -out $BACKUP_PATH/$OUTPUT_ENCRYPTED_FILE -pass file:$AES_FTP_PASSWORD_FILE
      rm $BACKUP_PATH/$BACKUP_FILENAME
      BACKUP_FILENAME=$OUTPUT_ENCRYPTED_FILE
fi

echo "Uploading backup ${BACKUP_FILENAME} ..."

ftp -n -i $FTP_SERVER $FTP_PORT <<EOF
user $FTP_USERNAME $FTP_PASSWORD
binary
passive
put $BACKUP_PATH/$BACKUP_FILENAME $FTP_DIR/$BACKUP_FILENAME
quit
EOF

echo "Deleting temporary file ${BACKUP_FILENAME}..."
rm $BACKUP_PATH/$BACKUP_FILENAME
echo "Backup complete."

crontab

0 20 * * * /full_path_to/backup.sh >/tmp/backup.log 2>&1