pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gaetano Mendola <mendola@bigfoot.com>
To: kris pal <kristhegambler@yahoo.com>
Subject: Re: vacuum pg_attribute causes high load
Date: Sat, 28 Aug 2004 12:21:33 +0200
Message-ID: <41305CAD.3000501@bigfoot.com> (raw)
In-Reply-To: <20040828034735.11121.qmail@web52802.mail.yahoo.com>
References: <m34qmoi89l.fsf@wolfe.cbbrowne.com>
	<20040828034735.11121.qmail@web52802.mail.yahoo.com>

kris pal wrote:

>  
> Christopher,
>  
> Thanks for your detailed response. Now I get the idea why vacuum is 
> causing so much load. We should have vacuumed the database more often 
> but didn't do it as we are more used to managing Oracle databases. This 
> is the first Postgres instance that I am managing.
>  
> The datafile for production database ("bbs") is abt 6GB, though the 
> actual data is very small, abt 20MB. I used the dump (from pg_dump of 
> "bbs") of the production database  to import into a "test_database" - 
> its datafile size was < 20MB.
>  
> So instead of going through all these, am planning to have down time of 
> 15 mins and do the following:
>  
> 1)"pg_dump bmgs"
> 2)"drop the database  bmgs"  
> ** (this should essentially free up all the space occupied by 
> pg_attribute and its indexes - right ?? )
> 3)"recreate database bmgs"
> and
> 4)import the dump from step 1) into bmgs created in  step 3)
>  
>  
> Do you think there will be issues with this approach? The system catalog 
> will be built from scratch, and there won't be any data loss right. That 
> way I can reclaim the space and then run Vacuum more often.

Before to do it be sure that your pg_dump is non affected by not well ordered
objects, if yes you have to reorder the order creation ( manually ) in the dump,
some times happen.


Regards
Gaetano Mendola







view thread (8+ messages)  latest in thread

Message-ID: <41305CAD.3000501@bigfoot.com>
Permalink:  ../41305CAD.3000501@bigfoot.com/
Also on:    postgresql.org/message-id/41305CAD.3000501@bigfoot.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: mendola@bigfoot.com, kristhegambler@yahoo.com
  Subject: Re: vacuum pg_attribute causes high load
  In-Reply-To: <41305CAD.3000501@bigfoot.com>
  References: <m34qmoi89l.fsf@wolfe.cbbrowne.com> <20040828034735.11121.qmail@web52802.mail.yahoo.com> <41305CAD.3000501@bigfoot.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