Thread: data files management (tablespaces)
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
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.
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
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?
* 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