Thread: Distributing index's/tables/logs/etc.
Hi. I'd like to place my indexs on another partition. There's a hint that this can be done in one of the docs, but no info as to how one should go about doing this... I can guess which of the directories under 'base' holds my database, but how do I recognise and shift the indexs? They're all numbers! Has any one done this before? --Arsalan ------------------------------------------------------------------- People often hate those things which they do not know, or cannot understand. --Ali Ibn Abi Talib (AS)
I hva a problem with my server : i can't launch my PostgreSQL Server i work with Webmin and what is the syntax on command line ? _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
"Arsalan Zaidi" <azaidi@directi.com> writes:
> I'd like to place my indexs on another partition. There's a hint that this
> can be done in one of the docs, but no info as to how one should go about
> doing this...
shut down postmaster (essential step!), mv index file to wherever-you-
want-it, make a symlink from data directory to new location.
Note that you need to do this separately for each 1-gigabyte segment
of a large index or table, which can be a pain in the neck.
> I can guess which of the directories under 'base' holds my database, but how
> do I recognise and shift the indexs? They're all numbers!
"select relname,relfilenode from pg_class" will give the mapping. Also
see contrib/oid2name. The first segment of a large table is named
directly after the relfilenode; later ones are relfilenode.1,
relfilenode.2, etc. Also, if you're not too sure about the database
directory numbers, see "select datname,oid from pg_database".
What I'd actually suggest doing first is moving the WAL log to another
disk. For that, shut down postmaster, move entire pg_xlog directory to
another place, make a symlink for the directory. Much easier to
maintain since the only symlink is directory-level and you don't have to
worry about addition or removal of individual files.
regards, tom lane
Arsalan Zaidi wrote: > Hi. > > I'd like to place my indexs on another partition. There's a hint that this > can be done in one of the docs, but no info as to how one should go about > doing this... > > I can guess which of the directories under 'base' holds my database, but how > do I recognise and shift the indexs? They're all numbers! Use /contrib/oid2name to make file numbers to table/database names. Then shut down the database and symlink indexes to another device. That's all it takes. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
At 10:13 PM 03-02-2002 -0500, Bruce Momjian wrote: > >Use /contrib/oid2name to make file numbers to table/database names. >Then shut down the database and symlink indexes to another device. >That's all it takes. Just curious: how big can indexes get? Do they get split at X GB boundaries? Regards, Link.
Lincoln Yeoh wrote: > At 10:13 PM 03-02-2002 -0500, Bruce Momjian wrote: > > > >Use /contrib/oid2name to make file numbers to table/database names. > >Then shut down the database and symlink indexes to another device. > >That's all it takes. > > Just curious: how big can indexes get? Do they get split at X GB boundaries? Sorry, yes as Tom mentioned, they split at 1 gig boundaries. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Oops, sorry. I missed Tom's post. Link. At 12:03 AM 04-02-2002 -0500, Bruce Momjian wrote: >Lincoln Yeoh wrote: >> At 10:13 PM 03-02-2002 -0500, Bruce Momjian wrote: >> > >> >Use /contrib/oid2name to make file numbers to table/database names. >> >Then shut down the database and symlink indexes to another device. >> >That's all it takes. >> >> Just curious: how big can indexes get? Do they get split at X GB boundaries? > >Sorry, yes as Tom mentioned, they split at 1 gig boundaries. > >-- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > >
Thanks for the info everyone.... > Arsalan Zaidi wrote: > > Hi. > > > > I'd like to place my indexs on another partition. There's a hint that this > > can be done in one of the docs, but no info as to how one should go about > > doing this... > > > > I can guess which of the directories under 'base' holds my database, but how > > do I recognise and shift the indexs? They're all numbers! > > Use /contrib/oid2name to make file numbers to table/database names. > Then shut down the database and symlink indexes to another device. > That's all it takes. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 >