Thread: Running vacuum on cron

    Hi. Is there any way to put a vacuum and a vacuum analyze (the
second does everything the first does? Or do I need to use both?) in a cron
so that it is executed nightly?

    Thanks

--
Pedro Miguel G. Alves

THINK - Tecnologias de Informa��o
Av. Defensores de Chaves n� 15 4�D, 1000-109 Lisboa Portugal
Tel: +351 21 3590285   Fax: +351 21 3582729
HomePage: www.think.co.pt

Pedro Alves wrote:

>         Hi. Is there any way to put a vacuum and a vacuum analyze (the
> second does everything the first does? Or do I need to use both?) in a cron
> so that it is executed nightly?

Sure....this is from my cron...

30 0 * * * /usr/local/pgsql/bin/vacuumdb -q -a

-Fran

> Hi. Is there any way to put a vacuum and a vacuum analyze (the
> second does everything the first does? Or do I need to use both?) in a
cron
> so that it is executed nightly?

# crontab -e -u postgres
0 0 * * * vacuumdb --all --analyze

  Depending on your path and binary locations, you may have to fully
qualify the "vacuumdb" command, such as
"/usr/local/pgsql/bin/vacuumdb --all --analyze".  You can also take the
opportunity to do backup stuff if you want, here's a simple example:

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

/usr/local/pgsql/bin/vacuumdb --all --analyze

DATESTAMP=`date +%B_%d_%Y__%H:%M:%S`
BACKUPDIR=/usr/local/pgsql/backup
FILENAME=pg_daily_backup__${DATESTAMP}.bz

/usr/local/pgsql/bin/pg_dumpall | /usr/bin/bzip2 > $BACKUPDIR/$FILENAME
---------------

  The location where you put the backups should be included in your normal
file backup routine.  Keeping a second copy on a trusted machine (say,
your file server) also makes it easier to get a file if things go very bad
in a hurry.

steve

Hello Pedro,

Two solutions
1) Give access to PostgreSQL from root user and run commands from
/etc/crontab. Not secure.
2) su postgres and use the crontab command as explained in
http://www.lysator.liu.se/~forsberg/linux/cron.html

Best regards,
Jean-Michel POURE


Hi. Is there any way to put a vacuum and a vacuum analyze (the
>second does everything the first does? Or do I need to use both?) in a cron
>so that it is executed nightly?
>
>         Thanks
>
>--
>Pedro Miguel G. Alves
>
>THINK - Tecnologias de Informação
>Av. Defensores de Chaves nº 15 4ºD, 1000-109 Lisboa Portugal
>Tel: +351 21 3590285   Fax: +351 21 3582729
>HomePage: www.think.co.pt
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org

Pedro Alves wrote:
>
>         Hi. Is there any way to put a vacuum and a vacuum analyze (the
> second does everything the first does? Or do I need to use both?) in a cron
> so that it is executed nightly?

Use a crontab entry like:
0 3 * * * /usr/local/bin/psql -d <database> < /.../vacuum_analyze

make sure the crontab owner has permission to do a vacuum analyze.
Change the path of psql as appropriate.
And finally the file /.../vacuum_analyze should contain:
vacuum analyze \g
\q

Pedro Alves wrote:

>         Hi. Is there any way to put a vacuum and a vacuum analyze (the
> second does everything the first does? Or do I need to use both?) in a cron
> so that it is executed nightly?

Sorry to double, but the -z flag on vacuumdb will also do the analyze for you,
so you can do it all in one command.

-Fran