-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcheck_postgres_least_vacuum.sh
214 lines (186 loc) · 7.23 KB
/
check_postgres_least_vacuum.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
#!/bin/bash
# ========================================================================================
# Postgres least vacuum datenagios check using psql and bash.
#
# Author: Rumman
# This script expects psql to be in the PATH.
#
# Usage: ./check_postgres_least_vacuum [-h] [-v][ -H <host> ] [ -P <port> ] [ -U user ] [ -D dbname] [ -x <units> ]
# [-w <warn_period>] [-c <critical_period>]
# -h --host host (default 127.0.0.1)
# -p --port port (default 5432)
# -U --user database user (default postgres)
# -x --units units of measurement to display (s = seconds; m = minutes; h = hours; D = days; M = months; Y = year"
# -d --dbname dbname to connect with postgresql (default postgres)
# -w --warning warning threshold (default 1 day)
# -c --critical critical threshold (default 3 days)
# ========================================================================================
# Nagios return codes
STATE_OK=0
STATE_WARNING=1
STATE_CRITICAL=2
STATE_UNKNOWN=3
# set thresholds in bytes
WARNING_THRESHOLD=1
CRITICAL_THRESHOLD=3
HOST="127.0.0.1"
PORT=5432
USER=postgres
UNITS="D"
DBNAME=postgres
DEBUG=0
echo "working" > /tmp/check_postgres_least_vacuum.tmp
debug_print() {
if [ "$DEBUG" -eq 1 ];
then
OUTPUT=$1
echo $OUTPUT
fi
}
help_print() {
echo "Postgres least vacuum datenagios check using psql and bash."
echo ""
echo "2013 Vantage Labs LLC."
echo "# This script expects psql to be in the PATH."
echo ""
echo "Usage: ./check_postgres_least_vacuum [-h] [-v][ -H <host> ] [ -P <port> ] [ -U user ] [ -D dbname] [ -x <units> ] [-w <warn_period>] [-c <critical_period>]"
echo " -h --help help"
echo " -v --verbose verbose or debug mode"
echo " -H --host host (default 127.0.0.1)"
echo " -P --port port (default 5432)"
echo " -U --user database user (default postgres)"
echo " -D --dbname dbname to connect with postgresql (default postgres)"
echo " -x --units units of measurement to display (s = seconds; m = minutes; h = hours; D = days; M = months; Y = year"
echo " -w --warning warning threshold (default 1 day)"
echo " -c --critical critical threshold (default 3 days)"
}
# Parse parameters
while [ $# -gt 0 ]; do
case "$1" in
-h | --help)
help_print
exit 0;
;;
-v | --verbose)
DEBUG=1
;;
-H | --host)
shift
HOST=$1
;;
-P | --port)
shift
PORT=$1
;;
-U | --user)
shift
USER=$1
;;
-D | --dbname)
shift
DBNAME=$1
;;
-x | --unit)
shift
UNITS=$1
;;
-w | --warning)
shift
WARNING_THRESHOLD=$1
;;
-c | --critical)
shift
CRITICAL_THRESHOLD=$1
;;
*) echo "Unknown argument: $1"
exit $STATE_UNKNOWN
;;
esac
shift
done
debug_print "Verbose mode is ON"
debug_print "HOST=$HOST"
debug_print "PORT=$PORT"
debug_print "USER=$USER"
debug_print "DBNAME=$DBNAME"
debug_print "UNITS=$UNITS"
debug_print "WARNING_THRESHOLD=$WARNING_THRESHOLD"
debug_print "CRITICAL_THRESHOLD=$CRITICAL_THRESHOLD"
#Check for units
if [ $UNITS == 's' ];
then
let DIV=1
UNITS="seconds"
elif [ $UNITS == 'm' ];
then
let DIV=60
UNITS="minutes"
elif [ $UNITS == 'h' ];
then
let DIV=60*60
UNITS="hours"
elif [ $UNITS == 'D' ];
then
let DIV=60*60*24
UNITS="days"
elif [ $UNITS == 'M' ];
then
let DIV=60*60*24*30
UNITS="months"
elif [ $UNITS == 'Y' ];
then
let DIV=60*60*24*30*12
UNITS="years"
else
echo "!!!Invaild unit values!!!"
exit $STATE_UNKNOWN
fi
CURRENT_DATE=`eval date +%Y-%m-%d_%H:%M:%S`
CURRENT_DATE=`echo "$CURRENT_DATE" | sed -r 's/[_]+/ /g'`
FINAL_LEAST_VACUUM_DATE=$CURRENT_DATE
FINAL_LEAST_VACUUM_DATE=`echo "$FINAL_LEAST_VACUUM_DATE" | sed -r 's/[_]+/ /g'`
FINAL_LEAST_VACUUM_DATE_INT=`date --date="$FINAL_LEAST_VACUUM_DATE" +%s`
debug_print "Current_date = $FINAL_LEAST_VACUUM_DATE ($FINAL_LEAST_VACUUM_DATE_INT)"
debug_print "Command = psql -d $DBNAME -U $USER -Atc \"SELECT datname FROM pg_database WHERE datname NOT IN ('postgres')\" -h $HOST -p $PORT"
GET_DB_LIST=`psql -d $DBNAME -U $USER -Atc "SELECT datname FROM pg_database WHERE datallowconn and NOT datistemplate and datname NOT IN ('postgres')" -h $HOST -p $PORT`
if [ $? -gt 0 ];
then
echo "ERROR:; can't connect to Postgresql database"
exit $STATE_UNKNOWN
fi
debug_print "Database lists = $GET_DB_LIST"
array=(${GET_DB_LIST// / })
for i in "${!array[@]}"
do
DBNAME=${array[i]}
debug_print "Workiing for db = $DBNAME"
SQL="SELECT min(last_vacuum) FROM pg_stat_all_tables"
LEAST_VACUUM_DATE=`psql -d $DBNAME -U $USER -Atc "$SQL" -h $HOST -p $PORT`
if [ $? -gt 0 ];
then
echo "ERROR:; can't connect to Postgresql database"
exit $STATE_UNKNOWN
fi
LEAST_VACUUM_DATE_INT=`date --date="$LEAST_VACUUM_DATE" +%s`
debug_print "LEAST_VACUUM_DATE = $LEAST_VACUUM_DATE ($LEAST_VACUUM_DATE_INT) "
if [ "$LEAST_VACUUM_DATE_INT" -lt "$FINAL_LEAST_VACUUM_DATE_INT" ];
then
FINAL_LEAST_VACUUM_DATE=$LEAST_VACUUM_DATE
FINAL_LEAST_VACUUM_DATE_INT=`date --date="$FINAL_LEAST_VACUUM_DATE" +%s`
fi
done
debug_print "Least vacuum date for Postgresql db server at $HOST on $PORT $FINAL_LEAST_VACUUM_DATE"
# Calculate xlog diff in bytes
DIFF=`echo $"(( $(date --date="$CURRENT_DATE" +%s) - $(date --date="$FINAL_LEAST_VACUUM_DATE" +%s) ))/($DIV)"|bc`
if [ $DIFF -ge $CRITICAL_THRESHOLD ];
then
echo "CRITICAL: Difference between current date ($CURRENT_DATE) and least vacuum date ($FINAL_LEAST_VACUUM_DATE) = $DIFF $UNITS"
exit $STATE_CRITICAL
elif [ $DIFF -ge $WARNING_THRESHOLD ];
then
echo "WARNING: Difference between current date ($CURRENT_DATE) and least vacuum date ($FINAL_LEAST_VACUUM_DATE) = $DIFF $UNITS"
exit $STATE_WARNING
else
echo "OK: Difference between current date ($CURRENT_DATE) and least vacuum date ($FINAL_LEAST_VACUUM_DATE) = $DIFF $UNITS"
exit $STATE_OK
fi