Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 061749FA33C for ; Fri, 5 Jan 2007 14:29:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 26124-08 for ; Fri, 5 Jan 2007 14:29:42 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.228]) by postgresql.org (Postfix) with ESMTP id 1C9989FA182 for ; Fri, 5 Jan 2007 14:29:42 -0400 (AST) Received: by wr-out-0506.google.com with SMTP id i30so1399133wra for ; Fri, 05 Jan 2007 10:29:41 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=KdSgX8Ozn0jexmcz6MxNxGZDClVIdkiQXENEtDbAvArBospilgUHhdaGgikXaKVn2RhdnELJB8P+ZL5mDhadCJMRjFzv5xCQHFkNOH67GpP5Pb+TM1ZHS6thhia7P8ILqqunYF9Uh7Oy5hZkxCP5/mQozAh0XZF67ZEsJqHX0DI= Received: by 10.78.97.7 with SMTP id u7mr3154527hub.1168021779660; Fri, 05 Jan 2007 10:29:39 -0800 (PST) Received: by 10.78.105.20 with HTTP; Fri, 5 Jan 2007 10:29:38 -0800 (PST) Message-ID: Date: Fri, 5 Jan 2007 13:29:39 -0500 From: "Travis Whitton" To: "Mario Behring" Subject: Re: How to reduce a database Cc: pgsql-sql@postgresql.org In-Reply-To: <20061229150930.51974.qmail@web30006.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_20318_8347514.1168021779013" References: <20061229150930.51974.qmail@web30006.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/11 X-Sequence-Number: 27163 ------=_Part_20318_8347514.1168021779013 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I don't remember where I read it, but I saw something online a while back comparing vacuum stragies vs dumping with pg_dump and then reloading. The pg_dump and restore ended up compacting the database significantly more. I don't know if that still applies with 8.2, but it might be worth a try. I can find the article if you're interested. Travis On 12/29/06, Mario Behring wrote: > > Hi list, > > I have an openNMS server that uses a Postgres database. For those who are > not familiar, openNMS is an open source network management product. > > 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. > > Can you guys help me with some command line examples? > > There is this table, called EVENTS, that have the following structure: > > eventid | integer | not null > eventuei | character varying(256) | not null > nodeid | integer | > eventtime | timestamp without time zone | not null > eventhost | character varying(256) | > eventsource | character varying(128) | not null > ipaddr | character varying(16) | > eventdpname | character varying(12) | not null > eventsnmphost | character varying(256) | > serviceid | integer | > eventsnmp | character varying(256) | > eventparms | text | > eventcreatetime | timestamp without time zone | not null > eventdescr | character varying(4000) | > eventloggroup | character varying(32) | > eventlogmsg | character varying(256) | > eventseverity | integer | not null > eventpathoutage | character varying(1024) | > eventcorrelation | character varying(1024) | > eventsuppressedcount | integer | > eventoperinstruct | character varying(1024) | > eventautoaction | character varying(256) | > eventoperaction | character varying(256) | > eventoperactionmenutext | character varying(64) | > eventnotification | character varying(128) | > eventtticket | character varying(128) | > eventtticketstate | integer | > eventforward | character varying(256) | > eventmouseovertext | character varying(64) | > eventlog | character(1) | not null > eventdisplay | character(1) | not null > eventackuser | character varying(256) | > eventacktime | timestamp without time zone | > > I was thinking about using a DELETE FROM EVENTS WHERE eventtime = point in time>......but I am kind of worried on what this could cause on > other tables, if there is some relations between them or something..... > > Here is the vacuumd-configuration.xml file: > > > DELETE FROM node WHERE > node.nodeType = 'D'; > DELETE FROM > ipInterface WHERE ipInterface.isManaged = 'D'; > DELETE FROM if Services > WHERE ifServices.status = 'D'; > 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'; > > > Any help is appreciated. > > Thank you. > > Mario > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > ------=_Part_20318_8347514.1168021779013 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I don't remember where I read it, but I saw something online a while back comparing vacuum stragies vs dumping with pg_dump and then reloading. The pg_dump and restore ended up compacting the database significantly more. I don't know if that still applies with 8.2, but it might be worth a try. I can find the article if you're interested.

Travis

On 12/29/06, Mario Behring < mariobehring@yahoo.com> wrote:
Hi list,

I have an openNMS server that uses a Postgres database. For those who are not familiar, openNMS is an open source network management product.

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.

Can you guys help me with some command line examples?

There is this table, called EVENTS, that have the following structure:

 eventid                 | integer                     | not null
 eventuei                | character varying(256)      | not null
 nodeid                  | integer                     |
 eventtime               | timestamp without time zone | not null
 eventhost               | character varying(256)      |
 eventsource             | character varying(128)      | not null
 ipaddr                  | character varying(16)       |
 eventdpname             | character varying(12)       | not null
 eventsnmphost           | character varying(256)      |
 serviceid               | integer                     |
 eventsnmp               | character varying(256)      |
 eventparms              | text                        |
 eventcreatetime         | timestamp without time zone | not null
 eventdescr              | character varying(4000)     |
 eventloggroup           | character varying(32)       |
 eventlogmsg             | character varying(256)      |
 eventseverity           | integer                     | not null
 eventpathoutage         | character varying(1024)     |
 eventcorrelation        | character varying(1024)     |
 eventsuppressedcount    | integer                     |
 eventoperinstruct       | character varying(1024)     |
 eventautoaction         | character varying(256)      |
 eventoperaction         | character varying(256)      |
 eventoperactionmenutext | character varying(64)       |
 eventnotification       | character varying(128)      |
 eventtticket            | character varying(128)      |
 eventtticketstate       | integer                     |
 eventforward            | character varying(256)      |
 eventmouseovertext      | character varying(64)       |
 eventlog                | character(1)                | not null
 eventdisplay            | character(1)                | not null
 eventackuser            | character varying(256)      |
 eventacktime            | timestamp without time zone |

I was thinking about using a DELETE FROM EVENTS WHERE  eventtime = <some point in time>.... ..but I am kind of worried on what this could cause on other tables, if there is some relations between them or something.....

Here is the vacuumd-configuration.xml file:

<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>

Any help is appreciated.

Thank you.

Mario

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

------=_Part_20318_8347514.1168021779013--