Re: [ADMIN] Vacuum script
On Fri, 17 Dec 1999, Bruce Momjian wrote: It would be nice for new users; I think it would make it easier for them to actually set out and do it. Many new users are of the not-so-knowledgable variety, and shell scripting isn't something they want to undertake. Can someone modify the vacuumdb shell script to do that? i tried it... it seems to work -- nek;( Applied. SGML manual updated. New usage message updated. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
RE: [ADMIN] Vacuum script
I finally added this script to /etc/cron.daily (I use RedHat 5.1, and this directory
contains scripts that are ran by root each day at 4:02) :
#!/bin/sh
su postgres -c 'psql -t -c "select datname from pg_database order by datname;"
template1 | xargs -n 1 psql -q -c "vacuum analyze;"'
I think it's the best way to go under RedHat. All the scripts in /etc/cron.daily are
run one by one, this way there is no transient overload on the machine, if some heavy
scripts are added (and this one may be heavy).
Thanks for every answer.
Nicolas Huillard
-Message d'origine-
De: Lamar Owen [SMTP:[EMAIL PROTECTED]]
Date: jeudi 16 décembre 1999 17:44
À: Nicolas Huillard
Cc: '[EMAIL PROTECTED]'
Objet: Re: [ADMIN] Vacuum script
Nicolas Huillard wrote:
Thanks for this very simple and beautiful line !
You could simplify it by only having :
02 0 * * * echo "vacuum verbose;" | psql faq
Frankly, I'd prefer a script that automatically adapt to any added database in the
whole system : as an administrator, I don't want to edit my/postgres crontab each
time a new DB is added to Postgres...
I use the following crontab entry here (one line):
03 0 * * * psql -t -c "select datname from pg_database" template1 |
xargs -n 1 psql -q -c "vacuum analyze"
This pulls out all database names in simplified format (no header and no
count), pipes it to the command splitter xargs, which executes a quite
mode psql VACUUM for each database output by the first psql. Syntax to
xargs is tricky here -- it is in fact nonobvious that the simplest
syntax is the correct syntax. If you want some output e-mailed to you,
strip out the -q, and add verbose to the vacuum ("vacuum verbose
analyze").
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11
Re: [ADMIN] Vacuum script
On 1999-12-16, [EMAIL PROTECTED] mentioned: On Thu, 16 Dec 1999, Peter Eisentraut wrote: Actually such a script, called vacuumdb, with exactly those options is included in the distribution and should be installed in your bin dir right next to psql. Unfortunately, the vacuumdb script does not run through *all* your databases, only one that you specify. The other scripts that have been posted will vacuum all databases in a host installation. Point granted, but you could use something like this for db in `psql -t -A -d template1 -c "select datname from\ pg_database"`; do vacuum $db ; done rather than re-inventing the wheel. Perhaps this sort of option should be included in the script though, as it is clearly needed by a lot of people. Will ponder. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [ADMIN] Vacuum script
On 18 Dec, Peter Eisentraut wrote: Point granted, but you could use something like this for db in `psql -t -A -d template1 -c "select datname from\ pg_database"`; do vacuum $db ; done rather than re-inventing the wheel. Certainly. I wrote my perl script because I originally intended to do other database-maintenance with it as well. Of course, that hasn't happened yet :-) Perhaps this sort of option should be included in the script though, as it is clearly needed by a lot of people. Will ponder. It would be nice for new users; I think it would make it easier for them to actually set out and do it. Many new users are of the not-so-knowledgable variety, and shell scripting isn't something they want to undertake. -- Keith [EMAIL PROTECTED] maintainer of alt.os.linux.slackware FAQ
Re: [ADMIN] Vacuum script
On 18 Dec, Peter Eisentraut wrote: Point granted, but you could use something like this for db in `psql -t -A -d template1 -c "select datname from\ pg_database"`; do vacuum $db ; done rather than re-inventing the wheel. Certainly. I wrote my perl script because I originally intended to do other database-maintenance with it as well. Of course, that hasn't happened yet :-) Perhaps this sort of option should be included in the script though, as it is clearly needed by a lot of people. Will ponder. It would be nice for new users; I think it would make it easier for them to actually set out and do it. Many new users are of the not-so-knowledgable variety, and shell scripting isn't something they want to undertake. Can someone modify the vacuumdb shell script to do that? -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [ADMIN] Vacuum script
On Fri, 17 Dec 1999, Bruce Momjian wrote: It would be nice for new users; I think it would make it easier for them to actually set out and do it. Many new users are of the not-so-knowledgable variety, and shell scripting isn't something they want to undertake. Can someone modify the vacuumdb shell script to do that? i tried it... it seems to work -- nek;( --- /usr/bin/vacuumdb Sun Sep 26 22:41:17 1999 +++ ./vacuumdb Sat Dec 18 08:14:03 1999 @@ -42,2 +42,3 @@ --verbose) verbose="verbose";; + --alldb) dbname="`psql -t -A -d template1 -c 'SELECT datname FROM +pg_database'`";; @@ -89,5 +90,5 @@ fi - -psql $PASSWDOPT -tq $AUTHOPT $PGHOSTOPT $PGPORTOPT -c "vacuum $verbose $analyze $table" $dbname - +for db in $dbname; do +psql $PASSWDOPT -tq $AUTHOPT $PGHOSTOPT $PGPORTOPT -c "vacuum $verbose $analyze +$table" $db +done; if [ $? -ne 0 ]; then
Re: [ADMIN] Vacuum script
On Thu, 16 Dec 1999, Nicolas Huillard wrote: Hello, Does anybody already wrote a cron script that periodically (with cron) vacuum all (or part of all) database on a single server ? I'm sure everyone has this kind of stuff : $ pg_vacuum [-h hostname] [-u] [options] [dbname] Can someone share his script, or can I find it on the CVS (please tell me where, because I've never used that) Hi, I have this line in my crontab: 02 0 * * * /usr/bin/psql faq /home/michel/bin/vacuum and /home/michel/bin/vacuum is: vacuum verbose; Ok, this is not the best, but it works, Errare è umano, ma per fare veramente casino ci vuole la password di root. -- Michel ZioBudda Morelli [EMAIL PROTECTED] http://ziobudda.enter.it Italian Linux FAQ http://ziobudda.enter.it/FAQ/ Italian Linux Presshttp://ziobudda.enter.it/ILP/
RE: [ADMIN] Vacuum script
Thanks for this very simple and beautiful line ! You could simplify it by only having : 02 0 * * * echo "vacuum verbose;" | psql faq Frankly, I'd prefer a script that automatically adapt to any added database in the whole system : as an administrator, I don't want to edit my/postgres crontab each time a new DB is added to Postgres... Nicolas -Message d'origine- De: ZioBudda [SMTP:[EMAIL PROTECTED]] Date: jeudi 16 décembre 1999 15:09 À: Nicolas Huillard Cc: '[EMAIL PROTECTED]' Objet: Re: [ADMIN] Vacuum script On Thu, 16 Dec 1999, Nicolas Huillard wrote: Hello, Does anybody already wrote a cron script that periodically (with cron) vacuum all (or part of all) database on a single server ? I'm sure everyone has this kind of stuff : $ pg_vacuum [-h hostname] [-u] [options] [dbname] Can someone share his script, or can I find it on the CVS (please tell me where, because I've never used that) Hi, I have this line in my crontab: 02 0 * * * /usr/bin/psql faq /home/michel/bin/vacuum and /home/michel/bin/vacuum is: vacuum verbose; Ok, this is not the best, but it works, Errare è umano, ma per fare veramente casino ci vuole la password di root. -- Michel ZioBudda Morelli [EMAIL PROTECTED] http://ziobudda.enter.it Italian Linux FAQ http://ziobudda.enter.it/FAQ/ Italian Linux Presshttp://ziobudda.enter.it/ILP/
RE: [ADMIN] Vacuum script
On Thu, 16 Dec 1999, Nicolas Huillard wrote: Thanks for this very simple and beautiful line ! You could simplify it by only having : 02 0 * * * echo "vacuum verbose;" | psql faq Frankly, I'd prefer a script that automatically adapt to any added database in the whole system : as an administrator, I don't want to edit my/postgres crontab each time a new DB is added to Postgres... Debian GNU/Linux system distrubuted with excelent script of this sort - it asks Postgres, get a list of DBs and vacuum them all in a loop. Oleg. Oleg Broytmann http://members.xoom.com/phd2/ [EMAIL PROTECTED] Programmers don't die, they just GOSUB without RETURN.
RE: [ADMIN] Vacuum script
Do you have a URL to this script, or can you send it back via mail, if you have/use it ? (I use RedHat, and there is no script like that in it). Nicolas -Message d'origine- De: Oleg Broytmann [SMTP:[EMAIL PROTECTED]] Date: jeudi 16 décembre 1999 15:45 À: Nicolas Huillard Cc: '[EMAIL PROTECTED]' Objet: RE: [ADMIN] Vacuum script Debian GNU/Linux system distrubuted with excelent script of this sort - it asks Postgres, get a list of DBs and vacuum them all in a loop. Oleg.
Re: [ADMIN] Vacuum script
On Thu, 16 Dec 1999, Mark Jewiss wrote: Hello, On Thu, 16 Dec 1999, ZioBudda wrote: Can someone share his script, or can I find it on the CVS (please tell me where, because I've never used that) I've got a perl script which will look through what db's you have, then make a tar.gz file out of the backup file produced from a vacuum and pg_dump. Sorry, but I am not the writer. However, pls send me your script. Windows ha un'utility per correggere automaticamente i problemi. Si chiama FDISK. -- Michel ZioBudda Morelli [EMAIL PROTECTED] http://ziobudda.enter.it Italian Linux FAQ http://ziobudda.enter.it/FAQ/ Italian Linux Presshttp://ziobudda.enter.it/ILP/
Re: [ADMIN] Vacuum script
Nicolas Huillard wrote:
Thanks for this very simple and beautiful line !
You could simplify it by only having :
02 0 * * * echo "vacuum verbose;" | psql faq
Frankly, I'd prefer a script that automatically adapt to any added database in the
whole system : as an administrator, I don't want to edit my/postgres crontab each
time a new DB is added to Postgres...
I use the following crontab entry here (one line):
03 0 * * * psql -t -c "select datname from pg_database" template1 |
xargs -n 1 psql -q -c "vacuum analyze"
This pulls out all database names in simplified format (no header and no
count), pipes it to the command splitter xargs, which executes a quite
mode psql VACUUM for each database output by the first psql. Syntax to
xargs is tricky here -- it is in fact nonobvious that the simplest
syntax is the correct syntax. If you want some output e-mailed to you,
strip out the -q, and add verbose to the vacuum ("vacuum verbose
analyze").
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11
Re: [ADMIN] Vacuum script
Here is a perl script I use for vacuuming. It's
in my postgres crontab, so that it runs with the
proper environment variables. There's probably
a more elegant solution, but this works okay for
me.
--Keith
[EMAIL PROTECTED]
#!/usr/bin/perl
### vacuum.plx
### Copyright K. Keller 07/24/1999
### This script can be distributed under the GNU GPL.
### Look at their web site for details: www.gnu.org
### No warranty for any purpose whatsoever; caveat user.
use strict;
use Pg;
# Useful to do autoflushing on stdout, but not necessary
use FileHandle;
autoflush STDOUT 1;
# Every PostgreSQL installation should have template1
my $conn=Pg::connectdb("dbname=template1");
# Get the list of databases from the Postgres master list
my $sql="select datname from pg_database";
my $result=$conn-exec($sql);
print "Vacumming databases... ";
my @row;
while (@row=$result-fetchrow)
{
# Connect to the database
my $conn=Pg::connectdb("dbname=$row[0]");
# Make sure it's the right one, and print the name
my $dbname=$conn-db;
print "$dbname ";
# Issue the vacuum sql command
my $sql="vacuum";
my $result=$conn-exec($sql);
}
print "\n";
Re: [ADMIN] Vacuum script
On 1999-12-16, Nicolas Huillard mentioned: Does anybody already wrote a cron script that periodically (with cron) vacuum all (or part of all) database on a single server ? I'm sure everyone has this kind of stuff : $ pg_vacuum [-h hostname] [-u] [options] [dbname] Can someone share his script, or can I find it on the CVS (please tell me where, because I've never used that) Actually such a script, called vacuumdb, with exactly those options is included in the distribution and should be installed in your bin dir right next to psql. I think it's not in the RPMs though. (boo) -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
