# Postgres vacuum on cron.. no use pg_autovacuum (one concern)

## CarlUman

While installing and reading about postgresql I found that I should run vacuum using cron.  More research uncovered pg_autovacuum.  More digging on my system and I found that /etc/init.d/pg_autovacuum was part of the package with postgresql.  Not much info out there on this nice thing.  Lets get it setup and running.

/etc/conf.d/pg_autovacuum

```

PGUSER=postgres

# Set the location where you want the log stored

PG_AUTOVACUUM_LOG=/home/postgres/data/pg_autovacuum.log

```

Now there are already some things set in that config but I can't seem to find any documentation on them

```
/etc/init.d/pg_autovacuum start
```

First it asks for a password.  This is the password for the user set in /etc/conf.d/pg_autovacuum.

Got an error that said that I need to change some things in my postgresql.conf

```
stats_start_collector = true

stats_row_level = true

```

Try again

```
/etc/init.d/postgresql restart

/etc/init.d/pg_autovacuum start
```

Error again and says to check the pg_autovacuum.log so I do...

 *Quote:*   

> 
> 
> [2005-07-19 21:39:49 Local time zone must be set--see zic manual page] ERROR:   Failed connection to database template1 with error: fe_sendauth: no password supplied
> 
> [2005-07-19 21:39:49 Local time zone must be set--see zic manual page] ERROR:   Failed connection to database template1 with error: fe_sendauth: no password supplied.
> ...

 

Well, that's nice!  I entered the password what can be missing.

A little digging and I found /usr/share/doc/postgresql-8.0.1-r4/contrib/README.pg_autovacuum In there I found that there are some parms one of which is -P.  Look in /etc/init.d/pg_autovacuum found the command...

```
 start-stop-daemon -o --chuid $PGUSER --start --quiet --exec /usr/bin/pg_autovacuum -- -D -v $VACUUM_BASE -V $VACUUM_SCALE -s $SLEEP_BASE -S  $SLEEP _SCALE -L $PG_AUTOVACUUM_LOG
```

Wouldn't know, no -P so I add it

```
 start-stop-daemon -o --chuid $PGUSER --start --quiet --exec /usr/bin/pg_autovacuum -- -D -v $VACUUM_BASE -V $VACUUM_SCALE -s $SLEEP_BASE -S  $SLEEP _SCALE -L $PG_AUTOVACUUM_LOG -P $PGUSER_PASSWORD
```

Now also need to add that to that parm to the /etc/conf.d/pg_autovacuum

```
PGUSER_PASSWORD=xxxx
```

start again

```
/etc/init.d/pg_autovacuum start
```

GOT IT!

Now for my question/concern.  The /etc/conf.d/pg_autovacuum has the postmaster (postgres) password in it.  Is there any to avoid this?  I'm guessing if I turned the option (can't remember off hand what it is) to not require passwords for local users for postgresql that I would not need the password (the -P option).

Hopefully this will get some good dialog going and help others out as well since info on this seems to be scarce.

Thanks  :Very Happy: 

Carl

----------

## commonloon

Normally (by default) pg_hba.conf has your localhost as a 'TRUST' You should be able to connect to it w/o a password ig PGHOST=localhost -- try it w/o a password. alternatively, I imagine you can chmod the pg_autovacuum conf file so that only root or postgres can access it -- if they can become root or postgres then obviously they can at least get a hold of the raw data files...

Hope this helps....

----------

## limn

Perhaps something like

```
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

local   all         postgres                          ident sameuser
```

in the pg_hba.conf would be preferable to putting the password in /etc/init.d/pg_autovacuum.

----------

