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
|
#! /bin/bash
#
# Script for automatic maintenance of Debian PostgreSQL, to be run by cron,
# with the owner being the postgres superuser
# Use of this script with PostgreSQL passwords is likely to be insecure.
# The preferred method for ensuring access is to have "peer" authentication
# enabled by having this line in /etc/postgresql/pg_hba.conf:
#
# local all ident sameuser
#
# this will allow any user on the local machine to connect as
# himself without giving a password. This is now the default configuration for
# the Debian package.
syntax() {
echo Syntax: $0 [-v] [-a] [[-P password file] [-u user -p password]] [-u]
exit 1
}
verbose=
analyse=
while getopts avlu:p:P: arg
do
case $arg
in
a)
analyse=analyze
;;
v)
verbose=verbose
;;
u)
user=$OPTARG
;;
p)
password=$OPTARG
;;
P)
pfile=$OPTARG
;;
*)
syntax;;
esac
done
if [ -n "$user" -a -z "$password" ]
then
syntax
fi
if [ -z "$user" -a -n "$password" ]
then
syntax
fi
if [ -n "$pfile" -a -n "$user" ]
then
syntax
fi
if [ -n "$pfile" -a -n "$password" ]
then
syntax
fi
set -a
if [ -n "$pfile" ]
then
if [ ! -e "$pfile" -o ! -r "$pfile" ]
then
cat << EOM
The file, $pfile, specified by -P must exist and be readable by this
script.
EOM
exit 1
else
. "$pfile"
fi
fi
. /etc/postgresql/postgresql.env
# see if we're allowed to run
if [ -x /usr/lib/postgresql/bin/can_i_run ]
then
if ! /usr/lib/postgresql/bin/can_i_run
then
echo "PostgreSQL is currently disabled - can_i_run fails."
exit 1
fi
fi
if [ -z "$user" ]
then
dblist=$(/usr/bin/psql -t -c "select datname from pg_database order by datname" -d template1)
else
UNAME="-U$user"
dblist=$(/usr/bin/psql $UNAME -W -t -c "select datname from pg_database order by datname" -d template1 2>/dev/null <<EOI | tail +2
$password
EOI
)
fi
dblist=$(echo "$dblist" | grep -v template0 | sed -e 's/^ //1')
IFS='
'
(
if [ -n "${UNAME}" ]
then
echo $password
fi
for database in $dblist
do
if [ -n "$verbose" ]
then
echo "select 'Vacuuming ${database}';"
fi
echo "\c '${database}'"
echo "vacuum ${verbose} ${analyse};"
done
) | /usr/bin/psql -d template1 -t -q ${UNAME} 2>&1 | grep -v "Password:"
|
