Running vacuum on cron

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

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

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

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

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