PostgreSQL Backup script to schedule daily -pg_basebackup

Physical backups are all-or-nothing – you take a full copy of the database cluster and you can restore all of it. The challenge is to make the backup consistent. Of course, the easiest way to do that would be to stop PostgreSQL completely and then make a copy of the data using one of the typical Unix tools like cp, scp or rsync. Unfortunately, it is not often that you have the possibility to shutdown your database. Therefore you need to take some precautions to ensure your backup is consistent. To take manual, consistent, physical backups, following steps are required:

1. You need to create a checkpoint using pg_start_backup(‘some label’)
2. Copy contents of the data directory
3. Stop backup using pg_stop_backup()

This can be simplified by running pg_basebackup:

If you want to have Point-In-Time-Recovery, you need to configure WAL archiving by enabling it:

You also want to define archive_command in a way it will copy WAL segments to a separate location.

Restoring base backup is easy – copy the data into clear PostgreSQL data directory and you are all set – once you start the PostgreSQL process, you will restore your data up to the time of the backup. To recover your PostgreSQL to point-in-time, using archived WAL logs, you still need to recover the base backup but then you need to create a recovery.conf file with, at least, a restore_command defined – you want to tell PostgreSQL how it can copy back archived WAL segments to be replayed at the start of the PostgreSQL process.

This script will take the backup of postgreSQL cluster  daily basis  and it will  remove the backup files if backup age  reached 7 days. its the 

#Backup Dir
#Make sure this is a dedicated mount point to PostgreSQL Backups
#Don't put traling / in path
#Backup Details
backup_dir=/backup
export PGPASSWORD="Nijam@1234"
export PGPORT="5432"
echo -e "\n\nBackup Status: $(date +"%d-%m-%y")" >> $backup_dir/Status.log
echo -e "-----------------------" >> $backup_dir/Status.log
echo -e "\nStart Time: $(date)\n" >> $backup_dir/Status.log
/usr/lib/postgresql/9.6/bin/pg_basebackup -U postgres  -w -D $backup_dir/PostgreSQL_Base_Backup_$(date +"%d-%m-%y") -l "`date`" -P -F tar -z -R &>> $backup_dir/Status.log
echo -e "\nEnd Time: $(date)" >> $backup_dir/Status.log

#Auto Deletion for Backups
#Value 7 for retention_duration will keep 8 days backups

#retention_duration=7
#find $backup_dir/PostgreSQL_Base_Backup* -type d -mtime +$retention_duration -exec rm -rv {} \;

To Shedule the backup at crontab :

export VISUAL=vim; crontab -e
0 2 * * * sh /var/lib/postgresql/fullbck.sh

Note: Once backup is completed please check it and restore the backup to someone testing server, it is for validating the postgresql backup whether script taking backup correctly or not.

Contact Us

*Name

*Email

*Mobile No

*Organization

*Description

Fields with (*) are compulsory.