PostgreSQL Daily Continuous Backup Script

backup_pg.sh

#!/bin/bash

backup_dir=/backup/pg_backups
wal_dir=/backup/postgresql
today=$(date +%Y-%m-%d.%H:%M:%S)
yesterday=$(find $backup_dir -maxdepth 1 -type d -name "*" -exec basename {} \; | tail -n +2 | grep -v "lost+found" | sort -rn | head -1) 

psql -c "select pg_start_backup('$today', true);"

if [ $? = 0 ]
then
    mkdir -p $backup_dir/$today
    tar cvj --one-file-system -f $backup_dir/$today/base.tar.bz2 /var/lib/postgresql
    psql -c 'select pg_stop_backup();'

    cd $wal_dir
    # find the latest numerical *.backup file and extract the wal segment name it applies to
    breakpoint=`ls *.backup | sort -r | head -n1 | sed -e 's/\..*$//'`

    # get the linenumber of applicable wal segment in the directory listing, generate a list to 
    # archive of all those that are in that first set of lines, including the backup wal segment
    arline=`ls | sort | sed -ne "/^$breakpoint$/ =" `
    archive=`ls | sort | head -n $arline`

    # get the line number of the wal segment immediately before the backup. generate the list to 
    # remove of all those in that set of lines, excluding the backup wal segment
    rmline=`echo "$arline - 1" | bc`
    remove=`ls | sort | head -n $rmline`

    tar cvjf $backup_dir/$yesterday/full-wal.tar.bz2 $archive
    rm $remove
    tar cvjf $backup_dir/$today/pit-wal.tar.bz2 $wal_dir

    cd $backup_dir
    ls -1d * | sort -rn | tail -n +15 | xargs rm -vr 
    cd $OLDPWD
else
    echo "could not backup database on $today" | mail -s "backup failure" [email protected]
fi