Bluecat Proteus Purge Incremental Purge

#!/bin/bash

# Unlike the built-in purge included in Proteus, this script only purges dynamic data. Refer to the
# hist_tables and dynamic_history_condition variables if you would like to see the actual content
# that is being purged.
#
# Unlike the previously documented dynamic puge, this purge is accomplised via an external script
# rather than a postgresql stored procedureor function.
#
# The script will purge history data in a small batches which puts  a lighter load on Proteus than the
# purge which is run through the Proteus UI.  Dong Han - June 15, 2011
#
# Updated on 3/07/2012 by dgadoury@BCN to include pre and post purge object counts.

# The usage command was part of the original script, however, the script as written does not support
# it so the days to keep needs to be hard coded.
#usage ./incremental_dynamic_purge.sh [n -number of days data to keep, default is 10]

# Database size, object counts, and timestamps are logged to /var/log/purge_history.log
# Purge transactions and other purge related to data are logged to /var/log/purge_data.log

### User editable variables
daystokeep=180

### Global Variables DO NOT EDIT ###
PSQL=psql

# Create and clear $idfile - needs to be created early in the script as it is referenced by purge_launcher
idfile='/tmp/history_pkey'
touch $idfile >$idfile

bsize=500
num=$bsize

date2=`date -d "-$daystokeep days" +"%F %T"`

# Create and clear purge data log file
logfile='/tmp/purge_data.log'
#>$logfilet

logfile2='/var/log/purge_history.log'

# $max_hist used when purging the history tables - refer to table_purge()
max_hist=`$PSQL -U postgres -d proteusdb -t -c "SELECT ID FROM HISTORY WHERE timestamp < timestamp '$date2' ORDER BY id DESC LIMIT 1;"`


# Database tables to be purged
hist_tables=( "entity_history" "metadata_value_history" "entity_link_history" "metadata_field_history" "access_rights_history" "filter_history" "lease_summary" "history" )

### Dynamic events to purge
dynamic_history_condition="history.comment  IN ('CLIENT:DHCPDynamicUpdater', 'CLIENT:DNSDynamicAdd', 'CLIENT:DNSDynamicDelete', 'CLIENT:DNSDynamicUpdate', 'DHCP Dynamic Updater operation', 'DNS Dynamic Add operation', 'DNS Dynamic Delete operation')"

function create_in() {
    array=( "$@" )

    in_string=""
    for i in `seq 0 $(( ${#array[@]} - 1 ))`; do
        #echo "$i: ${array[i]}"
        in_string="$in_string ${array[i]},"
    done

#   echo `date +"%b_%d_%G_%H:%M:%S":`.$in_string >>$logfile

    in_string="$in_string ${array[${#array[@]} - 1]}"
    echo $in_string;
}

### Purge Function
function table_purge(){

    local count=0
    local total=0
    local tbl=$1


    while  [ "$num" -eq "$bsize" ]
    do

        if [ "$tbl" == "lease_summary" ]
        then
            $PSQL  -U postgres -d proteusdb -t -o $idfile -c "SELECT id FROM $tbl WHERE expire_time < timestamp '$date2' AND NOT EXISTS (SELECT lease_summary_id FROM lease WHERE lease_summary.id = lease_summary_id ) limit $bsize ;"
        elif [ "$tbl" == "history" ]
        then
            $PSQL  -U postgres -d proteusdb -t -o $idfile -c "SELECT id FROM $tbl WHERE id < $max_hist AND $dynamic_history_condition order by id limit $bsize ;"

        else
            local cmd="SELECT history_pkey FROM $tbl ht WHERE NOT EXISTS (SELECT 1 FROM history where  history.id = ht.history_id ) OR EXISTS (SELECT 1 FROM history where  history.id = ht.history_id AND history.id < $max_hist AND $dynamic_history_condition )  order by history_pkey  limit $bsize  ;"

            $PSQL  -U postgres -d proteusdb -t -o $idfile -c  "$cmd"

        fi

        num=`wc -w $idfile | awk '{print $1}'`

        echo " table $tbl record number $num ===========" >>$logfile

        if [ $num -gt 0 ]
        then

            echo `date +"%b_%d_%G_%H:%M:%S":`" get number $num "  >>$logfile

            filecontent=( `cat $idfile `)

            ids=$(create_in "${filecontent[@]}")
            echo "**************************************$ids===============================" >>$logfile

            echo "************=====$ids====="  >>$logfile
            count=`expr $count + 1`
            let "remainder = count % 10"


            if [ $remainder -eq 0 ]
            then

                total=`expr $count*$bsize`
                echo `date +"%b_%d_%G_%H:%M:%S":` "======== $total orphan $tbl has been purged" >>$logfile

            fi

            if [[ "$tbl" == "lease_summary" || "$tbl" == "history" ]] ; then

                $PSQL  -U postgres -d proteusdb -c "DELETE FROM $tbl WHERE id in ( $ids );"
            else
                echo `date +"%b_%d_%G_%H:%M:%S":`" purge table $tbl old record of $num"
                $PSQL  -U postgres -d proteusdb -c "DELETE FROM $tbl WHERE history_pkey in ( $ids );"
            fi
        fi

    done

    echo `date +"%b_%d_%G_%H:%M:%S":`" completed delete old data in $tbl" >>$logfile2
}

#Main flow

### What is the size of the database?
echo `date +"%b_%d_%G_%H:%M:%S":`" Preparing to purge." >> $logfile2
echo `date +"%b_%d_%G_%H:%M:%S":`" The size of the database as of is" `$PSQL -U postgres -d proteusdb -t -c "select pg_size_pretty(pg_database_size('proteusdb')) ;"` >> $logfile2

### Calculate the pre-purge object count and output to /var/log/purge_history.log
echo `date +"%b_%d_%G_%H:%M:%S":`"The pre-purge dynamic object count is: " >> $logfile2

# Pre-purge table object count
for table in ${hist_tables[@]}
do
        cmd="SELECT count(1) FROM $table ;"
        count=`$PSQL -U postgres -t -d proteusdb -c "$cmd"`
        printf "%-22s: %u\n" "$table" "$count" >> $logfile2
done

### What data will be purged?
echo `date +"%b_%d_%G_%H:%M:%S":`" historical data older than $date2 will be purged" >>$logfile2

### When does the purge start
echo `date +"%b_%d_%G_%H:%M:%S":`" purge starts at: `date`" >>$logfile2

### Purge individual tables
for table in ${hist_tables[@]}
do
    echo `date +"%b_%d_%G_%H:%M:%S":`" purge old data in $table" >>$logfile2
    num=$bsize
    table_purge $table
done

### Clean up on aisle 5
rm -f $idfile
rm -f $logfile

echo `date +"%b_%d_%G_%H:%M:%S":`" purge ends at : `date` " >>$logfile2

### Calculate post-purge object count
echo `date +"%b_%d_%G_%H:%M:%S":`"The post-purge dynamic object count is: " >> $logfile2

 table in ${hist_tables[@]}
do
        cmd="SELECT count(1) FROM $table ;"
        count=`$PSQL -U postgres -t -d proteusdb -c "$cmd"`
        printf "%-22s: %u\n" "$table" "$count" >> $logfile2
done