Lists: | pgsql-admin |
---|
From: | Judy Jecelin <jjecelin(at)yahoo(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | indexes separate from data |
Date: | 2002-04-02 22:27:09 |
Message-ID: | 20020402222709.35512.qmail@web12802.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
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/
From: | "Marin Dimitrov" <marin(dot)dimitrov(at)sirma(dot)bg> |
---|---|
To: | "Judy Jecelin" <jjecelin(at)yahoo(dot)com>, <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: indexes separate from data |
Date: | 2002-04-03 07:50:34 |
Message-ID: | 013201c1dae4$3cd5fca0$9b0e32d4@sirma.bg |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
----- 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. "
From: | Jean-Luc Lachance <jllachan(at)nsd(dot)ca> |
---|---|
To: | Marin Dimitrov <marin(dot)dimitrov(at)sirma(dot)bg> |
Cc: | Judy Jecelin <jjecelin(at)yahoo(dot)com>, pgsql-admin(at)postgresql(dot)org |
Subject: | Re: indexes separate from data |
Date: | 2002-04-03 19:33:29 |
Message-ID: | 3CAB5909.F73F001A@nsd.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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
From: | "Marin Dimitrov" <marin(dot)dimitrov(at)sirma(dot)bg> |
---|---|
To: | "Jean-Luc Lachance" <jllachan(at)nsd(dot)ca> |
Cc: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: indexes separate from data |
Date: | 2002-04-04 07:25:48 |
Message-ID: | 00c501c1dba9$f1adf4f0$9b0e32d4@sirma.bg |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
----- 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. "