On  8 Jul, Lamar Owen wrote:
>  Try the following one-liner (yes, one liner. e-mail will break it up,
>  and the syntax is kindof picky -- xargs is a great tool, even if it is a
>  little farkled):
>
>  psql -t -c "select datname from pg_database" template1|xargs -n 1 psql
>  -q -c "vacuum analyze"
>
>  This produces a list of the database names, strips the column header and 
>  row count, feeds it into xargs, which will feed each argument one at a
>  time (-n 1) to the vacuum script. This script is going in my crontab
>  tonight... ;-)

Hrm, that is pretty nice, actually.  Here is my perl script,
pretty short:

----------------------------------------------

#!/usr/bin/perl

use strict;
use Pg;
use FileHandle;

# Print output as it happens
autoflush STDOUT 1;

# Make a connection to postmaster
my $conn=Pg::connectdb("dbname=template1");

# Here's the sql...
my $sql="select datname from pg_database";

# ... that is executed here, gets the list of databases
my $result=$conn->exec($sql);

# Tell the user what's happening
print "Vacumming databases... ";

my @row;
# @row will contain the results (in a Pg-module format) in turn
while (@row=$result->fetchrow)
{
        # Connect to the database contained in @row
        my $conn=Pg::connectdb("dbname=$row[0]");

        # Get the ''official'' name of the database
        my $dbname=$conn->db;

        # Tell user
        print "$dbname ";

        # SQL statement...
        my $sql="vacuum";

        # ...processed here
        my $result=$conn->exec($sql);
}

# Print a newline
print "\n";

----------------------------------------

I've tested it a little bit, and it seems to work okay.
Of course, it's *much* longer than one line.  :-)  But
I can also use this file to do other postgres tasks,
and so can put this script into a cron job and modify
this one, rather than try to deal with the syntax of
the crontab file (I *always* have to whip out the man
pages).

Though it'd be weird to run pg_dumpall from a perl script.
Probably better to keep that separate.

-- Keith
[EMAIL PROTECTED]



Reply via email to