Thread: running vacuum in scripts
This seems like it should be a frequently asked question, but I am having trouble finding the answer. I am in the process of switching to using Postgres, and realize that I need to run vacuum analyze regularly on the tables. This is on a Unix system so cron is the obvious choice. The problem is I don't want to put the user name and password in the script. As far as I can tell vacuum must be run by the table or database owner. It wouldn't be as bad to have the password in the script if it was a limited permissions user. Any suggestions on the best methods? Thanks, Belinda
You could use the system wide crontab, which is configured via /etc/crontab, and there you can specify what user should execute the command, without needing passwords. The system wide crontab is executed as root and it will su to the user you specify. For vacuuming, you could try to use the autovacuum daemon, it is included in the contrib part of postgres. If you installed from source, you will likely need to separately install autovacuum, if you installed a prepackaged postgres, chances are that you already have the pg_autovacuum executable installed. It is fairly easy to set up. HTH, Csaba. On Mon, 2005-09-19 at 20:27, Belinda M. Giardine wrote: > This seems like it should be a frequently asked question, but I am having > trouble finding the answer. I am in the process of switching to using > Postgres, and realize that I need to run vacuum analyze regularly on the > tables. This is on a Unix system so cron is the obvious choice. The > problem is I don't want to put the user name and password in the script. > As far as I can tell vacuum must be run by the table or database owner. > It wouldn't be as bad to have the password in the script if it was a > limited permissions user. Any suggestions on the best methods? > > Thanks, > Belinda > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
On Sep 19, 2005, at 2:27 PM, Belinda M. Giardine wrote: > As far as I can tell vacuum must be run by the table or database > owner. > It wouldn't be as bad to have the password in the script if it was a > limited permissions user. Any suggestions on the best methods? Setup a .pgpass file so you don't need to embed the password in the script: http://www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
On Tue, 20 Sep 2005, Csaba Nagy wrote: > You could use the system wide crontab, which is configured via > /etc/crontab, and there you can specify what user should execute the > command, without needing passwords. The system wide crontab is executed > as root and it will su to the user you specify. > > For vacuuming, you could try to use the autovacuum daemon, it is > included in the contrib part of postgres. If you installed from source, > you will likely need to separately install autovacuum, if you installed > a prepackaged postgres, chances are that you already have the > pg_autovacuum executable installed. It is fairly easy to set up. > > HTH, > Csaba. > > Thanks. I didn't find autovacuum anywhere in our install. It was done from source so I sent a request to the sysadmin. It does sound like what I was looking for. Belinda
On Tue, 20 Sep 2005, John DeSoi wrote: > > On Sep 19, 2005, at 2:27 PM, Belinda M. Giardine wrote: > > > As far as I can tell vacuum must be run by the table or database > > owner. > > It wouldn't be as bad to have the password in the script if it was a > > limited permissions user. Any suggestions on the best methods? > > Setup a .pgpass file so you don't need to embed the password in the > script: > > http://www.postgresql.org/docs/8.0/interactive/libpq-pgpass.html > > > John DeSoi, Ph.D. > http://pgedit.com/ > Power Tools for PostgreSQL > Thanks, this also could be useful. It sounds like I won't need it for this if I use autovacuum, but could come in handy later. Belinda
You can set up pg_hba.conf so that only certain Unix users that have access to the local Unix PostgreSQL socket can access the database without a password (every other process uses a TCP/IP connection); then move the socket location to other than /tmp and restrict its access w/ Unix controls. Details are in the PostgreSQL documentation, and it works fine. -- Dean On 2005-09-19 11:27, Belinda M. Giardine wrote: >This seems like it should be a frequently asked question, but I am having >trouble finding the answer. I am in the process of switching to using >Postgres, and realize that I need to run vacuum analyze regularly on the >tables. This is on a Unix system so cron is the obvious choice. The >problem is I don't want to put the user name and password in the script. >As far as I can tell vacuum must be run by the table or database owner. >It wouldn't be as bad to have the password in the script if it was a >limited permissions user. Any suggestions on the best methods? > >Thanks, >Belinda > > >---------------------------(end of broadcast)--------------------------- >TIP 6: explain analyze is your friend > >
On Tue, Sep 20, 2005 at 11:04:44AM -0400, Belinda M. Giardine wrote: > > For vacuuming, you could try to use the autovacuum daemon, it is > > included in the contrib part of postgres. If you installed from source, > > you will likely need to separately install autovacuum, if you installed > > a prepackaged postgres, chances are that you already have the > > pg_autovacuum executable installed. It is fairly easy to set up. > > Thanks. I didn't find autovacuum anywhere in our install. It was done > from source so I sent a request to the sysadmin. It does sound like what > I was looking for. http://cvs.distributed.net/viewcvs.cgi/stats-sql/tools/ has some scripts you might find useful for running autovacuum. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, 20 Sep 2005, Jim C. Nasby wrote: > On Tue, Sep 20, 2005 at 11:04:44AM -0400, Belinda M. Giardine wrote: > > > For vacuuming, you could try to use the autovacuum daemon, it is > > > included in the contrib part of postgres. If you installed from source, > > > you will likely need to separately install autovacuum, if you installed > > > a prepackaged postgres, chances are that you already have the > > > pg_autovacuum executable installed. It is fairly easy to set up. > > > > Thanks. I didn't find autovacuum anywhere in our install. It was done > > from source so I sent a request to the sysadmin. It does sound like what > > I was looking for. > > http://cvs.distributed.net/viewcvs.cgi/stats-sql/tools/ has some scripts > you might find useful for running autovacuum. > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > Thanks, yes these scripts do look useful. Belinda