Re: indexes separate from data - Mailing list pgsql-admin

From Jean-Luc Lachance
Subject Re: indexes separate from data
Date
Msg-id 3CAB5909.F73F001A@nsd.ca
Whole thread Raw
In response to indexes separate from data  (Judy Jecelin <jjecelin@yahoo.com>)
List pgsql-admin
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

pgsql-admin by date:

Previous
From: Jean-Marc Pigeon
Date:
Subject: Re: Request for comment...
Next
From: Tom Lane
Date:
Subject: Re: Backup pg_shadow