Thread: when datas filesystem is full how to do

I have installed postgresql in /usr/local filesystem on RH Linux. But Running for two months,the datas are growing
toofast.There is no enough space at /usr/local filesystem. I want to make this filesystem bigger or move the database
toanother  
filesystem and don't stop the database.Someone can tell me how to do that?Thanks.

jovi

guly wrote:
>     I have installed postgresql in /usr/local filesystem on RH Linux. But Running for two months,the datas are
growingtoo fast.There is no enough space at /usr/local filesystem. I want to make this filesystem bigger or move the
databaseto another  
> filesystem and don't stop the database.Someone can tell me how to do that?Thanks.

I don't know about all the aspects regarding Postgresql, but you could
create another partition, either from current free space, or by adding
another drive and migrate /usr/local to it.

Create the new space (format existing unused space, add a new drive)
mount the new space (/tmpmnt or something)
Boot to single user, insure your postgresql database is not active.
Copy /usr/local to /tmpmnt
Modify /etc/fstab to mount /usr/local on /tmpmnt

Once you verify this all works, you will need to reboot to single user,
unmount your new /usr/local so you can remove the old stuff that was in
the old /usr/local partition/directory in order to recover that space.

Others may shoot this down because of issues with Postgresql I'm not
aware of, but I do this all the time to move stuff around or create new
space.

--
Until later, Geoffrey

Geoffrey writes:
> guly wrote:
> >     I have installed postgresql in /usr/local filesystem on RH Linux. But Running for two months,the datas are
growingtoo fast.There is no enough space at /usr/local filesystem. I want to make this filesystem bigger or move the
databaseto another  
> > filesystem and don't stop the database.Someone can tell me how to do that?Thanks.
>
> I don't know about all the aspects regarding Postgresql, but you could
> create another partition, either from current free space, or by adding
> another drive and migrate /usr/local to it.
>
I guess the problem is, that he doesn't want to stop the database.

I'd rather suggest to look into tablespaces.
Create a new tablespace on some other filesystem and move the contents
of the database by using ALTER TABLE/INDEX SET TABLESPACE...
Presumably part of the data will remain on /usr/local but if the goal
is to provide sufficiant space that should not harm.
Of course that's assuming we speak of PG 8 here.

But I'd make sure first, that it's really the data and not unvacuumed
space or no longer needed WALs filling the disk.

For details see postgresqls excellent docs (I can say that, I'm also
just learning postgresql from the docs).

Note that I didn't test that.

HTH
Morus

Morus Walter <morus.walter@tanto.de> writes:
> I guess the problem is, that he doesn't want to stop the database.

Yeah ... I'm afraid that's an impractical goal.

> I'd rather suggest to look into tablespaces.
> Create a new tablespace on some other filesystem and move the contents
> of the database by using ALTER TABLE/INDEX SET TABLESPACE...

Although the postmaster may stay up, this would still involve taking
exclusive locks on large tables for long enough to copy them to another
disk.  That would probably knock out his application just as effectively
as a full database shutdown.  And of course it's not even an option
unless he's running PG 8.0.

            regards, tom lane