From: Travis Whitton <tinymountain@gmail.com>
To: Mario Behring <mariobehring@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to reduce a database
Date: Fri, 5 Jan 2007 13:29:39 -0500
Message-ID: <cf9b4f3e0701051029k49422636j77da164dbd735428@mail.gmail.com> (raw)
In-Reply-To: <20061229150930.51974.qmail@web30006.mail.mud.yahoo.com>
References: <20061229150930.51974.qmail@web30006.mail.mud.yahoo.com>
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
>
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: tinymountain@gmail.com, mariobehring@yahoo.com
Subject: Re: How to reduce a database
In-Reply-To: <cf9b4f3e0701051029k49422636j77da164dbd735428@mail.gmail.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