Taking PostgreSQL Basebackup script
this script will run at every night 1 AM and will take the postgresql basebackup of local tablespace
# crontab -e
0 1 * * * sh /home/postgres/eds/pune.sh
:wq
#cat /home/postgres/eds/pune.sh
# Config:
DB="POSTGRES_DB_NAME"
U="USERNAME"
export PGPASSWORD="tcs@12"
export PGPORT="5444"
export PGHOST="10.0.4.101"
BASEBACKUP_LOG=/var/log/postgresql/basebackup.log
[ -f $BASEBACKUP_LOG ] && mv -f $BASEBACKUP_LOG $BASEBACKUP_LOG.old
BACKUP_PATH=/path/to/backup/$(date +%F)
/opt/edb/as9.6/bin/pg_basebackup -D $BACKUP_PATH -Ft -z -v 2>&1 | ts '%F %T %Z' &> $BASEBACKUP_LOGTo create a backup of a local database where the tablespace in /opt/ts is relocated to ./backup/ts:
# crontab -e
0 1 * * * sh /home/postgres/eds/pune.sh
:wq
#cat /home/postgres/eds/pune.sh
# Config:
DB="POSTGRES_DB_NAME"
U="USERNAME"
export PGPASSWORD="tcs@12"
export PGPORT="5444"
export PGHOST="10.0.4.101"
/opt/edb/as9.6/bin/pg_basebackup -D /backup/data -T /opt/ts=/home/backup/tsPostgreSQL 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
Login with Google