Re: Is that a bug? - Mailing list pgsql-admin

Tomaz Borstnar writes:
>At 06:59 27.12.2000, R D wrote the following message:
>> >
>> > Does dropping and recreating the index help?
>>
>>10x! It worked!
>
>Anyone have some utility to recreate the index for any given database? This
>way I don't have to study the database and do lots of manual work which
>computers can do so much faster.

This is what I think you're asking for, and then some.

It's a grotesque mess of perl and shell script that we use nightly to
vacuum everything.  Since it's usually (at least for our databases) a
lot faster to drop each index, vacuum the tables, and then recreate
the index, that's what this does.

This does all tables in all databases, and assumes that they're all
owned by the same user (www in our case).  It uses "pg_dump -s" to get
the strings used to build each index (cheating :) and then just feeds
them back when it's done.  It fits our scenario nicely, but your
mileage will probably vary wildly.

One of the reasons it's wacky is that since it's run from cron, I
don't want to see the output unless there's a problem.  So this
collects the output of the vacuum, and then just eats it unless
there's a warning or error of some kind.  (I could have done the whole
thing in perl, but the first version of it was mostly shell, and then
it grew.  Kind of like a fungus.)

Developers, feel free to include this or something like it with the
PostreSQL distribution.  Credit me or not; doesn't matter.


Todd
--
Todd R. Eigenschink             TEK Interactive Group, Inc.
todd@tekinteractive.com         http://www.tekinteractive.com/
System Administrator            (219) 459-2521


----------------------------------------------------------------------
#!/bin/sh

PATH=/usr/pgsql/bin:$PATH

if [ `whoami` != 'www' ]; then
    echo "autovac must be run by www!"
    exit 1
fi

psql -l -A -q -t| tr '|' ' ' | grep -v '^template1 ' | \
while read DATABASE PGUSERID DATAPATH
do
    perl - $DATABASE <<'EOF' \
        | psql -e $DATABASE > /tmp/db.psql.$$ 2>&1
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

    perl - $DATABASE /tmp/db.psql.$$ <<'PROCESS'
my $database = shift;
my ($line, $mark);
while (<>)
{
    $mark = 1 if $_ !~ /^(VACUUM|CREATE|DROP)/;
    $line .= $_;
}

#(print "No errors, but here's the output:\n"), $mark = 1 if not $mark;
if ($mark)
{
    print "================================================================\n";
    print "DATABASE: $database\n";
    print $line;
    print "================================================================\n";
}
PROCESS

done

rm -f /tmp/db.psql.$$