· 8 years ago · Apr 08, 2018, 10:28 PM
1#!/bin/bash
2
3# Unlike the built-in purge included in Proteus, this script only purges dynamic data. Refer to the
4# hist_tables and dynamic_history_condition variables if you would like to see the actual content
5# that is being purged.
6#
7# Unlike the previously documented dynamic puge, this purge is accomplised via an external script
8# rather than a postgresql stored procedureor function.
9#
10# The script will purge history data in a small batches which puts a lighter load on Proteus than the
11# purge which is run through the Proteus UI. Dong Han - June 15, 2011
12#
13# Updated on 3/07/2012 by dgadoury@BCN to include pre and post purge object counts.
14
15# The usage command was part of the original script, however, the script as written does not support
16# it so the days to keep needs to be hard coded.
17#usage ./incremental_dynamic_purge.sh [n -number of days data to keep, default is 10]
18
19# Database size, object counts, and timestamps are logged to /var/log/purge_history.log
20# Purge transactions and other purge related to data are logged to /var/log/purge_data.log
21
22### User editable variables
23daystokeep=180
24
25### Global Variables DO NOT EDIT ###
26PSQL=psql
27
28# Create and clear $idfile - needs to be created early in the script as it is referenced by purge_launcher
29idfile='/tmp/history_pkey'
30touch $idfile >$idfile
31
32bsize=500
33num=$bsize
34
35date2=`date -d "-$daystokeep days" +"%F %T"`
36
37# Create and clear purge data log file
38logfile='/tmp/purge_data.log'
39#>$logfilet
40
41logfile2='/var/log/purge_history.log'
42
43# $max_hist used when purging the history tables - refer to table_purge()
44max_hist=`$PSQL -U postgres -d proteusdb -t -c "SELECT ID FROM HISTORY WHERE timestamp < timestamp '$date2' ORDER BY id DESC LIMIT 1;"`
45
46
47# Database tables to be purged
48hist_tables=( "entity_history" "metadata_value_history" "entity_link_history" "metadata_field_history" "access_rights_history" "filter_history" "lease_summary" "history" )
49
50### Dynamic events to purge
51dynamic_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')"
52
53function create_in() {
54 array=( "$@" )
55
56 in_string=""
57 for i in `seq 0 $(( ${#array[@]} - 1 ))`; do
58 #echo "$i: ${array[i]}"
59 in_string="$in_string ${array[i]},"
60 done
61
62# echo `date +"%b_%d_%G_%H:%M:%S":`.$in_string >>$logfile
63
64 in_string="$in_string ${array[${#array[@]} - 1]}"
65 echo $in_string;
66}
67
68### Purge Function
69function table_purge(){
70
71 local count=0
72 local total=0
73 local tbl=$1
74
75
76 while [ "$num" -eq "$bsize" ]
77 do
78
79 if [ "$tbl" == "lease_summary" ]
80 then
81 $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 ;"
82 elif [ "$tbl" == "history" ]
83 then
84 $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 ;"
85
86 else
87 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 ;"
88
89 $PSQL -U postgres -d proteusdb -t -o $idfile -c "$cmd"
90
91 fi
92
93 num=`wc -w $idfile | awk '{print $1}'`
94
95 echo " table $tbl record number $num ===========" >>$logfile
96
97 if [ $num -gt 0 ]
98 then
99
100 echo `date +"%b_%d_%G_%H:%M:%S":`" get number $num " >>$logfile
101
102 filecontent=( `cat $idfile `)
103
104 ids=$(create_in "${filecontent[@]}")
105 echo "**************************************$ids===============================" >>$logfile
106
107 echo "************=====$ids=====" >>$logfile
108 count=`expr $count + 1`
109 let "remainder = count % 10"
110
111
112 if [ $remainder -eq 0 ]
113 then
114
115 total=`expr $count*$bsize`
116 echo `date +"%b_%d_%G_%H:%M:%S":` "======== $total orphan $tbl has been purged" >>$logfile
117
118 fi
119
120 if [[ "$tbl" == "lease_summary" || "$tbl" == "history" ]] ; then
121
122 $PSQL -U postgres -d proteusdb -c "DELETE FROM $tbl WHERE id in ( $ids );"
123 else
124 echo `date +"%b_%d_%G_%H:%M:%S":`" purge table $tbl old record of $num"
125 $PSQL -U postgres -d proteusdb -c "DELETE FROM $tbl WHERE history_pkey in ( $ids );"
126 fi
127 fi
128
129 done
130
131 echo `date +"%b_%d_%G_%H:%M:%S":`" completed delete old data in $tbl" >>$logfile2
132}
133
134#Main flow
135
136### What is the size of the database?
137echo `date +"%b_%d_%G_%H:%M:%S":`" Preparing to purge." >> $logfile2
138echo `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
139
140### Calculate the pre-purge object count and output to /var/log/purge_history.log
141echo `date +"%b_%d_%G_%H:%M:%S":`"The pre-purge dynamic object count is: " >> $logfile2
142
143# Pre-purge table object count
144for table in ${hist_tables[@]}
145do
146 cmd="SELECT count(1) FROM $table ;"
147 count=`$PSQL -U postgres -t -d proteusdb -c "$cmd"`
148 printf "%-22s: %u\n" "$table" "$count" >> $logfile2
149done
150
151### What data will be purged?
152echo `date +"%b_%d_%G_%H:%M:%S":`" historical data older than $date2 will be purged" >>$logfile2
153
154### When does the purge start
155echo `date +"%b_%d_%G_%H:%M:%S":`" purge starts at: `date`" >>$logfile2
156
157### Purge individual tables
158for table in ${hist_tables[@]}
159do
160 echo `date +"%b_%d_%G_%H:%M:%S":`" purge old data in $table" >>$logfile2
161 num=$bsize
162 table_purge $table
163done
164
165### Clean up on aisle 5
166rm -f $idfile
167rm -f $logfile
168
169echo `date +"%b_%d_%G_%H:%M:%S":`" purge ends at : `date` " >>$logfile2
170
171### Calculate post-purge object count
172echo `date +"%b_%d_%G_%H:%M:%S":`"The post-purge dynamic object count is: " >> $logfile2
173
174 table in ${hist_tables[@]}
175do
176 cmd="SELECT count(1) FROM $table ;"
177 count=`$PSQL -U postgres -t -d proteusdb -c "$cmd"`
178 printf "%-22s: %u\n" "$table" "$count" >> $logfile2
179done