Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 70E879FA0B7 for ; Fri, 29 Dec 2006 11:09:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 66019-02 for ; Fri, 29 Dec 2006 11:09:39 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from web30006.mail.mud.yahoo.com (web30006.mail.mud.yahoo.com [209.191.69.23]) by postgresql.org (Postfix) with SMTP id 271BD9F9F08 for ; Fri, 29 Dec 2006 11:09:40 -0400 (AST) Received: (qmail 51976 invoked by uid 60001); 29 Dec 2006 15:09:30 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type; b=0OGQCVPYSx45ySV6pV4uD1hPiyefK5J37PqwDiPiNdb/mbg1zKbZtw3nloFHg2YatcDFn2pB6hBHBw7AWaDAG9CYOoDJu3jE5p1ovoolPX8LzPHpet8CwrGa4CkT06dag3Ydo+psET0POTqc5tXP976LHqg7GylvO3qyxtVSe9k= ; Message-ID: <20061229150930.51974.qmail@web30006.mail.mud.yahoo.com> Received: from [200.161.5.135] by web30006.mail.mud.yahoo.com via HTTP; Fri, 29 Dec 2006 07:09:30 PST Date: Fri, 29 Dec 2006 07:09:30 -0800 (PST) From: Mario Behring Subject: How to reduce a database To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1015859022-1167404970=:49309" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/187 X-Sequence-Number: 27143 --0-1015859022-1167404970=:49309 Content-Type: text/plain; charset=ascii Content-Transfer-Encoding: quoted-printable Hi list,=0A=0AI have an openNMS server that uses a Postgres database. For t= hose who are not familiar, openNMS is an open source network management pro= duct.=0A=0AAnyway, the openNMS database is very large now, more than 25GB (= considering all tables) and I am starting to have disk space issues. The op= enNMS product has a vacuumdb procedure that runs every 24 hours and reads a= vacuumd-configuration.xml file for parameters on what to do. =0A=0AThe pro= blem 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 t= hat. I don't know how to do it though.=0A=0ACan you guys help me with some = command line examples?=0A=0AThere is this table, called EVENTS, that have t= he following structure:=0A=0A eventid | integer = | not null=0A eventuei | character varying(256) = | not null=0A nodeid | integer |=0A = eventtime | timestamp without time zone | not null=0A eventho= st | character varying(256) |=0A eventsource = | character varying(128) | not null=0A ipaddr | char= acter varying(16) |=0A eventdpname | character varying(12= ) | not null=0A eventsnmphost | character varying(256) = |=0A serviceid | integer |=0A eventsnmp = | character varying(256) |=0A eventparms | = text |=0A eventcreatetime | timestamp withou= t time zone | not null=0A eventdescr | character varying(4000)= |=0A eventloggroup | character varying(32) |=0A eventl= ogmsg | character varying(256) |=0A eventseverity = | integer | not null=0A eventpathoutage | cha= racter varying(1024) |=0A eventcorrelation | character varying(1= 024) |=0A eventsuppressedcount | integer |=0A ev= entoperinstruct | character varying(1024) |=0A eventautoaction = | character varying(256) |=0A eventoperaction | characte= r varying(256) |=0A eventoperactionmenutext | character varying(64) = |=0A eventnotification | character varying(128) |=0A eventtt= icket | character varying(128) |=0A eventtticketstate = | integer |=0A eventforward | character var= ying(256) |=0A eventmouseovertext | character varying(64) |= =0A eventlog | character(1) | not null=0A eve= ntdisplay | character(1) | not null=0A eventackus= er | character varying(256) |=0A eventacktime | = timestamp without time zone |=0A=0AI was thinking about using a DELETE FROM= EVENTS WHERE eventtime =3D ......but I am kind of wor= ried on what this could cause on other tables, if there is some relations b= etween them or something.....=0A=0AHere is the vacuumd-configuration.xml fi= le:=0A=0A=0A DELETE FROM node WHERE node.nodeType =3D 'D'; <= /statement>=0A DELETE FROM = ipInterface WHERE ipInterface.isManaged =3D 'D'; =0A DELETE FROM if Services WHERE if= Services.status =3D 'D'; =0A DELETE FROM even ts WHERE NOT EXISTS (SELECT svclosteventid FR= OM outages WHERE svclosteventid =3D events.eventid UNION SELECT svcregained= eventid FROM out ages WHERE svcregainedeventid =3D events.eventid UNIO= N SELECT eventid FROM notifications WHERE eventid =3D events.eventid) AND e= venttime & lt; now() - interval '6 weeks'; =0A=0A=0AAny help is appreciated.=0A=0AThank you.=0A=0AMario=0A=0A= =0A=0A__________________________________________________=0ADo You Yahoo!?= =0ATired of spam? Yahoo! Mail has the best spam protection around =0Ahttp:= //mail.yahoo.com --0-1015859022-1167404970=:49309 Content-Type: text/html; charset=ascii Content-Transfer-Encoding: quoted-printable
Hi list,

