PostgreSQL Daily backup automated shell script
this script will take the postgresql base backup based on daily changes (if you scheduled daily) & it will delete the old backup if backup retention period is reached 2 days. you can call this as postgresql incremental backup Script because this Script is taking backup of changes only means it is taking archive and XLOG files only. If postgresql instance is crashed means you can recover the data by using point in time recovery.
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
Tag:postgresql, script
Login with Google