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
|
#!/usr/bin/perl -wT
# Vacuum (and optionally analyze) all databases in all clusters.
#
# (C) 2005 Martin Pitt <mpitt@debian.org>
use lib '/usr/share/postgresql-common';
use Getopt::Long;
use PgCommon;
# untaint environment
$ENV{'PATH'} = '/bin:/usr/local/bin:/usr/bin';
delete @ENV{'IFS', 'CDPATH', 'ENV', 'BASH_ENV'};
$full = 0;
$analyze = 0;
$verbose = 0;
$force = 0;
$cluster = '';
# Vacuum/analyze a cluster according to the $fulll, $analyze, $verbose, $force
# settings.
# Arguments: <version> <cluster>
sub vacuum_cluster {
my ($v, $c) = @_;
%info = cluster_info $v, $c;
if (!$info{'running'}) {
print "Skipping cluster $v/$c which is not running\n";
return;
}
if (!$force && $info{'avac_enable'}) {
print "Skipping cluster $v/$c since autovacuuming is enabled for it\n";
return;
}
print "Doing maintenance on cluster $v/$c...\n";
# fork a vacuumdb subproces, change user id to the cluster owner
if (fork) {
wait;
print STDERR "calling vacuumdb on cluster $v/$c failed\n" if $?;
} else {
chdir '/';
change_ugid $info{'owneruid'}, $info{'ownergid'};
@options = ('--cluster', "$v/$c", '-a');
push @options, '-f' if $full;
push @options, '-z' if $analyze;
push @options, '-v' if $verbose;
push @options, '-q' unless $verbose;
exec '/usr/bin/vacuumdb', @options or
error 'could not execute vacuumdb';
}
}
exit 1 unless GetOptions ('full|f' => \$full, 'analyze|a' => \$analyze,
'verbose|v' => \$verbose, 'force' => \$force, 'cluster|c=s' => \$cluster);
if ($#ARGV != -1) {
print "Usage: $0 [--cluster <version/cluster>] [-f|--full] [-a|--analyze] [-v|--verbose] [--force]\n";
exit 1;
}
if ($cluster) {
my ($v, $c) = split ('/', $cluster, 2);
error 'Cluster does not exist' unless cluster_exists $v, $c;
vacuum_cluster $v, $c;
} else {
for $v (get_versions) {
for $c (get_version_clusters $v) {
vacuum_cluster $v, $c;
}
}
}
__END__
=head1 NAME
pg_maintenance - perform maintenance tasks on all clusters
=head1 SYNOPSIS
B<pg_maintenance> [B<-f>|B<--full>] [B<-a>|B<--analyze>] [B<-v>|B<--verbose>]
[B<--force>]
=head1 DESCRIPTION
This script performs maintenance actions on all databases in all clusters. In
particular, this calls B<vacuumdb> on all databases, which frees up unused
space and helps to improve database performance (if B<--analyze> is specified).
This script is intended to be called regularly in a cronjob.
=head1 OPTIONS
=over 4
=item B<-c>, B<--cluster> I<version/cluster>
Perform maintenance only on specified cluster. By default, all clusters are
handled.
=item B<-f>, B<--full>
Perform a "full vacuum", which is more effective but takes more time
and needs to completely lock tables while working on it.
=item B<-a>, B<--analyze>
Perform some statistical analyses on the tables which helps to improve
database performance. This only needs read access to the tables, thus does
not require any locking.
=item B<-v>, B<--verbose>
Passed to B<vacuumdb>, which prints lots of information about the vacuuming and
analysis.
=item B<--force>
By default a cluster is not processed if autovacuuming is enabled for it. If
this option is specified, the cluster is processed regardless of the autovacuum
daemon status.
=back
=head1 SEE ALSO
L<vacuumdb(1)>, L<vacuum(7)>, L<analyze(7)>, L<pg_ctlcluster(1)>
=head1 AUTHOR
Martin Pitt L<E<lt>mpitt@debian.orgE<gt>>
|
