Asagidaki script Postgresql Transactional Log backup ve point in time recovery icin tarafimdan yazilmistir. #!/bin/bash # variables and parameters # SQLFullBackup Filename file_name="SqlBackup" current_time=$(date "+%Y%m%d%H%M%S") new_filename=$file_name$current_time # Yesterdays SQLbackup folder and file name Ytime1=$(date -d "yesterday" "+%Y%m%d%H%M%S") # yesterday date Yfile_name="SQLBackup_Archive" #filename for sqlBackup subdirectory Ynew_filename=$Yfile_name$Ytime1 #filename for sqlBackup subdirectoryname with yesterday timestamp # Regular Colors Black="\033[0;30m" # Black Red="\033[0;31m" # Red Green="\033[0;32m" # Green Yellow="\033[0;33m" # Yellow Blue="\033[0;34m" # Blue Purple="\033[0;35m" # Purple Cyan="\033[0;36m" # Cyan White="\033[0;37m" # White # Bold BBlack="\033[1;30m" # Black BRed="\033[1;31m" # Red BGreen="\033[1;32m" # Green BYellow="\033[1;33m" # Yellow BBlue="\033[1;34m" # Blue BPurple="\033[1;35m" # Purple BCyan="\033[1;36m" # Cyan BWhite="\033[1;37m" # White # Underline UBlack="\033[4;30m" # Black URed="\033[4;31m" # Red UGreen="\033[4;32m" # Green UYellow="\033[4;33m" # Yellow UBlue="\033[4;34m" # Blue UPurple="\033[4;35m" # Purple UCyan="\033[4;36m" # Cyan UWhite="\033[4;37m" # White # Background On_Black="\033[40m" # Black On_Red="\033[41m" # Red On_Green="\033[42m" # Green On_Yellow="\033[43m" # Yellow On_Blue="\033[44m" # Blue On_Purple="\033[45m" # Purple On_Cyan="\033[46m" # Cyan On_White="\033[47m" # White # High Intensity IBlack="\033[0;90m" # Black IRed="\033[0;91m" # Red IGreen="\033[0;92m" # Green IYellow="\033[0;93m" # Yellow IBlue="\033[0;94m" # Blue IPurple="\033[0;95m" # Purple ICyan="\033[0;96m" # Cyan IWhite="\033[0;97m" # White # Bold High Intensity BIBlack="\033[1;90m" # Black BIRed="\033[1;91m" # Red BIGreen="\033[1;92m" # Green BIYellow="\033[1;93m" # Yellow BIBlue="\033[1;94m" # Blue BIPurple="\033[1;95m" # Purple BICyan="\033[1;96m" # Cyan BIWhite="\033[1;97m" # White # High Intensity backgrounds On_IBlack="\033[0;100m" # Black On_IRed="\033[0;101m" # Red On_IGreen="\033[0;102m" # Green On_IYellow="\033[0;103m" # Yellow On_IBlue="\033[0;104m" # Blue On_IPurple="\033[0;105m" # Purple On_ICyan="\033[0;106m" # Cyan On_IWhite="\033[0;107m" # White # Color Reset Color_Off="\033[0m" # Text Reset # Custom Colors OK="$On_IGreen $BIWhite OK $Color_Off" NO="$On_IRed $BIWhite NOT OK!! $Color_Off" Hilite_Clr="$On_ICyan $BIWhite" # kew words that you want to highlight # Temp directory for 1st fullbackup file DIRbcktmp="/storage/sqlBcktmp/" #pg_basebackup directory, shoud not contain full directory for a new backup. DIRbcktmpF=$DIRbcktmp"*" # to truncate all files from sqlBcktmp # Real backup directory for SQLFULLbackup file DIR2H="/storage/sqlBackup" # DIR2=$DIR2H"/" #SQLFullBackup directory DIR2F=$DIR2"*" # To count number of backup files # Archive directory for backup SQLFULLBackup and xlog files DIR3H="/storage/sqlArchive/" DIR3A=$DIR3H$Ynew_filename # SQLFullBackup yes DIR3F=$DIR3A"/*" # To count number of backup files after moving. DIR3FH=$DIR3A"/" # To count number of backup files after moving. # log filenames new_filename_log="BF"$new_filename".log" #BeFore copy log which is under new_filenamearchive_log="AF"$new_filename".log" # After copy log new_fileLogdiff_log="DIFF"$new_filename".log" # logging parameters #logging directory DIRLOG="/storage/cronLogs/sqlBackupLogs/" DIRLOGBF=$DIRLOG"sqlBackupLogsBF/" #SQLFullBackupLog Directory for count DIRLOGAF=$DIRLOG"sqlBackupLogsAF/" #SQLArchiveBackupLog Directory for count DIRLOGDIFF=$DIRLOG"sqlLogdiff/" f1B=$DIRLOGBF$new_filename_log # log file BeFore copy f2A=$DIRLOGAF$new_filenamearchive_log # log file AFter copy fdiff=$DIRLOGDIFF$new_fileLogdiff_log # Difference of two log file rc=$?; # exit from the process S=5 # sleep time #Progress start time for duration log START=$(date +%s); clear # TITLE OF BASH PROGRAM. SQLBACKUP echo "$On_ICyan $BIWhite SQLBACKUP PROGRAM $Color_Off \n\n" # check temporary SQLFullBackup directory. if the directory is not empty stop all the progress and exit # if the program exits from the progress, its not a problem because xlog files resuming to take xlog backup. # which means you can restore SQLfullbackup file from yesterday, then you have to restore totaly 2 days xlog files. if [ "$(ls -A $DIRbcktmp)" ]; then echo "$On_IRed $BIWhite ERROR!! $Color_Off $Hilite_Clr $DIRbcktmp $Color_Off $On_IRed $BIWhite isnot empty. $Color_Off $NO \n" sleep $S echo "$On_IRed $BIWhite $DIRbcktmp is not empty. Please Check it $Color_Off \n" sleep $S echo "$On_IRed $BIWhite Exiting Program!!! $Color_Off \n" exit $rc; else echo "$On_IBlue There is no file in $Hilite_Clr $DIRbcktmp.$Color_Off $On_IBlue Resuming SQLFullBackup Progress. $Color_Off $OK \n" fi # start SQLFullBackup Progress sleep $S echo "$On_IBlue Postgresql SQLFullBackup Progress STARTS Now. $Color_Off $OK \n" sleep $S echo "$On_IBlue Creating SQLFullBackup File:$Color_Off $Hilite_Clr $new_filename $Color_Off \n" if pg_basebackup -U postgres -p 5433 -P --xlog --format=t -D $DIRbcktmp$new_filename;then echo "$On_IBlue SQLFullBackup Progress Completed :$Color_Off $Hilite_Clr $new_filename $Color_Off $OK \n" else echo "$On_IRed $BIWhite There is an ERROR during backup process..! $Color_Off $NO \n" exit $rc; fi # transfer SQLFullBackup from sqlBcktmp to sqlBackup directory sleep $S echo "$On_IBlue SQLFullBackup File $Color_Off $Hilite_Clr $new_filename $Color_Off $On_IBlue Moving From $Color_Off $Hilite_Clr $DIRbcktmp To $DIR2 $Color_Off \n" if mv $DIRbcktmpF $DIR2 ; then echo "$On_IBlue Done. $Color_Off $OK" else echo "$On_IRed $BIWhite Cant move $Color_Off $Hilite_Clr $new_filename $Color_Off !! $NO \n" exit $rc fi # Since the last SQLFullBackup taken, we have to move all the previous SQLFullBackup and Xlog files to the sqlArhive directory. #Progress Finished Time for duration log END=$(date +%s); #Duration time calculation diffsec="$(expr $START - $END)" echo|awk -v D=$diffsec '{printf "Backup File: '$new_filename' - Backup Duration: %02d:%02d:%02d\n",D/(60*60),D%(60*60)/60,D%60}'| tee -a /storage/cronLogs/duration.log # Log dosyalarindaki Verileri database insert et # step 7: Log dosyalarinin icine dosya boyutlarinida koy # step 9: Belli bir süreden önceki Archive icinde bulunan tum dosyalari sil # tar yap, hash al,hash ile beraber dosyayi karsi tarafa yolla, karsi tarafta dosya hash kontrol et.
