Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

What's is the difference between streaming replication Vs hot standby vs warm standby ?

Script to kill ALL IDLE Connection In postgreSQL