Re: Distributing index's/tables/logs/etc. - Mailing list pgsql-general

From Tom Lane
Subject Re: Distributing index's/tables/logs/etc.
Date
Msg-id 17235.1012672162@sss.pgh.pa.us
Whole thread Raw
In response to Distributing index's/tables/logs/etc.  ("Arsalan Zaidi" <azaidi@directi.com>)
List pgsql-general
"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

pgsql-general by date:

Previous
From: Cees van de Griend
Date:
Subject: Preformance
Next
From: Tom Lane
Date:
Subject: Re: index does not improve performance