Back-up postgres on linux

.pgpass

localhost:5432:DATABASE:USER:PASSWORD

readme.md

1. Make new pgpass file in the root folder

$ vi .pgpass

And paste the content from /root/.pgpass

2. Create new file /root/pg_backup.sh and paste the content
3. Change permissions

$ chmod 700 /root/pg_backup.sh

4. Add new cron

$ crontab -e

0 0 * * * /root/pg_backup.sh

pg_backup.sh

#!/bin/bash
# This script will backup the postgresql database
# and store it in a specified directory

# Constants

USER="user_name_of_db"
DATABASE="name"
HOST="localhost"
BACKUP_DIRECTORY="/root/backup_db"

# Date stamp (formated YYYYMMDD)
# just used in file name
CURRENT_DATE=$(date "+%Y%m%d")
 
# Database named (command line argument) use pg_dump for targed backup
pg_dump -U $USER $DATABASE -h $HOST | gzip - > $BACKUP_DIRECTORY/$DATABASE\_$CURRENT_DATE.sql.gz

# Cleanup old backups

find $BACKUP_DIRECTORY/* -mtime +7 -exec rm {} \;