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]
