Learn how to backup Postgres database on Linux, using cronjobs. Very simple and very straight forward guide step by step using only scripts.
- Make new .pgpass file in the root folder
$ vi .pgpass
And paste the content from /root/.pgpass
- Create new file
/root/pg_backup.shand paste the content - Change permissions
$ chmod 700 /root/pg_backup.sh
- Add new cron
$ crontab -e
Add at the end of the file the content:
0 0 * * * /root/pg_backup.sh
.pgpass
localhost:5432:DATABASE:USER:PASSWORD
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 {} \;
