Thread: indexes separate from data

Hi,

A relatively novice user of PostgreSQL but not
of other databases... I'm trying to understand the
use of initlocation....I understand having separate
data storage areas (perhaps for each database
in your postmaster), but is there some way to have
a separate location for indexes?  How would this
be designated through PostgreSQL?

__________________________________________________
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/

----- Original Message -----
From: "Judy Jecelin"

>
> Hi,
>
> A relatively novice user of PostgreSQL but not
> of other databases... I'm trying to understand the
> use of initlocation....I understand having separate
> data storage areas (perhaps for each database
> in your postmaster), but is there some way to have
> a separate location for indexes?  How would this
> be designated through PostgreSQL?
>

since every index is stored as separate file, you should identify the files
corresponding to the indexes and move them to the proper device (while the
database is shut down) then create symlinks in the database directory
pointing to the moved files

to identify the names of the OS files corresponding to indexes connect to
the DB and execute
something like:

select relname, relfilenode
from  pg_class
where relkind = 'i';

make sure u're moving the files from the proper database directory (execute
"select datname, oid from pg_database"  to see the OS directory name
corresponding to each database)

tablespaces are in the TODO list, so one day the whole process should be
easier

hth,

    Marin

----
"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "

Interesting...  What happens when you drop and recreate the index???

Marin Dimitrov wrote:
>
> ----- Original Message -----
> From: "Judy Jecelin"
>
> >
> > Hi,
> >
> > A relatively novice user of PostgreSQL but not
> > of other databases... I'm trying to understand the
> > use of initlocation....I understand having separate
> > data storage areas (perhaps for each database
> > in your postmaster), but is there some way to have
> > a separate location for indexes?  How would this
> > be designated through PostgreSQL?
> >
>
> since every index is stored as separate file, you should identify the files
> corresponding to the indexes and move them to the proper device (while the
> database is shut down) then create symlinks in the database directory
> pointing to the moved files
>
> to identify the names of the OS files corresponding to indexes connect to
> the DB and execute
> something like:
>
> select relname, relfilenode
> from  pg_class
> where relkind = 'i';
>
> make sure u're moving the files from the proper database directory (execute
> "select datname, oid from pg_database"  to see the OS directory name
> corresponding to each database)
>
> tablespaces are in the TODO list, so one day the whole process should be
> easier
>
> hth,
>
>     Marin

----- Original Message -----
From: "Jean-Luc Lachance"

> Interesting...  What happens when you drop and recreate the index???
>

repeat the whole procedure again


hth,

    Marin


----
"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "