Thread: data files management (tablespaces)

data files management (tablespaces)

From
"Victor Yegorov"
Date:
Hello.

I wonder, is it possible in PostgreSQL to manage data files, stored on
disks? I mean, I'd like to tell DB where store this or that object.

Now I have a server running a DB and a small application that uses
it. At the time, when project started, there were enough space on HD
(4Gb). Now it seems, that soon I'm going to have problems.

At now, I'm daily backuping and then deleting all "expired" records. But
that is not the solution I want.

Is it possible to add one (or more) disks and tell Postgres to store, say,
indicies or big tables on them? Such things in ORACLE are called
tablespaces.

I've searched the docs, but found nothing apropriate, may be I've missed the
point.

I know, I can just upgrade hardware and move my stuff to the new server. But
still - is it possible to manage objects?

Thanks.


--

Victor Yegorov

Re: data files management (tablespaces)

From
Stephan Szabo
Date:
On Sun, 18 May 2003, Victor Yegorov wrote:

> Hello.
>
> I wonder, is it possible in PostgreSQL to manage data files, stored on
> disks? I mean, I'd like to tell DB where store this or that object.
>
> Now I have a server running a DB and a small application that uses
> it. At the time, when project started, there were enough space on HD
> (4Gb). Now it seems, that soon I'm going to have problems.
>
> At now, I'm daily backuping and then deleting all "expired" records. But
> that is not the solution I want.
>
> Is it possible to add one (or more) disks and tell Postgres to store, say,
> indicies or big tables on them? Such things in ORACLE are called
> tablespaces.
>
> I've searched the docs, but found nothing apropriate, may be I've missed the
> point.
>
> I know, I can just upgrade hardware and move my stuff to the new server. But
> still - is it possible to manage objects?

Generally not live, and not easily. :(  Right now about the best you can
do is move files around while the system is down and use symlinks.  This
doesn't really help on really big tables since the new extents (after each
gig iirc) will get created in the default place and you'd need to down the
server in order to move it safely I think.



Re: data files management (tablespaces)

From
Thierry Missimilly
Date:
One solution i used is :
1) Run the contrib tools oid2name and note which DB or table i want to move in
an other disk / FS.
2) pg_ctl stop
3)cd $PGDATA/base
4) mv <DBnumber> <my_new_filesystem>
5) ln -s <my_new_filesystem>/<DBnumber>  <DBnumber>
6) pg_ctl start



Victor Yegorov wrote:

> Hello.
>
> I wonder, is it possible in PostgreSQL to manage data files, stored on
> disks? I mean, I'd like to tell DB where store this or that object.
>
> Now I have a server running a DB and a small application that uses
> it. At the time, when project started, there were enough space on HD
> (4Gb). Now it seems, that soon I'm going to have problems.
>
> At now, I'm daily backuping and then deleting all "expired" records. But
> that is not the solution I want.
>
> Is it possible to add one (or more) disks and tell Postgres to store, say,
> indicies or big tables on them? Such things in ORACLE are called
> tablespaces.
>
> I've searched the docs, but found nothing apropriate, may be I've missed the
> point.
>
> I know, I can just upgrade hardware and move my stuff to the new server. But
> still - is it possible to manage objects?
>
> Thanks.
>
> --
>
> Victor Yegorov
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Attachment

Re: data files management (tablespaces)

From
Stephan Szabo
Date:
On Tue, 20 May 2003, Victor Yegorov wrote:

> * Stephan Szabo <sszabo@megazone23.bigpanda.com> [20.05.2003 02:23]:
> > Generally not live, and not easily. :(  Right now about the best you can
> > do is move files around while the system is down and use symlinks.  This
> > doesn't really help on really big tables since the new extents (after each
> > gig iirc) will get created in the default place and you'd need to down the
> > server in order to move it safely I think.
>
> Is it possible to find info about system files somewhere?

What sort of information?


Re: data files management (tablespaces)

From
Victor Yegorov
Date:
* Stephan Szabo <sszabo@megazone23.bigpanda.com> [20.05.2003 18:28]:
>
> What sort of information?

How data storage on disk is organized, i.e. where oids are stored.
I've already received answer from Thierry Missimilly, never mind.

Sorry for beeing noisy.

--

Victor Yegorov