We ran into a problem with some tables that were taking forever to
vacuum due to the indices we had created. The trick of dropping the
indices and recreating them after the vacuum works very well, so I
looked for a way to automate it. Here's what I came up with. I call
it "autovac".
It processes all tables in all databases automatically. Be aware that
each index will be re-created as the user who runs autovac, unless you
make some changes.
It's a shell script that uses perl to do the text processing, and psql
to actually execute the commands.
Comments and suggestions are welcome.
Todd
--------8<--8<---cut here--->8-->8------------
#!/bin/sh
PATH=/usr/pgsql/bin:$PATH
psql -l -A -q -t| tr '|' ' ' | grep -v '^template1 ' | \
while read DATABASE PGUSERID DATAPATH
do
echo "================================================================"
echo "DATABASE: $DATABASE"
perl - $DATABASE <<'EOF' | psql $DATABASE
my $database = shift;
open SCHEMA, "/usr/pgsql/bin/pg_dump -s $database |";
while (<SCHEMA>)
{
if (/CREATE TABLE \"(\S+)\"/)
{ $table{$1} = []; }
elsif (/CREATE\s+INDEX\s+\"\S+\" on \"(\S+)\" using/)
{ push @{$table{$1}}, $_; }
}
close SCHEMA;
foreach my $table (sort keys %table)
{
foreach (@{$table{$table}})
{
/CREATE\s+INDEX\s+\"(\S+)\"\s+on/;
print "DROP INDEX $1;\n";
}
print "VACUUM ANALYZE $table;\n";
foreach (@{$table{$table}}) { print; }
}
EOF
echo "================================================================"
exit
done
--------8<--8<---cut here--->8-->8------------
