PostgreSQL Full backup and incremental backup script
cat /home/script/Postgres_backup.sh #!/bin/bash PGDATA=/data/ PGHOME=/opt/PostgreSQL/9.3/bin HOT=/backup WAL_LOC=/archive/ LOG=/home/script/script_logs TODAY=$(date +"%m-%d-%Y") /opt/PostgreSQL/9.3/bin/psql -X -U postgres -h localhost -c 'select now() as "Online Backup Start Time";' >> $LOG/backupinfo_$TODAY.log /opt/PostgreSQL/9.3/bin/psql -X -U postgres -h localhost -c "select setting from pg_settings where name='data_directory'; " >> $LOG/backupinfo_$TODAY.log #psql -U postgres -c " select pg_switch_xlog(); " >> $LOG/backupinfo_$TODAY.log ## Adding Checkpoint for consistent backup echo "CHECKPOINT; SELECT pg_start_backup('Full Backup'); -- Making Sure all in WAL is archived after checkpoint select pg_switch_xlog();"|/opt/PostgreSQL/9.3/bin/psql -X -U postgres -h localhost >> $LOG/backupinfo_$TODAY.log tar -cvzf $HOT/onlinebackup_$TODAY.tar.gz $PGDATA/* >> $LOG/backupinfo_$TODAY.log tar --remove-files -cvzf $HOT/arch_$TODAY.tar $WAL_LOC/* >> $LOG/backupinfo_$TODAY.log /opt/PostgreSQL/9.3/bin/psql -X -U postgres -h localhost -c " select pg_stop_backup(); " >> $LOG/backupinfo_$TODAY.log ## Archiving all transaction in Mid of Backup /opt/PostgreSQL/9.3/bin/psql -X -U postgres -h localhost -c "select pg_switch_xlog();" >> $LOG/backupinfo_$TODAY.log /opt/PostgreSQL/9.3/bin/psql -X -U postgres -h localhost -c 'select now() as "Online Backup End Time";' >> $LOG/backupinfo_$TODAY.log # Online Backup file Retention policy will be 2 day's find /backup -name "onlinebackup_*.tar.gz" -mtime +2 -exec ls -l {} \; >> $LOG/backupinfo_$TODAY.log find /backup -name "onlinebackup_*.tar.gz" -mtime +2 -exec rm -r {} \; # Archive Backup file Retention policy will be 2 day's find /backup -name "arch_*.tar" -mtime +2 -exec ls -l {} \; >> $LOG/backupinfo_$TODAY.log find /backup -name "arch_*.tar" -mtime +2 -exec rm -r {} \; #mail -s " `hostname` :: Fullbackup Information" rdba-alerts@enterprisedb.com < $LOG/backupinfo_$TODAY.log exit 0
Comments
Post a Comment