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

"Arsalan Zaidi" <azaidi(at)directi(dot)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