I have an openNMS server that uses a Postgres dat= abase. For those who are not familiar, openNMS is an open source network ma= nagement product.

Anyway, the openNMS database is very large now, mo= re 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 hou= rs 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           &= nbsp;         | not null
 eventuei &nbs= p;            &= nbsp; | character varying(256)      | not null
 nodeid = ;            &n= bsp;    | integer       &= nbsp;           &nbs= p; |
 eventtime &n= bsp;            = ; | timestamp without time zone | not null
 eventhost     &= nbsp;         | character varying(2= 56)      |
 eventsource      =        | character varying(128)  &n= bsp;   | not null
 ipaddr    =             &nb= sp; | character varying(16)       | eventdpname =             | charac= ter varying(12)       | not null
 eventsnmphost &= nbsp;         | character varying(2= 56)      |
 serviceid   &nb= sp;           | integer&n= bsp;            = ;        |
 eventsnmp    &nbs= p;          | character varyin= g(256)      |
 eventparms     &nbs= p;        | text    =             &nb= sp;       |
 eventcreatetime    =      | timestamp without time zone | not null eventdescr &= nbsp;            | c= haracter varying(4000)     |
 eventloggroup   &nb= sp;       | character varying(32)  =      |
 eventlogmsg       &nb= sp;     | character varying(256)      |
 eventseverity &nbs= p;         | integer  &nb= sp;            =       | not null
 eventpathoutage    &n= bsp;    | character varying(1024)     |<= /span>
 eventcor= relation        | character varying(1024= )     |
 eventsup= pressedcount    | integer      = ;            &n= bsp;  |
&= nbsp;eventoperinstruct       | character vary= ing(1024)     |
 eventautoaction     &n= bsp;   | character varying(256)      |
 eventoper= action         | character varying(256)      |
 eventoperactionmenutext | character v= arying(64)       |
 eventnotification   = ;    | character varying(128)      = |
 eventt= ticket            | = character varying(128)      |
 eventtticketstate  = ;     | integer           &= nbsp;         |
 eventforward  &nb= sp;         | character varying(256= )      |
 eventmouseovertext      = | character varying(64)       |
 eventlog  &= nbsp;           &nbs= p; | character(1)          &n= bsp;     | not null
 eventdisplay     &= nbsp;      | character(1)    &= nbsp;           | not nul= l
 eventa= ckuser            | = character varying(256)      |
 eventacktime  &nbs= p;         | timestamp without time= zone |

I was thinking about using a DELETE FROM EVENTS WHERE  eventtime =3D <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-config= uration.xml file:

<VacuumdConfiguration period=3D"86400000" ><= br>    <statement><!-- this deletes all the nodes t= hat have been marked as deleted - it relies on cascading deletes --> DEL= ETE FROM node       WHERE node.nodeType =3D '= D'; </statement>
    <statement><!-- this = deletes all the interfaces that have been marked as deleted - it relies on = cascading deletes --> DELETE FROM       ip= Interface WHERE ipInterface.isManaged =3D 'D'; </statement>
    &l= t;statement><!-- this deletes all the services that have been marked = as deleted - it relies on cascading deletes --> DELETE FROM if &nbs= p;    Services WHERE ifServices.status =3D 'D'; </stateme= nt>
    <statement><!-- this deletes any even= ts that are not associated with outages - Thanks to Chris Fedde for this --= > DELETE FROM even      ts WHERE NOT EXISTS (SE= LECT svclosteventid FROM outages WHERE svclosteventid =3D events.eventid UN= ION SELECT svcregainedeventid FROM out      ages W= HERE svcregainedeventid =3D events.eventid UNION SELECT eventid FROM notifi= cations WHERE eventid =3D events.eventid) AND eventtime &  &n= bsp;   lt; now() - interval '6 weeks'; </statement>
<= /VacuumdConfiguration>

Any help is appreciated.

Thank you.

Mario

_______________________= ___________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mai= l has the best spam protection around
http://mail.yahoo.com --0-1015859022-1167404970=:49309--