Example of cron job for database maintenance?

[Guest]

Question to the group: Does anybody have an example for me for a job that automatically performs database management every night?

I have the postgresql database running on a Linux server so running cron jobs is a part of life. But I have no idea how to start a PL/pgSQL job that runs the vacuum, cluster and analyze commands. And since it might help the performance to do these commands during the night a few times a week I am looking for a way to do this.

Anybody happen to have an example of such a job? Both the cron command as well as the PL/pgSQL script I would need?

[Guest]

Well, since nobody seems to have this readily available I have tried something myself.

So, I switched to the postgres user, used crontab -e to make a new one and put the following text in the crontab:

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
HOME=/opt/data/postgres

00 3 * * * /usr/bin/vacuumdb --full --analyze "PT3 DB"

Every day at 03:00 I run the vacuum job with analysis included. And it seems to work.

Enjoy.

[driemekasten]

Please look at the following thread: http://www.pokertracker.com/forums/viewtopic.php?f=17&t=9381.

[Guest]

That is also my own thread