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.
Show quoted text
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:
Show quoted text
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