Обсуждение: Rather large Postgres directory

Hi,

Unfortuneatly the partition that has the Postgres DB has filled up beause of files in the Postgres directory. this partition is 85GB

I tried using Table space to point it at a new partition so I did

exampledb=# CREATE TABLESPACE fastspace LOCATION '/var/example/postgres';

which didn't work so I did

example=# SET default_tablespace = fastspace;

but that still didn't work

I also tried VACUUM FULL; last night but this hasn't freed up any space, the offending files are under /var/databases/8.1/main/base/2832253#

and the files inside the directory look like

 2833195  2836410  2912054  2937747    3463991
2832506 

and there a awful lot of them (85G to be precise) This database server serves our legacy web infrstructure to put things into perspective our current production database (Mysql) is 4.7G so I can't imagine our old database would be 85G!, I presume it just requires a bit of a clean up but im not sure where to start apart from the VACUUM,

As you probaly can tell I haven't got much experiene with Postgres so any help would be great

Thanks,
Will

On Wed, 2009-04-29 at 13:36 +0100, william pink wrote:
> Hi,
>
> Unfortuneatly the partition that has the Postgres DB has filled up
> beause of files in the Postgres directory. this partition is 85GB
>
> I tried using Table space to point it at a new partition so I did
>
> exampledb=# CREATE TABLESPACE fastspace LOCATION
> '/var/example/postgres';
>
> which didn't work so I did
>
> example=# SET default_tablespace = fastspace;
>
> but that still didn't work

You have to move the relations to the table space with alter table or
alter index etc...

>
> I also tried VACUUM FULL; last night but this hasn't freed up any
> space, the offending files are
> under /var/databases/8.1/main/base/2832253#
>
> and the files inside the directory look like
>
>  2833195  2836410  2912054  2937747    3463991
> 2832506

At this point you may be better off just doing a backup and restore and
then figure out why your maintenance routines are failing.

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997

On Wed, Apr 29, 2009 at 4:00 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, Apr 29, 2009 at 9:59 AM, Joshua D. Drake <jd@commandprompt.com> wrote:
>
>> At this point you may be better off just doing a backup and restore and
>> then figure out why your maintenance routines are failing.
>
> OP said he was running 8.1.  I think autovacuum was turned on by
> default on 8.1, so first step after dump restore is to turn on
> autovacuum.

'was turned OFF by default in 8.1'

On Wed, Apr 29, 2009 at 9:59 AM, Joshua D. Drake <jd@commandprompt.com> wrote:

> At this point you may be better off just doing a backup and restore and
> then figure out why your maintenance routines are failing.

OP said he was running 8.1.  I think autovacuum was turned on by
default on 8.1, so first step after dump restore is to turn on
autovacuum.

On Mittwoch 29 April 2009 william pink wrote:
> Hi,
>
> Unfortuneatly the partition that has the Postgres DB has filled up
> beause of files in the Postgres directory. this partition is 85GB
>
> I tried using Table space to point it at a new partition so I did
>
> exampledb=# CREATE TABLESPACE fastspace LOCATION
> '/var/example/postgres';
>
> which didn't work so I did
>
> example=# SET default_tablespace = fastspace;
>
> but that still didn't work

What do you mean? Did you expect postgres to move your existing db to
the new tablespace? It won't to that of course!

> I also tried VACUUM FULL; last night but this hasn't freed up any
> space

Possibly because it did not run as you are out of space. For VACUUM, you
need spare space.

> and there a awful lot of them (85G to be precise) This database
> server serves our legacy web infrstructure to put things into
> perspective our current production database (Mysql) is 4.7G so I
> can't imagine our old database would be 85G!, I presume it just
> requires a bit of a clean up but im not sure where to start apart
> from the VACUUM,
>
> As you probaly can tell I haven't got much experiene with Postgres so
> any help would be great

I think something like this should help:
CREATE TABLE a2 TABLESPACE fastspace AS
    select * FROM a1;
This will copy all data from existing table a1 into a2, where a2 is in
the new tablespace. Afterwards, drop table a1, and reverse the command
to copy back all data. As this kills all your constraints etc (see
http://www.postgresql.org/docs/8.3/interactive/sql-cluster.html , the
example is there also ), it might be easier to pg_dump ; drop database ;
and then restore.

mfg zmi
--
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4