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

Batch Script to Create postgres backup and copy backup files from local drive to network also send email alert to user

Following document help to copy latest files from local drive and  paste on the network drive.

this document also delete files older than 10 days from the network drive where we are copying the backup files.

to copy backup files we will create following files on the  c:\users\sarzaidi\documents\ path .

we would also need to create a folder named log under  c:\users\sarzaidi\documents\ 

1. backup.bat.

this file will copy backup from local drive to mapped network drive.

drive will be mapped at runtime.

2. sendemail.bat

this file will use powershell and emailcommand.ps1 file to send email .

3. emailcommand.ps1

this file contains command to send email.

in this file we can specify sender ,receiver ,email subject and body we can also use this file to attach file to email.

4.main.bat

this file calls backup.bat and sendemail.bat to perform backup and send email.

we can execute this file from command prompt or we can schedule this file to execute the task whenever we need. 

 1. backup.bat

set backuplog=backup_db_%date:~10%%date:~4,2%%date:~7,2%.log

echo set batchscriptdir="c:\program files\postgresql\backups\"

echo set backupsourcedir="c:\program files\postgresql\backups\"

echo set backupdestinationdir=\\ccps-sql-01.ad.ccps.net\ccps_backups\db_backups\kp_dev

pg_dump  -u postgres   -p 5432 -f c -b -v -d eor4 > "c:\program files\postgresql\backups"\%backupname%

net use x: \\ccps-sql-01.ad.ccps.net\ccps_backups\db_backups\kp_dev

x:

echo " xcopy  /y /f /i /c:  %backupsourcedir%*.* x:\"

echo backup started from local:%backupsourcedir% to azure . >> %batchscriptdir%logs\%backuplog%

xcopy  /y /f /i /d:%date:~4,2%-%date:~7,2%-%date:~10% %backupsourcedir%*.* x: >> %batchscriptdir%logs\%backuplog%

echo backup completed at \\ccps-sql-01.ad.ccps.net\ccps_backups\db_backups\kp_dev >> %batchscriptdir%logs\%backuplog%

x:

echo delete older than 10 day  started. >> %batchscriptdir%logs\%backuplog%

forfiles /m *.bak /d -10 /c "cmd /c echo @path "

echo delete older than 10 day completed >> %batchscriptdir%logs\%backuplog%

c:

net use x: /delete /y

echo done 

2. sendemail.bat

set powershellscriptpath=%batchscriptdir%emailcommand.ps1

powershell -noprofile -executionpolicy bypass -command "& '%powershellscriptpath%'";

3. emailcommand.ps1

$from_ = "sarzaidi@mydomain.com"

$to_ = "sarzaidi@mydomain.com","arzaidi@lmkr.com"

$subject_ = "doa dev 168 backup"

$body_ = "backup completed logs at avaliable at \\infoportal-dev\logs "

$smtpserver_  =  "outlook.mydomain.com"

send-mailmessage -from $from_ -to $to_ -subject $subject_ -body $body_ –smtpserver $smtpserver_

4.main.bat

set batchscriptdir="c:\program files\postgresql\backups\"

set logdir=eor_backup_%date:~10%%date:~4,2%%date:~7,2%

set backuplog=backup_%date:~10%%date:~4,2%%date:~7,2%.log

set emaillog=sendemail_%date:~10%%date:~4,2%%date:~7,2%.log

set backupsourcedir="c:\program files\postgresql\backups\"

set backupdestinationdir=\\ccps-sql-01.ad.ccps.net\ccps_backups\db_backups\kp_dev

set backupname=vo8_%date:~10%%date:~4,2%%date:~7,2%.backup

c:

cd %batchscriptdir%logs

md %logdir%

move /y *.log %logdir%

 c:

cd %batchscriptdir%

echo backup.bat started. >> %batchscriptdir%logs\%backuplog%

call backup.bat >> %batchscriptdir%logs\%backuplog%

echo backup.bat ended. >> %batchscriptdir%logs\%backuplog%

c:

cd %batchscriptdir%

echo sendemail started. >> %batchscriptdir%logs\%emaillog%

call sendemail.bat >> %batchscriptdir%logs\%emaillog%

echo sendemail ended. >> %batchscriptdir%logs\%emaillog%

rem cd %batchscriptdir%logs

rem md unittestlogs_%date:~10%%date:~4,2%%date:~7,2%

rem mv %batchscriptdir%logs\*.log unittestlogs_%date:~10%%date:~4,2%%date:~7,2%

forfiles /p "%batchscriptdir%logs" /s /d -6 /c "cmd /c if @isdir == true rd /s /q @path"

cd %batchscriptdir%

echo done