Thread: Distributing index's/tables/logs/etc.

Distributing index's/tables/logs/etc.

From
"Arsalan Zaidi"
Date:
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)



Server ???

From
"The Viracocha"
Date:
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


Re: Distributing index's/tables/logs/etc.

From
Tom Lane
Date:
"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

Re: Distributing index's/tables/logs/etc.

From
Bruce Momjian
Date:
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

Re: Distributing index's/tables/logs/etc.

From
Lincoln Yeoh
Date:
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.


Re: Distributing index's/tables/logs/etc.

From
Bruce Momjian
Date:
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

Re: Distributing index's/tables/logs/etc.

From
Lincoln Yeoh
Date:
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
>
>


Re: Distributing index's/tables/logs/etc.

From
"Arsalan Zaidi"
Date:
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
>