PostgreSQL Database Backup shell script

pg_backup.sh

BACKUP_DIR=<back up location>
DAYS_TO_KEEP=14
FILE_SUFFIX=_db_suffix.sql
DATABASE=<Databae Name>
USER=<Database username >

FILE=`date +"%Y%m%d%H%M%S"`${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.
PGPASSWORD="your password" pg_dump -h localhost -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 '{}' ';'

# Now open your linux crontab and add below line . You may change backup time
# */1 * * * * <Your shell script location here> 
# this crontab run every minute