Re: Rather large Postgres directory

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