Skip site navigation (1) Skip section navigation (2)

Re: auto vacuum

From: Steve Singer <ssinger_pg(at)sympatico(dot)ca>
To: Duane Hebert <duane(dot)hebert(at)group-upc(dot)com>
Cc: torontopug(at)postgresql(dot)org
Subject: Re: auto vacuum
Date: 2009-01-30 23:19:30
Message-ID: BLU0-SMTP63FF9F59EE969E1F6BE88EACC60@phx.gbl (view raw, whole thread or download thread mbox)
Lists: torontopug
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 

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:

In response to

torontopug by date

Next:From: George KaoDate: 2009-02-06 22:07:51
Subject: temporarily offset foreign key constraints
Previous:From: Duane HebertDate: 2009-01-30 15:10:40
Subject: auto vacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group