Postgresql daily backup script.

pgsql_backup.sh

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

BACKUP_DIR=/pg_backup
DAYS_TO_KEEP=14
FILE_SUFFIX=_pg_backup.sql
DATABASE=
USER=postgres

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

OUTPUT_FILE=${BACKUP_DIR}/${FILE}

# do the database backup (dump)
# use this command for a database server on localhost. add other options if need be.
pg_dump -U ${USER} ${DATABASE} -F p -f ${OUTPUT_FILE}

# gzip the mysql database dump file
gzip $OUTPUT_FILE

# show the user the result
echo "${OUTPUT_FILE}.gz was created:"
ls -l ${OUTPUT_FILE}.gz

# prune old backups
find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*${FILE_SUFFIX}.gz" -exec rm -rf '{}' ';'

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

BACKUP_DIR=/backup_psql
LOG_FILE=${BACKUP_DIR}/backup.log
DAYS_TO_KEEP=5
FILE_SUFFIX=_pg_backup.sql
DATABASE=database_name
USER=postgres
PGPASSWORD=new_password

# Set working directory to BACKUP_DIR
cd $BACKUP_DIR || exit

FILE=$(date +"%Y%m%d%H%M")${FILE_SUFFIX}
OUTPUT_FILE=${BACKUP_DIR}/${FILE}

{
  echo "Starting backup: $(date)"

  # do the database backup (dump)
  pg_dump -U ${USER} ${DATABASE} -F p -f ${OUTPUT_FILE}
  if [ $? -eq 0 ]; then
    echo "Database backup successful"
  else
    echo "Database backup failed"
  fi

  # gzip the postgres database dump file
  gzip $OUTPUT_FILE
  if [ $? -eq 0 ]; then
    echo "Gzip successful"
  else
    echo "Gzip failed"
  fi

  # show the user the result
  echo "${OUTPUT_FILE}.gz was created:"
  ls -l ${OUTPUT_FILE}.gz

  # prune old backups
  find $BACKUP_DIR -maxdepth 1 -mtime +$DAYS_TO_KEEP -name "*${FILE_SUFFIX}.gz" -exec rm -rf '{}' ';'
  if [ $? -eq 0 ]; then
    echo "Old backups pruned successfully"
  else
    echo "Failed to prune old backups"
  fi

  echo "Backup completed: $(date)"
} >> ${LOG_FILE} 2>&1

sudo mkdir -p /backup_psql
sudo chown postgres:postgres /backup_psql
sudo chmod 700 /backup_psql
sudo crontab -u postgres -e
0 2 * * * /path/to/backup.sh

checking work script

sudo crontab -u postgres -l
sudo -u postgres /path/to/backup.sh
cat /backup_psql/backup.log