From: Mario Behring <mariobehring@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: How to reduce a database
Date: Fri, 29 Dec 2006 07:09:30 -0800 (PST)
Message-ID: <20061229150930.51974.qmail@web30006.mail.mud.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mariobehring@yahoo.com
Subject: Re: How to reduce a database
In-Reply-To: <20061229150930.51974.qmail@web30006.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox