Shell script to backup postgres database and schedule at specific time

In this document we will use two scripts.
First script backup the Postgresql database and second script deletes backup older than 90 days.



1. backup.sh

# This script backup the postgresql db mydb at the path /home/postgres/9.5/backups


#!/bin/sh

pg_dump  -U postgres   -p 5432 -F c -b -v -d mydb> /home/postgres/9.5/backups/mydb_`date +%d-%m-%Y"_"%H_%M_%S`.backup

# after creating backup.sh file execute following command to make the file executable.

# chmod -R 775 backup.sh

 2. housekppping.sh

# This script deletes backup files older than the 90 days 

#!/bin/sh

logfile=/home/postgres/9.5/backups/backup.log

rm -f $logfile

for file in `find /home/postgres/9.5/backups  -mtime +90 -type f -name '*.backup' `

do

  echo "deleting: " $file >> $logfile

  rm $file

done

exit 0

# after creating backup.sh file execute following command to make the file executable.

# chmod -R 775 housekppping.sh

Execute following command to schedule your script to run at 1 pm and 10 pm daily 

[root]# crontab -e

0 13 * * * /home/postgres/9.5/backups/backup.sh

0 22 * * * /home/postgres/9.5/backups/backup.sh

0 22 * * * /home/postgres/9.5/backups/housekeeping.sh





Use following query to restore the backup.


pg_restore -U postgres -F c  -v  -d mydb  < mydb_21-02-2017_13_00_04.backup