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

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to configure Replication Manager (repmgr) ?