Thread: vacuuming
Is it common practice to setup a cron job to vacuum your databases nightly? Do you just do something like: psql my_db < vacuum in cron? I was reading about administration and it talked about how vacuuming is such a good idea and all. If i am going to cron vacuum, is there anything else I should be doing, or is just "psql my_db < vacuum" all that needs done on a nightly basis. Brian /-------------------------- signal@shreve.net -----------------------------\ | Brian Feeny | USR TC Hubs | ShreveNet Inc. (318)222-2638 | | Network Administrator | Perl, Linux | Web hosting, online stores, | | ShreveNet Inc. | USR Pilot | Dial-Up 14.4-56k, ISDN & LANs | | 89 CRX DX w/MPFI, lots of |-=*:Quake:*=-| http://www.shreve.net/ | | mods/Homepage coming soon |LordSignal/SN| Quake server: 208.206.76.47 | \-------------------------- 318-222-2638 x109 -----------------------------/
> Is it common practice to setup a cron job to vacuum your databases > nightly? Do you just do something like: > > psql my_db < vacuum > > in cron? I was reading about administration and it talked about how > vacuuming is such a good idea and all. > > If i am going to cron vacuum, is there anything else I should be doing, or > is just "psql my_db < vacuum" all that needs done on a nightly basis. > Brian, you might try adding a something to the shell script that you are kicking off with cron a dump of your databases. After you dump them, then you could also push them to another machine on your network, or if a tape is in the tape drive, write them to tape. That's just good old UNIX admin advice; I hope its helpful...james
> > Brian, > > you might try adding a something to the shell script that you are kicking off > with cron a dump of your databases. After you dump them, then you could also > push them to another machine on your network, or if a tape is in the tape drive, > write them to tape. > > That's just good old UNIX admin advice; I hope its helpful...james > > Good point............thanks. /-------------------------- signal@shreve.net -----------------------------\ | Brian Feeny | USR TC Hubs | ShreveNet Inc. (318)222-2638 | | Network Administrator | Perl, Linux | Web hosting, online stores, | | ShreveNet Inc. | USR Pilot | Dial-Up 14.4-56k, ISDN & LANs | | 89 CRX DX w/MPFI, lots of |-=*:Quake:*=-| http://www.shreve.net/ | | mods/Homepage coming soon |LordSignal/SN| Quake server: 208.206.76.47 | \-------------------------- 318-222-2638 x109 -----------------------------/
Hi, > -----Original Message----- > From: owner-pgsql-general@hub.org [mailto:owner-pgsql-general@hub.org]On > Behalf Of Brian > Sent: zondag 28 juni 1998 18:33 > To: pgsql-general@postgreSQL.org > Subject: [GENERAL] vacuuming > Is it common practice to setup a cron job to vacuum your databases > nightly? Do you just do something like: Every night I dump my database, and vacuum it afterwards. One thing to keep in mind that you don't want to do as root since pgsql doesn't like this. So you have to run cron as a db admin user. (give this user permission to use cron) regards Rob den Boer
On Tue, 30 Jun 1998, Rob den Boer wrote:
> Hi,
>
> > -----Original Message-----
> > From: owner-pgsql-general@hub.org [mailto:owner-pgsql-general@hub.org]On
> > Behalf Of Brian
> > Sent: zondag 28 juni 1998 18:33
> > To: pgsql-general@postgreSQL.org
> > Subject: [GENERAL] vacuuming
>
> > Is it common practice to setup a cron job to vacuum your databases
> > nightly? Do you just do something like:
>
>
> Every night I dump my database, and vacuum it afterwards.
Why would you dump every night? That sounds quite painful :(
On Tue, 30 Jun 1998, Rob den Boer wrote: > Hi, > > > -----Original Message----- > > From: owner-pgsql-general@hub.org [mailto:owner-pgsql-general@hub.org]On > > Behalf Of Brian > > Sent: zondag 28 juni 1998 18:33 > > To: pgsql-general@postgreSQL.org > > Subject: [GENERAL] vacuuming > > > Is it common practice to setup a cron job to vacuum your databases > > nightly? Do you just do something like: > > > Every night I dump my database, and vacuum it afterwards. > One thing to keep in mind that you don't want to do as root > since pgsql doesn't like this. > So you have to run cron as a db admin user. (give this user permission to > use cron) I have read "pgsql" doesn't like this, but I actually have several databases that have granted permissions to root. Will it give an error if you vacuum as root? > > regards > Rob den Boer > > /-------------------------- signal@shreve.net -----------------------------\ | Brian Feeny | USR TC Hubs | ShreveNet Inc. (318)222-2638 | | Network Administrator | Perl, Linux | Web hosting, online stores, | | ShreveNet Inc. | USR Pilot | Dial-Up 14.4-56k, ISDN & LANs | | 89 CRX DX w/MPFI, lots of |-=*:Quake:*=-| http://www.shreve.net/ | | mods/Homepage coming soon |LordSignal/SN| Quake server: 208.206.76.47 | \-------------------------- 318-222-2638 x109 -----------------------------/
> I have read "pgsql" doesn't like this, but I actually have several > databases that have granted permissions to root. Will it give an error if > you vacuum as root? When I installed pgsql I read that it was not advisable to use root, so I have a admin user with full privileges on the database, and I 'su' to that user. Also, I push the compressed dumpfile onto another server, and this is easier done as a normal user rather than root. Most machines (Linux) don't like root ftp or rcp even when you have host equivelance. regards Rob den Boer
> > Every night I dump my database, and vacuum it afterwards. > Why would you dump every night? That sounds quite painful :( Why should I want a backup when I never need it anyway??? ;-)) regards Rob
At 15:31 +0300 on 1/7/98, Rob den Boer wrote: > Why should I want a backup when I never need it anyway??? ;-)) Oh, be serious. The frequency of dumps depends on the application in hand. Not everybody wants to backup every day. We have an Oracle-based application (yuck), which presents data derived from the main university's database. The two databases are separate for security reasons. However, this database needs no backup at all. If it's ruined, it will be recreated by the end of the day anyway... The funny thing is that the Oracle admins around here insisted on backing it up, thus rendering it unavailable to the web for an hour a day - when it's totally unnecessary. The moral of the story is that one should think intelligently about one's backup policy. It's worth noticing in this context that backup and vacuum frequencies both relate to the same thing - how fast things change in the database. If there are updates all the time, inserts, deletes - you probably need to update frequently. You also need to vacuum frequently! Nevertheless, it's not a tight coupling. For example, if the things that change rapidly are mostly temporary tables, or utility tables (used for session management, for example), then you may still need to vacuum frequently, to reclaim the space, but you wouldn't necessarily need to backup as often. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
Hi Herouth, > > Why should I want a backup when I never need it anyway??? ;-)) > Oh, be serious. The frequency of dumps depends on the application in hand. My remark was not so serious intended, hence the smiley... ok, serious now; > Not everybody wants to backup every day. We have an Oracle-based > application (yuck), which presents data derived from the main university's Of course, it depends on the situation. In my situation it is obvious to do a backup and vacuum overnight, since this db is used in an office 9/5 enviroment. regards Rob den Boer