postgresql daily backup

pg_backup.sh

#!/bin/bash
#
# Backup a Postgresql database into a daily file.
#

BACKUP_DIR="/var/lib/pgsql/11/backups"
DAYS_TO_KEEP=2
FILE_SUFFIX=_pg_backup.sql
DB=$(psql -t -c "SELECT datname FROM pg_database WHERE datistemplate = false;" | sed -e 's/^[[:space:]]*//' )
USER=postgres

FILE=`date +"%Y-%m-%d-%H%M"`${FILE_SUFFIX}

# do the database backup (dump)

while IFS= read -r DATABASE; do
  printf "Processing $DATABASE backup...\n"
  pg_dump -U ${USER} ${DATABASE} -Fc -f ${BACKUP_DIR}/${DATABASE}_${FILE}
  echo "DONE ${DATABASE} backup..."
done <<< "$DB"

#prune old backups
find ${BACKUP_DIR} -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*.sql" -delete
# done