pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: if <zeylie@gmail.com>
To: pgsql-admin@postgresql.org
Subject: vaccuming very large table problem
Date: Fri, 15 Feb 2008 13:56:09 +0300
Message-ID: <f37dba8b0802150256x4bfe638duc921ef686b623e04@mail.gmail.com> (raw)

Hello list!

We use postgresql as a backend to our email gateway, and keep al
emails for in database. Using postgres version 7.4.8 (yes, i know it's
old), and rather specific table schema (the application was desined
that way) -- all emails split into 2kb parts and fed up into
pg_largeobject. So, long story short, i now have a catch-22 situation
-- database using about 0.7TB and we are running out of space ;-)
I can delete some old stuff but i cannot run full vacuum to reclaim
disk space (i takes way more than full weekend) and i also cannot
dump/restore as there's no free space (2x database)

So, with this restrictions aplied, i figured out that i can somehow
zero out all old entries in pg_largeobject or even physically delete
these files, and rebuild all neccesary indexes.

What is the best way to do this?
IMO, dd'ing /dev/zero to this files will cause postgres to
reinitialize these empty blocks, and after this will still need to
vacuum full over 0.7TB, am i right?
And if i delete them, then start postmaster, there'll be lots of
complaining but will the latest data be saved?

How can i delete, for instance, first 70% of data reasonably fast?

P.S.  Please cc me, as i'm not subscribed yet.
Thanks in advance!

regards,
if



view thread (3+ messages)  latest in thread

Message-ID: <f37dba8b0802150256x4bfe638duc921ef686b623e04@mail.gmail.com>
Permalink:  ../f37dba8b0802150256x4bfe638duc921ef686b623e04@mail.gmail.com/
Also on:    postgresql.org/message-id/f37dba8b0802150256x4bfe638duc921ef686b623e04@mail.gmail.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 using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: zeylie@gmail.com
  Subject: Re: vaccuming very large table problem
  In-Reply-To: <f37dba8b0802150256x4bfe638duc921ef686b623e04@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