Re: auto vacuum

I have a db that stores real time data for a scada system. I've noticed
that when it uses postgres it fills the hard drive with 1GB files
in the data folder. I've tried to set the auto vacuum parameters but it
looks like auto vacuum won't work when the tables
are being accessed. These systems are continuous process control systems
so I can't stop them at any particular time.

I see that I can run a full vacuum manually and that more or less works with
things running so I thought that I would
schedule that. I wanted to write a simple script or batch file but it
requests a password and it doesn't seem to take one
from the command line.

This doesn't seem like such an unusual problem so I was wondering how people
generally deal with this. I would prefer
not to switch to MySql.

On Fri, 30 Jan 2009, Duane Hebert wrote:

> I have a db that stores real time data for a scada system. I've noticed that
> when it uses postgres it fills the hard drive with 1GB files
> in the data folder. I've tried to set the auto vacuum parameters but it
> looks like auto vacuum won't work when the tables
> are being accessed. These systems are continuous process control systems so
> I can't stop them at any particular time.

A few questions

1. Are these continious queries part of one long running transaction? Or
many smaller transactions (that end)

2. What version of Pg

3. What are your settings for auto vacuum in your postgresq.conf file
* autovacuum (should be true)
* autovacuum_threshold
* autovacuum_naptime

Auto vacuum will only vacuum a table if it thinks it has a reason too.

If you do want to run vacuum from a script you can

Use a .pgpass file
see http://www.postgresql.org/docs/8.3/interactive/libpq-pgpass.html

Change your pg_hba.conf file to 'trust' connections from the local machine.

>
> I see that I can run a full vacuum manually and that more or less works with
> things running so I thought that I would
> schedule that. I wanted to write a simple script or batch file but it
> requests a password and it doesn't seem to take one
> from the command line.
>
> This doesn't seem like such an unusual problem so I was wondering how people
> generally deal with this. I would prefer
> not to switch to MySql.
>
>
>
> --
> Sent via torontopug mailing list (torontopug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/torontopug
>