Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id D20169FA589
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 29 Dec 2006 13:15:35 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 17761-02-2 for <pgsql-sql-postgresql.org@postgresql.org>;
	Fri, 29 Dec 2006 13:14:42 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mx1.ipf.is (mack.ipf.is [85.197.192.164])
	by postgresql.org (Postfix) with ESMTP id CFD119FA508
	for <pgsql-sql@postgresql.org>; Fri, 29 Dec 2006 13:14:59 -0400 (AST)
Received: (qmail 30851 invoked by uid 1010); 29 Dec 2006 17:11:07 +0000
Received: from 88.149.106.19 by mack (envelope-from <gnari@hive.is>,
	uid 1008) with qmail-scanner-1.25-st-qms
	(clamdscan: 0.88.4/1639. spamassassin: 3.1.4. perlscan: 1.25-st-qms.
	Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):.
	Processed in 2.825519 secs); 29 Dec 2006 17:11:07 -0000
X-Antivirus-HIVE-Mail-From: gnari@hive.is via mack
X-Antivirus-HIVE: 1.25-st-qms (Clear:RC:1(88.149.106.19):SA:0(-2.4/4.5):.
	Processed in 2.825519 secs Process 30829)
Received: from dsl-149-106-19.hive.is (HELO ?192.168.1.33?) (88.149.106.19)
	by mx1.ipf.is with SMTP; 29 Dec 2006 17:11:03 +0000
Subject: Re: How to reduce a database
From: Ragnar <gnari@hive.is>
To: Mario Behring <mariobehring@yahoo.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20061229150930.51974.qmail@web30006.mail.mud.yahoo.com>
References: <20061229150930.51974.qmail@web30006.mail.mud.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1
Date: Fri, 29 Dec 2006 17:18:41 +0000
Message-Id: <1167412721.6369.371.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.8.1 
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200612/188
X-Sequence-Number: 27144

On fös, 2006-12-29 at 07:09 -0800, Mario Behring wrote:

> Anyway, the openNMS database is very large now, more than 25GB
> (considering all tables) and I am starting to have disk space issues.
> The openNMS product has a vacuumdb procedure that runs every 24 hours
> and reads a vacuumd-configuration.xml file for parameters on what to
> do. 

> The problem is that this process is not reducing the database size.
> What I need to do is to delete some records based on timestamp fileds
> or something like that. I don't know how to do it though.

before you start deleting random rows in a database you are not
too familiar with, let us start by trying to determine your
actual problem.

you should issue a manual VACUUM VERBOSE, and look at the
output of that first. possibly you will find that the database 
is suffering from bloat due to too small fsm settings.

if that is the case, a VACUUM FULL might be indicated, but
note that it will take exclusive locks, so you should
schedule that for some time where you can afford downtime.

if this is not enough, then you can take a look at deleting
rows, but you would probably get better advice on that
from the OpenNMS community. I assume they have
mailinglists or forums. 


> <VacuumdConfiguration period="86400000" >
>     <statement><!-- this deletes all the nodes that have been marked
> as deleted - it relies on cascading deletes --> DELETE FROM node
> WHERE node.nodeType = 'D'; </statement>
>     <statement><!-- this deletes all the interfaces that have been
> marked as deleted - it relies on cascading deletes --> DELETE FROM
> ipInterface WHERE ipInterface.isManaged = 'D'; </statement>
>     <statement><!-- this deletes all the services that have been
> marked as deleted - it relies on cascading deletes --> DELETE FROM if
> Services WHERE ifServices.status = 'D'; </statement>
>     <statement><!-- this deletes any events that are not associated
> with outages - Thanks to Chris Fedde for this --> DELETE FROM even
> ts WHERE NOT EXISTS (SELECT svclosteventid FROM outages WHERE
> svclosteventid = events.eventid UNION SELECT svcregainedeventid FROM
> out      ages WHERE svcregainedeventid = events.eventid UNION SELECT
> eventid FROM notifications WHERE eventid = events.eventid) AND
> eventtime &      lt; now() - interval '6 weeks'; </statement>
> </VacuumdConfiguration>

these all seem to be deletes (no VACUUMs), so you might want
to check if vacuumd is actually running to be 100% sure.

also, what version postgres is this ?

gnari

>