pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
drop database, vacuum full and disk space
3+ messages / 3 participants
[nested] [flat]

* drop database, vacuum full and disk space
@ 2008-02-28 17:17 Antonio Grassi <agrassic@gmail.com>
  2008-02-29 20:28 ` Re: drop database, vacuum full and disk space Chris Hoover <revoohc@gmail.com>
  2008-03-01 04:23 ` Re: drop database, vacuum full and disk space Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Antonio Grassi @ 2008-02-28 17:17 UTC (permalink / raw)
  To: pgsql-admin

Hi list. I've searched the archives and I've seen a lot of posts related to
database size, vacuuming, etc., but I can't totally understand this.

I've a small database of about 300Mb (when initially loaded), whose data is
almost completely replaced in the night (via various "drop table" and the
load of dumps of such tables from other postgresql installation).

Altough I've autovacuum activated, the size of this database is now about
3Gb. I've also full vacuumed the base by hand from time to time since the
base was created, but that didn't help either.

Yesterday I recreated the database with another name, thus having database A
with 3Gb and database B with 300Mb. Then I made a "drop database A", and ran
vacuumdb -af. But the space used by postgresql didn't go down. This makes me
think that vacuum full just releases pages associated to existent database
objects, is this correct?

So my question is: is there a way to reclaim those 3Gb for the operating
system, without deleting (rm) the data dir, doing initdb again and
recreating the database (to finish up with one database of about 300Mb and
300Mb of disk usage)?

Thanks in advance,
Antonio

PS: Yes, I've very few disk space, and no, recreating the database from
scratch (in the 'rm' sense) wouldn't be too easy (work place
particularities).

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: drop database, vacuum full and disk space
  2008-02-28 17:17 drop database, vacuum full and disk space Antonio Grassi <agrassic@gmail.com>
@ 2008-02-29 20:28 ` Chris Hoover <revoohc@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Chris Hoover @ 2008-02-29 20:28 UTC (permalink / raw)
  To: Antonio Grassi <agrassic@gmail.com>; +Cc: pgsql-admin

On Thu, Feb 28, 2008 at 12:17 PM, Antonio Grassi <agrassic@gmail.com> wrote:

> Hi list. I've searched the archives and I've seen a lot of posts related
> to database size, vacuuming, etc., but I can't totally understand this.
>
> I've a small database of about 300Mb (when initially loaded), whose data
> is almost completely replaced in the night (via various "drop table" and the
> load of dumps of such tables from other postgresql installation).
>
> Altough I've autovacuum activated, the size of this database is now about
> 3Gb. I've also full vacuumed the base by hand from time to time since the
> base was created, but that didn't help either.
>
> Yesterday I recreated the database with another name, thus having database
> A with 3Gb and database B with 300Mb. Then I made a "drop database A", and
> ran vacuumdb -af. But the space used by postgresql didn't go down. This
> makes me think that vacuum full just releases pages associated to existent
> database objects, is this correct?
>
> So my question is: is there a way to reclaim those 3Gb for the operating
> system, without deleting (rm) the data dir, doing initdb again and
> recreating the database (to finish up with one database of about 300Mb and
> 300Mb of disk usage)?
>
> Thanks in advance,
> Antonio
>
> PS: Yes, I've very few disk space, and no, recreating the database from
> scratch (in the 'rm' sense) wouldn't be too easy (work place
> particularities).
>

Try running a reindex (reindexdb -e -d <db>) and cluster all of the tables.
These are blocking transactions, but should release all of the bloat in your
db.

The cluster has to be run on a table by table basis and will cluster the
table on the primary key if no other index is provided.  See the
documentation for more information.

HTH,

Chris

-- 
Come see how to SAVE money on fuel, decrease harmful emissions, and even
make MONEY.  Visit http://colafuelguy.mybpi.com and join the revolution!

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: drop database, vacuum full and disk space
  2008-02-28 17:17 drop database, vacuum full and disk space Antonio Grassi <agrassic@gmail.com>
@ 2008-03-01 04:23 ` Scott Marlowe <scott.marlowe@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Scott Marlowe @ 2008-03-01 04:23 UTC (permalink / raw)
  To: Antonio Grassi <agrassic@gmail.com>; +Cc: pgsql-admin

On Thu, Feb 28, 2008 at 11:17 AM, Antonio Grassi <agrassic@gmail.com> wrote:
> Hi list. I've searched the archives and I've seen a lot of posts related to
> database size, vacuuming, etc., but I can't totally understand this.
>
> I've a small database of about 300Mb (when initially loaded), whose data is
> almost completely replaced in the night (via various "drop table" and the
> load of dumps of such tables from other postgresql installation).
>
> Altough I've autovacuum activated, the size of this database is now about
> 3Gb. I've also full vacuumed the base by hand from time to time since the
> base was created, but that didn't help either.
>
> Yesterday I recreated the database with another name, thus having database A
> with 3Gb and database B with 300Mb. Then I made a "drop database A", and ran
> vacuumdb -af. But the space used by postgresql didn't go down. This makes me
> think that vacuum full just releases pages associated to existent database
> objects, is this correct?

If you drop all but the template0/1 databases, I'm guessing you'll
still see 3G used, because it sounds like you've got bloat in your
system tables, like pg_class etc.

Just guessing.  You can use oid2name and du -sh to get an idea how
much each db dir is using.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2008-03-01 04:23 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-28 17:17 drop database, vacuum full and disk space Antonio Grassi <agrassic@gmail.com>
2008-02-29 20:28 ` Chris Hoover <revoohc@gmail.com>
2008-03-01 04:23 ` Scott Marlowe <scott.marlowe@gmail.com>

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