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